pgvector HNSW in Production: Index Tuning, Query Performance, and When to Switch to a Managed Service

Production vector search with pgvector fails silently when you treat HNSW as a drop-in replacement for a B-tree. The defaults are tuned for laptop benchmarks, not for a 500k-row table with 1024-dimensional embeddings.

1. HNSW vs IVFFlat: when each makes sense (data size thresholds, recall vs build-time tradeoffs)

IVFFlat partitions the vector space into lists inverted cells and probes a subset at query time. It builds fast and uses less memory, but its recall collapses at high dimensionality unless you probe so many cells that you defeat the index. In practice, IVFFlat is defensible below ~100k vectors or dimensionality under 512, where probing 50–100 cells out of a few hundred still filters aggressively.

HNSW (Hierarchical Navigable Small World) builds a layered proximity graph. It consumes roughly 2–4x the raw vector memory, builds slower, and does not benefit from parallel construction in pgvector, but it delivers stable recall at 1024+ dimensions without runtime probes tuning. The crossover point is approximately 100k–500k vectors: above this range, IVFFlat’s recall-latency trade-off becomes worse than HNSW’s fixed memory cost.

2. Critical HNSW parameters: m (connectivity), ef_construction (build time), ef (query time)

Three parameters control the graph, not two.

• m: the number of bi-directional links created for each node during insertion. It directly controls index size and graph density. In pgvector, the default is 16. For 1024-dimensional work, m=24 or m=32 is the practical range; below 16 the graph becomes too sparse for high recall, and above 50 the memory cost grows linearly while latency gains flatten.
• ef_construction: the size of the dynamic candidate list when inserting a node. Default is 64. Raising this to 128–200 improves graph quality and recall, but increases build time roughly proportionally.
• ef (query-time, exposed as hnsw.ef_search in pgvector): the candidate list size during search. It must be set at least equal to your LIMIT. This is your runtime recall dial; the index does not need rebuilding when you change it.

3. Worked example: 500k vectors, 1024 dimensions — benchmark numbers for index build time and p99 query latency

Consider a documents table with 500k rows of 1024-dimension float32 embeddings (~2GB heap), running on a 16 vCPU, 128GB RAM instance with SSD-backed Postgres 16.

CREATE INDEX ON documents 
USING hnsw (embedding vector_cosine_ops) 
WITH (m = 24, ef_construction = 100);

Based on typical production deployments, the build completes in approximately 20–40 minutes. The resulting index occupies roughly 4–6GB on disk. max_parallel_maintenance_workers does not reduce this duration because the graph insertion phase in pgvector’s HNSW implementation is serial; only a single backend performs the graph linking.

At query time:

SET hnsw.ef_search = 64;
SELECT id FROM documents 
ORDER BY embedding <=> $1 LIMIT 10;

Under concurrent load, p99 latency is approximately 12–20 ms. Raising ef_search to 256 pushes p99 to roughly 35–55 ms. For reference, exact brute-force ORDER BY over the same table without an index yields p99 latencies exceeding 500 ms. If your application serves embeddings from cache and vector search latency is additive to an LLM call, the difference between 15 ms and 50 ms is the difference between one and three dropped SLOs.

4. The recall-latency curve: how to pick ef for your latency budget

ef_search does not scale linearly with recall. The curve is logarithmic: you pay ms-per-point-of-recall at the top end. Estimated from typical production deployments on 500k 1024-d vectors with m=24:

ef_search	Approx. Recall@10	Approx. p99 Latency
32	0.88	6–10 ms
64	0.94	12–20 ms
128	0.97	22–32 ms
256	0.99	35–55 ms

The knee of the curve sits between 64 and 128. If your latency budget is 25 ms p99, ef_search=128 is your ceiling. Do not cargo-cult ef_search=256 unless you have measured recall against a held-out brute-force ground truth on your actual embeddings; synthetic embeddings behave differently.

5. Postgres-specific gotchas: shared_buffers, parallel workers, WAL amplification during index build

HNSW is random-access heavy. If the index does not fit in shared_buffers, you will see sudden latency cliffs not reflected in EXPLAIN costs because Postgres buffer eviction thrashes during graph traversal. For a 5GB HNSW index, shared_buffers should be at least 8GB, and ideally large enough to fit the index plus relational working set.

Index builds are serial for the graph phase, so provisioning 64 cores for a CREATE INDEX wastes money. Where the cores do matter is WAL flush throughput: each HNSW insertion touches multiple random pages, generating dense WAL traffic. A rebuild of a 5GB HNSW index can produce 20–40GB of WAL, based on typical production deployments, which will crush physical replication lag if you stream to read replicas. Build during maintenance windows, or build the index on a restored standby and fail over.

Set maintenance_work_mem to at least 2GB before building or reindexing. The default 64MB causes repeated candidate-list spills and can inflate build time by 30–50 percent on large dimensions.

6. When pgvector is enough vs when to evaluate Pinecone/Weaviate/Qdrant

pgvector is enough when your vector dataset sits under ~2–5M rows, when you join embeddings to relational metadata in the same transaction, and when your team already runs Postgres. It is particularly strong when you need ACID consistency between vectors and application state.

Evaluate dedicated vector databases when:

• Your uncompressed vectors exceed memory and you cannot shard Postgres horizontally without reinventing routing logic. Pinecone and Weaviate abstract this away, but at the cost of per-query cost opacity.
• Your query pattern is heavily filtered ANN (e.g., WHERE tenant_id = $1 AND category = $2 ORDER BY embedding). Qdrant’s payload/HNSW interleaving and Weaviate’s filtered vector indices generally outperform Postgres’s bitmap-AND-then-HNSW approach when selectivity is low.
• You lack DBA capacity to manually tune shared_buffers, monitor replication lag, and schedule index rebuilds after mass updates. HNSW in pgvector degrades on churned tables because dead tuples are not efficiently removed from the graph without REINDEX INDEX CONCURRENTLY.

Do not switch because of a blog post benchmark; switch when Postgres becomes the ops bottleneck, not when you simply dislike SQL.

Practical checklist

1. Benchmark recall@k for your actual embeddings against brute-force ground truth; do not rely on generic benchmarks.
2. Size shared_buffers to cache the full HNSW index plus headroom; monitor pg_statio_user_indexes for read ratios under 99%.
3. Set maintenance_work_mem >= 2GB immediately before CREATE INDEX or REINDEX.
4. Schedule HNSW builds during low-traffic windows and watch pg_stat_replication for WAL-driven replica lag.
5. Pick ef_search by querying at your latency SLO, not by chasing 0.99 recall you do not need.
6. Re-evaluate pgvector once your working set exceeds available RAM or your filtered ANN queries routinely fall back to bitmap heap scans.

Simple Agent automates HNSW parameter selection, shared_buffers calibration, and ef_search tuning based on your latency and recall targets.