Batch Upsert & Index Provisioning Patterns

Overview

A vector database’s batch upsert and index provisioning pipeline is the ingestion backbone for vector search. When migrating vectors from external systems or loading freshly computed embeddings, the pipeline accepts batches of documents—each carrying metadata fields plus an _embedding vector—through a single /table/{table}/batch HTTP endpoint. This endpoint handles both inserts and updates atomically, using document IDs as the deduplication key.

Before vectors can be searched, a semantic index must exist on the table. Index provisioning creates the index with a specified template (which defines the distance metric and indexing algorithm) and an embedding model name (which determines the expected dimensionality). The provisioning process is asynchronous: the API returns immediately, and the index builds in the background. Migration scripts must poll for readiness before running smoke tests.

This two-phase pattern—bulk data loading followed by index creation and verification—is designed for large-scale migration scenarios where millions of vectors need to be ingested efficiently before the search index is built in a single pass over the data.

Core Concepts

Batch Upsert Semantics

The /table/{table}/batch endpoint accepts an array of documents. Each document must include a unique id field, arbitrary metadata fields, and an _embedding field containing the vector as a float array. If a document with the same ID already exists, it is replaced entirely (upsert). This enables idempotent retry: a failed batch can be resubmitted without creating duplicates. Batch sizes of 100–500 documents balance throughput against request payload limits.

Semantic Index Templates

An index template defines the search algorithm (HNSW, flat scan, IVF) and distance metric (cosine, dot-product, Euclidean). Templates are pre-configured for common use cases: hnsw-cosine for general-purpose semantic search, flat-dot for small datasets where exact search is practical, and ivf-cosine for billion-scale indexes that trade recall for speed. The template is specified at index creation time and cannot be changed without rebuilding.

Asynchronous Index Build

Index creation is a background operation because building an HNSW graph or IVF codebook over millions of vectors is compute-intensive. The API returns an index status that transitions through creating → building → ready (or failed). Scripts must poll the index status endpoint and wait for ready before issuing queries. Querying a non-ready index returns empty results or an error, depending on the build phase.

Smoke Test Queries

After an index reaches ready, migration scripts run filtered semantic search queries against known vectors to verify correctness. A typical smoke test upserts a document with a known embedding, searches for it, and checks that it appears in the top-1 results with the expected metadata. This catches dimension mismatches, metric configuration errors, and data corruption early, before the migration is declared complete.

Embedding Payload Structure

Each document in the batch payload carries the embedding as a special _embedding field (prefixed with underscore to distinguish it from user metadata). The embedding is a JSON array of floats matching the index’s configured dimension. Metadata fields (title, source, timestamps, tags) are stored alongside the vector and returned with search results, avoiding a separate metadata lookup.

How It Works

The pipeline follows a strict sequence: create the table, load data in batches, create the semantic index, wait for it to build, then run verification queries. Each step is idempotent so the pipeline can be restarted from any point after a failure.

Batch Upsert with Retry Logic

import requests
import time
from typing import List, Dict, Any

API_BASE = "https://api.mydb.example/v1"

def batch_upsert(
    table: str,
    documents: List[Dict[str, Any]],
    api_key: str,
    batch_size: int = 200,
    max_retries: int = 3,
) -> int:
    """
    Upsert documents to a vector database table in batches.
    Each document must have 'id', '_embedding', and metadata fields.

    Returns total number of documents upserted.
    """
    headers = {
        "Authorization": f"Bearer {api_key}",
        "Content-Type": "application/json",
    }
    url = f"{API_BASE}/table/{table}/batch"
    total = 0

    for i in range(0, len(documents), batch_size):
        batch = documents[i : i + batch_size]

        for attempt in range(max_retries):
            resp = requests.post(url, json={"documents": batch},
                                 headers=headers, timeout=30)

            if resp.status_code == 200:
                total += len(batch)
                print(f"  Upserted batch {i//batch_size + 1}: "
                      f"{len(batch)} docs (total: {total})")
                break
            elif resp.status_code == 429:
                wait = 2 ** attempt
                print(f"  Rate limited, waiting {wait}s...")
                time.sleep(wait)
            else:
                resp.raise_for_status()

    return total

Index Provisioning and Readiness Polling

