Storing and Querying

You have a database with a vector column. You have code that generates embeddings. Now you need to put them together: insert documents with their embeddings and query them by semantic similarity. This lesson covers the INSERT-query loop that powers every RAG system, from a single Python script to a production pipeline handling millions of documents.

What you'll learn

• INSERT embeddings alongside content using parameterized queries; psycopg2 handles the Python-to-SQL vector conversion after register_vector(conn)
• Cosine similarity search uses the <=> operator: ORDER BY embedding <=> query_vector LIMIT 10 returns the 10 most similar documents
• PostgreSQL can run the embedding API call and the INSERT in the same transaction; if either fails, nothing is committed
• Filtering with WHERE clauses (metadata, date ranges, categories) combined with vector search gives you hybrid retrieval without a separate search engine

The problem: vectors in, results out

Generating embeddings and setting up pgvector are preparation. The actual RAG loop is:

1. Embed the user's query
2. Find the most similar documents in the database
3. Pass those documents to an LLM as context

This lesson covers steps 1 and 2 -- everything from inserting a document corpus to retrieving relevant chunks for a given query. By the end, you'll have a working search endpoint you can use in any RAG pipeline.

Options & when to use each

Option	What it's good for	What it costs you	When to pick it
Naive insert-then-query	Simple, works for thousands of documents	No batching means slow insertion at scale	Prototyping, small document sets
Batch insert with COPY	10-100x faster for large corpora	More complex error handling; can't easily interleave with application logic	Loading 10,000+ documents at once
Streaming insert with async	Handles continuous ingestion from multiple sources	Requires async Python (asyncio, asyncpg); harder to debug	Production pipelines with ongoing document updates

Build it: insert documents and query them

Full pipeline: from documents to search results

Here is a concrete example demonstrating the implementation details.

import psycopg2
from pgvector.psycopg2 import register_vector
from openai import OpenAI

# Setup
openai_client = OpenAI()
conn = psycopg2.connect(
    host="localhost", port=5432,
    dbname="your_database", user="postgres", password="your_password"
)
conn.autocommit = True
register_vector(conn)

# --- STEP 1: Embed and insert documents ---

def embed(texts: list[str]) -> list[list[float]]:
    """Generate embeddings for a list of texts."""
    response = openai_client.embeddings.create(
        model="text-embedding-3-small",
        input=texts,
        dimensions=768
    )
    sorted_data = sorted(response.data, key=lambda d: d.index)
    return [d.embedding for d in sorted_data]

def insert_documents(
    cur,
    documents: list[dict],   # [{"content": "...", "metadata": {...}}, ...]
    batch_size: int = 50
) -> int:
    """
    Embed and insert documents in batches.
    Returns the number of documents inserted.
    """
    total = 0
    for i in range(0, len(documents), batch_size):
        batch = documents[i : i + batch_size]
        texts = [d["content"] for d in batch]
        embeddings = embed(texts)

        for doc, emb in zip(batch, embeddings):
            cur.execute(
                """INSERT INTO documents (content, metadata, embedding)
                   VALUES (%s, %s, %s)""",
                (doc["content"], doc.get("metadata", {}), emb)
            )
        total += len(batch)
        print(f"Inserted {total}/{len(documents)} documents")
    return total

# Example corpus
corpus = [
    {"content": "PostgreSQL is a widely-used open-source relational database.", "metadata": {"topic": "databases"}},
    {"content": "Python is a programming language known for readability.", "metadata": {"topic": "programming"}},
    {"content": "pgvector adds vector similarity search to PostgreSQL.", "metadata": {"topic": "databases"}},
    {"content": "Machine learning models often use embeddings for text similarity.", "metadata": {"topic": "ml"}},
    {"content": "Django is a Python web framework with an ORM.", "metadata": {"topic": "programming"}},
]

with conn.cursor() as cur:
    insert_documents(cur, corpus)

Step 2: Search by semantic similarity

Here is a concrete example demonstrating the implementation details.

def search(
    cur,
    query: str,
    limit: int = 5,
    metadata_filter: dict | None = None
) -> list[dict]:
    """
    Semantic search: embed the query, find the most similar documents.
    Returns a list of {content, metadata, distance} dicts sorted by similarity.

    Cosine distance via <=> ranges from 0 (identical) to 2 (opposite).
    """
    # Embed the query
    query_embedding = embed([query])[0]

    # Build the query with optional metadata filter
    if metadata_filter:
        filter_clauses = " AND ".join(
            f"metadata->>'{k}' = %({k})s" for k in metadata_filter
        )
        sql = f"""
            SELECT content, metadata, embedding <=> %(query_vec)s AS distance
            FROM documents
            WHERE {filter_clauses}
            ORDER BY embedding <=> %(query_vec)s
            LIMIT %(limit)s
        """
        params = {"query_vec": query_embedding, "limit": limit, **metadata_filter}
    else:
        sql = """
            SELECT content, metadata, embedding <=> %(query_vec)s AS distance
            FROM documents
            ORDER BY embedding <=> %(query_vec)s
            LIMIT %(limit)s
        """
        params = {"query_vec": query_embedding, "limit": limit}

    cur.execute(sql, params)
    return [
        {"content": row[0], "metadata": row[1], "distance": float(row[2])}
        for row in cur.fetchall()
    ]

