Understanding and Reducing Application Contention
summary
Explains what lock waits, deadlocks and serialization errors are, how to detect them with PG stat activity and pg_locks and by enabling log_lock_waits, and why long transactions and HA (prepared transactions, synchronous replication) enlarge contention windows. Recommends server‑side functions and batching tradeoffs, testing with realistic data and pgbench latency metrics, and highlights a performance comparison showing roughly 16000 vs 1600 transactions in the same 200ms bound for Postgres versus CockroachDB.
AI-written summary · about
chapters (22)
- 0:00Intro
- 2:55How do I know waits are happening now?
- 5:01How do I know lock waits happened?
- 7:01Batching Transactions
- 7:28High Availability
- 9:03Frequency of Contention
- 13:23Conversational Transactions (with Front End)
- 14:07Conversational Transactions (with App Tier)
- 14:45Server-side Procedures
- 15:58Testing for concurrency
- 18:45Deadlock detector
- 20:37How do I know deadlocks happened?
- 21:29Avoiding deadlocks
- 22:21Application Design Anti-Patterns
- 25:24Application Design Pattern Tuning (1a)
- 27:34Default Transaction Isolation
- 29:12Impacts of SERIALIZABLE Transaction Isolation
- 30:01Impacts of SERIALIZABLE failures
- 31:00Correctness of SERIALIZABLE retry
- 32:47e.g. CockroachDB Problems
- 34:34Postgres-BDR approaches to xact contention
- 36:08CockroachDB vs PostgreSQL Performance
related talks
04mRoad to Percona Live 2022 - Yahav Biran - PostgreSQL Talk
41mConcurrency, Scalability and Transactions -- Myths and Surprises
51mAvoid PostgreSQL LWLock:buffer_content locks in Amazon Aurora: Tips and best practices
30mQueues in PostgreSQL
32mMonitoring Waits in PostgreSQL
01h 00mThe 10 Deadly PostgreSQL Sins
35mUnderstanding and Optimizing commit sychronization for maximum performance
42mAnatomy of Table-Level Locks in PostgreSQL