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

Every production incident I have traced back to a schema change had the same root cause: the SQL was fine; the sequencing was not. A column was renamed in one release and read by three services in another. A backfill saturated the primary's I/O budget during peak traffic. A CREATE INDEX queued behind an ACCESS EXCLUSIVE lock and the connection pool drained in eleven seconds.

In a monolith, a migration is a deployment detail. In a distributed system, the database schema is the single most widely shared mutable state you own — every service, every CDC consumer, every analytics job, and every dashboard is a downstream reader of a contract you are about to change. Treat migrations accordingly: as protocol changes across a fleet, not as DDL statements run by a robot at deploy time.

Why Database Migrations Break in Distributed Systems

The failure surface has three independent axes, and mature teams design against all three:

1. Time — how long the DDL holds a lock, and what the lock queue does to your connection pool.
2. Space — the window in which N running process versions coexist against one schema (Kubernetes rolling updates guarantee this window exists).
3. Blast radius — how many independent consumers depend on the column, index, or event you are mutating.

The Four Failure Modes

The Expand/Contract Protocol (Parallel Change)

This is the load-bearing pattern. It converts one dangerous atomic cutover into three boring, independently reversible releases.

Phase 0 — Expand: Never Break an Existing Reader

Expand means additive only. New columns arrive nullable with no default that forces a table rewrite, and absent defaults on high-volume tables should be expressed as application-level defaults or ALTER TABLE ... SET DEFAULT paired with a backfill. No renames. No drops. No type changes.

Phase 1 — Migrate: Backfill Is a Job, Not a Statement

Backfill is a workload with an SLO, not a SQL line item. Batch it, throttle it, checkpoint it, and watch replication lag while it runs.

Phase 2 — Contract: Delete Slowly and Deliberately

Only drop the old column after telemetry proves nobody reads it. A last_read_at metric on a dual-written field is worth more than any migration policy document.

Lock-Free DDL: A PostgreSQL and MySQL Playbook

PostgreSQL

-- ❌ ACCESS EXCLUSIVE + full table rewrite: blocks all reads and writes for the duration.
ALTER TABLE orders ALTER COLUMN status TYPE varchar(32);

-- ✅ Expand — metadata-only in PG 11+; no rewrite, no long lock.
ALTER TABLE orders ADD COLUMN status_v2 varchar(32);

-- ✅ Enforce correctness without a blocking validation scan.
ALTER TABLE orders
  ADD CONSTRAINT orders_status_v2_ck
  CHECK (status_v2 IN ('pending','paid','shipped','refunded')) NOT VALID;

-- ✅ VALIDATE takes SHARE UPDATE EXCLUSIVE: reads/writes continue.
ALTER TABLE orders VALIDATE CONSTRAINT orders_status_v2_ck;

-- ✅ Index without blocking writers (cannot run inside a transaction block).
CREATE INDEX CONCURRENTLY idx_orders_status_v2 ON orders (status_v2);

Two operational rules that follow from this: set lock_timeout on every migration session (3 seconds is a good default — better to fail fast than to queue your entire traffic behind your own DDL), and mark CREATE INDEX CONCURRENTLY migrations as non-transactional in your runner. In Liquibase that is runInTransaction="false"; Flyway requires a separate script configuration. A runner that wraps CONCURRENTLY in BEGIN will fail every time.

MySQL 8.0 and Table Rebuilds

-- ✅ Instant DDL: metadata-only change, no table copy.
ALTER TABLE orders
  ADD COLUMN status_v2 VARCHAR(32) NULL,
  ALGORITHM=INSTANT, LOCK=NONE;

When an operation cannot be INSTANT, do not accept a silent rebuild. Use gh-ost or pt-online-schema-change to copy into a shadow table and cut over via an atomic rename, and always specify ALGORITHM/LOCK explicitly so MySQL errors out instead of quietly degrading to COPY.

Making the Migration Runner Safe Under Concurrency

In Kubernetes, replicas: 12 means twelve processes may attempt migrations simultaneously. Use an advisory lock plus checksums, and prefer running migrations as a dedicated pre-deploy Job with backoffLimit: 0 so a failure halts the rollout instead of racing it.

import { Pool, PoolClient } from 'pg';
import { createHash } from 'node:crypto';

