LLM Best Practices

Autovacuum

2 min read

View markdown source

Overview

This page is the atomic definition. Postgres maintenance and tuning guidance lives at postgres. Autovacuum reclaims dead tuples left by MVCC updates and deletes and refreshes planner statistics, so query plans and disk usage stay healthy without manual VACUUM runs.

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;
  • 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).

Backlinks