Postgres Vector Search: Building a Production RAG Pipeline with pgvector and FastAPI

What vector search in Postgres actually means

Semantic search converts text into a vector — a list of floating-point numbers, usually between a few hundred and a few thousand dimensions — that represents meaning in high-dimensional space. Text with similar meaning ends up with vectors close together, measured by cosine distance or Euclidean distance. "Cancel my plan" and "how do I end my subscription" produce vectors near each other despite sharing almost no keywords.

pgvector is a Postgres extension that adds a native vector column type and distance operators to do this comparison efficiently inside the database. Instead of exporting data to a separate vector index, you store the embedding as a column on the same row as your source text and query it with SQL:

SELECT id, content
FROM documents
ORDER BY embedding <-> '[0.012, -0.045, ...]'
LIMIT 5;

The <-> operator is Euclidean distance; pgvector also provides <#> (negative inner product) and <=> (cosine distance), depending on how your embedding model was trained. This is the entire mechanism — nearest-neighbor search over a numeric representation of meaning, executed as an ordinary SQL query.

What RAG is, briefly

Retrieval-Augmented Generation retrieves relevant chunks of your own data and stuffs them into an LLM's prompt before asking a question, so the model answers using your actual content instead of memorized training data. The "retrieval" half is a search problem — find the k most relevant chunks for a query. The "generation" half is an LLM API call with those chunks pasted into context. Vector search makes the retrieval half work when keyword matching won't. A RAG pipeline is only as good as what it retrieves — no prompt engineering fixes bad retrieval.

Why pgvector over a dedicated vector database

Dedicated vector databases claim scale advantages: purpose-built ANN algorithms, distributed sharding, and embeddings-first tooling. That's real at certain scales. But most teams never hit those ceilings, and products rarely break there first.

The case for pgvector:

• One system to operate. Embeddings live in the same database as the rows they describe. No second connection string, no second backup job, no second thing that fails at 3am.
• Transactional consistency. Inserting a document and its embedding in the same transaction means you never have an embedding for a rolled-back document or a document with no embedding because a sync job failed halfway.
• You already know the tooling. Migrations, indexing strategy, query planning, monitoring — all the Postgres you already run. Not a new system with its own operational quirks to learn under fire.
• Joins, for free. Filtering vector search by tenant ID, permission level, publish date, or any relational column is a WHERE clause, not a metadata filter bolted onto a vector API with its own syntax.

The honest tradeoff: at very large scale, or if vector search is the primary workload of a dedicated system, a purpose-built vector database's specialized indexing and horizontal scaling may outperform a single Postgres instance. Most RAG use cases — internal docs, support knowledge bases, product search, chat-with-your-data — never approach that ceiling. Start with the Postgres you have. Split it out later if you hit a real, measured limit rather than a hypothetical one.

Step 1: Enable pgvector

On a self-managed instance, one command run as a superuser (or a role with CREATE EXTENSION privileges):

CREATE EXTENSION IF NOT EXISTS vector;

Most managed Postgres providers include pgvector in their allow-list. Check whether extensions need to be enabled through a dashboard versus raw SQL — some providers gate which extensions you can enable directly. Either way, the extension behaves identically once active.

Confirm:

SELECT * FROM pg_extension WHERE extname = 'vector';

Step 2: Design the schema

Store the embedding as a column on (or referencing) the row holding source text. For RAG, store chunks of documents rather than whole documents, since embedding models have limited context windows and retrieval works better on smaller, focused passages.

CREATE TABLE documents (
    id BIGSERIAL PRIMARY KEY,
    source_id TEXT NOT NULL,          -- original file or URL
    title TEXT,
    content TEXT NOT NULL,            -- the chunk text
    metadata JSONB DEFAULT '{}',      -- tenant_id, tags, permissions
    embedding VECTOR(1536),           -- dimension matches your model
    content_tsv TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', content)) STORED,
    created_at TIMESTAMPTZ DEFAULT now()
);

Key design choices:

• metadata as JSONB holds tenant isolation, access control, and source attribution without schema migrations as filter dimensions grow.
• content_tsv as a generated column precomputes the full-text search vector so you're not re-tokenizing content on every query — this enables cheap hybrid search.
• Chunk-level rows, not document-level rows. Store a source_id to trace chunks back to parent documents, but embed and index at chunk granularity.

Step 3: Pick an embedding model and dimension

The dimension in VECTOR(1536) must exactly match the embedding model producing your vectors. This is the single most common source of bugs: switch models later without migrating the column, and inserts silently fail or produce garbage similarity scores.

Decision tree:

