pgvector HNSW index keeps growing and VACUUM takes hours: bloat from updates and deletes, and how to keep it under control

#Symptoms

• The HNSW index is far larger than when it was built, although the row count is about the same.
• Autovacuum on the chunk table runs for hours; pg_stat_progress_vacuum sits in vacuuming indexes.
• Search latency slowly rises; recall drops a little.
• Disk usage grows steadily on a table whose size "should" be stable.

#Why it happens

Each UPDATE in PostgreSQL writes a new row version and a new index entry. A RAG pipeline that re-embeds changed documents by updating the embedding column, or deletes and re-inserts all chunks of a document, creates dead tuples in the table and dead elements in the HNSW graph.

VACUUM has to remove those dead elements and repair the graph links that pointed at them. For HNSW that repair is expensive: it does neighbour searches, much like an insert. On a large index with many deletes, vacuuming the index can take longer than building it from scratch.

#Step 1: measure

-- Dead tuples and last (auto)vacuum on the table
SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum, autovacuum_count
FROM pg_stat_user_tables WHERE relname = 'doc_chunks';

-- Index size now vs a fresh build estimate
SELECT pg_size_pretty(pg_relation_size('doc_chunks_emb_hnsw')) AS hnsw_size,
       pg_size_pretty(pg_relation_size('doc_chunks')) AS table_size;

-- Vacuum progress, if one is running
SELECT pid, phase, heap_blks_scanned, heap_blks_total, index_vacuum_count
FROM pg_stat_progress_vacuum;

To know what a fresh index would weigh, build one on a sample in a test copy, or compare with the size recorded at the original build.

Also look at the ingestion job: how many rows does it update or delete per day compared with the table size? Anything above a few percent a day will show up as bloat.

#Step 2: reindex first, then vacuum

The pgvector documentation recommends this for HNSW: rebuild the index concurrently, then vacuum. The new index contains no dead elements, so the vacuum doesn't have to repair the graph.

SET maintenance_work_mem = '16GB';
SET max_parallel_maintenance_workers = 7;
REINDEX INDEX CONCURRENTLY doc_chunks_emb_hnsw;
VACUUM (VERBOSE) doc_chunks;

If an autovacuum is already grinding through the index, it will be cancelled by the REINDEX lock request only if it's a regular (not anti-wraparound) autovacuum. Check pg_stat_activity for autovacuum: VACUUM ... (to prevent wraparound) before deciding; never cancel wraparound vacuums blindly.

REINDEX CONCURRENTLY needs temporary disk for a second copy of the index. Check free storage first.

#Step 3: tune autovacuum for this table

Defaults (vacuum at 20% dead tuples) mean a 50-million-row table waits for 10 million dead rows before vacuuming. Make it smaller and more frequent:

ALTER TABLE doc_chunks SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold    = 10000,
  autovacuum_vacuum_cost_limit   = 2000
);

Smaller, regular vacuums keep the graph repair work per run bounded. Give autovacuum enough memory (autovacuum_work_mem) to avoid multiple index passes.

#Step 4: change the write pattern

The best fix is fewer dead index entries.

Moving volatile columns away matters because any update to the row creates a new version and a new HNSW entry, unless it qualifies as a HOT update. HOT updates aren't possible when an indexed column changes, and only happen if there's free space on the page; setting fillfactor to 90 on the table helps them for metadata-only updates.

ALTER TABLE doc_chunks SET (fillfactor = 90);   -- applies to new pages; rewrite to apply fully

#Step 5: periodic rebuild for heavy-churn tables

For tables where churn is unavoidable (news, tickets, chat history), schedule a monthly REINDEX INDEX CONCURRENTLY during low traffic and track index size before and after. Partitioned tables make this easier: old partitions stop changing, and only the current partition needs rebuilding.

#Step 6: check recall after cleanup

After a reindex, run your recall check (a fixed query set compared with exact search) to confirm quality is back to baseline:

-- exact top-10 for one query, for comparison with the index result
BEGIN; SET LOCAL enable_indexscan = off;
SELECT id FROM doc_chunks ORDER BY embedding <=> $1 LIMIT 10;
COMMIT;

#Decision table

#Prevent it next time

• Make ingestion idempotent and diff-based with chunk content hashes.
• Keep embeddings in a row that only changes when the text changes.
• Alert when the vector index grows more than 30% without matching row growth.
• Add "reindex vector indexes" to the monthly maintenance runbook for high-churn tables.