Fraught Feedback: Trying and Failing to Implement Adaptive Behavior in Postgres
summary
Attempts use a PID-style feedback controller to pace autovacuum by converting desired removal rates into per-page delays (example: 1,000 dead tuples/sec, ~40 tuples/page → 40 ms/page) but the prototype (CSV logging, hacky patch) runs into deadline, phase-1/phase-3 and multi-table scheduling problems and ends with delays often zero. Predictive work builds per-table age distributions and a target freeze duration but needs a full histogram in shared memory stats, proves complex, and yields a simple pragmatic fix: stop scanning all-visible pages when freezing fails.
AI-written summary · about
chapters (20)
- 0:00Introduction
- 1:56Why adaptive algorithms
- 4:24Types of adaptive algorithms
- 6:32Feedback controller for vacuum
- 10:06Deadline-based vacuum pacing
- 18:30Flexible vacuum rate control
- 21:22Testing the vacuum controller
- 24:07Challenges with evaluation criteria
- 26:58Is pacing the right approach
- 28:00XID wrap-around and freezing
- 29:50Current freezing strategies
- 32:02Predictive freezing proposal
- 36:00Rethinking freezing criteria
- 39:30Target freeze duration
- 41:39Hot tail workload results
- 43:04Insert-only table issues
- 45:50Complexity of histogram tracking
- 47:15Simpler freezing approach
- 48:39Lessons learned from failure
- 50:04Conclusion and Q&A
related talks
47mImproved Freezing in Postgres Vacuum: From Idea to Commit
19mVacuuming Enhancements in PostgreSQL 18: Faster, Smarter, More Predictable
47mAutoVacuum - Tuning and Monitoring
27mMaintaining Large Tables in PostgreSQL
32mOptimizing autovacuum: PostgreSQL’s vacuum cleaner
52mBulk data processing and PostgreSQL thingy
55mUnderstanding Autovacuum
33mPostgres Storytelling: Support in the Darkest Hour