Zero-Downtime Database Migrations: The Expand–Contract Playbook for Distributed Systems

Most outages I have been paged for were not caused by application code. They were caused by a 40-second ALTER TABLE that queued behind a long-running read, a backfill that saturated a replication stream, or a service that started reading a column that another region's replica did not have yet. In a distributed production environment, a database migration is not a DDL statement — it is a multi-week, multi-service, multi-phase rollout protocol.

This article is the playbook I use: the expand–contract (parallel change) discipline, the exact DDL that avoids lock amplification in PostgreSQL, online schema change strategy for MySQL, chunked and idempotent backfills, dual-write/dual-read verification, and the coordination primitives that keep N and N+1 versions of your services alive simultaneously.

Why Migrations Break Production: Three Structural Failure Modes

Before the patterns, the physics. Distributed migrations fail in three distinct ways that require three distinct mitigations.

1. Lock queue amplification (not lock duration)

In PostgreSQL, a DDL statement requests an ACCESS EXCLUSIVE lock. It does not jump the queue. If a 10-minute analytics query holds ACCESS SHARE on the table, every subsequent SELECT queues behind your ALTER TABLE. You instantly have a full table outage caused by a statement that itself took 50 milliseconds. The danger is never the duration of the DDL — it is the duration of the lock wait, multiplied by the arrival rate of new queries behind it.

Mitigation: always set lock_timeout and treat a timeout as a retryable, non-fatal failure.

2. Mixed-version reads across a fleet

During a rolling deploy you have pods running v47 and pods running v48 against the same schema. If v48 reads a column that v47 inserts as NULL, you get silent data corruption, not a clean error. In multi-region topologies, add replication lag: a reader in eu-west-1 may be consuming a schema state that the primary applied 90 seconds ago.

Mitigation: every schema change must be backwards compatible across at least two application versions, and reads must tolerate NULL for every newly added column.

3. Non-atomic DDL across shards

If you run 64 shards, you run 64 ALTER TABLE statements with zero transactional atomicity. Shard 37 will fail on a constraint you forgot about, at which point half your fleet is on the old schema, half on the new, and your migration tool has no rollback. DDL is a long-running distributed job, and it needs idempotency, retries, and per-shard progress tracking — the same properties you would demand of any other job.

The Expand–Contract Pattern

The core discipline: never mutate a schema in one step. Instead, change it in an additive phase, transition the application to the new shape, then remove the old shape in a separate, later release.

Phases 1–5 are all reversible within seconds via a feature flag. Only phase 6 is a one-way door, and by the time you reach it you have weeks of production evidence.

Phase 1: Expand — DDL That Never Blocks

Every statement here is chosen to avoid a full-table rewrite or a long-held ACCESS EXCLUSIVE lock.

-- Run each statement in its own short transaction with a bounded lock wait.
BEGIN;
SET LOCAL lock_timeout = '3s';   -- fail fast instead of queueing the world
SET LOCAL statement_timeout = '0'; -- the DDL itself may legitimately take time

-- Additive column: nullable, no default => metadata-only in PG 11+.
ALTER TABLE orders
  ADD COLUMN IF NOT EXISTS fulfillment_state text;

-- Constraint added NOT VALID: no full-table scan under ACCESS EXCLUSIVE.
ALTER TABLE orders
  ADD CONSTRAINT orders_fulfillment_state_check
  CHECK (fulfillment_state IN ('pending','picked','shipped','cancelled'))
  NOT VALID;
COMMIT;

-- Validate separately: takes only SHARE UPDATE EXCLUSIVE, allows reads AND writes.
ALTER TABLE orders VALIDATE CONSTRAINT orders_fulfillment_state_check;

-- CREATE INDEX CONCURRENTLY cannot run inside a transaction block.
-- It takes two brief locks and does not block writes, but it CAN fail and
-- leave an INVALID index behind -- check pg_index.indisvalid afterwards.
CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_fulfillment_state
  ON orders (fulfillment_state)
  WHERE fulfillment_state IS NOT NULL;

