tuning-autovacuum-and-bloatlisted

# 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 ```sql 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: ```sql 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