Changing the shape while it is running

A schema change on a laptop is instant. The same change on a live table of fifty million rows can lock it for minutes, during which every query waits and the application times out.

The danger is the lock

Many changes take a lock that blocks reads and writes while the table is rewritten.

CREATE INDEX CONCURRENTLY in Postgres builds without blocking writes. It is slower and cannot run inside a transaction, and it is what you want on a live table.

The queue behind the lock

Worse than the lock itself is what happens behind it. A migration waiting for a lock sits in a queue, and every query arriving after it queues too, even ones that would not have conflicted. A change expected to take a moment can stall the entire application while it waits to start.

Setting a short lock timeout turns that into a failed migration rather than an outage, and a failed migration can be retried.

Expand and contract

The pattern that makes migrations safe is to never have the code and the schema disagree:

1. expand    add the new column, deploy code that writes both
                and reads the old one
2. backfill  fill the new column in batches, not one statement
3. switch    deploy code that reads the new column
4. contract  stop writing the old one, then drop it later

Four deployments where one seems possible. What it buys is that at every point the previous version of the code still works against the current schema, so a rollback is always available. Ship the schema change and the code change together and rolling back the code leaves it running against a schema it does not understand.

Backfilling

UPDATE orders SET new_col = old_col WHERE new_col IS NULL;

On fifty million rows this is one enormous transaction: it holds locks, grows the log, and if it fails at 90 percent it rolls back entirely. Do it in batches of a few thousand with a pause between them, and it becomes interruptible and resumable.