RAG without the SaaS: Postgres + pgvector on your own infra

Published March 3, 2025

Every RAG tutorial starts with "first, spin up a vector database." Pinecone, Weaviate, Qdrant, Milvus — pick one, get an API key, start paying for another managed service. If you already run Postgres, you can skip this. pgvector gives you vector similarity search inside the database you're already backing up, monitoring, and paying for. For most teams, that's the right call.

This isn't "vector databases are bad." At a certain scale they earn their place — I'll get to where that line is. But most RAG deployments never get there, and adding a new stateful service to your infra has a real cost: another thing to back up, monitor, and secure, and another failure mode in your deploy.

Enabling the extension

CREATE EXTENSION IF NOT EXISTS vector;

That's it if you're on a managed Postgres that ships pgvector (RDS, Cloud SQL, Supabase, Neon, Timescale all support it as of early 2025). If you're self-hosting, you need the extension compiled and installed on the Postgres server itself — it's not a pure-SQL extension, so CREATE EXTENSION will fail if the .so/.dylib isn't present. Check your distro's package (postgresql-16-pgvector on Debian-based systems, or build from source against github.com/pgvector/pgvector).

Embeddings table design

Don't bolt embeddings onto your existing content table as an afterthought column. Keep them in a dedicated table — it makes re-embedding, versioning, and index management much less painful.

CREATE TABLE document_chunks (
    id BIGSERIAL PRIMARY KEY,
    document_id BIGINT NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    chunk_index INT NOT NULL,
    content TEXT NOT NULL,
    content_tsv TSVECTOR GENERATED ALWAYS AS (to_tsvector('english', content)) STORED,
    embedding VECTOR(1536),
    embedding_model TEXT NOT NULL DEFAULT 'text-embedding-3-small',
    created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
    UNIQUE (document_id, chunk_index)
);

A few decisions baked into this schema, worth calling out:

Choosing a distance function

pgvector supports several operators, and the one you pick has to match the index you build and the metric your embedding model was trained against:

| Operator | Meaning | Typical use | |---|---|---| | <-> | Euclidean (L2) distance | General-purpose, older embedding models | | <=> | Cosine distance | Most modern embedding models (OpenAI, Cohere, most open-weight models) | | <#> | Negative inner product | Normalized embeddings where you want raw dot product |

If you're unsure, check your embedding provider's docs — OpenAI and most current open-weight embedding models are trained for cosine similarity, so <=> is usually right. A query looks like:

SELECT id, content, 1 - (embedding <=> :query_embedding) AS similarity
FROM document_chunks
ORDER BY embedding <=> :query_embedding
LIMIT 10;

Note the 1 - distance in the SELECT — <=> returns a distance (0 = identical, 2 = opposite), so if you want a similarity score for display, invert it. The ORDER BY should use the raw distance operator, not the inverted score, so the index can be used.

Indexes: HNSW vs IVFFlat

Without an index, pgvector does an exact nearest-neighbor scan — correct, but linear in table size. Once you're past roughly tens of thousands of rows, you want an approximate index.

HNSW (Hierarchical Navigable Small World) — the default recommendation as of pgvector 0.5+. Better query performance and recall than IVFFlat at similar build cost, and — importantly — it doesn't need to be rebuilt as the table grows the way IVFFlat does.

CREATE INDEX ON document_chunks
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);

At query time, tune hnsw.ef_search (default 40) to trade recall for latency:

SET hnsw.ef_search = 100;

IVFFlat — older, cheaper to build, but needs to be rebuilt (or at least, needs lists re-tuned) as the table grows, and has a training step that requires data already in the table.

CREATE INDEX ON document_chunks
USING ivfflat (embedding vector_cosine_ops)
WITH (lists = 100);

lists should scale roughly with sqrt(row_count) — for 1M rows, something like 100–300 lists is a common starting range; pgvector's own docs give the fuller guidance. Because the index is trained on the data present at build time, an IVFFlat index built on an empty or small table performs poorly — build it after you've loaded a representative amount of data, and re-run REINDEX if the table grows substantially.