const LOCK_KEY = '8142119003'; // stable, app-specific int64

export interface Migration {
  id: string;
  sql: string;
  transactional?: boolean; // false for CREATE INDEX CONCURRENTLY
}

export async function runMigrations(pool: Pool, migrations: Migration[]): Promise<void> {
  const client: PoolClient = await pool.connect();
  try {
    // Session-scoped lock: auto-released if the process is OOM-killed.
    const { rows } = await client.query<{ locked: boolean }>(
      'SELECT pg_try_advisory_lock($1::bigint) AS locked',
      [LOCK_KEY],
    );
    if (!rows[0].locked) {
      console.info('migrator lock held elsewhere; skipping');
      return;
    }

    await client.query(`
      CREATE TABLE IF NOT EXISTS schema_migrations (
        id         text PRIMARY KEY,
        checksum   text NOT NULL,
        applied_at timestamptz NOT NULL DEFAULT now()
      )`);

    for (const m of migrations) {
      const checksum = createHash('sha256').update(m.sql).digest('hex');
      const prior = await client.query<{ checksum: string }>(
        'SELECT checksum FROM schema_migrations WHERE id = $1', [m.id],
      );
      if (prior.rowCount === 1) {
        if (prior.rows[0].checksum !== checksum) {
          throw new Error(`checksum drift on applied migration ${m.id}`);
        }
        continue;
      }

      const transactional = m.transactional !== false;
      if (transactional) await client.query('BEGIN');
      await client.query("SET LOCAL lock_timeout = '3s'");
      await client.query("SET LOCAL statement_timeout = '120s'");

      try {
        await client.query(m.sql);
        await client.query(
          'INSERT INTO schema_migrations (id, checksum) VALUES ($1, $2)',
          [m.id, checksum],
        );
        if (transactional) await client.query('COMMIT');
      } catch (err) {
        if (transactional) await client.query('ROLLBACK');
        throw new Error(`migration ${m.id} failed: ${(err as Error).message}`, { cause: err });
      }
    }
  } finally {
    await client.query('SELECT pg_advisory_unlock($1::bigint)', [LOCK_KEY]).catch(() => {});
    client.release();
  }
}

Backfills: Batched, Resumable, and Rate-Limited

Keyset pagination — never OFFSET. Persist the cursor so a pod eviction costs you one batch, not an hour. Throttle with a floor on batch duration rather than a fixed sleep, so you adapt to instance size.

const JOB = 'orders.status_v2';

export async function backfillOrders(pool: Pool, batchSize = 500): Promise<void> {
  const { rows: [job] } = await pool.query<{ cursor: string | null; done: boolean }>(
    `INSERT INTO backfill_jobs (name, cursor, done) VALUES ($1, NULL, false)
     ON CONFLICT (name) DO UPDATE SET name = EXCLUDED.name
     RETURNING cursor, done`, [JOB],
  );
  if (job.done) return;

  let lastId = job.cursor ? Number(job.cursor) : 0;

  for (;;) {
    const startedAt = Date.now();
    const { rows, rowCount } = await pool.query<{ id: string }>(
      `UPDATE orders
          SET status_v2 = legacy_status_to_v2(status)
        WHERE id IN (
          SELECT id FROM orders
           WHERE id > $1 AND status_v2 IS NULL
           ORDER BY id
           LIMIT $2
           FOR UPDATE SKIP LOCKED
        )
        RETURNING id`,
      [lastId, batchSize],
    );
    if (!rowCount) break;

    lastId = Number(rows[rows.length - 1].id);
    await pool.query('UPDATE backfill_jobs SET cursor = $2 WHERE name = $1',
      [JOB, String(lastId)]);

    // Adaptive throttle: hold each batch to a floor of wall-clock time.
    const elapsed = Date.now() - startedAt;
    if (elapsed < 50) await new Promise((r) => setTimeout(r, 50 - elapsed));
  }

  await pool.query('UPDATE backfill_jobs SET done = true WHERE name = $1', [JOB]);
}

Watch replication lag and p99 write latency, not rows-per-second. If a read replica lags past your failover threshold, you have converted a data migration into an availability risk.

Dual Writes and Read Fallbacks

