Inserting Vectors: The Ingestion Pipeline

Inserting data into a vector database—commonly called Upserting—is a computationally expensive operation. Unlike a traditional database that simply writes a row to a list, a vector database must:

Validate the vector dimensions.
Store the raw vector and metadata.
Update the Index (Graph/Centroids).
Persist to the Write-Ahead Log.

In this lesson, we will explore the "U" in CRUD (Upsert). We will see why batching is mandatory, how to avoid hitting API rate limits, and the best practices for generating unique IDs that prevent data duplication.

1. Why "Upsert" and not "Insert"?

In Pinecone and Chroma, the standard command is upsert.

If the ID does not exist: The database creates a new entry.
If the ID already exists: The database overwrites the existing entry with the new vector/metadata.

Why it matters: This gives you "Idempotency." You can run the same ingestion script 10 times, and your database will still only have the correct number of items. This is essential for handling network failures during ingestion.

2. Ingestion Batching: The Key to Throughput

API overhead is the biggest bottleneck in vector search. If you have 100,000 documents and you call index.upsert() 100,000 times, your ingestion will take hours.

The Production Strategy: Use Batches.

Small Batches (1 to 10): High overhead, slow.
Medium Batches (100 to 200): The "Sweet Spot" for network reliability and throughput.
Large Batches (1000+): High risk of hitting API timeout or memory errors in your Python script.

graph LR
    A[Raw Data] --> B[Chunking Engine]
    B --> C[Batch: 1-100]
    B --> D[Batch: 101-200]
    C --> E[Upsert Call]
    D --> E
    E --> F[Vector Index]

3. Generating Robust IDs

Your ID is the only link between your vector database and your primary source of truth (SQL/S3).

Bad ID: Random integers (1, 2, 3). Hard to track if you have multiple data sources.
Good ID: Deterministic UUIDs or Content-based Hashes.

The Content Hash Strategy: If you use a SHA-256 hash of the document content as the ID, you gain Automatic De-duplication. If you try to insert the exact same paragraph twice, the ID will be identical, and Pinecone will simply "Update" the existing record instead of creating a duplicate.

4. Handling Rate Limits (429 Errors)

Managed providers like Pinecone and AWS OpenSearch have Rate Limits. If you send data too fast, they will return a 429 Too Many Requests error.

Best Practice: Implement Exponential Backoff. In your Python script, wrap your upsert in a try/except block. If a 429 happens, wait 1 second, then try again. If it fails again, wait 2 seconds, then 4, etc.

5. Python Example: The Production Ingestion Pattern

Here is a template you can use for any production ingestion script.

import time
import uuid

def upsert_with_retry(index, vectors, retries=5):
    attempt = 0
    while attempt < retries:
        try:
            index.upsert(vectors=vectors)
            return True
        except Exception as e:
            if "429" in str(e): # Rate limit hit
                wait_time = 2 ** attempt
                print(f"Rate limit hit. Waiting {wait_time}s...")
                time.sleep(wait_time)
                attempt += 1
            else:
                raise e
    return False

# Ingestion Loop
data_to_store = [...] # List of 10k items
BATCH_SIZE = 100

for i in range(0, len(data_to_store), BATCH_SIZE):
    batch = data_to_store[i:i + BATCH_SIZE]
    
    # Prepare Pinecone-style vector objects
    vectors = []
    for item in batch:
        vectors.append({
            "id": str(uuid.uuid4()), # Or hash-based ID
            "values": item['embedding'],
            "metadata": item['metadata']
        })
    
    upsert_with_retry(index, vectors)

6. Cold Start and Ingestion Latency

Note that in many vector databases, data is not searchabel immediately after an upsert.

Chroma: Usually searchable instantly.
Pinecone: Usually 1-2 seconds (Eventual Consistency).
OpenSearch: Depends on the refresh_interval (default 1s).

Lesson: If your test script upserts a vector and immediately queries for it, it might fail. Always add a time.sleep(1) in your test scripts for reliability.

Summary and Key Takeaways

Insertion is the foundation of your search quality.

Upsert is your friend: Embrace idempotency to prevent duplicates.
Batching is mandatory: Aim for 100 vectors per call.
Use Deterministic IDs: Link your vectors to your source truth.
Resilience over Speed: Implement retries for rate-limited cloud APIs.

In the next lesson, we will look at Updating and Deleting Vectors, learning how to keep your index clean and up-to-date as your data evolves.

Exercise: Ingestion Optimization

You have 1,000 documents, and each document produces 10 chunks (10,000 vectors total).
Each vector is 1536D.
Your network speed allows for 5 UPSERTS per second.

How long will the ingestion take if you send them one by one?
How long will it take if you use batches of 100? (Assume batch processing time overhead is negligible).
Why is it physically impossible to do this "all at once" on a standard internet connection?

Inserting Vectors: The Art of Bulk Upserts and Data Integrity