Zero-Downtime Migrations and Safe Rollbacks: Evolving a Live Database

Database

PostgreSQL

Migrations

Production

2 hours ago2573 words·13 min read

In one sentence: the previous article showed you how to version schema changes — this one answers two tougher production questions. How do you change a schema without interrupting reads and writes at all? And once a migration has written bad data, how do you back out safely?

Reference the previous article in this series:

What You'll Get Out of This

By the end, you'll be able to:

Explain why even a "simple-looking" schema change can bring the whole service down.
Master the universal recipe for zero-downtime change — the expand/contract two-phase pattern — and the iron rule underneath it: at every moment, both the old and the new code must keep working.
List the DDL moves that are genuinely safe in production (non-blocking add column, concurrent index build, NOT VALID constraints, three-phase type change).
Think through why "rolling back" a migration that damaged data usually doesn't mean down — it means rolling forward a compensating migration — and know when the backup is the right tool.

What You Need to Know

You've read the previous article and get that "migration = versioned schema change" and "the migrations table is the git log."
You only need basic DDL/index knowledge; the SQL is all PostgreSQL.
The focus is strategy — the tool (Drizzle, Prisma) is just the executor here.

A Scenario to Start With: the 2 a.m. Alert

You did it exactly "by the book" from the last article: you wrote a migration, ran drizzle-kit migrate, deployed. Everything looked fine. Then, ten minutes later, the alert fires: database connections maxed out, P99 latency jumping to tens of seconds.

What did you break? It was something that looked completely harmless:

sql

ALTER TABLE orders ADD COLUMN region text;

Then, to fill it in from a related table:

sql

UPDATE orders o
SET region = c.region
FROM customers c
WHERE o.customer_id = c.id;

That single UPDATE on a few million rows is one enormous transaction. It holds locks on every row it touches, and because it scans and writes so many rows, it pins the shared buffer and the pending write queue for a long time. Meanwhile your app is doing the same kind of writes — they all queue up behind it. Postgres is fine with many readers, but when those readers start waiting, the whole site stalls.

Now the second variant, the one that makes you sweat at a different hour: you deploy a migration that copies price from integer to numeric, and a backfill that joins an old orders_legacy table. It "worked," but for a batch of older orders the join found no match, so those rows ended up price = NULL — and your new frontend code happily multiplied NULL into a menu price. Bad data is now in production, and the app has already served it.

These are the two problems this article is about. Same migration, two flavors of harm: downtime (the schema change itself blocks traffic) and corruption (the change shipped wrong data).

First, Reframe: What Makes a Schema Change Dangerous?

Let's be precise about where the risk actually sits. From the last article you know: an ALTER TABLE that triggers a full table rewrite holds an ACCESS EXCLUSIVE lock — the strongest lock there is — and that blocks every read and write for as long as the rewrite lasts.

But there's a subtler failure mode with the ordering of deploy, not the DDL itself. Consider the two players in play:

The database — its schema is a fixed snapshot at any moment.
The application — process A is still running last week's code; process B just rolled out this week's code.

If you change the schema in a way that breaks either old or new code, someone is always broken, no matter how fast you deploy. Classic examples:

You drop a column the old code still reads → old process crashes.
You add a NOT NULL column the new code inserts but the old code doesn't → old process fails its INSERT.
You rename a column → both old and new code fight over it.

So the real target of zero-downtime migration is not "run the DDL fast." It's: make the schema a superset the old code can live with, and the new code can live with, at every point in time. That way the database never becomes a place where only one generation of the app can run.

This is the principle the whole article hangs on. Now let's turn it into a concrete pattern.

The Zero-Downtime Pattern: Expand → Migrate → Contract

The industry-standard solution is expand/contract (also called "parallel change"). It breaks the fear into three steps, each of which is individually backward-compatible:

txt

Expand          Migrate          Contract
schema  ──── add new ─▶ backfill ─▶ swap app ─▶ drop old
         (old code  (fill data  (app reads/writes (remove the
          still works) with new)   the new shape)   dead column)

Three rules keep it safe:

