"PostgreSQL Autovacuum Tuning: Stop the Bloat"
"postgres autovacuum not keeping up? Learn to spot dead-tuple bloat with pg_stat_user_tables, tune scale factor and cost limits per table, and when to use VACUUM FULL."
Dead tuples keep piling up, disk usage keeps climbing while your row count barely moves — the classic sign that postgres autovacuum not keeping up with your write workload. Postgres's MVCC means every UPDATE and DELETE leaves a dead row version behind, and if the autovacuum daemon can't clean them as fast as your app creates them, the table bloats: bigger on disk, slower to scan, slower indexes. This guide shows how to detect bloat, find out *why* autovacuum is falling behind, tune it globally and per table, and when VACUUM FULL is (and isn't) the answer. Last updated: October 4, 2026 What you'll learn How dead tuples and bloat actually work in Postgres's MVCC model How to check table bloat with pg_stat_user_tables ( n_dead_tup ) and size queries Why autovacuum falls behind: scale factor, cost limits, long transactions The settings that matter for write-heavy tables, globally and per table VACUUM vs VACUUM FULL — and the safe online alternatives Postgres Dead Tuples Growing: How to Check Table Bloat in Postgres When you UPDATE a row, Postgres doesn't overwrite it — it writes a new row version and marks the old one dead. VACUUM reclaims dead versions so their space can be reused. Autovacuum is just an automatic VACUUM that kicks in when dead tuples exceed a threshold: trigger: n_dead_tup > autovacuum_vacuum_threshold + autovacuum_vacuum_scale_factor * reltuples Defaults: threshold 50, scale factor 0.1 (0.02 since PostgreSQL 17). So a 10M-row table gets vacuumed once it accumulates ~1M dead tuples. First, check the damage: -- Which tables have the most dead tuples right now? SELECT relname, n_live_tup, n_dead_tup, round(100.0 * n_dead_tup / NULLIF(n_live_tup + n_dead_tup, 0), 1) AS dead_pct, last_autovacuum, last_vacuum FROM…