Senior LevelPostgreSQL
How do you tune HNSW index memory limits (maintenance_work_mem) for massive vector builds?
❓ Interview Prompt: Explain `pgvector` HNSW memory requirements, `maintenance_work_mem`, `hnsw.ef_construction`, `hnsw.m`, and parallel index build workers.
Building 'pgvector' HNSW indexes on millions of 1536-dimensional vector embeddings requires significant RAM:
Memory Tuning Strategy:
1
'maintenance_work_mem': Increase to 2GB-8GB to keep the HNSW graph construction entirely in RAM.
2
'max_parallel_maintenance_workers': Set to 4-8 workers to parallelize graph construction.
3
HNSW Parameters: 'm = 16', 'ef_construction = 64' (balances search recall vs index build memory).
Code Implementation Example
sql
-- Tune memory and parallel workers for fast HNSW index build
SET maintenance_work_mem = '4GB';
SET max_parallel_maintenance_workers = 4;
CREATE INDEX idx_vec_hnsw ON vector_embeddings
USING hnsw (embedding vector_cosine_ops)
WITH (m = 16, ef_construction = 64);💡Tuning maintenance_work_mem and parallel workers for fast HNSW index builds.
🎯 Interview Key Takeaway
Set maintenance_work_mem high when building pgvector HNSW indexes to prevent graph construction spilling to disk.
#pgvector#HNSW#maintenance_work_mem#Vector Indexing#Senior
Back to all PostgreSQL Questions→