Concurrency Deep-Dive
summary
Postgres uses MVCC with per-row Xmin/Xmax transaction IDs (32-bit, monotonically increasing) and compare-and-swap to expire versions, so reads never block writes and vice versa. Autovacuum (introduced in Postgres 9) reclaims dead row versions or databases run out of disk and risk XID wraparound outages (real incidents like Sentry/Joyent). Defaults use read committed; enable serializable to avoid serialization anomalies but expect aborts and retries. Use ON CONFLICT for idempotent inserts.
AI-written summary · about
chapters (21)
- 0:00Introduction to concurrency
- 2:02Acid transaction basics
- 5:10Consistency and durability
- 7:07Concurrency race conditions
- 8:44Database locking mechanisms
- 11:06Timestamp ordering
- 14:01MVCC explained
- 17:19MVCC data updates
- 18:33Row version internals
- 22:04Vacuum process overview
- 24:54Avoiding transaction wraparound
- 26:58Configuring autovacuum
- 28:51Isolation levels
- 30:40Read committed isolation
- 32:37Repeatable read anomalies
- 35:50Serializable isolation
- 38:53Isolation level summary
- 40:09Advanced locking and monitoring
- 41:45Deadlock handling
- 43:31Upsert handling
- 45:18Q&A and concluding remarks
related talks
45mTransactions and Concurrency Control Patterns
49mA Detailed Understanding of MVCC and Autovacuum Internals in PostgreSQL 14
50mExplaining the PG concurrency control mechanisms using rollercoasters
28mUnderstanding Transaction Isolation for App Developers
48mInside Postgres: why VACUUM matters
50mWe have multiple concurrent versions of this title trying to understand MVCC
27mImplementing Strict Serializability with pg_xact
31m5 Wrong Hypotheses about PostgreSQL Multi-Transaction Locks