Why can a quick ALTER TABLE freeze every query on a busy table?
ALTER TABLE needs a strong lock that waits behind any running query on the table, and every new query then queues behind the ALTER. One long transaction can therefore stall the entire table. Set lock_timeout so the DDL gives up quickly.
Explain it simply
Picture a one-lane bridge. A heavy truck (a long SELECT) is on it. A road-roller (your ALTER TABLE) wants the whole bridge to itself, so it waits at the entrance. Now every bike and car arriving behind the roller must wait as well, even though the truck is the real cause. Soon the whole road is jammed.
Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock, which conflicts with everything, including plain SELECT. Lock requests are served in order, so later queries cannot overtake the waiting ALTER.
Example
SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN note text;
-- ERROR: canceling statement due to lock timeout (just retry later)
-- find who blocks whom
SELECT pid, pg_blocking_pids(pid) AS blocked_by, left(query, 50)
FROM pg_stat_activity
WHERE cardinality(pg_blocking_pids(pid)) > 0;Adding a nullable column without a default is metadata-only and fast once the lock is granted. The danger is the waiting, not the work.
What to say in the interview
"The ALTER waits for existing queries, and everything behind it queues. I set lock_timeout and retry, avoid running DDL while long transactions exist, and use pg_blocking_pids to find the culprit."
Common mistakes
- Assuming a metadata-only ALTER cannot cause an outage.