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;