pgvector Index Management Embedding Pipeline Optimization

Autovacuum Tuning for pgvector Tables

This page shows how to tune per-table autovacuum settings for high-churn pgvector tables so dead tuples and tombstoned HNSW graph nodes are reclaimed promptly instead of accumulating into bloat. It scopes the problem narrowly: given an embedding table under frequent UPDATE/DELETE/re-embed load, how to set autovacuum_vacuum_scale_factor, cost limits, and insert-driven vacuum thresholds, then confirm the daemon is actually keeping n_dead_tup under control via pg_stat_user_tables.

Up: Monitoring Vector Indexes with pg_stat Views

Vector tables punish default autovacuum settings. Postgres is MVCC, so every re-embed of a row writes a new tuple version and leaves the old one dead; because an HNSW index also holds a graph node per tuple, a dead heap tuple leaves a tombstoned node in the graph whose space is only reclaimed when VACUUM runs. The server-wide default autovacuum_vacuum_scale_factor of 0.2 waits until a fifth of a large table is dead before acting — far too lax when each dead row also bloats a multi-gigabyte index. Tightening these per-table keeps the index dense and preserves the recall you tuned for, and it directly reduces the bloat measured in detecting HNSW index bloat with pgstattuple.

Prerequisites

  • PostgreSQL 15+ with autovacuum enabled globally (autovacuum = on, the default). PG13+ is required for autovacuum_vacuum_insert_scale_factor.
  • A pgvector table under measurable churn — re-embedding, deletes, or frequent upserts — with an HNSW or IVFFlat index on it.
  • SELECT on pg_stat_user_tables and the ability to ALTER TABLE ... SET (...) storage parameters (table owner or superuser).
  • A rough sense of the table’s row count and daily change rate, which also drives the footprint math in pgvector storage overhead analysis.

The autovacuum cycle for a churned vector table

How re-embedding creates dead tuples that autovacuum reclaims A left-to-right cycle of four boxes. First, an UPDATE or re-embed writes a new tuple version and tombstones the old HNSW graph node. Second, dead tuples accumulate and n_dead_tup rises. Third, the threshold of scale_factor times reltuples plus the base threshold is crossed. Fourth, autovacuum runs, reclaiming dead tuples and index free space, and the table returns to a dense state. An arrow loops from the last box back to the first. Dead tuples in, dense index out 1 · RE-EMBED New tuple written old graph node tombstoned 2 · ACCUMULATE n_dead_tup rises index space wasted 3 · THRESHOLD scale_factor × rows + base threshold crossed 4 · VACUUM Reclaim dead tuples index space freed table returns to a dense state · cycle repeats
Each re-embed leaves a dead tuple and a tombstoned graph node; aggressive autovacuum closes the loop before bloat compounds.

Step-by-step procedure

1. Measure the current dead-tuple pressure

Read pg_stat_user_tables to see how many dead tuples the table carries and when autovacuum last touched it. A large n_dead_tup with an old last_autovacuum means the defaults are too lax.

SQL
SELECT
    relname,
    n_live_tup,
    n_dead_tup,
    round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct,
    last_autovacuum,
    autovacuum_count
FROM pg_stat_user_tables
WHERE relname = 'doc_chunks';

2. Set an aggressive per-table vacuum threshold

Lower autovacuum_vacuum_scale_factor so vacuum triggers at a small fraction of dead rows, and raise the cost limit so the worker actually finishes promptly instead of being throttled. These are storage parameters, so they attach to the table and override the server defaults.

SQL
ALTER TABLE doc_chunks SET (
    autovacuum_vacuum_scale_factor  = 0.02,   -- vacuum at ~2% dead, not 20%
    autovacuum_vacuum_threshold     = 1000,   -- plus a flat floor
    autovacuum_vacuum_cost_limit    = 2000,   -- let the worker do more work per round
    autovacuum_vacuum_cost_delay    = 2       -- ms; small delay keeps it responsive
);

3. Trigger vacuums on insert-heavy growth

