Which schema changes take heavy locks in PostgreSQL, and how do you avoid outages during migrations?

Summary

Many ALTER TABLE forms take an ACCESS EXCLUSIVE lock, which blocks reads and writes. Even a fast DDL statement can cause an outage if it waits behind a long query, because every new query then queues behind the waiting DDL. Set lock_timeout, retry, and use lock-friendly variants.

Longer Answer

Safe patterns:

• SET lock_timeout = '2s'; before DDL, and retry on timeout.
• CREATE INDEX CONCURRENTLY instead of CREATE INDEX.
• Adding a column with a constant default is instant since PostgreSQL 11 (no table rewrite).
• Adding NOT NULL or a foreign key: first add a CHECK (col IS NOT NULL) NOT VALID or the FK NOT VALID (fast), then VALIDATE CONSTRAINT (takes a weaker lock while scanning).
• Changing a column type usually rewrites the table; use expand-and-contract (new column, backfill, switch).

Tools such as squawk lint migrations for these problems.

Key Points

• ACCESS EXCLUSIVE blocks everything.
• Lock queueing turns a waiting DDL into an outage.
• lock_timeout + retry.
• CONCURRENTLY, NOT VALID + VALIDATE, instant defaults.

What interviewers look for

Understanding lock queueing. Many outages come from a 'fast' ALTER waiting behind one slow query.