When each makes sense: use HNSW unless you have a specific reason not to — it's the better default for read-heavy workloads and doesn't need retraining. Consider IVFFlat only if build time or build-time memory is a hard constraint and you can tolerate periodic reindexing.

Match the index's vector_cosine_ops / vector_l2_ops / vector_ip_ops to whichever distance operator you're actually querying with — an index built for cosine distance won't be used by a query using <->.

Chunking basics

Chunking is a bigger lever on retrieval quality than the index tuning above, and it's outside pgvector's job entirely — it happens before you ever call the embedding API.

Rough guidance that holds up across most text-heavy use cases:

Store chunk_index (as in the schema above) so you can reconstruct order and pull neighboring chunks for extra context at answer time — this is a genuinely useful trick: retrieve the top-k chunks by similarity, then also fetch chunk_index - 1 and chunk_index + 1 for each hit, to give the LLM more surrounding context than the raw match alone.

Hybrid search: combining full-text and vector

Pure vector search misses exact-match cases — product SKUs, error codes, proper nouns the embedding model wasn't trained to weight heavily. Pure full-text search misses semantic matches — "cheap" not matching "affordable." Combining both, generally with a re-ranking or weighted-blend step, usually beats either alone.

WITH vector_results AS (
    SELECT id, content, embedding <=> :query_embedding AS distance,
           row_number() OVER (ORDER BY embedding <=> :query_embedding) AS rank
    FROM document_chunks
    ORDER BY embedding <=> :query_embedding
    LIMIT 20
),
text_results AS (
    SELECT id, content,
           ts_rank_cd(content_tsv, plainto_tsquery('english', :query_text)) AS rank_score,
           row_number() OVER (ORDER BY ts_rank_cd(content_tsv, plainto_tsquery('english', :query_text)) DESC) AS rank
    FROM document_chunks
    WHERE content_tsv @@ plainto_tsquery('english', :query_text)
    LIMIT 20
)
SELECT
    COALESCE(v.id, t.id) AS id,
    COALESCE(v.content, t.content) AS content,
    -- Reciprocal Rank Fusion: combine both rankings into one score
    COALESCE(1.0 / (60 + v.rank), 0.0) + COALESCE(1.0 / (60 + t.rank), 0.0) AS combined_score
FROM vector_results v
FULL OUTER JOIN text_results t ON v.id = t.id
ORDER BY combined_score DESC
LIMIT 10;

This uses Reciprocal Rank Fusion (RRF) — a simple, effective way to blend two ranked lists without needing to normalize scores that live on different scales (cosine distance and ts_rank_cd aren't comparable numbers). The constant 60 is a standard RRF damping factor; it de-emphasizes rank differences far down the list. You don't need to tune it much.

For the full-text side, a GIN index on content_tsv keeps that half fast:

CREATE INDEX ON document_chunks USING gin (content_tsv);

Keeping embeddings in sync with source data

This gets skipped in demos and bites teams in production. Source content changes; embeddings don't update themselves.

The straightforward approach: a trigger that marks a chunk's embedding stale on update, plus a background job that re-embeds stale rows.

ALTER TABLE document_chunks ADD COLUMN embedding_stale BOOLEAN NOT NULL DEFAULT false;

CREATE OR REPLACE FUNCTION mark_embedding_stale()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.content IS DISTINCT FROM OLD.content THEN
        NEW.embedding_stale := true;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER document_chunks_content_change
    BEFORE UPDATE ON document_chunks
    FOR EACH ROW
    EXECUTE FUNCTION mark_embedding_stale();

A worker polls for embedding_stale = true, calls the embedding API, and clears the flag. This decouples "content changed" from "re-embedding happened," since embedding calls are a network round trip you don't want inside your write transaction. For deletions, the ON DELETE CASCADE above handles cleanup automatically when a parent document is removed — don't rely on application code to remember orphaned chunks.

Honest limits

pgvector is good, not magic. Here's where it stops being the right tool:

If you're not at that scale — and most internal tools, most single-tenant SaaS products, most "search our docs" features are nowhere near it — the operational simplicity of not running a second database beats the theoretical ceiling of a dedicated store you're not close to hitting.

Start with what you have. Add a dedicated vector store when you have a specific, measured reason to, not because a tutorial told you it's the default first move.