The full answer
The migration took a lock that blocked the application's queries and held it for a long time. Most ALTER TABLE forms take an ACCESS EXCLUSIVE lock, the one lock that blocks even plain SELECTs, and some of them rewrite the whole table while holding it. Safe migrations split the change into steps that only hold strong locks for an instant, and give up quickly if they cannot get the lock.
What usually causes it
| Change | Why it hurts on a big table |
|---|---|
ALTER COLUMN ... TYPE | Rewrites the table and its indexes under ACCESS EXCLUSIVE |
ADD COLUMN with a volatile default (DEFAULT clock_timestamp()), an identity or stored generated column | Rewrites the table |
ADD CONSTRAINT ... CHECK or SET NOT NULL | Scans every row while locked |
CREATE INDEX without CONCURRENTLY | Blocks all writes until the build finishes |
Adding a column with no default, or with a constant default, is fast: PostgreSQL stores the default in the table's metadata and rewrites nothing.
How to make the same changes safely
- Indexes:
CREATE INDEX CONCURRENTLY. It takes longer, cannot run inside a transaction block, and can leave an invalid index if it fails, which you then drop and retry. - Constraints: add them as
NOT VALID(instant), thenVALIDATE CONSTRAINTin a separate step, which only takes a lock that lets reads and writes continue. - Type changes: expand and contract. Add a new column, write to both from the application, backfill in small batches, switch reads, then drop the old column.
- Backfills: update in batches of a few thousand rows with commits in between, never one giant
UPDATE.
Guard every migration with a lock timeout
SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN channel text;
If a long-running query or an open transaction holds a lock on the table, the migration fails after 3 seconds instead of waiting indefinitely for it while the deploy hangs. Retry it a few times with a pause. Set it per migration session, not server-wide.
Find the blocker during an incident
Use pg_stat_activity and pg_blocking_pids(pid) to see who is waiting on whom, and the pg_locks view for the locks held. Usually it is either the migration itself or a forgotten transaction that the migration is waiting behind.
What interviewers want: knowing which changes take ACCESS EXCLUSIVE and which rewrite the table, CONCURRENTLY, NOT VALID plus VALIDATE, expand and contract for type changes, batched backfills, and lock_timeout on every migration.
Last updated 16 October 2026
Sources