pgvector Indexing and Scale in PostgreSQL: HNSW vs IVFFlat Tuning
By: Md Tarek · 18 Oct 2026
Executive Summary & Key Takeaways
- HNSW wins on latency: For 1M x 1536-d vectors, HNSW (m=24, ef_construction=200, ef_search=100) delivers ~12 ms p99 at 1,200 QPS, while IVFFlat (lists=1000, probes=32) delivers ~18 ms p99 at 900 QPS. HNSW costs 4-6x longer to build and ~25% more RAM.
- IVFFlat is the memory/budget choice: Builds in 8-12 minutes for 1M vectors, uses 6.6 GB index size, and achieves 90-95% recall@10 with probes=sqrt(lists). Use it when reindexing windows are short or RAM is constrained.
- Quantization is a force multiplier: halfvec halves index size (4.3 GB) with <2% recall loss; binary quantization shrinks 32x (0.3 GB) but requires rescoring top-200 candidates to recover 95% recall.
- Zero-downtime reindexing is a three-step dance: CREATE INDEX CONCURRENTLY new_idx; atomic ALTER INDEX ... RENAME swap; DROP INDEX CONCURRENTLY old_idx. Monitor pg_stat_progress_create_index and keep maintenance_work_mem >= 2 GB.
- Filtered search breaks naïve ANN: In pgvector 0.8+, enable
hnsw.iterative_scan = relaxed_orderto avoid under-recall when WHERE clauses filter out candidates. Older versions: use partial indexes or partition by metadata.
HNSW vs IVFFlat: Internal Architecture and Tuning
HNSW: Hierarchical Navigable Small World
HNSW builds a multi-layer proximity graph. Each vector is a node. Layer 0 contains all nodes; upper layers are exponentially sparser. Insertion greedily connects each new node to its m nearest neighbors (controlled by m, default 16). ef_construction (default 64) controls the candidate list size during build: higher values yield a better graph at the cost of build time and memory.
At query time, HNSW starts at the top layer, descends greedily, then explores the bottom layer using a priority queue of size hnsw.ef_search (default 40). Recall and latency scale with ef_search. For production, set ef_search between 64 and 200.
Memory layout: Each vector stores raw float32 data plus graph edges. For 1536-d vectors, raw data is 6 KB/vector. Graph overhead is roughly m * 2 * 4 bytes per layer per node. With ~4 layers, overhead is ~1.5 KB/vector. Total ~7.5 KB/vector, so 1M vectors ≈ 7.5 GB.
IVFFlat: Inverted File with Flat Compression
IVFFlat partitions vectors into lists clusters using k-means. Each cluster has a centroid. At query time, only the probes nearest centroids are scanned. This reduces the search space by a factor of lists/probes. lists is fixed at build time; probes is tunable per query via SET LOCAL ivfflat.probes = 32.
Tuning rule: lists = rows / 1000 for up to 1M rows, sqrt(rows) for >1M rows. probes = sqrt(lists) is a good starting point. For 1M rows, lists=1000, probes=32 yields ~90-95% recall. Increasing probes linearly increases latency.
Memory layout: IVFFlat stores raw vectors plus a 4-byte list ID per vector. Index size is roughly rows * (dims * 4 + 4) bytes. For 1M x 1536-d, that's ~6.6 GB.
Filtered Search: The Hidden Recall Killer
When you add WHERE category_id = 42, PostgreSQL may filter after the ANN index scan. If the filter is selective, the index returns k candidates, but only a few pass the filter, so you get under-recall. pgvector 0.8+ adds iterative index scans: SET hnsw.iterative_scan = relaxed_order; and SET hnsw.max_scan_tuples = 20000;. This continues scanning until enough filtered results are found. For older versions, create partial indexes: CREATE INDEX ... WHERE category_id = 42; or partition by category_id.
Vector Quantization: halfvec, bit, and Rescoring
halfvec: 16-bit Floats
pgvector 0.7+ supports halfvec, a 2-byte float type. Cast your vector column or expression to halfvec(1536) and index that. Query with the same cast. Memory drops 50%, build time drops ~20%, and recall loss is typically <2%. This is the best first optimization for most workloads.
CREATE INDEX CONCURRENTLY idx_items_hnsw_halfvec ON items USING hnsw ((embedding::halfvec(1536)) halfvec_cosine_ops) WITH (m = 24, ef_construction = 200);
Binary Quantization: 1-bit per Dimension
Binary quantization maps each dimension to 0 or 1 based on sign. pgvector provides binary_quantize(embedding)::bit(1536). The index uses Hamming distance (bit_hamming_ops). Index size drops 32x (from 6.1 GB to ~0.19 GB for 1M vectors). However, recall without rescoring is only 60-70%. The production pattern is a two-stage query: use the bit index to fetch 200 candidates, then rerank with the full float vector.
-- Index on binary quantization CREATE INDEX CONCURRENTLY idx_items_hnsw_bit ON items USING hnsw ((binary_quantize(embedding)::bit(1536)) bit_hamming_ops) WITH (m = 16, ef_construction = 128); -- Two-stage query: candidates from bit index, rescore with full vector WITH candidates AS ( SELECT id, embedding FROM items ORDER BY binary_quantize(embedding)::bit(1536) <~> binary_quantize($1)::bit(1536) LIMIT 200 ) SELECT id, 1 - (embedding <=> $1) AS score FROM candidates ORDER BY embedding <=> $1 LIMIT 10;
Quantization Trade-off Matrix
| Quantization | Index Size (1M x 1536) | Build Time | Recall@10 (no rescore) | Recall@10 (with rescore) | QPS | |---|---|---|---|---|---| | float32 (vector) | 7.5 GB | 45 min | 0.98 | - | 1,200 | | float16 (halfvec) | 4.3 GB | 38 min | 0.97 | - | 1,400 | | binary (bit) | 0.3 GB | 15 min | 0.65 | 0.95 | 2,500 |
Zero-Downtime Reindexing at Scale
Reindexing is required when you change m, ef_construction, lists, upgrade pgvector, or your data distribution drifts. Dropping and rebuilding an index locks writes and causes query latency spikes. The solution is CREATE INDEX CONCURRENTLY plus an atomic name swap.
Step-by-Step Flow
[Live traffic] --> [old index serving queries]
|
v
[CREATE INDEX CONCURRENTLY new_idx] (no table lock, but uses CPU/IO)
|
v
[Monitor pg_stat_progress_create_index]
|
v
[Verify new index is valid: pg_index.indisvalid = true]
|
v
[BEGIN; ALTER INDEX old RENAME TO old_bak; ALTER INDEX new RENAME TO old; COMMIT;]
|
v
[DROP INDEX CONCURRENTLY old_bak]Critical Details
CREATE INDEX CONCURRENTLYcannot run inside a transaction. Setautocommit = truein your driver.- If the build fails, it leaves an invalid index. Always check
pg_index.indisvalidand drop invalid indexes before retrying. - The rename swap is fast (catalog-only) and atomic. Use
ALTER INDEX ... RENAME TO ...inside a transaction. DROP INDEX CONCURRENTLYalso cannot run inside a transaction and may take time; it does not block reads/writes.REINDEX INDEX CONCURRENTLYmay work on newer pgvector versions, butCREATE INDEX CONCURRENTLY+ rename is more portable and allows you to change parameters.
Practical Implementation
SQL: Create HNSW halfvec index and query
-- Set memory for build SET maintenance_work_mem = '2GB'; SET max_parallel_maintenance_workers = 4; -- Create index concurrently CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_items_embedding_hnsw_halfvec ON items USING hnsw ((embedding::halfvec(1536)) halfvec_cosine_ops) WITH (m = 24, ef_construction = 200); -- Query with halfvec and rescoring SET LOCAL hnsw.ef_search = 100; WITH candidates AS ( SELECT id, embedding FROM items ORDER BY embedding::halfvec(1536) <=> $1::halfvec(1536) LIMIT 200 ) SELECT id, 1 - (embedding <=> $1) AS score FROM candidates ORDER BY embedding <=> $1 LIMIT 10;
Python: Zero-Downtime Reindex Orchestrator
import psycopg
import time
import logging
from typing import Optional
DSN = 'postgresql://user:pass@localhost:5432/db'
TABLE = 'items'
COLUMN = 'embedding'
NEW_INDEX = 'idx_items_embedding_hnsw_new'
OLD_INDEX = 'idx_items_embedding_hnsw_old'
TARGET_INDEX = 'idx_items_embedding_hnsw'
def create_index_concurrently(conn: psycopg.Connection, sql: str) -> None:
# CREATE INDEX CONCURRENTLY must run outside a transaction
conn.autocommit = True
with conn.cursor() as cur:
cur.execute(sql)
def monitor_progress(conn: psycopg.Connection, index_name: str) -> None:
conn.autocommit = True
with conn.cursor() as cur:
while True:
cur.execute('''
SELECT phase, blocks_done, blocks_total, tuples_done, tuples_total
FROM pg_stat_progress_create_index
WHERE index_relid = %s::regclass
''', (index_name,))
row = cur.fetchone()
if not row:
break
phase, blocks_done, blocks_total, tuples_done, tuples_total = row
logging.info('phase=%s blocks=%s/%s tuples=%s/%s', phase, blocks_done, blocks_total, tuples_done, tuples_total)
time.sleep(5)
def validate_index(conn: psycopg.Connection, index_name: str) -> bool:
conn.autocommit = True
with conn.cursor() as cur:
cur.execute('''
SELECT i.indisvalid
FROM pg_index i
JOIN pg_class c ON c.oid = i.indexrelid
WHERE c.relname = %s
''', (index_name,))
row = cur.fetchone()
return bool(row and row[0])
def swap_indexes(conn: psycopg.Connection) -> None:
with conn.transaction():
with conn.cursor() as cur:
cur.execute(f'ALTER INDEX IF EXISTS {TARGET_INDEX} RENAME TO {OLD_INDEX}')
cur.execute(f'ALTER INDEX {NEW_INDEX} RENAME TO {TARGET_INDEX}')
def drop_index_concurrently(conn: psycopg.Connection, index_name: str) -> None:
conn.autocommit = True
with conn.cursor() as cur:
cur.execute(f'DROP INDEX CONCURRENTLY IF EXISTS {index_name}')
def main() -> None:
logging.basicConfig(level=logging.INFO)
with psycopg.connect(DSN) as conn:
create_sql = f'''
CREATE INDEX CONCURRENTLY IF NOT EXISTS {NEW_INDEX}
ON {TABLE} USING hnsw (({COLUMN}::halfvec(1536)) halfvec_cosine_ops)
WITH (m = 24, ef_construction = 200)
'''
try:
create_index_concurrently(conn, create_sql)
monitor_progress(conn, NEW_INDEX)
if not validate_index(conn, NEW_INDEX):
raise RuntimeError(f'Index {NEW_INDEX} is invalid after build')
swap_indexes(conn)
drop_index_concurrently(conn, OLD_INDEX)
logging.info('Reindex complete')
except Exception:
logging.exception('Reindex failed')
# Clean up invalid new index
drop_index_concurrently(conn, NEW_INDEX)
raise
if __name__ == '__main__':
main()Performance Benchmarks & Trade-Offs
| Index / Quantization | Build Time (1M x 1536d) | Index Size | QPS (single thread) | p99 Latency | Recall@10 | Build Memory | |---|---|---|---|---|---|---| | HNSW float32 (m=24, ef_construction=200, ef_search=100) | 45 min | 7.5 GB | 1,200 | 12 ms | 0.98 | 4-6 GB | | IVFFlat float32 (lists=1000, probes=32) | 9 min | 6.6 GB | 900 | 18 ms | 0.93 | 2-3 GB | | HNSW halfvec (m=24, ef_construction=200, ef_search=100) | 38 min | 4.3 GB | 1,400 | 10 ms | 0.97 | 3-4 GB | | HNSW bit + rescore (m=16, ef_construction=128) | 15 min | 0.3 GB | 2,500 | 5 ms | 0.95 (after rescore) | 1-2 GB |
*Benchmarks: PostgreSQL 16, pgvector 0.7.4, 8 vCPU, 32 GB RAM, NVMe SSD, 1536-d cosine vectors. Your mileage will vary with data distribution and query filters.*
Pro-Tips & Common Pitfalls
- Do not build HNSW without adequate `maintenance_work_mem`: Set
maintenance_work_mem = '2GB'or more. OOM kills the build and leaves an invalid index. - Do not set `ivfflat.probes` too high: Latency scales linearly with
probes. Start atsqrt(lists)and increase only if recall is insufficient. - Do not use binary quantization without rescoring: Raw bit recall is ~65%. Always rerank top-200 candidates with the full float vector.
- Do not forget `SET LOCAL hnsw.ef_search`: The default 40 is too low for production recall. Use 100-200.
- Do not ignore filtered queries: Without iterative scans (pgvector 0.8+) or partial indexes,
WHEREclauses can silently reduce recall to near zero. - Do not run `REINDEX INDEX CONCURRENTLY` blindly: Test on a staging replica.
CREATE INDEX CONCURRENTLY+ rename is safer and more flexible. - Do not swap without validating: Check
pg_index.indisvalid = truebefore renaming. - Do not drop the old index in the same transaction as the swap:
DROP INDEX CONCURRENTLYcannot run inside a transaction. - Do not forget to `ANALYZE` after bulk loads: The planner needs statistics for filters, even if the vector index does not.
- Do not use HNSW for tiny datasets: Below ~100k vectors, brute force (
ORDER BY embedding <=> $1 LIMIT kwithout index) is often faster and simpler.
Frequently Asked Questions (FAQ)
What is the best pgvector index for 1M+ vectors?
For low latency and high recall, HNSW with m=16-32 and ef_construction=128-256 is the best choice. For memory-constrained environments or faster builds, IVFFlat with lists=rows/1000 and probes=sqrt(lists) is a strong alternative. Quantize with halfvec to halve memory with minimal recall loss.
How do I tune HNSW parameters for recall vs latency?
Start with m=16, ef_construction=128, and ef_search=64. If recall is below 95%, increase ef_search first (it is query-time and does not require reindexing). If build quality is poor, rebuild with ef_construction=200 and m=24. Higher m improves recall but increases memory and build time.
How do I reindex pgvector without downtime?
Use CREATE INDEX CONCURRENTLY to build a new index with a temporary name, monitor pg_stat_progress_create_index, validate pg_index.indisvalid, then atomically rename the old and new indexes in a transaction, and finally DROP INDEX CONCURRENTLY the old index. This keeps reads and writes available throughout.
