PostgreSQL autovacuum and table bloat

Standard VACUUM does more than “free space”: it makes dead row versions reusable, maintains planner statistics and the visibility map, and prevents transaction ID/multixact wraparound. Most systems should leave autovacuum enabled.

Routine observation

SELECT
  schemaname, relname,
  n_live_tup, n_dead_tup,
  last_vacuum, last_autovacuum,
  vacuum_count, autovacuum_count,
  last_analyze, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 30;

Statistics are estimates and can reset, so one n_dead_tup threshold cannot prove bloat. Combine table size, update rate, query latency, autovacuum logs, and trends.

Inspect active vacuum work:

SELECT
  pid, datname, relid::regclass AS relation,
  phase, heap_blks_total, heap_blks_scanned, heap_blks_vacuumed,
  index_vacuum_count, dead_tuple_bytes, num_dead_item_ids,
  indexes_total, indexes_processed
FROM pg_stat_progress_vacuum;

These column names target PostgreSQL 18. Older majors can expose a different progress-view shape, so version-portable monitoring should inspect the target catalog first.

Why it does not trigger or keep up

• a large table makes the default scale factor translate into too many changed rows;
• workers, I/O capacity, or maintenance memory are insufficient;
• long transactions, prepared transactions, slots, or standby snapshots block reclamation;
• conflicting locks repeatedly cancel vacuum;
• sustained writes exceed cleanup capacity.

Override a measured hot table before making aggressive global changes:

ALTER TABLE app.events SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.01
);

These are examples, not universal values. Calculate expected trigger frequency from table size and daily changes, then observe I/O, WAL, latency, and completion time.

Manual maintenance boundary

VACUUM (ANALYZE, VERBOSE) app.events;

Plain VACUUM mainly makes space reusable inside the relation and normally does not shrink the file back to the operating system. VACUUM FULL rewrites the table, needs extra disk, and takes ACCESS EXCLUSIVE; it is not routine cleanup.

Measuring and repairing bloat

Dead-tuple ratios from pg_stat_user_tables are estimates. pgstattuple scans the relation and reports exact dead tuples and free space:

CREATE EXTENSION IF NOT EXISTS pgstattuple;

SELECT * FROM pgstattuple('app.events');
-- dead_tuple_count, dead_tuple_percent, free_space, free_percent

When space must be returned to the operating system, plain VACUUM is not enough and VACUUM FULL blocks reads and writes for the whole rewrite. The usual online options:

# pg_repack rebuilds the table in the background with only short locks
sudo -u postgres pg_repack -h db.example -U postgres -d commerce --table=events

-- Index-only bloat: rebuild one index without locking the table
REINDEX INDEX CONCURRENTLY app.events_pkey;

REINDEX CONCURRENTLY is built in since PostgreSQL 12. pg_repack is a third-party extension with its own release and operational track — install it, pin its version, and rehearse it separately from core features.

When VACUUM FULL appears stuck

It is almost always waiting for ACCESS EXCLUSIVE behind existing sessions. Identify the blockers before retrying:

SELECT a.pid, a.usename, a.state, a.wait_event, left(a.query, 160) AS query
FROM pg_stat_activity a
WHERE a.pid = ANY (pg_blocking_pids(12345));  -- pid of the VACUUM FULL session

Cancel or terminate the holders only after confirming they are disposable. Even when it runs, VACUUM FULL needs free disk roughly equal to the table size and rewrites every index — another reason to prefer pg_repack for routine space reclamation.

Transaction ID wraparound defense

Transaction IDs are 32-bit: after roughly two billion transactions, old tuples would appear to lie in the future, and PostgreSQL stops accepting writes before that can happen. Autovacuum prevents this by freezing old tuples so their IDs can be reused.

Track freeze age per database and per table:

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database
ORDER BY xid_age DESC;

SELECT n.nspname, c.relname,
       age(c.relfrozenxid) AS xid_age,
       pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class c
JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
  AND n.nspname NOT IN ('pg_catalog', 'information_schema')
ORDER BY xid_age DESC
LIMIT 20;

Common alert starting points: a database xid_age past one billion transactions deserves attention, and past 1.5 billion is an emergency — calibrate against your transaction rate. If autovacuum cannot freeze fast enough, force a freeze during a low-traffic window and watch it in pg_stat_progress_vacuum:

VACUUM (FREEZE, VERBOSE) app.events;

Omit the table name to freeze the whole database when database-level age is the problem.

Read PostgreSQL 18 Routine Vacuuming for normative behavior.