Let's break autovacuum in Postgres: reproducing failures to make it observable
9 hours ago
- Autovacuum prevents bloat and transaction ID wraparound, but failures are silent and hard to monitor.
- Using raw dead tuple count is misleading; compute 'autovacuum pressure' (dead tuples / threshold) for a normalized signal.
- Coroot detects problems by combining pressure >2x and at least 512MiB of dead data, then identifies root cause via system views.
- Five common failure modes: autovacuum disabled on table, long transaction holding horizon, replication slot pinning horizon, all workers busy, vacuum throttled.
- Each failure has distinct detection fingerprints: reloptions, xmin age by holder, worker count, wait event.
- The tool provides specific findings with table name, cause, and fix, reducing guessing.
- The approach uses existing Postgres system views (pg_stat_user_tables, pg_stat_activity, pg_stat_progress_vacuum, etc.) with smart aggregation.