Three rules I enforce in review: never add a NOT NULL column with a non-constant default to a large table; never add a foreign key without NOT VALID; never run DDL without lock_timeout. A migration linter (Atlas, squawk) catches all three automatically in CI.

Retry logic for lock-timeout failures

const DDL_RETRIES = 8;

async function applyDdl(pool: Pool, sql: string): Promise<void> {
  for (let attempt = 0; attempt < DDL_RETRIES; attempt++) {
    try {
      await pool.query(sql);
      return;
    } catch (err: unknown) {
      const e = err as { code?: string };
      // 55P03 = lock_not_available (the lock_timeout fired). Safe to retry.
      if (e.code === '55P03') {
        const backoff = Math.min(60_000, 500 * 2 ** attempt) + Math.random() * 250;
        await new Promise((r) => setTimeout(r, backoff));
        continue;
      }
      throw err; // 42P01 undefined_table, 23505 unique_violation, etc. => real bug
    }
  }
  throw new Error('DDL did not acquire lock after retries; escalate to on-call');
}

Phase 2: Dual-Write and the Outbox Escape Hatch

During dual-write, every write path populates both the legacy and the new representation. If both targets live in the same database, a single transaction is correct and simple.

interface SchemaFlags {
  writeFulfillmentState: boolean; // ON during expand->contract window
  readFulfillmentState: boolean;  // ON after backfill verification
}

async function updateOrderStatus(
  pool: Pool,
  flags: SchemaFlags,
  id: string,
  status: OrderStatus,
): Promise<Order> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    const { rows } = await client.query<OrderRow>(
      `UPDATE orders SET status = $2, updated_at = now()
         WHERE id = $1 RETURNING *`,
      [id, status],
    );
    if (rows.length === 0) throw new Error(`order ${id} not found`);

    if (flags.writeFulfillmentState) {
      // Same transaction: atomic with the legacy write. No divergence possible.
      await client.query(
        `UPDATE orders SET fulfillment_state = $2 WHERE id = $1`,
        [id, deriveFulfillmentState(status)],
      );
    }

    await client.query('COMMIT');
    return toDomain(rows[0]);
  } catch (err) {
    await client.query('ROLLBACK').catch(() => undefined);
    throw err;
  } finally {
    client.release();
  }
}

If the two targets are different systems — a second service's database, a search index, a cache — do not dual-write inline. A non-transactional second write either fails after commit (lost data) or blocks your API on a foreign system's availability. Use the transactional outbox: write an event row in the same transaction, and let a relay publish it with at-least-once delivery and idempotent consumers.

Phase 3: Chunked, Idempotent, Throttled Backfill

The backfill is the operation that most often causes incidents, because it competes with production traffic for I/O, WAL, and replication bandwidth. Design it as a resumable job, not a single UPDATE.

-- One chunk. Idempotent: re-running is a no-op once rows are populated.
-- SKIP LOCKED keeps the backfill from blocking hot user rows.
WITH batch AS (
  SELECT id
  FROM orders
  WHERE fulfillment_state IS NULL
    AND status IS NOT NULL
  ORDER BY id
  LIMIT 5000
  FOR UPDATE SKIP LOCKED
)
UPDATE orders o
SET fulfillment_state = CASE o.status
      WHEN 'paid'    THEN 'pending'
      WHEN 'shipped' THEN 'shipped'
      WHEN 'void'    THEN 'cancelled'
      ELSE 'pending'
    END
FROM batch
WHERE o.id = batch.id;

Operational guardrails that are non-negotiable:

• Throttle on a real signal, not a fixed sleep. Pause when replica lag exceeds a threshold or Threads_running / pg_stat_activity active count crosses a limit.
• Bound the batch size to keep each transaction short — 1k–10k rows depending on row width.
• Progress on `id`, not OFFSET; OFFSET degrades linearly and skips rows under concurrent deletes.
• Log completion counts so you can prove SELECT count(*) WHERE fulfillment_state IS NULL reaches zero.

