Zero-Downtime Database Migrations: A Practical Playbook

# Zero-Downtime Database Migrations: A Practical Playbook

TL;DR: Zero downtime database migrations work by splitting a single risky schema change into small, backward-compatible steps — expand the schema additively, dual-write and backfill data, cut reads over, then contract by removing what's no longer needed — so that old and new application code can both run against the same database during a rolling deploy.

Why "just run the migration" breaks production

A migration that works fine on your laptop can take a production system down for two unrelated reasons.

Locking. Many schema changes take an ACCESS EXCLUSIVE lock (Postgres) or block reads/writes (MySQL without ALGORITHM=INSTANT/INPLACE) for as long as the operation runs. On a small table that's milliseconds. On a table with tens of millions of rows, a naive ALTER TABLE can hold that lock for minutes, during which every query queued behind it — and every query queued behind those — piles up until the connection pool is exhausted.

Rolling deploys. Even if the migration itself is instant, you're almost never deploying application code and schema changes atomically. During a rolling deploy, old pods and new pods serve traffic against the same database simultaneously. If you rename a column and deploy new code that expects the new name, the old pods still running the previous release start throwing errors the moment the column disappears.

The expand/contract pattern (also called parallel change) solves both problems by making every migration safe to run before the code that depends on it, and safe to leave in an intermediate state for as long as a deploy takes.

The expand/contract pattern

The pattern breaks a breaking schema change into phases that are each independently safe:

1. Expand — add new columns, tables, or indexes without touching or removing anything existing. Old code keeps working untouched.

2. Migrate (dual-write + backfill) — new code writes to both old and new structures; a background job backfills historical data into the new structure.

3. Cut over — once the new structure is fully populated and verified, deploy code that reads from (and eventually only writes to) the new structure.

4. Contract — once no code path reads the old structure, drop it.

The rule that makes this safe under a rolling deploy: every deploy must be compatible with the schema version currently in the database, and every schema state must be compatible with both the previous and next application release. That's it — one rule, applied at every step.

Worked example: splitting a `full_name` column into `first_name` / `last_name`

This is one of the most common "trivial in a spec, painful in production" migrations. Say you have:

CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  full_name TEXT NOT NULL,
  email TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

and you need first_name and last_name as real, queryable, NOT NULL columns. Doing this as one migration plus one deploy means either a maintenance window or a window where half your fleet writes full_name and the other half expects first_name/last_name — and breaks.

Step 1 — Expand: add nullable columns

-- migration 1 (safe: additive, no lock contention on Postgres 11+)
ALTER TABLE users ADD COLUMN first_name TEXT;
ALTER TABLE users ADD COLUMN last_name TEXT;

Since Postgres 11, adding a column with a constant (non-volatile) default no longer rewrites the table — the default is stored once in the catalog and applied lazily as rows are read, so this ALTER TABLE is near-instant even on a huge table. Here we're not even setting a default, so it's cheap on every supported Postgres version. Deploy this migration alone; nothing in the application references the new columns yet, so old and new code both keep working.

Step 2 — Dual-write from the application

Deploy application code that writes first_name/last_name alongside full_name on every insert or update, while continuing to read from full_name:

async function updateUserName(id: number, fullName: string) {
  const [firstName, ...rest] = fullName.trim().split(/\s+/);
  const lastName = rest.join(" ") || null;

  await db.query(
    `UPDATE users
     SET full_name = $1, first_name = $2, last_name = $3
     WHERE id = $4`,
    [fullName, firstName, lastName, id]
  );
}

At this point, every new or updated row has all three columns populated. Existing rows still only have full_name.

Step 3 — Backfill historical rows

Backfill in small batches with a short pause between them, so the job never competes hard with production traffic for I/O or row locks:

// backfill-names.ts
const BATCH_SIZE = 1000;

async function backfillBatch(lastId: number): Promise<number | null> {
  const rows = await db.query(
    `SELECT id, full_name FROM users
     WHERE id > $1 AND first_name IS NULL
     ORDER BY id
     LIMIT $2`,
    [lastId, BATCH_SIZE]
  );

  if (rows.length === 0) return null;

  for (const row of rows) {
    const [firstName, ...rest] = row.full_name.trim().split(/\s+/);
    await db.query(
      `UPDATE users SET first_name = $1, last_name = $2 WHERE id = $3`,
      [firstName, rest.join(" ") || null, row.id]
    );
  }

  return rows[rows.length - 1].id;
}

async function runBackfill() {
  let lastId = 0;
  while (lastId !== null) {
    lastId = await backfillBatch(lastId);
    await new Promise((r) => setTimeout(r, 100)); // let production traffic through
  }
}

Run this as a one-off job (not inside the deploy pipeline), and monitor replication lag and query latency while it runs. On a very large table, this is also where MySQL users would reach for gh-ost or Percona's pt-online-schema-change instead of a plain UPDATE loop, since both tools handle exactly this kind of online backfill-and-cutover for InnoDB tables without holding long locks.

Step 4 — Validate and enforce `NOT NULL`

Once the backfill job reports zero remaining NULL rows, add the constraint. A plain ALTER TABLE users ALTER COLUMN first_name SET NOT NULL forces a full table scan under a lock. Use the two-step CHECK constraint trick instead, which is exactly what Postgres' own docs recommend for adding constraints to large tables without long-held exclusive locks:

