Understanding Autovacuum
summary
Explains exactly what autovacuum does: scans tables to add dead row space to the free space map, remove dead index entries, and replace old XIDs with a visibility map Frozen bit so wraparound never corrupts data. Shows tuning knobs (vacuum_cost_delay/limit, maintenance_work_mem, freeze_ ages) and emergency tools (vacuum full, pg_repack). Shares a Heap case where workload generates 500 GB of bloat in eight hours and fixing it required dropping autovacuum_naptime reduced from 60s to 1s* and increasing workers, costing heavy IO.
AI-written summary · about
chapters (16)
- 0:00Intro
- 0:44Overview
- 1:39What's an MVCC?
- 8:14VACUUM Problem Statement
- 13:04VACUUM FULL
- 14:34The XID Ring
- 20:02Perf-Improving Maintenance
- 25:11Tuning VACUUM
- 27:43Tuning Memory Use
- 29:21What's an I/O "point"?
- 32:35Row-Freezing Trade-Offs
- 41:58Row-Freezing TL;DR
- 44:00What tables should be autovacuumed?
- 46:38Autovacuum Pseudocode
- 49:15Problems Start With The Schema...
- 50:28Explosive Bloat
related talks
49mUnderstanding and Fixing Autovacuum
48mThe Good, The Bad, and The Bloated: An Autovacuum Story
32mOptimizing autovacuum: PostgreSQL’s vacuum cleaner
47mAutoVacuum - Tuning and Monitoring
59mHow Autovacuum Goes Wrong: And Can We Please Make It Stop Doing That?
49mA Detailed Understanding of MVCC and Autovacuum Internals in PostgreSQL 14
19mVacuuming Enhancements in PostgreSQL 18: Faster, Smarter, More Predictable
27mMaintaining Large Tables in PostgreSQL