How Autovacuum Goes Wrong: And Can We Please Make It Stop Doing That?
summary
Explains why autovacuum repeatedly fails: the soft cap autovacuum_vacuum_cost_limit is the dominant cause of slow, starved and backlogged vacuums; raising autovacuum_max_workers often makes things worse because the budget is split. Shows concrete failure modes — useless vacuums held back by xmin, corrupted btree indexes, slow disks, and poor scale-factor behavior on multi‑TB tables — and proposes actionable fixes: auto‑tune cost limits and worker counts, a per‑database collector (using the new TID store) to predict XID/MXID wraparound and prioritize or skip vacuums, and move toward goal‑based completion.
AI-written summary · about
chapters (9)
- 0:00Introduction and overview
- 1:15Major failure modes of autovacuum
- 3:44Cost limits and their problems
- 15:28Slow and stuck vacuum cases
- 21:38Spinning and useless vacuuming
- 30:03Starved and skipped tables
- 35:09Trigger vs goal-based systems
- 38:14Improvement ideas summary
- 40:11Q&A session
related talks
55mUnderstanding Autovacuum
48mThe Good, The Bad, and The Bloated: An Autovacuum Story
49mUnderstanding and Fixing Autovacuum
32mOptimizing autovacuum: PostgreSQL’s vacuum cleaner
47mAutoVacuum - Tuning and Monitoring
19mVacuuming Enhancements in PostgreSQL 18: Faster, Smarter, More Predictable
49mA Detailed Understanding of MVCC and Autovacuum Internals in PostgreSQL 14
27mMaintaining Large Tables in PostgreSQL