Problem in PostgreSQL SLRU - And how we are optimizing it
summary
Identifies severe SLRU contention where a 64-entry subtransaction cache overflow plus a long‑running transaction makes pgbench on a 128‑core machine fall from ~38,000 TPS to ~18,000 and then near zero; generating many multi‑xacts also drives vacuum and table bloat. Implements a configurable SLRU split into associative buffer banks (small fixed bank size, e.g., 16 slots), per‑bank locks and per‑bank LRU counters in PG 17. Tests show the patch avoids freezes, reduces global lock and cacheline churn, and yields 2–3x performance improvements under the problematic workloads.
AI-written summary · about
chapters (11)
- 0:00Introduction and agenda
- 1:40SLRU system design
- 4:22Identifying design problems
- 7:55Subtransaction cache overflow
- 11:51Snapshot and visibility analysis
- 19:14Multixact contention
- 24:00Multixact SLRU mechanics
- 28:35Proposed optimization strategies
- 35:04Implementing bank-based design
- 40:03Performance test results
- 43:59Summary and Q&A
related talks
31m5 Wrong Hypotheses about PostgreSQL Multi-Transaction Locks
01h 07mNavigating Challenging Customer Escalations in Postgres
56mPerformance Improvements in PostgreSQL9 2
25mScaling PostgreSQL Beyond Query Optimization
28mFast-path locking improvements in PG18
48mModern SQL features in PostgreSQL
51mAvoid PostgreSQL LWLock:buffer_content locks in Amazon Aurora: Tips and best practices
43mPostgres Performance: From Slow to Pro