Common Possible Preventative Measures to Avoid Locking Issue in PostgreSQL
summary
Main concrete rules: before PostgreSQL 11 adding a column with a DEFAULT rewrites the whole table and can block reads and writes for hours so avoid defaults on ALTER TABLE and instead add the column then UPDATE in small batches; always CREATE INDEX CONCURRENTLY to prevent write blocking; use set lock_timeout so DDL fails fast instead of piling into lock queues; minimize aggressive locks by populating a new table then RENAME and prevent deadlocks by ensuring transactions acquire row locks in a consistent order.
AI-written summary · about
chapters (7)
- 0:00Introduction
- 1:09Golden rule for columns
- 4:25Lock queues and timeouts
- 6:33Create indexes concurrently
- 8:19Delay aggressive locks
- 9:44Order commands to avoid deadlocks
- 12:10Q&A
related talks
39mAnatomy of Table-Level Locks in PostgreSQL
42mAnatomy of Table-Level Locks in PostgreSQL
40mrails db:migrate:safely
42mPostgreSQL is Nowhere Near Ready
30mQueues in PostgreSQL
57mPostgreSQL When It's Not Your Job
48mUnderstanding and Reducing Application Contention
42mPostgreSQL Replication Conflicts: Avoiding Pitfalls