Phase 4: Dual-Read and Shadow Comparison

Reading both representations and comparing them is the cheapest correctness insurance you will ever buy. Never flip reads without it.

if (flags.readFulfillmentState) {
  const legacy = deriveFulfillmentState(row.status);
  const next = row.fulfillment_state ?? legacy;

  if (row.fulfillment_state !== null && row.fulfillment_state !== legacy) {
    // Emit to metrics, do not throw: a mismatch must not break the request.
    logger.warn('migration.shadow_read_mismatch', {
      order_id: row.id,
      legacy,
      next,
      schema_version: row.schema_version,
    });
  }
  return { ...toDomain(row), fulfillmentState: next };
}
return { ...toDomain(row), fulfillmentState: deriveFulfillmentState(row.status) };

Gate the read switch on zero mismatches over a full business cycle — which means at least one week, covering month-end batch jobs and weekend traffic shapes.

Phase 5–6: Contract and the Reverse Migration

Contract is where you drop the legacy column and add NOT NULL. Do it only after every reader is on the new path, which you verify by grepping logs for legacy query fingerprints and by checking that no pod older than the current release is still running.

BEGIN;
SET LOCAL lock_timeout = '3s';
-- Backed by the VALIDATED constraint, this is a metadata-only operation in PG 12+.
ALTER TABLE orders ALTER COLUMN fulfillment_state SET NOT NULL;
COMMIT;

-- Later, once the code no longer references it:
ALTER TABLE orders DROP COLUMN IF EXISTS legacy_fulfillment_code;

Write the reverse migration for every forward one and test it in staging. For expand phases the reverse is trivial (DROP COLUMN); for contract phases the reverse is a restore, so schedule contracts on low-risk days and take an explicit snapshot first.

MySQL: Use an External Online Schema Change Tool

MySQL 8.0 supports ALGORITHM=INSTANT for some ADD COLUMN operations, but anything else falls back to INPLACE or — worse — COPY, which rebuilds the table. Use gh-ost (triggerless, uses binlog) or Percona's pt-online-schema-change (trigger-based).

gh-ost \
  --host=primary.internal --database=app --table=orders \
  --alter="ADD COLUMN fulfillment_state VARCHAR(16) NULL" \
  --chunk-size=1000 \
  --max-load="Threads_running=30" \
  --critical-load="Threads_running=100" \
  --switch-to-rbr --exact-rowcount \
  --postpone-cut-over-flag-file=/tmp/gh-ost.postpone \
  --panic-flag-file=/tmp/gh-ost.panic \
  --verbose

The --postpone-cut-over-flag-file is the important detail: it lets the migration copy data at leisure and hand you the final cut-over as a separate, scheduled, human-approved action, rather than a lock event that happens at 3 a.m.

Distributed Coordination: Schema Version Gates

In a fleet of services, the schema is a shared interface with a version. Encode it explicitly and let services refuse to perform work they cannot safely complete.

const REQUIRED_SCHEMA_VERSION = 20240217_02; // expand migration for fulfillment_state

async function assertSchemaReady(pool: Pool): Promise<void> {
  const { rows } = await pool.query<{ version: number }>(
    `SELECT max(version)::bigint AS version FROM schema_migrations WHERE applied = true`,
  );
  const applied = Number(rows[0]?.version ?? 0);
  if (applied < REQUIRED_SCHEMA_VERSION) {
    // Fail the readiness probe: keep the pod out of the load balancer
    // rather than serving traffic against a schema it cannot handle.
    throw new Error(
      `schema not ready: applied=${applied} required=${REQUIRED_SCHEMA_VERSION}`,
    );
  }
}

Wire this into your /readyz probe. During a rolling deploy, new pods stay unready until the migration job has been applied — which turns a class of corruption bugs into a benign deploy stall.

