Schema Migration Lock

An ALTER TABLE or other DDL statement takes an ACCESS EXCLUSIVE lock that conflicts with every other lock type, including a plain SELECT's ACCESS SHARE. Once that lock request is waiting, every later query on the table queues behind it too, so a DDL statement that is merely waiting, not yet running, is enough to take the table's entire traffic down.

PostgreSQL DDL statements take the most restrictive lock type, ACCESS EXCLUSIVE, which conflicts with all other lock modes. PostgreSQL's lock queue is first-in-first-out per table: once the DDL's lock request is queued, every subsequent request on that table, including plain SELECTs, queues behind it too, even while the DDL itself is still waiting and has not started running. The DDL is not skipped or deprioritized; it holds its place in line and blocks everything arriving after it for as long as it waits plus however long it then takes to run.

The failure mechanism: ALTER TABLE ADD COLUMN NOT NULL DEFAULT 'value' is issued on a table with 500 million rows while one long-running SELECT has held the table for 5 minutes. The ALTER waits for that SELECT to finish, and while it waits, every new SELECT, INSERT, UPDATE, and DELETE on the table queues behind it. The application's connection pool fills with queries waiting on the table lock; within roughly 60 seconds the pool is exhausted, and within roughly 120 seconds the feature area backed by that table is effectively down, entirely because of a DDL statement that has not even started executing yet.

PostgreSQL 11 removed the table rewrite for adding a column with a non-volatile default, but the ACCESS EXCLUSIVE lock is still acquired and must still wait for every active transaction on the table to finish first; a single long-running analytics query or a replica catching up can still queue the DDL for minutes. Table rewrites that are still required (adding NOT NULL without a default pre-PG11, changing a column's type, adding a constraint with validation against existing rows) on 100M+ row tables can take 30 to 60 minutes fully locked if not run through an online schema change tool.

Two PostgreSQL-specific escapes exist for the two different expensive parts of a migration, the lock wait and the row-by-row work, and they generalize the same idea: do the expensive part without holding ACCESS EXCLUSIVE, then take the exclusive lock only for a fast final step. CREATE INDEX CONCURRENTLY builds an index without taking ACCESS EXCLUSIVE, at the cost of 2 to 10x the build time and two full table scans instead of one; because of how it tracks visibility across those two scans it cannot run inside a transaction block at all, which matters because migration frameworks (Alembic, Flyway, Rails migrations) commonly wrap each migration in one transaction by default, so a CONCURRENTLY statement needs that wrapping explicitly disabled or the migration will fail outright before it even reaches the lock. Adding a constraint (CHECK or foreign key) as NOT VALID takes ACCESS EXCLUSIVE only briefly to record the constraint, deferring the expensive scan that checks existing rows against it to a separate VALIDATE CONSTRAINT statement, which takes only SHARE UPDATE EXCLUSIVE, a lock that does not block ordinary reads and writes. Both techniques split one expensive, fully-locking operation into a cheap locking step and an expensive non-locking step.

Lock queue priority does not exist in PostgreSQL: a DDL statement that has waited 10 minutes has no priority over a query that arrives in the 11th minute; both simply wait their turn in arrival order.