PostgreSQL — Table Bloat: Autovacuum Cannot Keep Up

Diagnose PostgreSQL table bloat where dead tuples accumulate and autovacuum never completes, tune per-table autovacuum settings, and rebuild with pg_repack.

advanceddebuggingpostgresqlvacuumperformance

Symptoms

pg_stat_user_tables shows n_dead_tup in the tens of millions and rising on hot tables
Queries on the table slow down over days; counts and scans read dead tuples too
The last_autovacuum timestamp is hours old or never set, and vacuum runs get cancelled by lock conflicts

Possible causes

High UPDATE and DELETE rates produce dead tuples faster than default autovacuum thresholds trigger and complete
Long-running transactions hold the xmin horizon, making recent dead tuples ineligible for cleanup
Autovacuum gets cancelled repeatedly by DDL or lock waits and restarts from scratch each time
HOT updates are disabled by indexed columns receiving frequent updates

Diagnosis steps

Measure dead tuples and vacuum history

psql -c "SELECT relname, n_live_tup, n_dead_tup, last_autovacuum, autovacuum_count FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 5;"

Interpretation

n_dead_tup comparable to n_live_tup (or larger) with a stale last_autovacuum is the bloat signature.

Check the xmin horizon blockers

psql -c "SELECT pid, state, xact_start, backend_xmin FROM pg_stat_activity WHERE backend_xmin IS NOT NULL ORDER BY xact_start LIMIT 5;"

Interpretation

Transactions hours old pin the xmin horizon; nothing after their start is vacuumable regardless of autovacuum effort.

Estimate the bloat factor

psql -c "SELECT pg_size_pretty(pg_total_relation_size('events')); " and compare with count estimates

Interpretation

Disk size several times the live data size quantifies the bloat and the win from a rebuild.

Root cause

PostgreSQL keeps old row versions until every concurrent reader could have finished with them. Dead tuples accumulate when production outpaces cleanup: the vacuum horizon is pinned by long transactions, or autovacuum fires too late, runs too slowly, or is cancelled and restarts. The table then carries dead weight into every scan.

Fix

Kill or bound long transactions holding the xmin horizon, tune per-table autovacuum (lower thresholds, higher cost limit), enable HOT-friendly designs by un-indexing frequently updated columns, and reclaim existing bloat with pg_repack or VACUUM FULL in a maintenance window.

Risk note

VACUUM FULL takes an exclusive lock for the whole rewrite. Prefer pg_repack for production; reserve VACUUM FULL for windows you own.

Verify the fix

After the rebuild and tuning, n_dead_tup stays a small fraction of n_live_tup, last_autovacuum is recent, and table size stays stable across a week of production traffic.

Symptom walkthrough

The statistics view carries the diagnosis:

relname | n_live_tup | n_dead_tup | last_autovacuum
orders  | 12,402,118  | 9,871,335   | 2026-10-21 03:12:44

Nine million dead tuples against twelve million live — nearly half the table is corpse weight, and the last autovacuum predates the current bloat. Before blaming autovacuum settings, check who pins the horizon:

SELECT pid, state, xact_start, backend_xmin
FROM pg_stat_activity
WHERE backend_xmin IS NOT NULL
ORDER BY xact_start;

A session idle in transaction since yesterday makes every dead tuple after its start invisible to vacuum — no amount of tuning fixes that.

Fix in practice

Fix the horizon first: kill or timeout idle transactions (idle_in_transaction_session_timeout). Then tune the hot table specifically:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.02,   -- trigger at 2% dead, not 20%
  autovacuum_vacuum_cost_limit = 2000,     -- run faster when it does run
  autovacuum_vacuum_insert_scale_factor = 0.05
);

Reclaim the existing bloat online:

pg_repack -d shop -t events   -- rebuild without exclusive locks

And design for HOT updates — every indexed column you update forces new index entries, defeating the heap-only tuple path:

-- if a counter column carries an index but updates every write:
DROP INDEX events_status_count_idx;