Boring migrations are the best migrations

Every team has a migration story that ends with someone awake at 2 a.m., watching a lock queue grow. The story is rarely about a clever mistake. It is almost always about one step doing too much at once.

Expand, then contract

The pattern that removes most of the risk has two halves. First you expand: add the new column, table, or index without touching anything that reads the old shape. Ship it. Later you contract: once nothing depends on the old shape, remove it.

-- Add the new column beside the old one
ALTER TABLE orders ADD COLUMN status_v2 text;

-- Build its index without blocking writes
CREATE INDEX CONCURRENTLY orders_status_v2_idx
  ON orders (status_v2);

Batching matters. A single UPDATE across a large table holds its locks for as long as it runs. Ten thousand rows at a time lets ordinary traffic slip through between batches.

“If a migration needs a maintenance window, it is really three migrations pretending to be one.

Write to both, read from one

While the backfill runs, new orders keep arriving. Until it finishes, the application writes both columns, so no row falls into the gap between the batch that already passed it and the deploy that reads the new one.

export async function setStatus(id: string, status: Status) {
  await db.orders.update(id, { status });
  // Write both columns until 0044 ships
  await db.orders.update(id, {
    status, status_v2: status,
  });
}

Reads stay on the old column until the backfill is done and a check confirms the two columns agree. Switching reads over is its own small deploy, which makes it its own small rollback.

See it end to end

This talk walks through the same expand and contract steps with pgroll, an open-source tool that keeps both schema versions live while a migration rolls out.

Play videoPlays on YouTube

The last step deserves a guard of its own. Before a contract step runs, the migration runner asks every deployed service whether anything still reads the column it is about to drop.

$ pnpm migrate up --env production✓ 0042_expand.sql     38 ms✓ 0043_backfill.ts    16m 04s, 4,812 batches■ 0044_contract.sql   held  api@1.18 still reads orders.status  2 applied, 1 held until api@1.19 is live.

Make every step reversible

Before merging, ask what you would do if this step had to be undone in five minutes. If the answer involves restoring from backup, split the step until the answer becomes “run the previous deploy.”

None of this is exciting, and that is the point. The best migration is the one nobody remembers, because it was a series of small, dull changes that each did exactly one thing.