Lock-safe DDL
On This Lesson 4 topics
This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.
In this lesson
apply DDL with minimal locking.
Planned topics
- Set
lock_timeout(for example 3 s) before DDL so it fails fast instead of queueing; retry in a quiet moment. CREATE INDEX CONCURRENTLYavoids blocking writes (cannot run inside a transaction; may leave an invalid index on failure to clean up).- Add constraints in two steps:
ADD CONSTRAINT ... NOT VALID, thenVALIDATE CONSTRAINT(lighter lock). - Adding a column with a constant default is fast on modern PostgreSQL (11+); a volatile default (like
random()) rewrites the table (verify version before recording). - For
NOT NULLon a large table: add a check constraintNOT VALID, validate, then setNOT NULL(PostgreSQL 12+ can use the validated check to skip the scan).
Draft outline details
Demonstration
SET lock_timeout = '3s';
CREATE INDEX CONCURRENTLY idx_orders_customer ON orders (customer_id);
ALTER TABLE orders ADD CONSTRAINT fk_cust FOREIGN KEY (customer_id)
REFERENCES customer(id) NOT VALID;
ALTER TABLE orders VALIDATE CONSTRAINT fk_cust;Check your understanding
What does lock_timeout do for a migration?
Show answer
Makes it give up instead of waiting in the lock queue.
Practice
Apply this lesson with the module's lab, exercises and quiz.
Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.