PostgreSQL's MVCC (Multi-Version Concurrency Control) is what makes it possible to run complex queries without blocking writes. But MVCC has a cost: dead tuples. Every UPDATE creates a new version of the row and leaves the old version behind. Every DELETE marks a row as dead but doesn't reclaim the space. Without vacuuming, your tables grow without bound.
Autovacuum is PostgreSQL's built-in solution. It runs in the background, reclaiming dead tuples and updating statistics. But the default configuration is tuned for small databases with moderate write loads. If you're running a write-heavy production workload, the defaults will not keep up.
Understanding the Problem
Table bloat manifests in three ways. First, disk usage grows faster than your data. A table with 10GB of live data might consume 50GB on disk because of dead tuples. Second, query performance degrades because the executor has to scan through dead tuples to find live ones. Third, index bloat makes even index-only scans slower as the index grows to contain entries pointing to dead tuples.
The worst case is transaction ID wraparound. PostgreSQL uses 32-bit transaction IDs that wrap around at roughly 2 billion. When wraparound is imminent, PostgreSQL forces a full-table vacuum that blocks all writes. If your table is large and autovacuum hasn't been keeping up, this can mean hours of downtime.
Key Configuration Parameters
The most impactful parameter is `autovacuum_vacuum_scale_factor`. The default is 0.2, meaning autovacuum triggers when 20% of the table consists of dead tuples. For a table with 100 million rows, that's 20 million dead tuples before vacuuming starts. Set this to 0.01 (1%) for large tables, or use `autovacuum_vacuum_threshold` with a fixed number for very large tables.
Increase `autovacuum_max_workers` from the default of 3 to at least 6. Each worker can vacuum one table at a time, and if you have more than 3 tables accumulating dead tuples simultaneously, some will have to wait.
Reduce `autovacuum_vacuum_cost_delay` from 2ms to 0 (or at most 1ms) on dedicated database servers. This parameter throttles vacuum activity to reduce I/O impact, but on modern SSDs, the default is far too conservative.
Per-Table Tuning
Not all tables are equal. Your high-write tables (audit logs, event streams, session data) need more aggressive vacuuming than your relatively static reference tables. Use ALTER TABLE to set per-table autovacuum parameters. For our highest-write tables, we use: autovacuum_vacuum_scale_factor = 0.005, autovacuum_analyze_scale_factor = 0.005, and autovacuum_vacuum_cost_delay = 0.
Monitoring
Track `pg_stat_user_tables.n_dead_tup` for every table. Alert when dead tuples exceed a threshold relative to live tuples. Monitor `pg_stat_activity` for long-running autovacuum workers — if they're running for hours, they can't keep up. Track the oldest transaction ID via `age(datfrozenxid)` from `pg_database` and alert well before the wraparound danger zone.