pgvector Indexing & Scale in PostgreSQL: HNSW vs IVFFlat Tuning
By: Md Tarek · 22 Oct 2026
Executive Summary & Key Takeaways
- pgvector Indexing and Scale in PostgreSQL is a recall-latency-memory trade-off: on 1M x 1536-d embeddings, HNSW at m=16, ef_construction=64, ef_search=40 typically lands at 3–7 ms p95 and 95–98% recall@10; IVFFlat at lists=1000, probes=10 lands at 8–15 ms p95 and 90–93% recall.
- Use HNSW as the default for low-latency RAG and mixed read/write workloads; use IVFFlat for append-heavy or memory-constrained systems where 90–95% recall is acceptable and index build time matters.
- Quantization is the biggest lever after index choice: halfvec cuts index size and memory roughly 50% with 1–3 recall points lost; binary quantization via binary_quantize + bit_hamming_ops cuts storage by ~96% but needs a top-100 exact rerank to recover recall.
- HNSW m and ef_construction are build-time immutable; ef_search is runtime tunable. IVFFlat lists is build-time immutable; probes is runtime tunable. Never REINDEX to change these parameters.
- Zero-downtime reindexing means CREATE INDEX CONCURRENTLY, verify pg_index.indisvalid, then DROP INDEX CONCURRENTLY the old index. Budget maintenance_work_mem, monitor pg_stat_progress_create_index, and reindex partition-by-partition at scale.
HNSW vs IVFFlat: The Real Mechanics Under Load
pgvector gives PostgreSQL two approximate nearest-neighbor index families with different failure modes. HNSW is a multi-layer proximity graph. IVFFlat is a coarse k-means quantizer plus inverted lists. The query planner does not understand vector recall; it only sees cost. You must benchmark recall@k and p95 latency with production filters, not just raw ANN latency.
Query vector
│
├─ HNSW: greedy graph walk
│ layer 2: 1 entry point ──► few neighbors
│ layer 1: ... ──► candidate heap grows to ef_search
│ layer 0: ... ──► top-k by distance
│
└─ IVFFlat: coarse quantizer
probe P of L lists ──► scan vectors in P lists ──► top-kHNSW search cost is driven by ef_search and m; it is sublinear but not free. Each visited node evaluates up to m neighbors, so raising ef_search from 40 to 200 can triple or quadruple p95 latency. HNSW inserts update the graph and create write amplification; high-delete workloads leave tombstones until vacuum/reindex. IVFFlat search scans P/L of the table. With lists=1000 and probes=10, you scan roughly 1% of vectors, but recall collapses if probes is too low or if the lists are imbalanced.
Production war story: a 12M-chunk RAG cluster using HNSW m=48 and ef_construction=200 built a 22 GB index, OOMed during a concurrent build, and produced 19 ms p95. Switching to halfvec with m=16 and ef_construction=96 reduced the index to 10.4 GB, cut p95 to 6 ms, and lost only 0.4 recall points after a top-100 rerank. The lesson: raise ef_search first, then m only when recall demands it.
For filtered search, pgvector 0.8 iterative scans help when filters are moderately selective:
SET LOCAL hnsw.iterative_scan = relaxed_order; SET LOCAL hnsw.max_scan_tuples = 20000; SET LOCAL hnsw.ef_search = 100;
If a tenant filter returns less than ~1% of rows, do not rely on post-filter ANN. Use a partial HNSW index per large tenant or partition by tenant_id so the index only contains candidate rows.
Quantization in pgvector: halfvec, bit, and expression indexes
pgvector does not ship product quantization (PQ) inside the index AM. What it does ship, starting in 0.7/0.8, is half-precision halfvec, binary bit vectors with Hamming/Jaccard distance, and sparsevec. That is enough to cut memory bandwidth and index size by 2x or more, but each method changes recall behavior.
halfvec is the safest general-purpose quantization. A vector(1536) column remains the source of truth; index an expression cast to halfvec(1536). PostgreSQL stores 2 bytes per dimension instead of 4. For 1M x 1536-d, raw vector storage drops from ~5.7 GiB to ~2.9 GiB before graph overhead. Recall degradation is usually small because cosine/L2 ranking is robust to float16 rounding, but always measure on your domain.
CREATE INDEX CONCURRENTLY idx_documents_embedding_half_hnsw
ON documents USING hnsw ((embedding::halfvec(1536)) halfvec_cosine_ops)
WITH (m = 16, ef_construction = 96);
SELECT id,
1 - ((embedding::halfvec(1536)) <=> $1::halfvec(1536)) AS cosine_similarity
FROM documents
WHERE tenant_id = $2
ORDER BY (embedding::halfvec(1536)) <=> $1::halfvec(1536)
LIMIT 10;The ORDER BY expression must match the index expression exactly. If you cast in SELECT but not in ORDER BY, PostgreSQL cannot use the expression index.
Binary quantization is the aggressive option. pgvector's binary_quantize maps a vector to sign bits; Hamming distance is cheap and the index is tiny. Recall can drop 5–20 points on its own, so use it as a candidate generator and rerank with the full vector.
CREATE INDEX CONCURRENTLY idx_documents_embedding_bit_hnsw
ON documents USING hnsw ((binary_quantize(embedding)::bit(1536)) bit_hamming_ops)
WITH (m = 32, ef_construction = 128);
WITH candidates AS (
SELECT id, embedding
FROM documents
WHERE tenant_id = $2
ORDER BY binary_quantize(embedding)::bit(1536)
<~> binary_quantize($1::vector(1536))::bit(1536)
LIMIT 100
)
SELECT id, 1 - (embedding <=> $1::vector(1536)) AS cosine_similarity
FROM candidates
ORDER BY embedding <=> $1::vector(1536)
LIMIT 10;Dimension limits matter. A plain vector index supports up to 2,000 dimensions; halfvec raises that to 4,000, which is why 3072-d text-embedding-3-large vectors should be indexed as halfvec or reduced with PCA/Matryoshka truncation. Do not store 3072-d vectors in a plain vector HNSW index and expect it to build.
Zero-Downtime Reindexing at Scale
Changing HNSW m, ef_construction, or IVFFlat lists requires a new index. REINDEX INDEX CONCURRENTLY rebuilds with the same parameters and is for bloat or corruption, not tuning. The safe pattern is shadow index, validate, then drop the old index. CREATE INDEX CONCURRENTLY cannot run inside a transaction block, so use autocommit and avoid statement_timeout. Set lock_timeout low so the build fails fast instead of blocking DDL behind a long transaction.
-- 1. Build the new index while reads and writes continue. CREATE INDEX CONCURRENTLY idx_documents_embedding_hnsw_m32_v2 ON documents USING hnsw (embedding vector_cosine_ops) WITH (m = 32, ef_construction = 200); -- 2. Validate. indisvalid and indisready must both be true. SELECT indexrelid::regclass AS index_name, indisvalid, indisready FROM pg_index WHERE indexrelid = 'idx_documents_embedding_hnsw_m32_v2'::regclass; -- 3. After the new index is valid, drop the old one concurrently. DROP INDEX CONCURRENTLY idx_documents_embedding_hnsw_m16_v1;
Monitor progress in a second session:
SELECT phase, tuples_done, tuples_total,
round(100.0 * tuples_done / NULLIF(tuples_total, 0), 2) AS pct
FROM pg_stat_progress_create_index;At scale, partition by tenant_id or time range and reindex one partition at a time. Each partition gets its own HNSW graph, which caps build memory and limits blast radius. A failed concurrent build leaves an invalid index; drop it with DROP INDEX CONCURRENTLY before retrying. For embedding model migrations, add embedding_v2 vector(...), dual-write, backfill in batches, build the new expression index concurrently, switch reads, then drop the old column and index after a rollback window.
Practical Implementation
SQL baseline for a production RAG table:
CREATE EXTENSION IF NOT EXISTS vector; CREATE TABLE documents ( id bigserial PRIMARY KEY, tenant_id bigint NOT NULL, content text NOT NULL, embedding vector(1536) NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); -- HNSW baseline for mixed read/write RAG. CREATE INDEX CONCURRENTLY idx_documents_embedding_hnsw ON documents USING hnsw (embedding vector_cosine_ops) WITH (m = 16, ef_construction = 64); -- Runtime tuning, per request or transaction. SET hnsw.ef_search = 80;
Python worker for a safe concurrent HNSW build and old-index retirement:
import logging
from dataclasses import dataclass
import psycopg
from psycopg import sql
log = logging.getLogger('pgvector.reindex')
@dataclass(frozen=True)
class HnswSpec:
table: str
index_name: str
column_and_opclass: str
m: int = 16
ef_construction: int = 96
maintenance_work_mem: str = '12GB'
def _index_is_valid(conn: psycopg.Connection, index_name: str) -> bool:
row = conn.execute(
'SELECT indisvalid AND indisready FROM pg_index WHERE indexrelid = to_regclass(%s)',
(index_name,),
).fetchone()
return bool(row and row[0])
def _drop_invalid(conn: psycopg.Connection, index_name: str) -> None:
log.warning('dropping invalid index %s', index_name)
conn.execute(
sql.SQL('DROP INDEX CONCURRENTLY IF EXISTS {}').format(sql.Identifier(index_name))
)
def build_hnsw_zero_downtime(dsn: str, spec: HnswSpec) -> None:
# autocommit is mandatory: CREATE INDEX CONCURRENTLY cannot run in a transaction
with psycopg.connect(dsn, autocommit=True) as conn:
conn.execute(sql.SQL('SET lock_timeout = {}').format(sql.Literal('2s')))
conn.execute('SET statement_timeout = 0')
conn.execute(
sql.SQL('SET maintenance_work_mem = {}').format(sql.Literal(spec.maintenance_work_mem))
)
conn.execute('SET max_parallel_maintenance_workers = 4')
if _index_is_valid(conn, spec.index_name):
log.info('index %s is already valid; skipping', spec.index_name)
return
_drop_invalid(conn, spec.index_name)
stmt = sql.SQL(
'CREATE INDEX CONCURRENTLY {} ON {} USING hnsw ({}) '
'WITH (m = %s, ef_construction = %s)'
).format(
sql.Identifier(spec.index_name),
sql.Identifier(spec.table),
sql.SQL(spec.column_and_opclass),
)
try:
conn.execute(stmt, (spec.m, spec.ef_construction))
except psycopg.Error:
log.exception('index build failed for %s', spec.index_name)
if not _index_is_valid(conn, spec.index_name):
_drop_invalid(conn, spec.index_name)
raise
if not _index_is_valid(conn, spec.index_name):
raise RuntimeError(f'index {spec.index_name} is not valid after build')
def drop_old_index_zero_downtime(dsn: str, old_index_name: str) -> None:
with psycopg.connect(dsn, autocommit=True) as conn:
conn.execute(sql.SQL('SET lock_timeout = {}').format(sql.Literal('2s')))
conn.execute(
sql.SQL('DROP INDEX CONCURRENTLY IF EXISTS {}').format(sql.Identifier(old_index_name))
)
if __name__ == '__main__':
logging.basicConfig(level=logging.INFO)
build_hnsw_zero_downtime(
dsn='postgresql://[email protected]:5432/rag',
spec=HnswSpec(
table='documents',
index_name='idx_documents_embedding_hnsw_m32_v2',
column_and_opclass='embedding vector_cosine_ops',
m=32,
ef_construction=200,
maintenance_work_mem='16GB',
),
)Performance Benchmarks & Trade-Offs
Representative results for 1M vectors x 1536 dimensions, 16 vCPU, 64 GB RAM, NVMe, PostgreSQL 16, pgvector 0.8, cosine distance, top-10, unfiltered. Treat these as calibration points, not universal constants.
| Strategy | Recall@10 | p95 latency | Index size | Build time | Best fit | |---|---:|---:|---:|---:|---| | Exact scan, no ANN index | 100% | 780–950 ms | 0 B | n/a | <50k rows or audit-grade exactness | | IVFFlat, lists=1000, probes=10 | 90–93% | 8–15 ms | ~5.8 GB | 8–15 min | Static or append-heavy, memory constrained | | HNSW, m=16, ef_construction=64, ef_search=40 | 95–98% | 3–7 ms | ~6.1 GB | 35–70 min | Low-latency RAG, mixed reads/writes | | HNSW halfvec, m=16, ef_construction=96, ef_search=80 | 94–97% | 2–5 ms | ~3.1 GB | 20–40 min | 1536-d embeddings, 2x memory savings | | HNSW bit + exact rerank top-100 | 88–94% final | 4–9 ms | ~220 MB + rerank | 5–12 min | Aggressive memory budget, high QPS |
Pro-Tips & Common Pitfalls
- [ ] Match the operator class to the query operator: vector_cosine_ops for <=>, vector_l2_ops for <->, vector_ip_ops for <#>, halfvec_cosine_ops for halfvec <=>, bit_hamming_ops for bit <~>. A mismatch silently disables the index.
- [ ] Do not build IVFFlat on an empty or tiny table. lists is fixed at build time and the k-means centers need representative data; load first, ANALYZE, then build.
- [ ] Do not raise HNSW m before testing ef_search. Raise ef_search from 40 to 80–120 first; m=32 or 48 increases build time, memory, and write amplification.
- [ ] Do not set ef_search globally to 500. Use SET LOCAL per request so one heavy query cannot blow p99 for the whole pool.
- [ ] Do not run CREATE INDEX CONCURRENTLY inside a transaction, with statement_timeout, or behind a long lock. Set lock_timeout to 2–5s and retry the build.
- [ ] Do not REINDEX INDEX CONCURRENTLY to change m, ef_construction, or lists. It reuses existing parameters; build a new index and drop the old one.
- [ ] Do not ignore invalid indexes after a failed concurrent build. Check indisvalid and drop them with DROP INDEX CONCURRENTLY before retrying.
- [ ] Do not use binary quantization without a rerank. Binary candidates are for recall-at-scale, not final ordering.
- [ ] Do not index 3072-d vectors with plain vector opclasses. Use halfvec(3072), reduce dimensions, or choose a different vector store.
- [ ] Do not post-filter highly selective tenants through a global HNSW index. Partition by tenant or use partial indexes for large tenants.
- [ ] Do not forget VACUUM and ANALYZE after bulk loads and large deletes; stale stats and graph tombstones hurt recall and latency.
- [ ] Do not SELECT * in hot vector queries. Fetch ids and scores first, then join payload columns.
Frequently Asked Questions (FAQ)
Should I use HNSW or IVFFlat in pgvector for production RAG?
Use HNSW as the default when p95 latency and recall matter. It handles mixed read/write traffic better and typically delivers 95–98% recall@10 at 3–7 ms p95 on 1M x 1536-d vectors. Use IVFFlat when the table is large, mostly static, memory-constrained, and 90–95% recall is acceptable. Benchmark recall@10 and p95 with your real filters before committing.
Does pgvector support product quantization or binary quantization?
pgvector does not expose product quantization in its index AM. It supports halfvec for 16-bit scalar storage, bit vectors with binary_quantize and Hamming/Jaccard distance, and sparsevec for sparse embeddings. Binary quantization can cut index size by ~96%, but it is usually a candidate-generation layer; rerank the top 50–200 candidates with the full vector to recover recall.
How do I reindex a pgvector HNSW index without downtime?
Build a new index with CREATE INDEX CONCURRENTLY and a new name, verify indisvalid and indisready, then DROP INDEX CONCURRENTLY the old index. This works for changing m, ef_construction, or expression/opclass. Use REINDEX INDEX CONCURRENTLY only to rebuild the same index definition. For multi-tenant systems, partition and reindex one partition at a time to cap memory and blast radius.
