tuning-autovacuum-and-bloatlisted

# Tuning Autovacuum and Bloat ## Overview Every `UPDATE`/`DELETE` leaves the old row version on disk as a **dead tuple**. Autovacuum reclaims dead tuples and manages transaction IDs. When writes outpace autovacuum you get bloat and, in the worst case, transaction-ID wraparound — a forced-downtime event. Tune proactively, not after it hurts. ## When to Use - Fast write workload where dead tuples may pile up. - Autovacuum queries running a long time (> ~1 hour is a warning sign). - Table or index disk usage growing faster than the data. - Preventing transaction-ID wraparound. ## Quick Reference | Problem | Cause | Fix | |---|---|---| | Dead tuples pile up | Writes outpace autovacuum | Tune autovacuum to run more aggressively | | Autovacuum runs > ~1h | Under-tuned settings | Retune ([Cybertec: tuning-autovacuum-postgresql]) | | **Txn ID wraparound** | IDs exhausted before reclaim | Catastrophic downtime — monitor & prevent | | Table bloat | Partly-filled 8KB pages | `pg_repack`; **avoid `VACUUM FULL`** (long lock) | | Index bloat | Same, on the index | `REINDEX INDEX CONCURRENTLY` | ## Monitoring Watch for long-running autovacuum before it becomes a problem: ```sql -- autovacuum workers running right now, oldest first SELECT pid, now() - xact_start AS running_for, query FROM pg_stat_activity WHERE query LIKE 'autovacuum:%' ORDER BY xact_start; ``` An autovacuum running longer than ~1 hour means your settings need tuning. ## Fixing Bloat - **Table bloat** comes from