Event schemas are migrations too

If you publish CDC or domain events to Kafka, the same rules apply with a sharper edge: consumers cannot be redeployed atomically with producers. Add fields as optional with sensible defaults, never rename a field in place (add, dual-publish, deprecate), and enforce compatibility at the registry — BACKWARD_TRANSITIVE or FULL_TRANSITIVE for anything with more than one consumer group.

Observability and Rollout Gates

Every phase needs a numeric gate, not a feeling:

• Lock wait time: max(now() - query_start) for wait_event_type = 'Lock' — alert above 2s.
• Replica lag: bytes and seconds; pause backfills above 10s or 64 MB.
• Backfill progress: count(*) WHERE new_col IS NULL trending monotonically to zero.
• Shadow mismatch rate: target exactly zero before flipping reads.
• Connection saturation: DDL and backfills eat pool slots; watch pg_stat_activity and PgBouncer queue depth.
• Deadlocks: deadlocks in pg_stat_database should not move during a backfill.

Operational Checklist

1. Migration is additive only; no NOT NULL without NOT VALID + VALIDATE.
2. lock_timeout set on every DDL transaction; retries are exponential and bounded.
3. Every new column is nullable and every reader tolerates NULL.
4. New columns are written by all live code paths (dual-write or outbox).
5. Backfill is chunked, SKIP LOCKED, idempotent, throttled, resumable.
6. Shadow reads compared in production for ≥ 1 business cycle with zero mismatches.
7. Feature flags exist for write-new, read-new, and are independently togglable.
8. Deploy order documented: expand → writers → backfill → readers → contract.
9. Reverse migration written and rehearsed on a staging snapshot.
10. Contract scheduled separately, with a snapshot and a low-risk window.

Pitfalls to Avoid

• Adding a `NOT NULL` column with a volatile default — full rewrite under ACCESS EXCLUSIVE.
• Running `CREATE INDEX CONCURRENTLY` in a transaction — it errors out, and the failed attempt leaves an INVALID index consuming writes.
• Dropping the old column in the same release as the new read path — no rollback window.
• Backfilling with `OFFSET` pagination — quadratic cost and skipped rows.
• Assuming replicas are schema-current — in multi-region setups, DDL replication lag is a real outage vector; gate on applied migration version per replica.
• Treating migration tooling as fire-and-forget — Flyway/Liquibase/golang-migrate are runners, not safety nets. Safety comes from the pattern.

The mental model that makes all of this tractable: the database is a distributed system with the longest deploy pipeline you own. Treat every schema change as an interface change with two supported versions, and zero-downtime stops being a heroic act and becomes the default outcome.

Sources & Technical References

• PostgreSQL Global Development Group — ALTER TABLE (NOT VALID, VALIDATE CONSTRAINT, non-rewriting ADD COLUMN semantics).
• PostgreSQL Global Development Group — CREATE INDEX (CONCURRENTLY, INVALID index states).
• PostgreSQL Global Development Group — Explicit Locking (lock modes and the lock queue).
• PostgreSQL Global Development Group — Client Connection Defaults (lock_timeout, statement_timeout, idle_in_transaction_session_timeout).
• PostgreSQL Wiki — Lock Dependency Information.
• Martin Fowler — ParallelChange (Expand/Contract).
• GitHub — gh-ost: GitHub's online schema migration tool for MySQL.
• Percona — pt-online-schema-change documentation.
• Oracle/MySQL — Online DDL Operations.
• Stripe Engineering — Online migrations at scale.
• Google SRE — Data Integrity: What You Read Is What You Wrote, Site Reliability Engineering, O'Reilly.
• Confluent — Schema Evolution and Compatibility.
• Ambler & Sadalage — Refactoring Databases: Evolutionary Database Design, Addison-Wesley (the canonical catalog of transitional schema refactorings).
• Atlas — Migration Linting Analyzers and Squawk — PostgreSQL Migration Rules.