During the transition window, every write must populate both fields atomically, and every read must tolerate the new field being NULL.

import type { Pool } from 'pg';

export type OrderStatus = 'pending' | 'paid' | 'shipped' | 'refunded';

export async function updateOrderStatus(
  db: Pool, id: string, next: OrderStatus,
): Promise<void> {
  await db.query(
    `UPDATE orders SET status = $2, status_v2 = $2, updated_at = now() WHERE id = $1`,
    [id, next],
  );
}

// Read fallback: v2 is authoritative only once non-null.
export function readOrderStatus(row: { status: string; status_v2: string | null }): OrderStatus {
  return (row.status_v2 ?? row.status) as OrderStatus;
}

The Mismatch Detector That Earns Its Keep

Run this continuously during Phase 1 and alert on any non-zero result:

SELECT count(*) AS mismatches
FROM orders
WHERE status_v2 IS NOT NULL
  AND status_v2 IS DISTINCT FROM status;

Event Streams: The Migration Isn't Done Until Consumers Move

If you emit OrderStatusChanged via Debezium CDC or an outbox table, a column rename is an event contract change. Apply the same expand/contract discipline at the schema-registry layer: add the new field as optional, keep the old field populated for at least one full consumer-deploy cycle, and rely on BACKWARD_TRANSITIVE (or stricter) compatibility to make the contract machine-checkable rather than aspirational.

Observability and Rollback Gates

-- Live blocking chains caused by your DDL or backfill.
SELECT pid, wait_event_type, wait_event,
       left(query, 80) AS query, now() - query_start AS age
FROM pg_stat_activity
WHERE state <> 'idle' AND wait_event_type = 'Lock'
ORDER BY age DESC;

Prefer roll-forward with kill switches over down-migrations. A feature flag that reverts the read path to the legacy column is instantaneous and lossless; a DROP COLUMN reversal is neither. Never ship a destructive migration in the same release as the code that stops using the column — separate them by at least two deploy cycles and a full business reporting period.

Production Migration Checklist

• Every DDL statement is additive in this release; nothing dropped or renamed.
• lock_timeout and statement_timeout set on every migration session.
• CREATE INDEX CONCURRENTLY / ALGORITHM=INSTANT used and marked non-transactional.
• Migration runs as a single locked job, not from every pod.
• Backfill is keyset-paginated, checkpointed, throttled, and idempotent.
• Mismatch detector is live and alerting before backfill starts.
• Old column has a last_read_at metric proving zero traffic before drop.
• Downstream CDC/event consumers have shipped their half of the contract.
• Rollback plan is a feature flag, not a reverse migration.

Pitfalls to Avoid

• `ADD COLUMN ... DEFAULT random()` on PostgreSQL < 11 forces a full rewrite under ACCESS EXCLUSIVE.
• `NOT VALID` constraints that are never validated silently accumulate violations; schedule VALIDATE CONSTRAINT explicitly.
• Backfilling inside the migration transaction turns a 40-minute job into a 40-minute lock.
• Assuming `CREATE INDEX CONCURRENTLY` can't fail — an invalid index can be left behind; check pg_index.indisvalid and REINDEX CONCURRENTLY when needed.
• Deploying code before schema on expand, or schema before code on contract. The order is always: expand schema → deploy code → backfill → contract.

Sources & Technical References

• PostgreSQL Global Development Group — ALTER TABLE and CREATE INDEX (CONCURRENTLY)
• PostgreSQL Global Development Group — Explicit Locking: Advisory Locks
• PostgreSQL Global Development Group — Transaction Isolation and Routine Vacuuming
• Oracle/MySQL Reference Manual — InnoDB Online DDL Operations
• GitHub — gh-ost: GitHub's Online Schema Migration Tool for MySQL
• Percona — pt-online-schema-change
• Martin Fowler — ParallelChange
• Debezium Documentation — Schema Evolution and Compatibility
• Redgate Flyway — Migration Concepts and Liquibase — Changeset `runInTransaction`
• Martin Kleppmann — Designing Data-Intensive Applications, Ch. 4 (Encoding and Evolution), O'Reilly, 2017.
• Google SRE — Site Reliability Engineering, Ch. 21 (Handling Cascading Failures).