• Pick one model per table and commit to it. Mixing embeddings from different models is meaningless — vectors don't share a coordinate space.
• Higher dimension isn't automatically better. Larger vectors capture more nuance but cost more storage and slow every distance calculation. For most product search and document-QA cases, a mid-sized general-purpose embedding model is the right default.
• Decide upfront whether you'll change models. If you might, add a model_version column alongside the embedding. Run two models side-by-side during migration and re-embed in the background instead of taking search offline.
• Match your distance operator to the model's training. Some models optimize for cosine similarity, others for dot product. Using the wrong operator won't error — it'll quietly return worse results.

Embed both stored content and query text through the same code path. A mismatch between how you embed content and how you embed queries is the second most common "why are my results all wrong" bug.

Step 4: Choose an index — HNSW vs IVFFlat

Without an index, a similarity query does a full sequential scan over every row. Fine at a few thousand rows, unacceptable at a few million. pgvector offers two index types:

For RAG pipelines with ongoing inserts, HNSW can work, but the trade-off differs from the conventional wisdom: HNSW has faster queries but slower builds and higher RAM usage. IVFFlat is fine for most workloads, including continuous ingestion. Choose based on your corpus size and tolerance for rebuild latency rather than insert friendliness alone. For a large, static corpus, HNSW may justify its build cost; for homelab or moderate-scale work, IVFFlat remains practical even with ongoing updates.

CREATE INDEX ON documents
USING hnsw (embedding vector_cosine_ops);

Match the index's distance operator class (vector_cosine_ops, vector_l2_ops, vector_ip_ops) to the operator you use in queries — an index built for cosine distance won't accelerate a query using Euclidean distance.

Also add a GIN index on the full-text column for hybrid search:

CREATE INDEX ON documents USING GIN (content_tsv);

Step 5: The ingestion pipeline (Python)

The ingestion job chunks source text, embeds each chunk, and writes chunk + embedding + metadata to Postgres in one transaction.

import psycopg
from openai import OpenAI

client = OpenAI()

def chunk_text(text: str, max_chars: int = 1000, overlap: int = 150) -> list[str]:
    chunks = []
    start = 0
    while start < len(text):
        end = start + max_chars
        chunks.append(text[start:end])
        start = end - overlap
    return chunks

def embed_batch(texts: list[str]) -> list[list[float]]:
    response = client.embeddings.create(
        model="text-embedding-3-small",
        input=texts,
    )
    return [item.embedding for item in response.data]

def ingest_document(conn, source_id: str, title: str, full_text: str, metadata: dict):
    chunks = chunk_text(full_text)
    embeddings = embed_batch(chunks)

    with conn.cursor() as cur:
        for chunk, embedding in zip(chunks, embeddings):
            cur.execute(
                """
                INSERT INTO documents (source_id, title, content, metadata, embedding)
                VALUES (%s, %s, %s, %s, %s)
                """,
                (source_id, title, chunk, psycopg.types.json.Json(metadata), embedding),
            )
    conn.commit()

Details that separate this from a fragile demo:

• Overlap between chunks. Hard boundaries lose context that spans edges; modest overlap preserves continuity for retrieval.
• Batch embedding calls. Sending texts in batches instead of one per chunk cuts request overhead.
• Commit per document, not per corpus. Failed ingestion halfway through doesn't roll back what succeeded.
• Idempotency. Detect "this source has already been ingested" — store a hash of source content in metadata — so re-runs don't duplicate chunks.

Step 6: The retrieval query

Embed the incoming question the same way you embedded content, then find nearest chunks:

SELECT id, content, title,
       embedding <=> %(query_embedding)s AS distance
FROM documents
ORDER BY embedding <=> %(query_embedding)s
LIMIT 8;

This works for many cases. But pure vector search has a known weakness: it's good at semantic similarity and bad at exact matches — product SKUs, error codes, proper nouns, anything where the literal string matters more than meaning. Full-text search excels there.

Hybrid search: vector + full-text via tsvector

Hybrid search combines both signals by running both queries and blending scores with reciprocal rank fusion, avoiding the problem of averaging differently-scaled distance metrics:

WITH vector_results AS (
    SELECT id, content, title,
           row_number() OVER (ORDER BY embedding <=> %(query_embedding)s) AS rank
    FROM documents
    ORDER BY embedding <=> %(query_embedding)s
    LIMIT 20
),
text_results AS (
    SELECT id, content, title,
           row_number() OVER (ORDER BY ts_rank(content_tsv, plainto_tsquery('english', %(query_text)s)) DESC) AS rank
    FROM documents
    WHERE content_tsv @@ plainto_tsquery('english', %(query_text)s)
    LIMIT 20
)
SELECT
    COALESCE(v.id, t.id) AS id,
    COALESCE(v.title, t.title) AS title,
    COALESCE(v.content, t.content) AS content,
    (1.0 / (60 + COALESCE(v.rank, 1000))) + (1.0 / (60 + COALESCE(t.rank, 1000))) AS score
