pgvector production practices

pgvector performs exact nearest-neighbor search by default. Search becomes approximate only after adding HNSW or IVFFlat. Index selection is an engineering tradeoff across recall, latency, memory, build time, and write cost.

Fix distance semantics first

Embedding generation, index, and query must use the same distance meaning. Cosine similarity is 1 - cosine distance. Store embedding model, dimension, normalization, and generation version; do not mix incomparable vectors in one column/index.

Establish an exact baseline

Sample the real query distribution and save exact top-k results. Compare approximate indexes on recall@k, p50/p95/p99 latency, insufficient-result rate, and resources—not one demonstration query.

BEGIN;
SET LOCAL enable_indexscan = off;

SELECT c.id
FROM document_chunks AS c
JOIN documents AS d ON d.id = c.document_id
WHERE d.tenant_id = $1
ORDER BY c.embedding <=> $2::vector
LIMIT 20;

ROLLBACK;

Disabling index scans is for baselines and diagnosis, not a production setting. Cover hot/cold tenants, common ACLs, time filters, new writes, deletions, and embedding-distribution drift.

HNSW and IVFFlat

CREATE INDEX CONCURRENTLY document_chunks_embedding_hnsw
ON document_chunks
USING hnsw (embedding vector_cosine_ops);

• HNSW generally has a better query speed/recall tradeoff and needs no training set, but builds more slowly, uses more memory, and costs more to maintain.
• IVFFlat builds faster and uses less memory, but needs representative existing data to form lists and generally has a weaker speed/recall tradeoff. Do not create it on an empty table and forget to rebuild.
• On an existing production table, prefer CREATE INDEX CONCURRENTLY and observe WAL, disk, build duration, and replica lag.

There is no universal m, ef_construction, ef_search, lists, or probes value. Start with defaults and an exact baseline, then tune against real filters.

Filtering changes recall

With approximate indexes, filters are normally applied after the index scan produces candidates. At the default hnsw.ef_search = 40, if only 10% of candidates satisfy tenant/ACL filters, the query can return fewer than its LIMIT even when more matching rows exist.

pgvector 0.8.0+ supports iterative scans that continue when initial candidates are insufficient:

BEGIN;
SET LOCAL hnsw.iterative_scan = strict_order;
SET LOCAL hnsw.ef_search = 200;

SELECT c.id, c.content, c.embedding <=> $1::vector AS distance
FROM document_chunks AS c
JOIN documents AS d ON d.id = c.document_id
WHERE d.tenant_id = $2
  AND d.access_scope && $3::text[]
ORDER BY c.embedding <=> $1::vector
LIMIT 20;

COMMIT;

Confirm the pgvector version offered by the cloud service. For a few skewed tenant values, consider list partitioning; for many values, partial indexes for large tenants, separate tables, or physical isolation may work better. Validate the choice with filtered recall and operational cost.

Launch bar

• Every embedding version has replayable ingestion, an exact gold set, and rollback.
• Record model version, filter bucket, candidate/result counts, distance distribution, latency, and truncation online.
• Sample exact searches regularly to calculate recall@k, bucketed by tenant/ACL.
• Rebuild embeddings by dual-writing to a new column/table, building a new index, evaluating, then atomically switching reads.
• Treat text and metadata as source of truth; vectors are rebuildable from versioned inputs.
• Produce full-text and vector candidates independently, then fuse or rerank with a versioned method.

Use the pgvector README sections on indexing, filtering, and monitoring as the authoritative parameter reference.