阅读中文版本

PostgreSQL Autovacuum Not Keeping Up — Bloat and XID Wraparound

Diagnose autovacuum lag by checking dead tuples, long transactions, and wraparound age, then tune thresholds and remove blockers.

psql -c "SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;"

psql -c "SELECT pid, state, xact_start FROM pg_stat_activity WHERE state <> 'idle' ORDER BY xact_start;"

Symptoms

When you hit “PostgreSQL Autovacuum Not Keeping Up — Bloat and XID Wraparound”, guessing wastes time. The guide below follows evidence: reproduce the symptom, run targeted diagnostics, identify the root cause, apply the fix, and verify it with concrete checks.

Observed symptoms include:

• Table disk usage grows while row counts stay flat
• Queries slow down on a table that only receives updates and deletes
• Logs warn “oldest xmin is far in the past” or float wraparound warnings

Reproduce and Diagnose

Reproduce the failure in isolation before changing any code. Work through the checks below in order; each one narrows the search space.

Step 1: Measure dead tuples and last vacuum

psql -c "SELECT relname, n_dead_tup, last_autovacuum FROM pg_stat_user_tables ORDER BY n_dead_tup DESC LIMIT 10;"

A large n_dead_tup with an old last_autovacuum confirms autovacuum is behind.

Step 2: Find long transactions blocking cleanup

psql -c "SELECT pid, state, xact_start FROM pg_stat_activity WHERE state <> 'idle' ORDER BY xact_start;"

A very old xact_start holds the horizon and prevents removing dead tuples.

Likely contributors, in order of frequency:

• A long-running or idle-in-transaction session holds back the xmin horizon
• Autovacuum thresholds are too high for a busy table, so it runs too rarely
• Heavy update/delete churn creates dead tuples faster than cleanup

Root Cause

PostgreSQL cannot remove a dead tuple that might still be visible to any active transaction, so the oldest running transaction defines the vacuum horizon. Long transactions plus high churn let dead tuples accumulate, inflating tables and pushing transaction-ID age toward wraparound.

The Fix

Eliminate long and idle-in-transaction sessions, tune per-table autovacuum thresholds and scale factors, schedule manual VACUUM for hot tables, and monitor the oldest xmin age.

Watch out: VACUUM FULL takes an exclusive lock and rewrites the table; use pg_repack for online bloat removal instead.

-- per-table autovacuum tuning for a hot table
ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.05,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_analyze_scale_factor = 0.02
);

-- check age toward wraparound
SELECT datname, age(datfrozenxid) AS xid_age FROM pg_database ORDER BY xid_age DESC;

-- manual cleanup of a hot table
VACUUM (VERBOSE, ANALYZE) events;

Verify and Prevent

After cleanup, confirm n_dead_tup drops and last_autovacuum is recent; verify the oldest xmin age stays well under the wraparound threshold.

Once verified, add a regression test that reproduces the original symptom so the failure cannot silently return.

This class of failure is usually environmental rather than pure logic, so capture the exact command, the environment variables, and the working directory in your bug report. Reproduce it in the smallest possible script, then widen the scope only once the minimal case is understood. A short written checklist for this failure mode saves hours the next time it appears.