Safe schema migrations and rollback

Why a migration breaks rollback

Rollback works on code because a deploy is just swapping one immutable artifact for the previous one — the old container image still exists, you point traffic back, done in seconds. The database has no previous artifact. It is one mutable, stateful thing, and a migration mutates it in place. Once DROP COLUMN legacy_name has run, “go back” has no meaning: the column and its data are gone, and even if you recreate the column, the rows are empty. The deploy and the schema change are coupled in the release, but only one half of that pair is reversible. So “just roll back the deploy” silently assumes the old code can still read the schema it finds — and a destructive change you shipped with the new code breaks exactly that assumption.

This is sharper than it looks because of how modern releases roll out. A rolling, canary, or blue-green deploy (covered last lesson) runs two versions of your code at the same time against one database. There is a window — sometimes minutes, sometimes the whole canary bake — where vN-1 and vN both serve live traffic. If vN’s migration changed the schema in a way vN-1 can’t tolerate, you’ve broken the still-running old version before you ever press rollback. The schema has to satisfy every version that is live, simultaneously.

Expand/contract: never change a column in one step

The discipline that makes this safe is parallel change, usually called expand/contract. You never mutate a column destructively in a single migration. You split the change across releases so that at every single moment, every version of the code that is running can read and write the schema as it currently is.

-- RELEASE 1, step EXPAND: additive only, runs BEFORE the new code deploys.
-- Old code ignores the new column; new code can start using it. Backward compatible.
ALTER TABLE users ADD COLUMN full_name text;        -- nullable, no rewrite
CREATE INDEX CONCURRENTLY idx_users_full_name ON users (full_name);

// RELEASE 1, app code: DUAL-WRITE — write both old and new on every mutation,
// and read from the old column still. Backfill existing rows in batches, not one UPDATE.
async function setName(id: string, name: string) {
  await db.query(
    `UPDATE users SET legacy_name = $2, full_name = $2 WHERE id = $1`,
    [id, name],
  );
}
// backfill job: copy existing data in bounded batches (e.g. 1k–10k rows per loop)
//   UPDATE users SET full_name = legacy_name
//   WHERE full_name IS NULL AND id IN (<next batch>);   -- repeat until none left

-- RELEASE 2: switch READS to full_name (code change only, no schema change).
-- RELEASE 3, step CONTRACT: drop the old column — ONLY after R2 is fully rolled out
-- and proven, when nothing reads legacy_name anymore. This is the one destructive step.
ALTER TABLE users DROP COLUMN legacy_name;

A rename is the canonical case, and it is not one RENAME COLUMN — it is add new column + backfill + dual-write + switch reads + drop old, spread across three or more releases. At no point does a running version meet a schema it can’t handle, so the previous artifact always still runs and rollback stays real the whole way through.

Ordering: additive before deploy, destructive after

Expand/contract dictates when each migration runs relative to the deploy, and getting this wrong is what re-creates the original incident. The additive migration (the expand) must run before the new code deploys — the column has to exist the instant new code starts, or new code crashes on a missing column. The destructive migration (the contract) must run after the new code is fully rolled out and you are confident you will not roll back — by then nothing reads the old column. Running the destructive change together with the deploy is precisely what makes rollback impossible, because the moment you roll the deploy back, the old code meets the schema the new code left behind.

Online DDL: the migration itself can take the table down

You’ve written an expand/contract sequence, the logic is sound — and then your CREATE INDEX on a 50-million-row table locks all writes for three minutes during the morning peak. The sequence was right; the mechanics were not.

Even an additive migration can cause an outage if the DDL locks the table. On Postgres, ADD COLUMN with a non-volatile default is fast and safe, but a plain CREATE INDEX takes a lock that blocks writes for the whole build — use CREATE INDEX CONCURRENTLY so writes keep flowing. Long ALTERs take an ACCESS EXCLUSIVE lock and, behind a queue of active queries, can stall the entire table under load. And the backfill must be batched — one giant UPDATE users SET full_name = legacy_name rewrites every row in a single transaction, holding locks, bloating the table, and running long enough to block autovacuum. Loop it in bounded batches of a few thousand rows instead, committing between them.

Forward-fix vs rollback

Because the database often can’t go back, the senior default for a bad release that carried a schema change is frequently forward-fix — ship a corrected version forward — rather than rollback. But that is only an option you keep open by making every migration backward-compatible in the first place: if every schema change is expand/contract, the previous artifact still runs against the new schema, so rollback also stays available as a genuine choice rather than a button that 500s production. Backward-compatible migrations don’t replace rollback with forward-fix; they make both safe so you can pick per incident.