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.

Read PostgreSQL 18 Routine Vacuuming for normative behavior.