Expand first, non-breaking. Add the new structure such that the old code doesn't even notice it: a new column (nullable or with a constant default), a new table, a new index. Old INSERTs just don't write it; old SELECTs never touch it.
Migrate in the background. Backfill the data — but in batches, not one giant UPDATE (that was our 2 a.m. mistake). This step runs while both codebases are still alive.
Contract last, only after the app has been migrated. Once every running process uses the new shape, then make it strict and drop the old one. If you contract too early, a process running the old code breaks.

Let's see each phase in action against our orders example.

Phase 1 — Expand

Add the column without breaking old code. Note we avoid NOT NULL for now:

sql

ALTER TABLE orders ADD COLUMN region text;

On PostgreSQL 11+, if you want it NOT NULL right away, give it a constant default — that's the metadata-only fast path, no rewrite:

sql

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

Because the default is a constant (not now() or something volatile), existing rows "pretend" to have it via the missing-value trick, and the lock is very short. A volatile default (DEFAULT now()) would rewrite the whole table — avoid it here.

Phase 2 — Migrate (backfill in batches)

Now fill real values, but never as one huge transaction. Batch by keyset pagination, one small transaction per batch:

sql

-- backfill in batches: one short transaction per batch; idempotent, resumable
WITH batch AS (
  SELECT o.id, c.region
  FROM orders o
  JOIN customers c ON o.customer_id = c.id
  WHERE o.region = 'unknown'   -- only fill rows that aren't filled yet (idempotent)
    AND o.id > $last_id
  ORDER BY o.id
  LIMIT 1000
)
UPDATE orders o
SET region = b.region
FROM batch b
WHERE o.id = b.id;

The WHERE o.id > $last_id ... LIMIT is keyset pagination: each batch is a few hundred rows, each its own tiny transaction, each holding locks for milliseconds. Because the predicate "only fill rows still equal to the placeholder" is idempotent, you can stop, resume, or re-run safely. That's the antidote to the single big UPDATE.

Phase 3 — Contract

Once the app has been deployed and every process is reading/writing the new shape, tighten and clean up. Now you can safely add the stricter constraints:

sql

-- add the constraint but leave it "not yet validated", so we skip a full-table scan
ALTER TABLE orders ADD CONSTRAINT orders_region_not_null
  CHECK (region IS NOT NULL) NOT VALID;

NOT VALID skips scanning the existing rows — it only enforces the rule for new writes. Then validate it in one pass once you're confident, using a lock that still allows reads:

sql

ALTER TABLE orders VALIDATE CONSTRAINT orders_region_not_null;

Validation takes a weaker lock (SHARE UPDATE EXCLUSIVE) that blocks concurrent writes to the table but not reads — so it can be done cheaply. Finally, drop what's no longer used:

sql

ALTER TABLE orders DROP COLUMN region;          -- if replaced by a separate table
DROP TABLE orders_legacy;                       -- delete only after confirming nobody references it

That's the whole trick: never let a single deploy make the database unreachable to one generation of the app.

The Safe-DDL Toolkit, in One Place

Some DDL you'll want ready. The rule of thumb: prefer each of these because they don't take a long exclusive lock.

Change	Safe approach	Why it's safe
Add a column	constant default (PG 11+) or nullable	metadata-only, no rewrite
Add an index	`CREATE INDEX CONCURRENTLY name ON t (col)`	builds without blocking reads/writes
Add a constraint/FK	add `NOT VALID`, then `VALIDATE`	no full scan during the add
Change a column type	three-phase: add new col → backfill → swap/drop	never rewrites the live one
Backfill data	keyset-batch, idempotent where-clause	short locks, resumable

Two warnings:

CREATE INDEX CONCURRENTLY can't run inside a transaction block (Drizzle migrations wrap in transactions by default — keep it standalone), and if it fails it can leave an invalid index behind.
If you must change a type, do it the three-phase way (new column → backfill → swap) instead of ALTER COLUMN ... TYPE, which rewrites the whole table.

Rolling Back a Migration That Wrote Bad Data

Now the harder one. In our scenario the migration produced NULL prices. The natural instinct is "just run the down migration and go back." Let's check why that's almost never the right answer in production.

Why `down` is the wrong reflex

Remember what a down migration actually is: it's the exact structural reverse of up. It assumes the table looks exactly like the moment up finished. But by now:

