Avoid PostgreSQL LWLock:buffer_content locks in Amazon Aurora: Tips and best practices
summary
Shows a reproducible mitigation for LWLock:buffer_content contention on Aurora Postgres using a simulation (bash docker on Kubernetes) that mimics a last‑mile delivery workload at scales up to 96vCPUs, 768GiB, 30TB with ~10k commits and peaks at 50% CPU. Uses CloudWatch anomaly detection on db load non‑cpu and Performance Insights to find queries, then applies concrete fixes: drop unused/duplicate indexes, add partial indexes, partition large relations, move reads to replicas, and front with PG Bouncer / RDS Proxy; the suite reduces or removes the spikes.
AI-written summary · about
chapters (11)
- 0:00Start
- 2:01Workload simulation
- 4:30Findings
- 5:48What is PostgreSQL buffer content lock?
- 12:11Monitor buffer content locks
- 14:19Minimize buffer content locks
- 14:51Keep frequently accessed data optimal
- 26:45Implement connection pooling
- 28:53Minimize connection churn
- 43:29Limit the database connections
- 47:30Key takeaways
related talks
48mUnderstanding and Reducing Application Contention
43mTuning PostgreSQL for High Write Workloads
42mPostgreSQL Distributed: Architectures & Best practices
39mAWS Aurora
56mPostgreSQL at low Level: stay curious!
27mAnalyzing Postgres performance problems using perf and eBPF
48mProblem in PostgreSQL SLRU - And how we are optimizing it
43mPostgreSQL in the cloud: Theory and Practice