Append-only embedding ingestion produces few dead tuples but still needs vacuum to set the visibility map and freeze tuples. autovacuum_vacuum_insert_scale_factor (PG13+) triggers a vacuum after enough inserts, which keeps index-only scans viable and defers wraparound-freeze storms.

SQL
ALTER TABLE doc_chunks SET (
    autovacuum_vacuum_insert_scale_factor = 0.05,
    autovacuum_vacuum_insert_threshold    = 5000
);

4. Tune analyze for the planner

Vector queries still rely on the planner for the metadata predicates that filter alongside the similarity search. Keep statistics fresh with a matching analyze scale factor.

SQL
ALTER TABLE doc_chunks SET (
    autovacuum_analyze_scale_factor = 0.02,
    autovacuum_analyze_threshold    = 1000
);

5. Confirm the settings landed

Storage parameters live in pg_class.reloptions. Verify they applied before waiting on the daemon.

SQL
SELECT relname, reloptions
FROM pg_class
WHERE relname = 'doc_chunks';

Parameter reference

Per-table autovacuum storage parameters and recommended values for a high-churn vector table. Values are starting points; scale the thresholds to the table’s size and change rate.

Parameter Type Default Production recommendation Notes
autovacuum_vacuum_scale_factor float 0.2 0.01–0.05 Fraction of reltuples that must be dead before vacuum; the single most impactful knob.
autovacuum_vacuum_threshold int 50 1000–5000 Flat floor added to the scale-factor trigger; raise for large tables to avoid churn.
autovacuum_vacuum_cost_limit int -1 (uses global 200) 2000–3000 Higher limit lets the worker reclaim more per cycle before sleeping.
autovacuum_vacuum_cost_delay ms 2 1–2 Sleep between cost rounds; keep low so vacuum finishes on hot tables.
autovacuum_vacuum_insert_scale_factor float 0.2 0.05–0.1 PG13+; triggers vacuum on insert volume to set the visibility map and freeze.
autovacuum_vacuum_insert_threshold int 1000 5000–10000 Flat insert floor paired with the insert scale factor.
autovacuum_analyze_scale_factor float 0.1 0.02–0.05 Keeps planner statistics fresh for metadata predicates on vector queries.

Verification

After the settings apply, watch n_dead_tup fall and last_autovacuum advance over the next churn cycle.

SQL
SELECT relname, n_dead_tup, last_autovacuum, autovacuum_count,
       round(100.0 * n_dead_tup / nullif(n_live_tup + n_dead_tup, 0), 2) AS dead_pct
FROM pg_stat_user_tables
WHERE relname = 'doc_chunks';

Force one manual pass to establish the floor, then confirm dead tuples were reclaimed:

SQL
VACUUM (VERBOSE, ANALYZE) doc_chunks;

A dead_pct that stabilizes in the low single digits and an autovacuum_count that keeps incrementing confirm the daemon is now keeping pace with the churn.

Troubleshooting

  • n_dead_tup keeps climbing despite tight settings. Autovacuum is starving on workers — check autovacuum_max_workers and whether other large tables monopolize them. A per-table scale factor cannot help if no worker is ever assigned; raise the worker count or stagger heavy tables.
  • Vacuum runs but takes hours and never catches up. The cost limit is throttling it. Raise autovacuum_vacuum_cost_limit (or lower autovacuum_vacuum_cost_delay) for this table so the worker does more I/O per round before sleeping.
  • Dead tuples exist but cannot be removed. A long-running transaction or an abandoned replication slot is holding back the xmin horizon; VACUUM VERBOSE reports “dead tuples cannot be removed yet.” Find it via pg_stat_activity (oldest xact_start) and pg_replication_slots, and terminate or advance it.
  • Index bloat grows even though the table looks clean. Vacuum reclaimed heap tuples but the HNSW graph is still sparse from tombstoned nodes; confirm with pgstattuple and, if free_percent is high, rebuild via scheduling reindex concurrently without downtime.
  • Autovacuum fires far too often and steals I/O. The thresholds are too aggressive for this table’s real change rate. Raise autovacuum_vacuum_threshold and the scale factor slightly so vacuum triggers on genuine churn rather than trivial deltas.