The Database Migration That Locked the Table for Nine Minutes

The migration was one line.

ALTER TABLE orders ADD COLUMN region text NOT NULL DEFAULT 'IN';

On the staging database with 40,000 rows it completed in 12 milliseconds. On production with 48 million rows it took nine minutes, during which every read and write to orders blocked, which meant checkout was down.

Why it was fast on some versions and not others

Adding a column with a default used to require rewriting the entire table, because every existing row needed the new value written into it.

Postgres 11 changed this. A default that is a constant is stored in the catalog and applied virtually to existing rows, so the operation is metadata only and instant regardless of table size.

The staging database was Postgres 16. So was production. That was not the difference.

The difference was NOT NULL combined with something else in the same statement. The actual migration file, which I had not read carefully, was:

ALTER TABLE orders
  ADD COLUMN region text NOT NULL DEFAULT 'IN',
  ALTER COLUMN status TYPE varchar(32);

The second clause was the problem. A type change requires a full table rewrite, and both clauses run under a single ACCESS EXCLUSIVE lock. The fast operation inherited the slow one's lock duration.

The lock is the thing to understand

ALTER TABLE takes an ACCESS EXCLUSIVE lock, which conflicts with everything including plain SELECT.

Worse, lock requests queue. If a long running query holds an ACCESS SHARE lock and your ALTER TABLE requests ACCESS EXCLUSIVE, your migration waits. And every query that arrives after it queues behind your migration, even though those queries would not have conflicted with the original one.

So a five second ALTER TABLE behind a two minute analytics query blocks the table for two minutes and five seconds, and everything piling up behind it.

This is the failure mode that surprises people most: the migration itself is fast and the outage is long.

Set a lock timeout so you fail rather than queue:

SET lock_timeout = '3s';
ALTER TABLE orders ADD COLUMN region text;

If the lock cannot be acquired in three seconds, the statement errors and nothing is blocked. Retry later. This one setting turns a potential outage into a failed migration you can rerun.

What is safe and what is not

On PostgreSQL 16, roughly:

Safe, metadata only:

• ADD COLUMN with no default, or with a constant default
• DROP COLUMN, which marks it dead rather than rewriting
• ADD CONSTRAINT ... NOT VALID, then VALIDATE CONSTRAINT separately
• CREATE INDEX CONCURRENTLY
• ALTER COLUMN ... DROP NOT NULL
• Renames

Requires a full rewrite:

• ALTER COLUMN ... TYPE, except for a few widening cases
• ADD COLUMN with a volatile default such as a function call
• SET NOT NULL on an existing column, which requires a full scan though not a rewrite
• Changing a column's collation

Takes a long lock without rewriting:

• CREATE INDEX without CONCURRENTLY, which blocks writes for the duration
• VALIDATE CONSTRAINT, which takes a weaker lock but still scans

The safe list is longer than most people assume, and the trap is combining a safe operation with an unsafe one in the same statement.

The patterns that work

Add a column safely

-- 1. add nullable, instant
ALTER TABLE orders ADD COLUMN region text;

-- 2. backfill in batches, no long lock
UPDATE orders SET region = 'IN'
WHERE region IS NULL AND id IN (
  SELECT id FROM orders WHERE region IS NULL LIMIT 5000
);
-- repeat until zero rows affected

-- 3. add the constraint without a full scan
ALTER TABLE orders ADD CONSTRAINT orders_region_not_null
  CHECK (region IS NOT NULL) NOT VALID;

-- 4. validate with a weaker lock
ALTER TABLE orders VALIDATE CONSTRAINT orders_region_not_null;

NOT VALID adds the constraint immediately and enforces it for new rows only. VALIDATE then scans existing rows under a SHARE UPDATE EXCLUSIVE lock, which does not block reads or writes.

Four steps instead of one, and no downtime.

Batch the backfill properly

DO $$
DECLARE
  updated int;
BEGIN
  LOOP
    UPDATE orders SET region = 'IN'
    WHERE id IN (SELECT id FROM orders WHERE region IS NULL LIMIT 5000);
    GET DIAGNOSTICS updated = ROW_COUNT;
    EXIT WHEN updated = 0;
    COMMIT;
    PERFORM pg_sleep(0.1);
  END LOOP;
END $$;

The COMMIT inside the loop matters. A single transaction updating 48 million rows holds locks for the duration, generates enormous WAL, and blocks vacuum from cleaning up. Batching with commits keeps each transaction short.

The pg_sleep gives replication a chance to keep up. On a system with read replicas, a fast backfill can push replication lag into minutes, which is its own incident.

Change a column type without a rewrite

-- 1. new column
ALTER TABLE orders ADD COLUMN status_new varchar(32);

-- 2. keep both in sync
CREATE FUNCTION sync_status() RETURNS trigger AS $$
BEGIN NEW.status_new := NEW.status; RETURN NEW; END $$ LANGUAGE plpgsql;

CREATE TRIGGER orders_sync_status BEFORE INSERT OR UPDATE ON orders
  FOR EACH ROW EXECUTE FUNCTION sync_status();

-- 3. backfill in batches
-- 4. deploy application code reading status_new
-- 5. drop the old column and the trigger

Slow and tedious and it is how you change a column type on a large table without downtime.

Indexes

CREATE INDEX CONCURRENTLY idx_orders_region ON orders (region);

CONCURRENTLY does not block writes. It takes longer and it cannot run inside a transaction, which means most migration frameworks need to be told to run it outside one.

If it fails partway you are left with an invalid index that has to be dropped:

SELECT indexrelid::regclass FROM pg_index WHERE NOT indisvalid;
DROP INDEX CONCURRENTLY idx_orders_region;

Checking for invalid indexes after a failed migration is worth adding to your runbook, because an invalid index is not used by the planner and consumes write overhead.

Deploy order

The other half of this problem. Your migration and your application deploy are not simultaneous, so both versions of the code must work with both versions of the schema.

Expand and contract is the pattern:

1. Expand. Add the new column, nullable. Old code ignores it.
2. Deploy code that writes both old and new.
3. Backfill existing rows.
4. Deploy code that reads new.
5. Contract. Drop the old column, in a later release.

Five deploys to rename a column. That is genuinely the cost, and the alternative is downtime.

The step people skip is the gap between 4 and 5. Dropping the old column in the same release as the code that stopped using it means a rollback breaks, because the previous version still expects the column. Leave at least one release between them.

What I do now

Read the generated migration before running it. ORMs produce migrations that combine operations, and the combination is frequently more dangerous than any single part. This is what I failed to do.

Set lock_timeout and statement_timeout in every migration. Failing fast is always better than queueing.

SET lock_timeout = '3s';
SET statement_timeout = '30s';

Test against production sized data. A migration tested on 40,000 rows tells you nothing about 48 million. Restore a production snapshot to a scratch database and time it. This is the single most valuable check and it is skipped almost universally.

Use a linter. Tools like squawk analyse migration SQL and flag unsafe operations before they merge. A CI check catching ALTER COLUMN TYPE on a large table is cheaper than an incident.

Watch replication lag during backfills, and pause if it grows.

Have a kill switch. Know how to cancel a running migration:

SELECT pg_cancel_backend(pid) FROM pg_stat_activity
WHERE query LIKE 'ALTER TABLE orders%';

pg_cancel_backend first, pg_terminate_backend only if that does not work. Knowing this before you need it is the difference between a two minute incident and a nine minute one, which is roughly the difference this cost us.