pgvector at Scale: Vacuum, Reindexing and Index Rebuilds

VACUUM removes dead tuples and makes their space reusable, but it does not shrink files on disk. For a vector table, that means the heap portion of the index can be cleaned while the graph or list structures retain entries pointing at dead rows. The practical consequence is that vacuum is necessary and regular, but it is not sufficient.

Autovacuum's defaults are tuned for small, low-churn tables. A vector table that receives re-embeddings or re-ingestion of documents is a high-churn table, and the relevant knobs are well documented in the PostgreSQL performance community (Scaling Postgres). A workable starting configuration for a table with an HNSW index:

ALTER TABLE documents SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.01
);

Scale factors of 0.02 mean autovacuum triggers after 2 percent of the table changes, instead of the default 20 percent. On a table of 10 million rows, that is roughly 200,000 dead tuples at default versus 200,000 at… the smaller setting fires after about 200,000 instead of 2 million. Lower thresholds cost more background I/O but keep bloat in a narrow band.

For long index builds, the memory settings matter more than anything else. The issue thread that tracked a stuck HNSW build shows the diagnostic: NOTICE: hnsw graph no longer fits into maintenance_work_mem after 570905 tuples. The fix was to raise maintenance_work_mem and note that SET LOCAL does not apply to CREATE INDEX CONCURRENTLY because the statement runs outside the transaction (pgvector issue #822). Use a session-level SET:

SET maintenance_work_mem = '8GB';
SET max_parallel_maintenance_workers = 4;
CREATE INDEX CONCURRENTLY documents_embedding_hnsw_new
  ON documents USING hnsw (embedding vector_cosine_ops)
  WITH (m = 16, ef_construction = 64);

A rule of thumb for sizing: HNSW's in-memory build working set scales with m and vector width. Watch the progress view during the build; if it sits at a fixed percentage for a long time, the graph has spilled to disk and the build is now many times slower:

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

Recall is the fraction of true nearest neighbors that the index returns. You cannot measure it without knowing the true answers, so the first maintenance task is to build a small, stable evaluation set.

1. Sample queries. Take 1,000 random documents from the corpus and use their vectors as query vectors. Store the query vector, not the text, so the test does not depend on the embedding service being up.
2. Compute the exact answers once, with the index disabled for the measurement session:

   SET LOCAL enable_indexscan = off;
   SET LOCAL enable_bitmapscan = off;
   

   Run the nearest-neighbor query for each sampled vector and store the top 10 ids in a ground_truth table keyed by query id.
3. Recompute recall on a schedule by running the same queries with the index enabled and comparing sets:

   WITH hits AS (
     SELECT gt.query_id,
            count(*) AS matched
     FROM ground_truth gt
     JOIN LATERAL (
       SELECT id
       FROM documents
       ORDER BY embedding <=> gt.query_vector
       LIMIT 10
     ) ann ON ann.id = gt.id
     GROUP BY gt.query_id
   )
   SELECT avg(matched) / 10.0 AS recall_at_10 FROM hits;
   

The result is a single number between 0 and 1. Record it at build time as the baseline you approved. If you prefer a packaged tool, pg_recall_guard does the same job across HNSW, IVFFlat and other index types by discovering indexes through the catalog rather than naming extensions (pg_recall_guard docs).

Decision rule, which is the part most teams lack:

• Recall within 2 percentage points of baseline: no action, keep monitoring weekly.
• Recall 2 to 5 points below baseline: schedule a rebuild in the next maintenance window; investigate whether a large backfill or deletion batch caused it.
• Recall more than 5 points below baseline: rebuild now, raise hnsw.ef_search or ivfflat.probes as an interim mitigation, and check whether a queued job is rewriting vectors.

The interim mitigation deserves a mention because it is often mistaken for a fix. Raising ef_search widens the candidate set at query time and recovers some of the lost neighbors, but it cannot recover nodes that are no longer reachable in the graph or rows in an unprobed list. It trades latency for a partial recovery, and the drift keeps getting worse underneath.

Not every recall dip needs a rebuild, and not every rebuild needs the same scope.

When lighter repair is enough: a bulk delete that removed 30 percent of rows, followed by vacuum, often restores acceptable behavior, because the remaining graph still reflects the live data. If recall only recovered partway, the tombstones are the issue and a rebuild is due. Similarly, a one-time re-embedding of a small percentage of a corpus can be handled by updating rows and reindexing affected entries, then watching recall.

When a full REINDEX is required: any structural change to the index definition (a new operator class, a change from IVFFlat to HNSW, a different lists value), sustained churn over many weeks, or recall that stays below the 5-point threshold after vacuum.

Check whether a rebuild is due before committing to one:

SELECT pg_size_pretty(pg_relation_size('documents_embedding_hnsw')) AS index_size,
       (SELECT count(*) FROM documents) AS live_rows;

If the index is more than roughly 1.3 times the size you would expect from live rows and vector width, the structure is carrying dead weight that only a rebuild reclaims.

Partial repairs to consider first: if a table is partitioned by tenant or time, rebuild only the partition that received the heavy churn. Partitioning the vector table is itself the strongest maintenance lever, because it bounds the scope of every future rebuild (pgvector issue #822 describes a partitioned setup with about 40 million rows per partition). Reindexing one partition at a time also keeps each build short enough to fit a maintenance window.

The parameter changes matter for cost. Rebuilding with ef_construction at 64 builds much faster than the higher values used for a careful initial build, at some cost in graph quality, which is a reasonable trade for an index that will be rebuilt again after the next churn cycle (Crunchy Data).

PostgreSQL gives two paths, and the right one depends on your version and on whether you can tolerate a brief lock.

Path 1: REINDEX INDEX CONCURRENTLY. On PostgreSQL 12 and later, this rebuilds the index while reads and writes proceed. It is the simplest correct option:

REINDEX INDEX CONCURRENTLY documents_embedding_hnsw;

It requires more total work and disk space than a plain rebuild (the old and new copies exist side by side), and it cannot run inside an explicit transaction block. Run it from a job that can retry, because a failed concurrent reindex leaves an invalid index behind that must be dropped before retrying.

Path 2: shadow index and rename. This gives you control over when the switch happens, and it works on older versions:

BEGIN;
CREATE INDEX CONCURRENTLY documents_embedding_hnsw_shadow
  ON documents USING hnsw (embedding vector_cosine_ops);
-- verify recall against the shadow index here
DROP INDEX documents_embedding_hnsw;
ALTER INDEX documents_embedding_hnsw_shadow
  RENAME TO documents_embedding_hnsw;
COMMIT;

One caveat: CREATE INDEX CONCURRENTLY cannot run inside a transaction block, so split the script: build the shadow index outside a transaction, validate it, then run the drop and rename in a short transaction. The rename itself takes an exclusive lock only for an instant. Note that while the shadow index exists, writes are maintained in both indexes, which slows inserts during the overlap and doubles the disk requirement.

A scheduling checklist:

The worked example: a 10 million row table with 1,536-dimensional vectors, a 3.5 GB HNSW index, and 15 percent of rows deleted over a quarter. Index size reads 4.6 GB against roughly 3.0 GB of live data, and recall against 1,000 ground-truth queries has fallen from 0.96 to 0.87. The gap is above the 5-point threshold, so rebuild. With maintenance_work_mem at 8 GB and ef_construction at 64, the concurrent build finishes without spill notices, validation shows recall back at 0.95, and the swap takes a second.

Automation is where this becomes sustainable. If your pipeline already calls embedding models to generate the vectors being indexed, putting the embedding call and the maintenance job behind one interface reduces the number of moving parts: Quanta Inference exposes text, image, video, audio and embedding models through one OpenAI-compatible API and prices in US dollars, which keeps re-embedding costs predictable when you plan a rebuild that includes re-vectorization (https://quantaintelligence.ai/inference).

For teams that want the retrieval stack, the maintenance jobs and the swap automation built together rather than scripted by hand, a specialist can pick up the design and work through it with you (https://quantaintelligence.ai/).

A repeatable routine, ordered from cheapest to most disruptive:

1. Weekly: run the recall query above and write the result to a metrics table with a timestamp. Alert on a drop greater than 2 points.
2. Weekly: check bloat. Compare index size to live row count and alert when the ratio exceeds 1.3.
3. Monthly: review autovacuum logs for the vector tables. If vacuum runs rarely and bloat climbs, lower the scale factors again.
4. Quarterly, or on the recall threshold: run the shadow-index rebuild with validation, or REINDEX INDEX CONCURRENTLY if a brief lock is acceptable.
5. After any bulk operation: a large delete, a bulk re-embedding or a schema change, run recall immediately rather than waiting for the weekly job.

Two habits prevent most incidents. First, never run a bulk update that rewrites every vector in a large table without planning the rebuild that follows; the update leaves a graph built from the old vectors plus new nodes, which is exactly the state that degrades recall fastest. Second, keep the ground-truth table small and versioned. A thousand queries and their exact neighbors is a few megabytes and gives you a number that is comparable across rebuilds. Without that baseline, you are managing recall by feel.

The broader lesson from vector search practice is that indexing is a maintenance commitment, not a one-time setup step. Embedding-based retrieval captures semantic similarity by placing related items close in vector space, which makes the index structure carry real meaning; that structure deserves the same operational care as any other index whose freshness affects correctness (Elastic, Weaviate).