Tuning Autovacuum And Bloat

Tuning Autovacuum and Bloat

Overview

UPDATE and DELETE leave dead row versions. Vacuum makes their space reusable and freezes old transaction IDs; it usually does not return table space to the filesystem. Tune per high-write table before dead tuples, index churn, or transaction-ID age becomes an incident.

Diagnose before rewriting

SELECT schemaname, relname, n_live_tup, n_dead_tup,
       last_autovacuum, autovacuum_count,
       last_autoanalyze, autoanalyze_count
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 20;

Statistics are estimates. Check write rate, vacuum progress, long-running transactions, replica feedback, and disk growth together. A long vacuum is not automatically unhealthy if it is making progress and transaction-ID age remains safe.

Always look for cleanup blockers: long-running transactions, abandoned idle in transaction sessions, old replication slots, and standby feedback. These can hold back the oldest removable row version even when autovacuum runs.

Start with per-table tuning

Large busy tables should not wait for a large fraction of all rows to change. A concrete starting point—not a universal optimum—is:

ALTER TABLE events SET (
  autovacuum_vacuum_scale_factor = 0.01,
  autovacuum_vacuum_threshold = 1000,
  autovacuum_vacuum_cost_limit = 2000
);

This requests vacuum after roughly 1% of estimated rows plus 1,000 changes and gives that table more work budget per cost-delay cycle. Measure I/O and vacuum duration, then tune one step at a time. On very large tables, derive the scale factor from the maximum dead tuples you can tolerate rather than copying a percentage.

Concrete parameter guidance, progress queries, and transaction-ID monitoring: reference/autovacuum-settings-and-wraparound.md.

Prevent wraparound

Measure both table and database age, and compare it with the configured setting:

SELECT c.oid::regclass, age(c.relfrozenxid) AS xid_age,
       current_setting('autovacuum_freeze_max_age')::bigint AS freeze_max_age
FROM pg_class AS c WHERE c.relkind IN ('r', 'm')
ORDER BY age(c.relfrozenxid) DESC;

SELECT datname, age(datfrozenxid) AS xid_age
FROM pg_database ORDER BY age(datfrozenxid) DESC;

autovacuum_freeze_max_age is the forced-autovacuum trigger horizon, not the shutdown boundary. Alert before it so there is time to remove blockers and let vacuum finish. Do not infer wraparound safety from dead-tuple counts.

Treat rapidly rising age, repeated canceled anti-wraparound vacuums, or blockers older than the vacuum horizon as urgent. Never terminate backends blindly as the first response. Identify the old transaction, slot, or standby-feedback blocker, follow incident procedures, and let vacuum finish; do not casually raise freeze limits.

Remediate bloat safely

• Normal VACUUM reuses space inside the relation and permits ordinary reads and writes.
• Table rewrites can return space to the filesystem but require extra disk and stronger locking. Use a carefully rehearsed online rewrite tool such as pg_repack when its prerequisites and operational tradeoffs are acceptable.
• VACUUM FULL takes an ACCESS EXCLUSIVE lock for the rewrite; avoid it on a live table unless downtime is intentional.
• Rebuild a bloated index with REINDEX INDEX CONCURRENTLY; follow writing-safe-migrations for lock timeouts and concurrent-operation failure handling.

For a bloated index on a heavily updated table, REINDEX INDEX CONCURRENTLY fixes the symptom; treat under-tuned per-table autovacuum as an underlying cause that must be corrected to prevent recurrence. Check that vacuum keeps pace, then apply concrete table settings such as autovacuum_vacuum_scale_factor = 0.01, autovacuum_vacuum_threshold = 1000, and autovacuum_vacuum_cost_limit = 2000, measuring and adjusting for the table. Also inspect update patterns and fillfactor for additional causes.

Keep index and table remedies distinct: use concurrent reindexing for the index; use pg_repack when the table itself must be rewritten online. Never substitute VACUUM FULL on a live table—it takes ACCESS EXCLUSIVE.

Common Mistakes

• Applying one aggressive cluster-wide setting instead of targeting the tables producing churn.
• Treating n_dead_tup as exact or using one snapshot without a rate.
• Canceling a long anti-wraparound vacuum repeatedly.
• Ignoring long-lived or idle-in-transaction sessions that hold back cleanup.
• Expecting normal vacuum to shrink the relation file.
• Running VACUUM FULL as routine maintenance on a live table.