Auto-vacuum Tuning

Postgres-specific.

Why auto-vacuum tuning matters

Postgres writes generate dead tuples. Auto-vacuum reclaims space; un-vacuumed tables bloat. Bloat causes slow queries, oversized indexes, and eventually full-table rewrites.

Default auto-vacuum settings are conservative for general use. Production databases with high write rates often need aggressive tuning. Symptoms: monotonically growing table size, slow sequential scans, high autovacuum_workers.

Untuned auto-vacuum on a write-heavy workload produces 30-50% bloat. The cost compounds: slower queries, higher I/O, larger backups.

The key parameters

autovacuum_vacuum_scale_factor: 0.2 default; 0.05 for write-heavy tables. Triggers vacuum when 20% (default) or 5% of rows are dead. Lower = more frequent.

autovacuum_vacuum_threshold: 50 default. Minimum dead tuples before considering vacuum. Combined with scale_factor: trigger = threshold + scale_factor × table_rows.

autovacuum_max_workers: 3 default. Concurrent workers; raise to 6-10 for many active tables. Watch I/O bandwidth; workers compete.

Per-table tuning

ALTER TABLE foo SET (autovacuum_vacuum_scale_factor = 0.05). Per-table parameter overrides the global.

Hot tables (high update or delete rate) get aggressive tuning. Cold tables can use defaults.

Identify candidates with: SELECT relname, n_dead_tup, n_live_tup FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 20.

Monitoring vacuum activity

pg_stat_user_tables shows per-table vacuum stats: last_vacuum, last_autovacuum, n_dead_tup. Alert when last_autovacuum age exceeds threshold.

Bloat estimation queries (pgstattuple, or community queries) reveal physical bloat. Schedule monthly bloat reports.

Long-running vacuum can lock briefly. Monitor pg_stat_progress_vacuum for in-flight operations; investigate if any run > 30 min.

Emergency interventions

VACUUM FREEZE on a critical table to manually trigger. Blocks briefly but completes the work.

VACUUM FULL rewrites the table; locks it; reclaims all space. Maintenance window operation; not for production hours.

pg_repack: online table rewrite without long locks. Required for write-heavy production tables that need full vacuum effects without downtime.