Direct Technical Solution: Unlike IVFFlat (which partitions vector spaces with k-means centroids), HNSW (Hierarchical Navigable Small World) constructs a multi-layer proximity graph in physical RAM during build time. For 2,000,000 vectors with 1,536 dimensions, the raw vectors alone occupy 2,000,000 × 1,536 × 4 bytes = 12.28 GB. Adding the HNSW graph neighbor connectivity lists (with m=16) increases total build RAM requirement to approximately 18 to 22 GB.

1. The Formula to Calculate Required `maintenance_work_mem`

Required RAM (Bytes) ≈ Rows × ((dimensions × 4) + (8 × M)) × 1.2 (Index Graph Overhead)

2. PostgreSQL Configuration Tuning

Temporarily allocate sufficient RAM to the session before triggering the index build, and utilize parallel CPU worker cores to accelerate graph edge exploration:

-- Execute inside the active database session before CREATE INDEX:
SET maintenance_work_mem = '24GB';
SET max_parallel_maintenance_workers = 4;
SET max_parallel_workers = 8;

-- Now trigger the HNSW index build
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_documents_hnsw
ON documents USING hnsw (embedding vector_cosine_ops) 
WITH (m = 16, ef_construction = 64);

-- Reset back to default after build completes
RESET maintenance_work_mem;

3. When to Use IVFFlat vs HNSW