Anatomy of Table-Level Locks in PostgreSQL
summary
Explains how PostgreSQL’s MVCC avoids row-level blocking but DDL takes table locks, with access exclusive conflicts everything illustrated by ALTER TABLE DETACH PARTITION blocking selects; recommends concrete tactics: use CREATE INDEX CONCURRENTLY, split ALTERs (add nullable column, batch UPDATE backfill, then SET NOT NULL), add a NOT VALID check constraint then VALIDATE to avoid full rewrites, and set lock_timeout for DDL with retry logic. Introduces pgroll implementing expand/contract multi-schema migrations, trigger-based backfills and easy rollbacks.
AI-written summary · about
chapters (10)
- 0:00Introduction and speaker intro
- 2:27MVCC concept in PostgreSQL
- 9:29Why table-level locks exist
- 12:37PostgreSQL lock modes explained
- 14:00Lock queue and timeouts
- 17:35Reducing lock contention techniques
- 24:55PostgreSQL improvements over versions
- 26:01Introducing PG Roll tool
- 34:26PG Roll features and benefits
- 35:50Q&A and future plans
related talks
42mAnatomy of Table-Level Locks in PostgreSQL
37mCommon Possible Preventative Measures to Avoid Locking Issue in PostgreSQL
47mAnatomy of concurrent data access in PostgreSQL
45mTransactions and Concurrency Control Patterns
50mExplaining the PG concurrency control mechanisms using rollercoasters
58mConcurrency in Postgres
48mUnderstanding and Reducing Application Contention
49mConcurrency Deep-Dive