Anatomy of Table-Level Locks in PostgreSQL
summary
Explains exact table-level modes like Access Share, Access Exclusive and shows how a 30-minute monthly SELECT can block an ALTER TABLE DETACH and queue dozens of simple reads behind one waiting DDL. Demonstrates practical fixes: use CONCURRENTLY where supported, split an ADD COLUMN NOT NULL DEFAULT into add column → batched UPDATE → VALIDATE CHECK USING NOT VALID then alter, and set lock_timeout. Describes the open-source tool pgroll using the expand/contract pattern to run migrations with overlapping schema versions and rollback capability.
AI-written summary · about
chapters (14)
- 0:00Introduction and speaker intro
- 1:07Speaker background and motivation
- 6:28MVCC and PostgreSQL concurrency
- 12:28Why locking is unavoidable
- 13:52Focus on DDL locks
- 15:16Table lock modes overview
- 18:12How PostgreSQL lock queue works
- 20:59Lock contention example
- 23:32Simple techniques to reduce lock contention
- 27:44Breaking DDLs into smaller steps
- 31:35Modern PostgreSQL optimizations
- 33:22Introducing PG Roll tool
- 40:11PG Roll features and benefits
- 41:38Q&A and closing remarks
related talks
39mAnatomy of Table-Level Locks in PostgreSQL
37mCommon Possible Preventative Measures to Avoid Locking Issue in PostgreSQL
58mConcurrency in Postgres
48mUnderstanding and Reducing Application Contention
47mAnatomy of concurrent data access in PostgreSQL
53mFuture of table access methods
53mRow Level Security
21mAgents on GuardRails: Deterministic Safety for Database Operations