# Run a search
with conn.cursor() as cur:
    results = search(cur, "open source database for storing vectors")
    for i, r in enumerate(results):
        print(f"{i+1}. [{r['distance']:.4f}] {r['content'][:80]}")

Expected output (approximate):

1. [0.2847] pgvector adds vector similarity search to PostgreSQL.
2. [0.3126] PostgreSQL is a widely-used open-source relational database.
3. [0.5891] Machine learning models often use embeddings for text similarity.
4. [0.7123] Python is a programming language known for readability.
5. [0.7340] Django is a Python web framework with an ORM.

The document about pgvector ranks first because "storing vectors" is closest in meaning. The PostgreSQL document ranks second. The ML document comes next (embeddings = vector related). The Python documents rank last because they're semantically distant from "open source database for storing vectors."

Step 3: Filtered search with metadata

Here is a concrete example demonstrating the implementation details.

with conn.cursor() as cur:
    # Search only within the "databases" topic
    results = search(
        cur,
        "open source database tools",
        metadata_filter={"topic": "databases"}
    )
    for r in results:
        print(f"[{r['distance']:.4f}] {r['content']}")

This returns only documents where metadata->>'topic' equals 'databases'. The vector search runs within that filtered subset.

Distance operators available in pgvector

pgvector provides three distance operators. Cosine is the default for semantic search, but the others have specific use cases:

Operator	Distance type	Value range	When to use
<=>	Cosine distance	0 to 2	Semantic search (default). 0 = identical direction, 1 = orthogonal, 2 = opposite
<->	Euclidean (L2) distance	0 to infinity	When vector magnitude matters (e.g., you normalized for document length)
<#>	Negative inner product	-infinity to +infinity	When you want the dot product directly (some models are optimized for this)

For RAG, stick with <=>. It's what pgvector's vector_cosine_ops index is optimized for, and it's the right metric for comparing semantic similarity of text embeddings.

Performance note: the index makes it fast

Without an HNSW index, the query above runs as a sequential scan -- PostgreSQL reads every row in the documents table and computes the distance to the query vector for each one. That's fine for 5 documents. It's not fine for 500,000.

Verify your index is being used:

EXPLAIN ANALYZE
SELECT content, embedding <=> '[0.1, 0.2, ...]'::vector AS distance
FROM documents
ORDER BY embedding <=> '[0.1, 0.2, ...]'::vector
LIMIT 10;

You should see Index Scan using documents_embedding_idx in the output, not Seq Scan.

What goes wrong

Mistake	How you notice it	The fix
Forgetting to register_vector before INSERT	psycopg2.errors.InvalidTextRepresentation: invalid input syntax for type vector	Call register_vector(conn) after connecting, before any vector operations
Embedding the query with a different model than the documents	Search results are random; relevant documents score worse than irrelevant ones	Use the same model and same dimensions parameter for both document and query embeddings. A mismatch puts them in different vector spaces
Using ORDER BY distance instead of ORDER BY embedding <=> query_vector	The distance alias isn't recognized in ORDER BY (PostgreSQL evaluates ORDER BY before SELECT aliases)	Repeat the expression: ORDER BY embedding <=> query_vector. Or use a subquery
No LIMIT clause	Query returns every document in the table, sorted	Always add LIMIT. For RAG, 5-20 results is typical
Metadata filter kills performance	Query with metadata filter is slow even with a vector index	Create a composite index or separate B-tree index on the metadata column. PostgreSQL can't use the vector index for the metadata part of the query

Confirm it worked

Run a targeted search and verify the ranking makes semantic sense:

with conn.cursor() as cur:
    results = search(cur, "Python web development")

    # The Django document should rank first or second
    topics = [r["metadata"].get("topic", "") for r in results[:2]]
    assert "programming" in topics, \
        f"Expected 'programming' in top results, got {topics}"

    # Distances should be monotonically increasing (sorted correctly)
    distances = [r["distance"] for r in results]
    assert distances == sorted(distances), \
        f"Results not sorted by distance: {distances}"

    print(f"Top result: {results[0]['content']}")
    print(f"Distance: {results[0]['distance']:.4f}")
    print("Search is working correctly.")

Next: Similarity Search