Explore a comprehensive conference talk on understanding and mitigating application contention in PostgreSQL databases. Delve into the intricacies of database lock waits, deadlocks, and serializable errors, learning how to identify and resolve these issues. Discover practical strategies for reducing contention, including batching transactions, implementing high availability, and optimizing application design patterns. Gain insights into testing mechanisms for high transaction rates and understand the impacts of different transaction isolation levels. Compare approaches to transaction contention in various database systems, including PostgreSQL, CockroachDB, and Postgres-BDR. Equip yourself with valuable knowledge to enhance database performance and reliability in your applications.
Understanding and Reducing Application Contention - PGConf NYC 2021
Overview
Syllabus
Intro
How do I know waits are happening now?
How do I know lock waits happened?
Batching Transactions
High Availability
Frequency of Contention
Conversational Transactions (with Front End)
Conversational Transactions (with App Tier)
Server-side Procedures
Testing for concurrency
Deadlock detector
How do I know deadlocks happened?
Avoiding deadlocks
Application Design Anti-Patterns
Application Design Pattern Tuning (1a)
Default Transaction Isolation
Impacts of SERIALIZABLE Transaction Isolation
Impacts of SERIALIZABLE failures
Correctness of SERIALIZABLE retry
e.g. CockroachDB Problems
Postgres-BDR approaches to xact contention
CockroachDB vs PostgreSQL Performance
Taught by
Confreaks
