Zero-Downtime Database Migrations: The Expand/Contract Pattern

The first time I took production down with a migration, the change looked completely harmless: rename a column from name to full_name. One line of SQL. The migration ran in milliseconds. And then every pod still running the old code started throwing column "name" does not exist, error rates spiked, and I learned the lesson every backend engineer eventually learns the hard way: the database and the application don't deploy atomically.

During any rolling deploy there is a window — seconds if you're lucky, minutes if you're not — where old code and new code run against the same schema at the same time. If your schema change is only compatible with the new code, the old code breaks. If you deploy the code first and it expects the new schema, the new code breaks. Either way, someone's requests are failing while your deploy is "in progress, all green."

The fix isn't a fancier deploy tool. It's a discipline for writing migrations so that every intermediate state is compatible with both versions of the code. That discipline has a name: expand/contract. Here's how I apply it, with a concrete column-rename walkthrough, the Postgres-specific traps, and how it fits into a TypeORM or Prisma workflow in CI.

Why naive migrations break deploys

Two separate failure modes hide inside a "simple" migration, and they compound.

Failure mode 1: locks. Most DDL in Postgres takes an ACCESS EXCLUSIVE lock on the table — the strongest lock there is. While it's held, every read and write on that table queues behind it. Usually that's fine because DDL is fast. But locks queue: if your ALTER TABLE waits behind one long-running SELECT, every query that arrives after your ALTER also waits — behind a migration that hasn't even started yet. A migration that "runs in 50ms" can stall your hottest table for 30 seconds because an analytics query was in flight.

Failure mode 2: version skew. Kubernetes, ECS, even a two-VM setup behind a load balancer — all roll instances gradually, which guarantees a period where v1 and v2 of your code both serve traffic. Any migration that removes or renames something v1 still uses will break v1 during that window. And if the deploy fails health checks and rolls back, you're now running v1 against the new schema indefinitely.

The naive rename fails both ways at once:

-- Do NOT ship this against a live table
ALTER TABLE users RENAME COLUMN name TO full_name;

Old code reads name: broken the instant the migration commits. There's no ordering of "migrate first" or "deploy first" that saves you — the change is incompatible with one of the two versions no matter what.

The expand/contract pattern

The pattern splits every breaking change into three phases, each shipped as its own deploy:

1. Expand. Add the new thing (column, table, index) without touching the old thing. The schema now supports both old and new code. Old code ignores the new column entirely.
2. Migrate. Ship code that writes to both old and new (dual-write) and reads from the new one with a fallback. Backfill existing rows in the background. Wait until the new column is fully populated and the code path is verified.
3. Contract. Ship code that no longer references the old column at all. Once that's fully rolled out (and you're past your rollback window), drop the old column in a final migration.

The invariant that makes this work: at every step, the currently deployed code and the code one version behind it both work against the current schema. That's the same N-1 compatibility rule you need for rolling deploys of a containerized stack — the schema is just another interface between versions.

Yes, it's slower — a rename becomes three deploys over a few days instead of one over lunch. That's the honest cost. For a hundred-row table on an internal tool I'll still do the naive rename and accept a blip; the pattern is a tool, not a religion.

A concrete walkthrough: renaming name to full_name

Step 1 — Expand. Add the new column, nullable, no default that rewrites the table:

-- Migration 1: pure addition, safe under load
ALTER TABLE users ADD COLUMN full_name text;

Adding a nullable column in Postgres is a metadata-only change — it doesn't rewrite the table, and the ACCESS EXCLUSIVE lock it takes is held for milliseconds (still set a lock_timeout; more below).

Step 2 — Dual-write. Deploy application code that writes both columns and prefers the new one on read. In a NestJS service with TypeORM this is small and boring:

// users.service.ts — during the migration window
async updateName(userId: string, fullName: string): Promise<void> {
  await this.usersRepo.update(userId, {
    name: fullName,       // old column: keeps v1 code working
    fullName: fullName,   // new column: what v2 reads
  });
}

getDisplayName(user: User): string {
  return user.fullName ?? user.name; // fallback while backfill runs
}

Step 3 — Backfill existing rows (next section), then verify: SELECT count(*) FROM users WHERE full_name IS NULL AND name IS NOT NULL should hit zero and stay there.

Step 4 — Contract the code. Deploy a version that reads and writes only full_name. The old column still exists but nothing touches it. I sit on this for at least a few days, because the moment you drop the column you can no longer roll back to any build that referenced it.

Step 5 — Contract the schema.

-- Migration 2: days later, after the dual-write code is fully retired
ALTER TABLE users DROP COLUMN name;

Two migrations, three code deploys, zero seconds of downtime.

Backfilling large tables in batches

The tempting backfill is one statement:

UPDATE users SET full_name = name WHERE full_name IS NULL; -- don't

