pgvector HNSW index build takes hours: maintenance_work_mem, parallel workers and building without blocking writes

#Symptoms

• CREATE INDEX ... USING hnsw runs for hours on a table of a few million rows.
• The server log shows a notice like:

NOTICE:  hnsw graph no longer fits into maintenance_work_mem after 1200000 tuples
DETAIL:  Building will take significantly more time.
HINT:  Increase maintenance_work_mem to speed up builds.

• Inserts into the table are blocked during the build (non-concurrent build).
• On a managed service, the build fails with out-of-memory or the instance restarts.

#Why HNSW builds are slow

Building HNSW means inserting every vector into a graph and searching the graph for its neighbours. That's a lot of distance calculations. pgvector builds the graph in memory while it fits in maintenance_work_mem, which is fast. When the graph outgrows that memory, it switches to writing it on disk page by page, which is much slower.

#Step 1: estimate the memory you need

A rough sizing, good enough for planning:

graph memory ≈ rows × (dims × 4 bytes + m × 2 × 8 bytes + overhead)

For 5 million rows of vector(1536) with m = 16: 5M × (6,144 + 256 + ~100) ≈ 32 GB. With halfvec(1536) the vector part halves (≈ 17 GB).

Compare with what the server can give a single maintenance operation without starving shared buffers and the OS. If it doesn't fit, see step 5.

Check the current index sizes on similar tables to calibrate:

SELECT pg_size_pretty(pg_relation_size('doc_chunks_emb_hnsw'));

#Step 2: give the build session memory and workers

Set these in the session doing the build, not globally:

SET maintenance_work_mem = '24GB';
SET max_parallel_maintenance_workers = 7;    -- leader + 7 workers
SET statement_timeout = 0;                   -- don't let a default timeout kill a 2-hour build

CREATE INDEX CONCURRENTLY doc_chunks_emb_hnsw
  ON doc_chunks USING hnsw (embedding vector_cosine_ops);

Parallel workers also need max_parallel_workers and max_worker_processes high enough at server level. On managed services these are server parameters; some need a restart.

Warning

Autovacuum workers also use maintenance_work_mem unless autovacuum_work_mem is set, and several can run at once. Don't raise it in postgresql.conf to 24 GB; set it only in the build session.

On a managed service with low memory, a large build can still exhaust RAM. Check free memory and the instance class first, and avoid running builds next to peak traffic.

#Step 3: watch progress

SELECT pid, phase,
       blocks_done, blocks_total,
       tuples_done, tuples_total,
       round(100.0 * tuples_done / nullif(tuples_total, 0), 1) AS pct
FROM pg_stat_progress_create_index;

Phases you'll see: initializing, loading tuples (the graph build, the long part), and for concurrent builds the waits for older transactions. If pct slows sharply partway, the graph has spilled out of memory.

#Step 4: concurrent or not

For a live table, use CONCURRENTLY. If it fails, clean up:

SELECT indexrelid::regclass FROM pg_index
WHERE indrelid = 'doc_chunks'::regclass AND NOT indisvalid;
DROP INDEX CONCURRENTLY doc_chunks_emb_hnsw;

#Step 5: when the graph can't fit

Partitioning also makes later rebuilds cheaper because you rebuild one partition at a time:

CREATE INDEX CONCURRENTLY ON doc_chunks_2026_q4 USING hnsw (embedding vector_cosine_ops);

#Step 6: load first, index second

For an initial load or a full re-embedding:

1. Create the table without the vector index.
2. Bulk load with COPY (binary format is fastest with the pgvector driver adapters).
3. Build the HNSW index once with enough memory and workers.
4. ANALYZE the table.

Inserting millions of rows into a table that already has an HNSW index is much slower than building at the end, because each insert searches the graph.

For a re-embed of a live table, build the new column's index next to the old one and switch queries afterwards, instead of dropping the old index first.

#Step 7: replicas and WAL

An index build writes a lot of WAL. On a primary with streaming replicas or read replicas:

• Watch replica lag during the build.
• Make sure WAL archive storage and max_wal_size can absorb it.
• On managed services, expect higher backup storage for that day.

#Checklist

#Prevent it next time

• Record build time and index size per million rows for each model dimension; use it to plan.
• Choose halfvec for large-dimension models from the start.
• Partition tables that will grow past tens of millions of vectors.
• Run large builds in a maintenance window with a written rollback (drop the new index).