The app moved on. Every process is running the new code that reads price as numeric. If you down to integer, the running app breaks immediately.
Data wasn't static. Since up ran, new orders were inserted, old orders updated. The down would undo those good writes too — or fail, because the shapes no longer line up.
A structural revert is not a data fix. down reverses the schema, but your problem is the values in it. Running down doesn't repair the NULLs; it just refuses the column, and the bad values are gone or still wrong elsewhere.

So the down migration is really a pre-production or everything-is-stopped tool (that's why test/CI environments love it). In a running system it's a liability.

Roll forward: the compensating migration

The production answer is to roll forward to a newer, correct state rather than back to an older one. You write a migration that fixes the bad rows, make it idempotent, and ship it. Think of it as git revert in Git: you don't travel back in time, you apply a new commit that happens to undo the damage.

For the NULL prices, that looks like a data-repair migration:

sql

-- restore prices that were wrongly set to NULL (re-runnable, idempotent)
UPDATE orders o
SET price = (
  SELECT c.legacy_price
  FROM orders_legacy c
  WHERE c.order_id = o.id
)
WHERE o.price IS NULL
  AND EXISTS (SELECT 1 FROM orders_legacy c WHERE c.order_id = o.id);

The keys to a safe compensating migration:

Idempotent: re-running it produces the same result (WHERE o.price IS NULL).
Targeted: touch only the rows you can actually repair; for the rest, fail loudly and alert.
Inspectable: it produces a clean end state you can verify with a SELECT.

When you do reach for backup / restore

Sometimes the damage is too broad to repair by hand. Then the honest options are:

Point-in-time restore (PITR) to just before the bad migration — but this rolls the whole database back, so anything written after that moment is lost, and the app must be paused.
Maintenance window: stop writes, apply the compensating migration or restore, verify, then resume. This is the "we accept a few minutes of downtime to guarantee correctness" trade.

The decision tree is: can you repair the specific rows with a forward migration? → do that. Is the corruption pervasive or does it cascade into derived/aggregate data? → then pause the app and restore/repair in a controlled window.

The pattern that prevents this whole class of bugs

The best "rollback" is the one you never need. Three habits cut this risk dramatically:

Deploy migration before app (for expand), app before contract. Expand migration first, then the app that uses it, then the contract migration. Never the reverse.
Ship behind a feature flag. Make the new behavior switchable from the app side, so you can turn it off instantly without touching the database.
Always have a working restore point — and rehearse it. In production, "backup exists" is not "backup works."

A Production Migration Checklist

Before you touch a live schema, run through these:

Is this change backward-compatible? Would last deployment's code still run?
Am I expanding first (add, not drop) and contracting later?
Does any ADD COLUMN / INDEX / CONSTRAINT avoid a full-rewrite / long exclusive lock?
Is the backfill batched, idempotent, and resumable — not one giant UPDATE?
Can I disable or validate the change from the app side (feature flag / smoke test)?
If it writes data, do I have a compensating (forward) migration drafted, and a verified restore point?
Have I rehearsed the rollback on a copy of the data?

Summary & Keywords

The danger is twofold — a schema change can block traffic (rewrite/long lock) or ship wrong data (bad backfill). The goal is to make the schema a superset that both the old and the new app can run against.
Expand → Migrate → Contract is the zero-downtime recipe: add first (non-breaking), backfill in batches, contract last. At no moment does a single deploy strand one generation of the app.
The safe-DDL building blocks — constant-default add column, CREATE INDEX CONCURRENTLY, NOT VALID + VALIDATE, three-phase type change, keyset-batch backfill.
Roll forward, not back — when a migration writes bad data, don't down it; write an idempotent compensating migration. Reserve backup/restore for pervasive corruption, done in a controlled window.

Keywords: zero-downtime migration, expand/contract, parallel change, CREATE INDEX CONCURRENTLY, NOT VALID, keyset pagination, backfill, compensating migration, roll forward, point-in-time restore, feature flag.

Try It Yourself

On a local Postgres with a few thousand rows, reproduce the downtime: run a single big UPDATE and watch pg_stat_activity while you query. Then redo it with keyset batches and feel the difference.
Write a "damaging" migration (set a column NULL on a subset), then write a correct, idempotent compensating migration and apply it. Confirm re-running it changes nothing.
Set up a branch where you must change a column type. Apply the three-phase approach (add new column → batch backfill → swap/drop) and verify old code never breaks mid-way.