PostgreSQL never overwrites a row in place. An UPDATE writes a new row version and marks the old one dead; a DELETE marks rows dead. This MVCC design lets readers and writers work without blocking each other — but it leaves dead row versions (“dead tuples”) behind. VACUUM cleans them up. Autovacuum does it automatically, and on busy tables it sometimes needs help.

What VACUUM does

  • Marks space from dead tuples as reusable for new rows (it doesn’t normally shrink the file).
  • Updates the visibility map, enabling fast index-only scans.
  • Freezes old transaction IDs to prevent transaction ID wraparound.
  • ANALYZE (often run together) updates statistics the query planner relies on.

If vacuuming falls behind, tables and indexes bloat: they grow with dead space, queries scan more pages, caches hold less useful data, and performance degrades.

Autovacuum

The autovacuum launcher starts workers when a table has accumulated enough changes:

vacuum threshold = autovacuum_vacuum_threshold (50) + autovacuum_vacuum_scale_factor (0.2) × rows

With the default 20% scale factor, a 100-million-row table waits for 20 million dead tuples before being vacuumed — far too late for a busy table.

Check what’s happening

Dead tuples and last vacuum per table:

SELECT relname, n_live_tup, n_dead_tup,
       round(n_dead_tup * 100.0 / nullif(n_live_tup + n_dead_tup, 0), 1) AS dead_pct,
       last_autovacuum, last_autoanalyze
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC LIMIT 15;

Running vacuum workers:

SELECT pid, relid::regclass, phase, heap_blks_scanned, heap_blks_total
FROM pg_stat_progress_vacuum;

Log autovacuum runs that take longer than a threshold:

log_autovacuum_min_duration = 1s

Tune autovacuum per table

For large, frequently updated tables, lower the scale factors:

ALTER TABLE orders SET (
  autovacuum_vacuum_scale_factor = 0.02,
  autovacuum_analyze_scale_factor = 0.01,
  autovacuum_vacuum_cost_limit = 2000
);

For append-only tables (logs, events), PostgreSQL 13+ also vacuums based on inserts (autovacuum_vacuum_insert_scale_factor), which keeps visibility maps current.

Global settings worth reviewing

autovacuum_max_workers = 3            # more on many-table databases
autovacuum_naptime = 30s              # how often to check (default 1min)
autovacuum_vacuum_cost_limit = 1000   # default 200 (via vacuum_cost_limit) is slow on SSDs
maintenance_work_mem = 512MB          # also used by vacuum (autovacuum_work_mem)

On SSD storage, the default cost limits throttle vacuum more than needed; raising them lets autovacuum keep up. Watch I/O while tuning.

What blocks vacuum

Vacuum can’t remove dead tuples that might still be visible to an open transaction. Common culprits:

  • Long-running transactions or sessions left “idle in transaction”:
SELECT pid, state, now() - xact_start AS xact_age, left(query, 60)
FROM pg_stat_activity
WHERE xact_start IS NOT NULL ORDER BY xact_start LIMIT 10;

Set idle_in_transaction_session_timeout to kill forgotten sessions.

  • Abandoned replication slots — check pg_replication_slots.
  • Long queries on standbys with hot_standby_feedback = on.

Manual VACUUM

VACUUM (VERBOSE, ANALYZE) orders;
ANALYZE;                         -- statistics only, all tables

Plain VACUUM runs alongside normal reads and writes.

VACUUM FULL vs pg_repack

When a table is badly bloated, plain VACUUM won’t return disk space. VACUUM FULL rewrites the table compactly — but takes an ACCESS EXCLUSIVE lock, blocking all reads and writes for the duration. On production, prefer pg_repack, which rebuilds tables and indexes online with only brief locks. For indexes alone, REINDEX INDEX CONCURRENTLY rebuilds without blocking.

Transaction ID wraparound

PostgreSQL uses 32-bit transaction IDs. Vacuum “freezes” old rows so IDs can be reused. If freezing falls behind for a very long time, PostgreSQL eventually refuses writes to protect data. Monitor:

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

Values approaching autovacuum_freeze_max_age (200 million by default) trigger aggressive anti-wraparound vacuums; values heading toward 2 billion are an emergency. Healthy autovacuum prevents this.

Summary

  • Leave autovacuum on — always.
  • Tune it to be more aggressive on big, busy tables.
  • Kill long idle transactions; remove unused replication slots.
  • Use pg_repack, not VACUUM FULL, to reclaim space on live systems.

Related: PostgreSQL performance tuning and PostgreSQL monitoring.

  • PostgreSQL
  • Performance
  • Maintenance