pgvector Best Practices

How to Use This List

Complete before production RAG launch.
Re-run when embedding model, pgvector version, or corpus size changes by 10x.
Pair with hybrid search checklist if FTS is enabled.

A - Extension and Types

Pin pgvector version in IaC. CREATE EXTENSION vector VERSION '0.8.0' matches OS package.
Schema-qualify extensions.vector. Avoid search_path surprises in pooled connections.
Fix dimension in DDL. vector(1536) plus CHECK (vector_dims(embedding) = 1536).
Store embed model and version in metadata. Re-embed campaigns need deterministic filters.
Match distance operator to index opclass. <=> with vector_cosine_ops for normalized text embeddings.

B - Indexes and Recall

Benchmark recall@k on your corpus. Do not trust default ef_search or ivfflat.probes.
Build HNSW after bulk load, not on empty tables. IVFFlat especially needs representative data.
Use CREATE INDEX CONCURRENTLY in production. Schedule RAM-heavy builds off-peak.
REINDEX after major re-embed or pgvector upgrade. Release notes flag mandatory rebuilds.
Keep brute-force gold queries. 50-200 labeled questions for regression tests.

C - Schema and Tenancy

Separate documents and chunks tables. Citations and idempotent ingest need stable source ids.
Enable RLS on tenant_id for SaaS. Set session variable per request at pooler.
Partial index WHERE embedding IS NOT NULL. Pending embed jobs should not bloat ANN index.
Add FTS generated column for hybrid. Same chunk row powers keyword + semantic search.
Hash source content at document level. Skip re-chunk when content_hash unchanged.

D - Operations

Monitor index size and idx_scan. Zero scans means wrong opclass or planner falling back to seq scan.
Tune hnsw.ef_search per environment. Staging can use higher recall than peak-hour production.
Watch autovacuum on churny chunk tables. Re-embed updates dead tuples fast.
Size RAM before HNSW build. maintenance_work_mem session bump for migration role only.
Document when to shard or offload. ADR criteria before billion-vector fantasy becomes mandate.

FAQs

Default index choice?

HNSW for most PG 18 RAG stores; IVFFlat if RAM severely constrained after benchmark.

Cosine vs L2?

Normalized text embeddings: cosine. Raw feature vectors: often L2.

How many neighbors in RAG?

Retrieve 8-20; rerank in app to 3-5 for LLM context.

Null embeddings?

pending/failed status column; worker claims with SKIP LOCKED.

Managed Postgres?

Verify extension availability and max maintenance_work_mem on instance class.

Backup bloat?

Vectors inflate physical backups; use size alerts on chunk table.

Logical replication?

Match extension version on subscriber; plan index rebuild.

Halfvec?

Evaluate recall impact before fleet-wide adoption.

Security?

RLS plus no PUBLIC SELECT on chunk table; embeddings can leak semantic content.

Evaluation cadence?

Quarterly recall@k and p95 latency on production-sized staging clone.

pgvector Basics - types and operators
IVFFlat vs HNSW Indexes - trade-offs
pgvector at Scale - memory and vacuum
RAG Storage Schema - table design
Extensions Best Practices - version pins

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PostGIS 3.5+, pgbouncer 1.x, and Patroni 3.x.

pgvector Best Practices

How to Use This List

Complete before production RAG launch.
Re-run when embedding model, pgvector version, or corpus size changes by 10x.
Pair with hybrid search checklist if FTS is enabled.

A - Extension and Types

Pin pgvector version in IaC. CREATE EXTENSION vector VERSION '0.8.0' matches OS package.
Schema-qualify extensions.vector. Avoid search_path surprises in pooled connections.
Fix dimension in DDL. vector(1536) plus CHECK (vector_dims(embedding) = 1536).
Store embed model and version in metadata. Re-embed campaigns need deterministic filters.
Match distance operator to index opclass. <=> with vector_cosine_ops for normalized text embeddings.

B - Indexes and Recall

Benchmark recall@k on your corpus. Do not trust default ef_search or ivfflat.probes.
Build HNSW after bulk load, not on empty tables. IVFFlat especially needs representative data.
Use CREATE INDEX CONCURRENTLY in production. Schedule RAM-heavy builds off-peak.
REINDEX after major re-embed or pgvector upgrade. Release notes flag mandatory rebuilds.
Keep brute-force gold queries. 50-200 labeled questions for regression tests.

C - Schema and Tenancy

Separate documents and chunks tables. Citations and idempotent ingest need stable source ids.
Enable RLS on tenant_id for SaaS. Set session variable per request at pooler.
Partial index WHERE embedding IS NOT NULL. Pending embed jobs should not bloat ANN index.
Add FTS generated column for hybrid. Same chunk row powers keyword + semantic search.
Hash source content at document level. Skip re-chunk when content_hash unchanged.

D - Operations

Monitor index size and idx_scan. Zero scans means wrong opclass or planner falling back to seq scan.
Tune hnsw.ef_search per environment. Staging can use higher recall than peak-hour production.
Watch autovacuum on churny chunk tables. Re-embed updates dead tuples fast.
Size RAM before HNSW build. maintenance_work_mem session bump for migration role only.
Document when to shard or offload. ADR criteria before billion-vector fantasy becomes mandate.

FAQs

Default index choice?

HNSW for most PG 18 RAG stores; IVFFlat if RAM severely constrained after benchmark.

Cosine vs L2?

Normalized text embeddings: cosine. Raw feature vectors: often L2.

How many neighbors in RAG?

Retrieve 8-20; rerank in app to 3-5 for LLM context.

Null embeddings?

pending/failed status column; worker claims with SKIP LOCKED.

Managed Postgres?

Verify extension availability and max maintenance_work_mem on instance class.

Backup bloat?

Vectors inflate physical backups; use size alerts on chunk table.

Logical replication?

Match extension version on subscriber; plan index rebuild.

Halfvec?

Evaluate recall impact before fleet-wide adoption.

Security?

RLS plus no PUBLIC SELECT on chunk table; embeddings can leak semantic content.

Evaluation cadence?

Quarterly recall@k and p95 latency on production-sized staging clone.

pgvector Basics - types and operators
IVFFlat vs HNSW Indexes - trade-offs
pgvector at Scale - memory and vacuum
RAG Storage Schema - table design
Extensions Best Practices - version pins

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PostGIS 3.5+, pgbouncer 1.x, and Patroni 3.x.

pgvector Best Practices

Search Documentation

pgvector Best Practices