Definition
Autovacuum is a set of background worker processes that PostgreSQL launches automatically to run VACUUM and ANALYZE without operator intervention. The autovacuum launcher wakes periodically (default: every 1 minute per autovacuum_naptime), checks pg_stat_user_tables, and starts a worker for any table that exceeds a threshold.
Vacuum trigger: autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * n_live_tup dead tuples.
Analyze trigger: autovacuum_analyze_threshold + autovacuum_analyze_scale_factor * n_live_tup changed tuples.
Default scale factors are 0.2 (20% of live rows). For large tables this means autovacuum is not triggered until millions of dead tuples accumulate. Lower the scale factor on large, high-churn tables.
Autovacuum can be blocked by long-running transactions that hold old snapshots, preventing cleanup of dead tuples newer than that snapshot. Monitor pg_stat_activity for long-lived idle-in-transaction sessions.
Autovacuum workers are throttled by autovacuum_vacuum_cost_delay and autovacuum_vacuum_cost_limit to avoid saturating I/O. On modern SSDs, these defaults are conservative; reduce the delay to let workers run faster.
When it applies
Enable autovacuum (it is on by default; never disable it in production). Tune per-table settings for tables with high churn or very large row counts. Monitor pg_stat_user_tables.last_autovacuum and last_autoanalyze; if these are hours old on a high-churn table, tighten the thresholds.
Example
SELECT relname, last_autovacuum, last_autoanalyze, n_dead_tup, n_live_tup
FROM pg_stat_user_tables
ORDER BY last_autovacuum NULLS FIRST;
ALTER TABLE events SET (
autovacuum_vacuum_scale_factor = 0.02,
autovacuum_analyze_scale_factor = 0.01,
autovacuum_vacuum_cost_delay = 2
);
SELECT pid, now() - query_start AS duration, query, state
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY duration DESC;Related concepts
- vacuum - the underlying operation autovacuum runs; read this first.
- query-plan - autovacuum ANALYZE keeps planner statistics current.
- sequential-scan - stale statistics from an un-analyzed table lead to bad plan choices.
- wal-mode - autovacuum generates WAL; heavy autovacuum adds WAL volume.
- postgres - the Postgres deep-dive.
Citing this term
See Autovacuum (llmbestpractices.com/glossary/autovacuum).