On a big table this is a long transaction that locks every row it touches, bloats the table with dead tuples in one shot, blows up WAL, and can stall replication. Batch it instead, with a pause between batches so autovacuum and your replicas can breathe:

// backfill.ts — run as a one-off job, not inside a request
const BATCH = 5_000;

async function backfill(dataSource: DataSource): Promise<void> {
  let updated = 0;
  do {
    const result = await dataSource.query(`
      UPDATE users
      SET full_name = name
      WHERE id IN (
        SELECT id FROM users
        WHERE full_name IS NULL AND name IS NOT NULL
        LIMIT $1
        FOR UPDATE SKIP LOCKED
      )
    `, [BATCH]);
    updated = result[1]; // rows affected
    await new Promise((r) => setTimeout(r, 200));
  } while (updated > 0);
}

SKIP LOCKED keeps the backfill from fighting live transactions over the same rows, and each batch commits independently — if the job dies at 60%, rerun it; the WHERE full_name IS NULL predicate makes it idempotent. That's the same property I lean on for idempotent payment writes: design the operation so running it twice is safe, then retries become boring.

Postgres-specific gotchas

This is where "I read about expand/contract" and "I've run it in production" diverge. The pattern is portable; the lock behavior is very Postgres-specific.

Operation	Behavior on a live table	Safe alternative
ADD COLUMN (nullable)	Metadata only, fast	Fine as-is
ADD COLUMN ... DEFAULT	Fast on PG 11+ (stored default); full rewrite on older versions	Verify your PG version first
SET NOT NULL	Full table scan under ACCESS EXCLUSIVE	CHECK (col IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT, then SET NOT NULL
CREATE INDEX	Blocks all writes for the whole build	CREATE INDEX CONCURRENTLY
DROP COLUMN	Metadata only, fast	Fine — the danger is code, not locks
ALTER COLUMN TYPE	Usually a full rewrite	Expand/contract with a new column

Three of these deserve more than a table row:

Always set lock_timeout. The queue-behind-a-long-query problem from earlier has a cheap fix — refuse to wait:

SET lock_timeout = '3s';
ALTER TABLE users ADD COLUMN full_name text;
-- If something holds the lock, the migration fails fast instead of
-- stalling every query on the table. Retry it; don't remove the timeout.

A failed migration you retry in a minute is a non-event. A migration that silently queues traffic for 40 seconds is an incident.

NOT NULL the safe way. ALTER TABLE ... SET NOT NULL scans the whole table while holding the exclusive lock. The three-step dance — add a NOT VALID check constraint (instant), VALIDATE CONSTRAINT (scans with only a light lock), then SET NOT NULL (on PG 12+ it sees the validated constraint and skips the scan) — gets you the same guarantee without the outage.

Indexes: always CONCURRENTLY. It builds slower and it can't run inside a transaction block — which matters because most migration runners wrap each migration in one. In TypeORM you set transaction: false for that migration file; in Prisma you split it into its own migration. A failed concurrent build leaves an INVALID index behind — drop it and rerun.

Coordinating with rolling deploys and CI

The workflow that has served me well across multi-service NestJS systems:

Migrations run before the new code rolls, as a separate step. Not in the app's onModuleInit — with six replicas you'd have six racing migration runners and a deploy that fails half-applied. One runner, then the rollout:

# CI deploy job
npm run typeorm migration:run   # or: npx prisma migrate deploy
kubectl rollout status deploy/api --timeout=120s || {
  echo "Rollout failed — schema is expand-only, so v1 keeps working"
  exit 1
}

This ordering is why the expand rule exists: the schema always changes first, so every migration must be compatible with the code that's still running. CI can enforce the cheap half of this — I lint migration files for DROP, RENAME, and SET NOT NULL and require an explicit marker comment before those merge. It's a regex, not a proof, but it catches the Friday-afternoon rename before it ships.

Two more rules I hold the team to: never edit a merged migration (Prisma checksums will scream anyway; TypeORM will silently let you create drift between environments), and contract migrations get their own PR with a link to the dashboard showing zero reads on the old column. The second rule has caught a forgotten cron job reading a "dead" column more than once.

Wrapping up

Zero-downtime migrations aren't a tool you install; they're a constraint you accept: every schema change must be compatible with the previous version of the code, and every code change must be compatible with the current schema. Expand/contract is just the systematic way to satisfy that constraint — add first, dual-write and backfill in the middle, remove last, and let days pass between the phases.

The costs are real: three deploys instead of one, dual-write code that exists only to be deleted, and backfill jobs to babysit. I pay them gladly on any table that takes production traffic, because the alternative is the thing I did years ago — a one-line rename, a spike of 500s, and a very long afternoon. Slow schema changes are a feature. The migration that takes three days and zero downtime beats the one that takes three seconds and takes you down with it.