VACUUM and Autovacuum in Plain English

Why dead tuples accumulate

PostgreSQL's MVCC design means every UPDATE creates a new row version and marks the old one as dead. Dead tuples can't just disappear — older transactions might still be looking at them. Once no transaction can possibly need them, they're garbage; VACUUM is the garbage collector.

What VACUUM actually does

Marks dead tuples as reusable space (regular VACUUM).
Updates the visibility map, which enables index-only scans.
Updates statistics if combined with ANALYZE.
VACUUM FULL rewrites the entire table to actually shrink it on disk — locks the table; rarely needed.

Autovacuum is the default and usually right

The autovacuum daemon wakes up periodically and vacuums tables when their dead-tuple ratio exceeds a threshold. For most workloads, leaving autovacuum at defaults is correct. The corrections come for hot tables where defaults under-vacuum (tune autovacuum_vacuum_scale_factor down per table).

How to spot vacuum problems

If a table's n_dead_tup in pg_stat_user_tables is in the millions, autovacuum isn't keeping up. Either the table is updated faster than autovacuum can clean, or autovacuum is being blocked by long-running transactions.

Code

Inspect dead-tuple counts·sql

SELECT relname, n_live_tup, n_dead_tup,
       round(100.0 * n_dead_tup / 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  20;

Manual vacuum (rarely needed in modern PG)·sql

-- Light vacuum: reclaim space, no lock
VACUUM (VERBOSE, ANALYZE) orders;

-- Aggressive: rewrite + shrink the table on disk
-- Holds an ACCESS EXCLUSIVE lock — never run on prod without a maintenance window
VACUUM FULL orders;

Tune autovacuum for a hot table·sql

-- Make autovacuum run when 5% of rows are dead, instead of the default 20%
ALTER TABLE hot_orders
SET (autovacuum_vacuum_scale_factor = 0.05,
     autovacuum_analyze_scale_factor = 0.02);

VACUUM and Autovacuum in Plain English

Why dead tuples accumulate

What VACUUM actually does

Autovacuum is the default and usually right

How to spot vacuum problems

Code

External links

Exercise

Progress

Comments 0