What does VACUUM do, and how does autovacuum work?

Summary

VACUUM marks space from dead tuples as reusable, updates the visibility map (enabling index-only scans) and freezes old transaction IDs. Autovacuum runs it automatically when a table's dead tuples exceed a threshold, and also runs ANALYZE to refresh planner statistics.

Longer Answer

Plain VACUUM does not shrink the file; it makes space reusable inside it. VACUUM FULL rewrites the table compactly but takes an ACCESS EXCLUSIVE lock, blocking all access; pg_repack does the same online.

Autovacuum triggers when dead tuples exceed autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor x rows (defaults 50 + 20%). On large, busy tables 20% is far too late, so lower the scale factor per table, and give autovacuum more throughput (autovacuum_vacuum_cost_limit, more workers). Autovacuum is blocked from cleaning tuples still visible to any open transaction, so long transactions and abandoned replication slots defeat it.

Code Example

ALTER TABLE events SET (autovacuum_vacuum_scale_factor = 0.01, autovacuum_analyze_scale_factor = 0.02);

SELECT relname, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;

Key Points

• Reclaims dead tuples for reuse; freezes XIDs.
• VACUUM FULL locks; pg_repack is online.
• Tune per-table scale factors on big tables.
• Long transactions block cleanup.