ADD COLUMN is not always free, and the lock queue is what takes you down

A migration adds one nullable column to the orders table. It runs in 3ms in staging against 40,000 rows. In production it takes the site down for forty minutes.

The post-incident theory is that the table is too big to alter, which is wrong. The table has 200 million rows and the ALTER itself would have finished in under a millisecond. It never got to run.

What actually happened is that a reporting query had been running for thirty-eight minutes, the ALTER queued behind it, and every query that arrived afterwards queued behind the ALTER.

The rewrite that mostly does not happen any more

Half the advice on this topic is out of date, and it is worth knowing which half.

Adding a column with a default used to rewrite the entire table. Postgres had to visit every row and write the new value. On a large table that is minutes to hours with an exclusive lock held throughout, and it is where the general fear of ADD COLUMN comes from.

Postgres 11 changed this. A non-volatile default is stored once in the catalog and applied at read time for rows that predate the change. No rewrite. The operation is a catalog update and completes in milliseconds on any table size.

The word non-volatile is doing the work there.

-- Metadata only. Instant on any table size.
ALTER TABLE orders ADD COLUMN channel text DEFAULT 'web';

-- Also metadata only. now() is STABLE, evaluated once.
ALTER TABLE orders ADD COLUMN migrated_at timestamptz DEFAULT now();

-- Full table rewrite. random() is VOLATILE, so every row needs its own value.
ALTER TABLE orders ADD COLUMN bucket double precision DEFAULT random();

The third one looks almost identical to the first two and behaves completely differently. There is no warning.

MySQL got there differently. InnoDB has supported instant column addition since 8.0.12, initially only at the end of the row and from 8.0.29 at an arbitrary position. The useful part is that you can demand it:

ALTER TABLE orders ADD COLUMN channel VARCHAR(32) NOT NULL DEFAULT 'web',
    ALGORITHM=INSTANT;

If the operation cannot be done instantly, this errors instead of silently falling back to copying the table. Stating the algorithm explicitly turns a surprise into a failed migration, which is the outcome you want at 2am.

The lock queue is the actual hazard

ALTER TABLE needs an ACCESS EXCLUSIVE lock, the strongest one, which conflicts with everything including plain SELECT. It needs it even for a pure catalog change, because the catalog entry must not move under a running query.

If any transaction holds any lock on that table, the ALTER waits. That much is expected.

What is not expected is what happens to everyone else while it waits. Postgres does not let new lock requests jump the queue, because doing so would let a stream of short readers starve a writer forever. So the queue is ordered, and a waiting ACCESS EXCLUSIVE blocks every request behind it.

sequenceDiagram
    participant R as Reporting query
    participant A as ALTER TABLE
    participant Q as Normal queries

    R->>R: holds ACCESS SHARE, 38 minutes to go
    A->>A: requests ACCESS EXCLUSIVE, waits
    Note over A: Blocked by R
    Q->>Q: request ACCESS SHARE
    Note over Q: Blocked by A, not by R.<br/>A short read now waits 38 minutes.
    R-->>A: finally commits
    A->>A: runs in 0.4ms
    Q->>Q: unblocked

One long transaction plus one instant migration equals a full outage on that table. Neither component is slow. The interaction is.

This is also why the same migration is harmless in staging. Staging has no thirty-eight minute reporting query, no idle-in-transaction connection left open by a debugging session, and no autovacuum running in the anti-wraparound mode that refuses to yield.

lock_timeout turns the outage into a retry

The fix is small and almost nobody sets it by default.

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN channel text DEFAULT 'web';

Now the ALTER waits three seconds for the lock. If it cannot get it, it fails, releases its place in the queue, and nothing else was ever blocked. Retry in a minute and it will probably succeed, because the blocking query has finished or the traffic pattern has shifted.

The migration failing is a much better outcome than the migration waiting. A failed migration is a red pipeline. A waiting migration is an outage that looks like a database problem.

Worth pairing with a look at what actually holds locks that long. idle in transaction sessions are the most common culprit and the most avoidable, since they usually mean an application opened a transaction and then did something slow that was not database work. Setting idle_in_transaction_session_timeout closes that class of problem independently of migrations.

The operations that genuinely still rewrite

Not everything is metadata now, and it is worth knowing which operations scan or rewrite so you can plan them differently.

The NOT VALID row is the one that changes how you write migrations. Adding a check constraint marked NOT VALID takes a brief lock and skips verification. VALIDATE CONSTRAINT afterwards scans the table using a weaker lock that does not block writes. Two steps, neither of which blocks traffic, instead of one that does.

That gives the safe path for a NOT NULL column on a large table:

-- 1. Instant, nullable.
ALTER TABLE orders ADD COLUMN channel text;

-- 2. Backfill in batches, outside a single transaction.
--    Small batches so autovacuum keeps pace with the dead tuples.
UPDATE orders SET channel = 'web'
WHERE id BETWEEN ? AND ? AND channel IS NULL;

-- 3. Brief lock, no scan.
ALTER TABLE orders ADD CONSTRAINT orders_channel_not_null
    CHECK (channel IS NOT NULL) NOT VALID;

-- 4. Scans without blocking writes.
ALTER TABLE orders VALIDATE CONSTRAINT orders_channel_not_null;

The batched backfill matters for a reason beyond lock duration. A single UPDATE touching 200 million rows creates 200 million dead tuples in one transaction, and vacuum cannot reclaim any of them until it commits. That is a table that doubles in size and an autovacuum that spends the next day catching up, which then interacts with everything else on the instance.

The shape this fits into

Every step above is the same idea applied to schema: make the change in stages where each stage is independently safe, rather than one atomic step that is correct and unavailable. That is expand, migrate, contract, and it is the same reasoning behind routing traffic through a bridge layer during a system migration rather than cutting over and behind keeping a soft-deleted column’s partial index predicate written literally so the planner can match it.

What I would actually change tomorrow is smaller than any of that. Put SET lock_timeout at the top of every migration your tooling generates. It costs nothing when the lock is free, and on the day someone leaves a transaction open it converts a forty minute outage into a pipeline that needs re-running.