def create_semantic_index(
    table: str,
    index_name: str,
    template: str,
    model_name: str,
    api_key: str,
) -> str:
    """
    Create a semantic index on a table and return the index ID.

    Args:
        template: Index template, e.g. 'hnsw-cosine'
        model_name: Embedding model, e.g. 'text-embedding-ada-002'
    """
    headers = {
        "Authorization": f"Bearer {api_key}",
        "Content-Type": "application/json",
    }
    resp = requests.post(
        f"{API_BASE}/table/{table}/indexes",
        json={
            "name": index_name,
            "template": template,
            "model": model_name,
        },
        headers=headers,
    )
    resp.raise_for_status()
    index_id = resp.json()["index_id"]
    print(f"Index '{index_name}' created with ID: {index_id}")
    return index_id


def wait_for_index_ready(
    table: str,
    index_id: str,
    api_key: str,
    timeout: int = 600,
    poll_interval: int = 5,
) -> bool:
    """
    Poll until an index reaches 'ready' status or timeout expires.
    Returns True if ready, False if timeout or failed.
    """
    headers = {"Authorization": f"Bearer {api_key}"}
    url = f"{API_BASE}/table/{table}/indexes/{index_id}"
    deadline = time.time() + timeout

    while time.time() < deadline:
        resp = requests.get(url, headers=headers)
        resp.raise_for_status()
        status = resp.json()["status"]

        if status == "ready":
            print(f"Index {index_id} is ready.")
            return True
        elif status == "failed":
            print(f"Index {index_id} build failed: "
                  f"{resp.json().get('error', 'unknown')}")
            return False

        print(f"  Index status: {status}, waiting {poll_interval}s...")
        time.sleep(poll_interval)

    print(f"Index {index_id} did not become ready within {timeout}s.")
    return False

Smoke Test with Filtered Semantic Search

import numpy as np

def run_smoke_test(
    table: str,
    index_name: str,
    api_key: str,
    test_embedding: List[float],
    expected_id: str,
) -> bool:
    """
    Search for a known vector and verify it appears in results.
    Returns True if the expected document is in the top results.
    """
    headers = {
        "Authorization": f"Bearer {api_key}",
        "Content-Type": "application/json",
    }
    resp = requests.post(
        f"{API_BASE}/table/{table}/semantic_search",
        json={
            "index": index_name,
            "vector": test_embedding,
            "top_k": 5,
            "include_metadata": True,
        },
        headers=headers,
    )
    resp.raise_for_status()
    results = resp.json()["results"]

    found_ids = [r["id"] for r in results]
    if expected_id in found_ids:
        rank = found_ids.index(expected_id) + 1
        print(f"  Smoke test PASSED: '{expected_id}' found at rank {rank}")
        return True
    else:
        print(f"  Smoke test FAILED: '{expected_id}' not in top results")
        print(f"  Got: {found_ids}")
        return False

Practical Applications

The batch upsert pipeline is the primary data loading path for a vector database system. For example, migration scripts can export vectors from external sources like Pinecone (using zero-vector queries, covered in a companion topic) and then feed them through this pipeline to populate the target tables. The idempotent upsert semantics allow scripts to be restarted after network failures without creating duplicate records.

Index provisioning is also used in CI/CD pipelines for search-enabled applications. When deploying a new embedding model or changing the distance metric, the deployment script creates a new index with the updated configuration, waits for it to build, runs smoke tests, and then atomically swaps the search endpoint to point to the new index. The old index remains available as a rollback target until the new one is verified in production.

For development and testing, the same pipeline supports small-scale table creation with flat-scan indexes that build instantly. Developers can load a few hundred test vectors, create a flat index, and run integration tests without waiting for HNSW graph construction.

Common Pitfalls

Querying before index is ready: The most common migration bug is issuing search queries while the index is still building. Always poll for ready status before running any search or smoke test. A building index may return partial results that pass weak assertions but fail under load.
Batch size too large: Sending thousands of documents with high-dimensional embeddings in a single request can exceed HTTP payload limits or cause timeouts. Keep batch sizes between 100–500 documents. For 1536-dim embeddings, a batch of 500 documents is roughly 12MB of JSON.
Template/dimension mismatch: Creating an index with a template that expects a different dimensionality than the upserted embeddings produces a failed index build. Always verify the model name matches the dimension of the _embedding vectors before creating the index.
Missing _embedding field: Documents without the _embedding field are stored as metadata-only records. They are not indexed for semantic search and silently disappear from search results. Validate that every document has the embedding field before submitting the batch.
Not handling partial batch failures: A batch request may succeed for some documents and fail for others (e.g., if one document has a malformed embedding). Check the response body for per-document errors rather than relying solely on the HTTP status code.
Index name collisions: Attempting to create an index with a name that already exists returns an error. Use unique names (e.g., including a timestamp or version) or check for existing indexes before provisioning.