FROM vector_results v
FULL OUTER JOIN text_results t ON v.id = t.id
ORDER BY score DESC
LIMIT 8;

This surfaces documents ranking high by either semantic similarity or keyword relevance — covering far more real queries than vector search alone. An exact error code gets caught by full-text search even if embedding similarity is mediocre; a differently-phrased query gets caught by vector search where full-text would miss.

Step 7: Wrap it in a FastAPI endpoint

The endpoint ties embedding, retrieval, and generation together. Keep retrieval separate from the LLM call so you can test and tune independently.

from fastapi import FastAPI
from pydantic import BaseModel
import psycopg
from openai import OpenAI

app = FastAPI()
client = OpenAI()

class QueryRequest(BaseModel):
    question: str
    top_k: int = 8

class QueryResponse(BaseModel):
    answer: str
    sources: list[dict]

@app.post("/search")
def search(req: QueryRequest):
    # Embed the question
    query_embedding = client.embeddings.create(
        model="text-embedding-3-small",
        input=req.question
    ).data[0].embedding
    
    # Retrieve chunks using hybrid search
    with psycopg.connect("dbname=mydb") as conn:
        with conn.cursor() as cur:
            cur.execute(
                """
                WITH vector_results AS (
                    SELECT id, content, title,
                           row_number() OVER (ORDER BY embedding <=> %s) AS rank
                    FROM documents
                    LIMIT %s
                ),
                text_results AS (
                    SELECT id, content, title,
                           row_number() OVER (ORDER BY ts_rank(content_tsv, plainto_tsquery('english', %s)) DESC) AS rank
                    FROM documents
                    WHERE content_tsv @@ plainto_tsquery('english', %s)
                    LIMIT %s
                )
                SELECT COALESCE(v.id, t.id) AS id,
                       COALESCE(v.title, t.title) AS title,
                       COALESCE(v.content, t.content) AS content,
                       (1.0 / (60 + COALESCE(v.rank, 1000))) + (1.0 / (60 + COALESCE(t.rank, 1000))) AS score
                FROM vector_results v
                FULL OUTER JOIN text_results t ON v.id = t.id
                ORDER BY score DESC
                LIMIT %s
                """,
                (query_embedding, req.top_k, req.question, req.question, req.top_k, req.top_k)
            )
            chunks = cur.fetchall()
    
    # Build context and call LLM
    context = "\n\n".join([f"**{row[1]}**\n{row[2]}" for row in chunks])
    
    response = client.chat.completions.create(
        model="gpt-4o-mini",
        messages=[
            {
                "role": "system",
                "content": "Answer the question using only the provided context. If the context doesn't contain the answer, say so."
            },
            {
                "role": "user",
                "content": f"Context:\n{context}\n\nQuestion: {req.question}"
            }
        ]
    )
    
    return QueryResponse(
        answer=response.choices[0].message.content,
        sources=[{"title": row[1], "content": row[2]} for row in chunks]
    )

A few points:

• Embed at request time. The same embedding model, same way, as ingestion. No shortcuts.
• Separate retrieval from generation. Test and tune search quality independently of LLM behavior.
• Pass chunks to the LLM with source titles. The model can cite what it retrieved, and you can verify it actually used your content.

Step 8: Production considerations

Monitoring and drift: Add logging to track how many results are returned from vector search alone vs. full-text alone vs. both. If one signal suddenly dominates, your data distribution has shifted or your embedding model is out of sync with what's in the database.

Re-embedding strategy: Models improve. When you upgrade, add a model_version column and run the new model over time, re-embedding chunks in batches in the background. Once everything is re-embedded, you can drop the old index and point queries to the new vectors.

Partition by tenant if you have multi-tenancy. A single metadata->>'tenant_id' filter in your WHERE clause works, but query planner can only do so much. If you have strict data isolation requirements and query volume is high per tenant, partition the documents table.

Vacuum and analyze regularly. Postgres' query planner relies on up-to-date statistics. After bulk loads or lots of deletes, ANALYZE documents to help the planner choose the right index.

Test recall and precision explicitly. Create a small labelled set of queries and their correct results. Run those queries regularly — retrieval quality should be a monitored metric, not a hope. If it degrades, check whether the data shifted, the model d