---
name: pgvector-postgres-rag
description: "Production RAG on Postgres with pgvector — embeddings, HNSW vs IVFFlat tuning, hybrid search, scaling. Production RAG on Postgres with pgvector: storing embeddings in vector/halfvec columns, choosing HNSW vs IVFFlat and tuning m, ef_construction, ef_search and lists/probes, distance ops (<=> cosine, <-> L2, <#> inner product), hybrid search combining ivfflat/hnsw with tsvector full-text and RRF, pgvectorscale StreamingDiskANN, and knowing when to graduate to a dedicated vector DB."
license: MIT
---

# 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

```sql
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.