-- Step 4a: add the constraint without validating existing rows (fast, brief lock)
ALTER TABLE users
  ADD CONSTRAINT first_name_not_null CHECK (first_name IS NOT NULL) NOT VALID;

-- Step 4b: validate separately — takes a SHARE UPDATE EXCLUSIVE lock,
-- which allows concurrent reads and writes
ALTER TABLE users VALIDATE CONSTRAINT first_name_not_null;

Deploy application code that now reads first_name/last_name (still writing full_name too, in case of rollback).

Step 5 — Contract: drop the old column

Only after the reading code has been live for a full deploy cycle with no rollback risk:

ALTER TABLE users DROP COLUMN full_name;
ALTER TABLE users DROP CONSTRAINT first_name_not_null; -- optional: convert to a real NOT NULL
ALTER TABLE users ALTER COLUMN first_name SET NOT NULL;
ALTER TABLE users ALTER COLUMN last_name SET NOT NULL;

Five small, individually boring migrations — and at no point did any of them require stopping traffic.

Lock behavior cheat sheet (PostgreSQL)

Always wrap risky DDL with a tight lock_timeout so a blocked ALTER TABLE fails fast and retries later, instead of queueing behind a long-running query and then itself blocking every subsequent query:

SET lock_timeout = '2s';
ALTER TABLE users ADD CONSTRAINT first_name_not_null CHECK (first_name IS NOT NULL) NOT VALID;

Tooling that implements this pattern for you

You don't have to hand-roll expand/contract every time:

• [pgroll](https://github.com/xataio/pgroll) (Postgres 14+) automates zero-downtime migrations by exposing old and new schema versions simultaneously through views, handling backfill and rollback for you.
• [gh-ost](https://github.com/github/gh-ost) and Percona's pt-online-schema-change perform online ALTER TABLE on MySQL/MariaDB by copying rows into a shadow table and replaying changes captured from the binlog (gh-ost) or via triggers (pt-osc), avoiding long-held locks on the original table.
• [strong_migrations](https://github.com/ankane/strong_migrations) (Ruby/Rails) statically flags migrations — like adding a NOT NULL column with a default, or renaming a column in use — that are unsafe on Postgres, MySQL, or MariaDB, before they ever hit a database.
• MySQL's native `ALGORITHM=INSTANT` (available since 8.0.12, and still current in the 8.4 LTS line) makes many ADD COLUMN operations metadata-only and near-instant, which removes the need for an external tool for that specific case — but doesn't help with backfills, type changes, or NOT NULL enforcement.

If you're carrying a schema that predates any of this discipline — wide tables with no safe way to add a constraint, or migrations that were always run during a maintenance window — that's usually a symptom of broader legacy modernization work, not just a migrations problem; it's one of the things we help clients untangle in legacy modernization engagements.

A short checklist

• [ ] Does this migration change or remove anything an already-deployed version of the app reads or writes?
• [ ] Can the DDL itself run without a long-held exclusive lock? (Check the cheat sheet above.)
• [ ] If data needs to move, is the backfill batched, throttled, and resumable?
• [ ] Is there a dual-write period long enough to safely backfill under real traffic?
• [ ] Is the "contract" step scheduled as a separate, later deploy — not bundled into the cutover?
• [ ] Do you have a lock_timeout set for DDL so a blocked migration fails fast instead of queueing?

FAQ

Is the expand/contract pattern only for Postgres?

No — it's a deployment discipline, not a database feature. The mechanics of how you avoid locks differ (Postgres' NOT VALID/VALIDATE CONSTRAINT, MySQL's ALGORITHM=INSTANT/INPLACE or gh-ost), but the phased expand → migrate → cut over → contract sequence applies to any relational database, and to many document stores as well.

How long should the dual-write phase last?

Long enough to run a full backfill under production load and complete at least one full deploy cycle without a rollback. For most teams that's anywhere from a few hours to a couple of weeks depending on table size and deploy cadence — there's no fixed number, but "as short as safely possible" beats leaving dual-write code around indefinitely, since it's extra complexity that should be temporary.

Do ORMs like Prisma or Rails' Active Record handle this automatically?

No. Migration tools generate the DDL for the state you declare, but they don't know your rollout strategy — splitting a single "add this column and make it required" change into expand/backfill/contract steps is still a manual decision, though gems like strong_migrations will at least stop you from deploying something obviously unsafe by mistake.

What's the biggest mistake teams make with zero-downtime migrations?

Bundling the schema change and the code that depends on it into a single deploy. Even a perfectly non-locking migration causes an outage if the very next line of the same deploy assumes the new column already has data in every row.

Sources

• PostgreSQL 11 Release Notes — fast `ADD COLUMN` with defaults
• A Missing Link in Postgres 11: Fast Column Creation with Defaults — brandur.org
• How to run short ALTER TABLE without long locking concurrent queries — depesz
• PostgreSQL 18 documentation — Modifying Tables
• pgroll — PostgreSQL zero-downtime migrations made easy (GitHub)
• gh-ost — GitHub's Online Schema-migration Tool for MySQL
• strong_migrations — Catch unsafe migrations in development (GitHub)
• MySQL 8.0: InnoDB Instant ADD COLUMN — MySQL Server Blog
• MySQL 8.4 Release Notes — Changes in MySQL 8.4.11 (2026-07-28)