pgvector RAG Pro

A focused guide for building and operating retrieval-augmented generation on Postgres with the pgvector extension. It covers the decisions that actually bite in production: which column type to store embeddings in, which index to build, how to tune that index for your recall/latency budget, and how to fuse vector search with full-text search. For backend engineers who already have Postgres and want RAG without standing up a separate vector database.

When to use it

• You're storing document/chunk embeddings (OpenAI, Cohere, local models) and want similarity search next to your relational data.
• Recall feels low or queries are slow, and you need to tune ef_search, probes, or rebuild the right index.
• You want hybrid search — semantic plus keyword — instead of pure cosine similarity.
• Embedding dimensions are large (1536, 3072) and storage/memory is a concern.

How it works

Enable with CREATE EXTENSION vector;. Store vectors in a vector(d) column, or halfvec(d) (16-bit floats) to roughly halve storage and index size — useful for high-dimension models. Query with the distance operators: <=> cosine, <-> L2, <#> negative inner product. Match the operator class on your index to the operator you query with (vector_cosine_ops, vector_l2_ops, vector_ip_ops).

Two index types:

• HNSW — best recall/latency, built incrementally. Tune m and ef_construction at build time; raise hnsw.ef_search per session for higher recall.
• IVFFlat — faster to build, lower memory. Set lists at build (~rows/1000), tune probes per query. Build it after loading representative data.

Hybrid search: combine a vector ANN query with a tsvector @@ to_tsquery full-text query and merge with Reciprocal Rank Fusion (RRF).

Example

curl -fsSL --create-dirs -o ~/.claude/skills/pgvector-postgres-rag/SKILL.md https://claudeskil.com/skills/pgvector-postgres-rag/SKILL.md

CREATE EXTENSION IF NOT EXISTS vector;
ALTER TABLE chunks ADD COLUMN embedding vector(1536);

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

SET hnsw.ef_search = 100;
SELECT id, content
FROM chunks
ORDER BY embedding <=> $1   -- query embedding
LIMIT 10;

Tips & gotchas

• The operator in ORDER BY must match the index's opclass, or Postgres ignores the index and does a seq scan. Check with EXPLAIN.
• Normalize vectors and use <=>/inner product consistently; don't mix L2 and cosine across ingest and query.
• IVFFlat built on an empty table gives terrible recall — load data first, then index.
• HNSW build is memory-hungry; bump maintenance_work_mem before CREATE INDEX.

FAQ

HNSW or IVFFlat? HNSW for the best query quality and you can tolerate slower builds and more memory. IVFFlat when build speed and footprint matter more than peak recall.

How do I raise recall without rebuilding? Increase hnsw.ef_search (HNSW) or ivfflat.probes (IVFFlat) per session — both trade latency for recall, no reindex needed.

Does it scale past one node? pgvector rides standard Postgres replication and partitioning; shard with Citus or partition by tenant for very large corpora.