5 Wrong Hypotheses about PostgreSQL Multi-Transaction Locks
summary
An index change triggers a vacuum ANALYZE and then the DB hits pathological SLRU aggregate accesses growth (millions/sec), locking up queries; killing long transactions or restarting helps briefly. Upgrading four major Postgres releases reduces SLRU hit rate; measurable limits appear — ~4,000 open txns, default multixact freeze age 400,000,000 wraps — so teams set autovacuum_multixact_freeze_max_age=1.5M and use manual VACUUM FREEZE plus alerts at 200k/sec to relieve pressure and keep Aurora Postgres stable.
AI-written summary · about
chapters (14)
- 0:00Introduction
- 2:46Multi-transaction locks
- 5:36First incident and hypothesis
- 8:04Root cause of lockups
- 11:11Escalation and failed fixes
- 14:02New hypotheses and experiments
- 16:30Reducing locks in production
- 18:16Seeking PostgreSQL expertise
- 19:59Upgrading and cache hits
- 21:46Load testing pressure points
- 23:10Hypothesis five breakthrough
- 25:17Auto vacuum freeze settings
- 27:02Learning from stability and new questions
- 29:09Key takeaways and call to action
related talks
48mProblem in PostgreSQL SLRU - And how we are optimizing it
45mMultiXacts in PostgreSQL: usage, side effects, and monitoring
33mPostgres Storytelling: Support in the Darkest Hour
25mScaling PostgreSQL Beyond Query Optimization
27mMaintaining Large Tables in PostgreSQL
33mPostgreSQL Observed—and Explained
47mImproved Freezing in Postgres Vacuum: From Idea to Commit
49mConcurrency Deep-Dive