Postgres migrations that don't stall the table

· Maksym Lyskov

A migration can lock every reader out of a busy Postgres table while it waits for a lock it hasn’t even got yet. The DDL had no lock timeout, or a NOT NULL column went in as two statements, or an index got built inside a transaction that blocks writes until it’s done.

Each of those has a fix you can write as a rule. A coding agent can then check every migration it writes against the rules, before a person reviews it.

The table and column names are made up.

The stall starts in the lock queue

Almost every ALTER TABLE needs an ACCESS EXCLUSIVE lock. Nothing else can touch the table while it’s held, reads included.

Usually that’s fine, because the statement holds the lock for a few milliseconds. The trouble is getting the lock. If a slow report query is reading orders when your migration starts, the ALTER TABLE waits for it. And every query that arrives after the ALTER TABLE queues behind it, even the plain reads that would have run alongside the report.

The API times out, and the migration itself has done nothing yet.

The fix is two lines at the top of every migration:

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

With lock_timeout, a migration that can’t get its lock in 3 seconds fails and lets the queue drain. I’d much rather retry a failed deploy when traffic drops than get paged for a stalled table.

Two statements turn a free column into a full scan

Here’s a migration an agent writes all the time, because it reads well:

ALTER TABLE orders ADD COLUMN is_archived boolean DEFAULT false;
ALTER TABLE orders ALTER COLUMN is_archived SET NOT NULL;

Since Postgres 11, the first statement is metadata only. Postgres stores the constant default once and hands it to old rows when they’re read. No rewrite, no scan.

The second statement undoes that. SET NOT NULL has to prove that no row is null, so it scans the whole table, and it does that while holding ACCESS EXCLUSIVE. On a big table that’s a long time with nothing getting through.

Write it as one statement instead:

ALTER TABLE orders ADD COLUMN is_archived boolean NOT NULL DEFAULT false;

Postgres knows every row gets false, so there’s nothing to check. It’s metadata only again.

There’s one case where the one-statement form is unsafe. A volatile default (gen_random_uuid(), random(), clock_timestamp(), any function whose value changes per row) can’t be stored once, so Postgres rewrites the table to fill it in. now() is fine here: it’s stable, so Postgres evaluates it once and stores the result. For volatile defaults, the safe path takes more than one deploy:

  1. Add the column nullable, with no default.
  2. Backfill it in batches of a few thousand rows.
  3. Add CHECK (col IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT in a separate statement. Validation takes a weaker lock, so reads and writes keep going.
  4. Run SET NOT NULL. Postgres sees the valid check constraint and skips the scan.

An agent shouldn’t try to do this one on its own. It’s a plan across deploys, and a person should own it.

The batch holds every lock until the end

Many migration tools run all pending migrations inside one transaction, so a failure rolls back the whole batch. TypeORM does this by default (migrationsTransactionMode: "all").

A lock taken inside a transaction is held until the transaction commits. So the ALTER TABLE in the first migration takes 5 ms, and its ACCESS EXCLUSIVE lock stays on the table while the next four migrations run their backfills, until the last one commits.

If your tool runs the batch in one transaction, count each lock as lasting the whole batch, and keep slow backfills out of any deploy that also changes a busy table.

The index you can’t build concurrently

A plain CREATE INDEX blocks writes to the table for the entire build. On a large table that can be minutes.

CREATE INDEX CONCURRENTLY keeps writes going. But it can’t run inside a transaction block, and migration tools usually wrap each migration, or the whole batch, in one. In TypeORM, a migration opts out of the transaction with public transaction = false, and under migrationsTransactionMode: "all" the runner rejects that with ForbiddenTransactionModeOverrideError. The whole batch fails before any migration runs, and you find out at deploy time.

So an agent that “fixes” a slow index by adding CONCURRENTLY can break the deploy. The safe options:

  • Small table: build the index inline. The lock is short.
  • Large table: give the index its own deploy at a quiet hour, or change how the tool runs transactions. That’s a config change with its own risks, so it’s a person’s call.

Find out what your tool does before you write the rule. The rule for the agent is then simple: never add CONCURRENTLY unless the tool can run it outside a transaction.

Two more that rewrite the table

Two rarer ones do the same damage:

  • ALTER COLUMN ... TYPE rewrites the table under ACCESS EXCLUSIVE unless the old type converts to the new one without changing the stored bytes (varchar(50) to text is safe, integer to bigint rewrites). For the rewriting kind, add a new column, backfill it, switch the code, drop the old one.
  • A CHECK or foreign-key constraint added without NOT VALID checks every existing row under a strong lock. Add it NOT VALID, then VALIDATE CONSTRAINT separately.

The check the agent runs

All of this fits in a short instruction an agent runs on every migration it writes or changes, before the PR goes up. It can read like this:

Before you open a PR with a migration, check each migration file:

BLOCKER, fix it before the PR:
1. No SET lock_timeout and statement_timeout at the top of up().
   Add them: lock_timeout '3s', statement_timeout '30s'.
2. ADD COLUMN ... DEFAULT <constant>, then a separate SET NOT NULL.
   Merge them: ADD COLUMN ... NOT NULL DEFAULT <constant>.

BLOCKER, report it and don't rewrite it:
3. NOT NULL DEFAULT with a volatile default (gen_random_uuid(),
   random(), clock_timestamp(), ...). now() is fine.
4. ALTER COLUMN ... TYPE that rewrites a large table.
5. CREATE INDEX on a large table. Never add CONCURRENTLY while the
   migration tool runs all migrations in one transaction: it rejects
   the override.

WARNING:
6. A CHECK or foreign-key constraint added without NOT VALID.
7. UPDATE with no bounded WHERE.

Say which table each finding touches and whether that table is large.
End with SAFE TO DEPLOY or FIX REQUIRED.

List your large tables in that instruction. A size class per table is enough, since row counts go stale, and without the list the agent can’t tell which locks matter. It fixes the two mechanical blockers itself and reports the others to you, because each of those needs a plan across deploys.

Sources: the Postgres docs on ALTER TABLE, explicit locking, lock_timeout and CREATE INDEX, and TypeORM’s migrations docs.