Concurrency in Postgres
summary
Explains exactly which SQL isolation levels map to which visibility rules and guarantees and shows the specific locks acquired on tables indexes rows transactions and query predicates so it is clear what conflicts can arise. Demonstrates a recursive SQL query that locates backends that block others without themselves being blocked outlines common race scenarios and fixes explains how Postgres 9.3 reduces foreign key locking pain and gives correct UPSERT patterns that are atomic.
AI-written summary · about
chapters (12)
- 0:00Start
- 4:42Locking (table-level locks)
- 5:38Locking (cont'd)
- 10:20Read committed (default)
- 11:51Repeatable read (cond)
- 13:27What's a write skew anomaly?
- 18:50Back to isolation levels: Serializable
- 22:47Business rule enforcing triggers
- 26:50What's a deadlock?
- 31:06More granular foreign key locks
- 41:09What's a WCTE
- 50:28Upsert thorny implementation details
related talks
41mConcurrency, Scalability and Transactions -- Myths and Surprises
39mAnatomy of Table-Level Locks in PostgreSQL
42mAnatomy of Table-Level Locks in PostgreSQL
47mAnatomy of concurrent data access in PostgreSQL
50mExplaining the PG concurrency control mechanisms using rollercoasters
48mUnderstanding and Reducing Application Contention
45mTransactions and Concurrency Control Patterns
47mMaximizing Database Performance With Isolation Levels