Designing Zero-Downtime Database Migrations in Distributed Production Environments

Schema changes are the one deployment artifact your rollback plan quietly forgets. A container image rolls back in seconds; a dropped column does not — and in a distributed system where a rolling deploy leaves three application versions talking to one database, the schema is the shared mutable state every version must agree on.

This article is a practitioner's blueprint for shipping schema evolution with zero downtime: expand/contract, idempotent backfills, lock-aware DDL, replication-lag-aware reads, and the CI guardrails that keep it honest.

Why Stop-the-World Migrations Break in Distributed Systems

Most migration failures are not SQL bugs. They are coordination bugs. Four failure modes dominate production incidents:

1. Version skew. During a rolling deploy, v41, v42, and v43 all serve traffic simultaneously. A migration that assumes only the new code exists will break the old pods still draining.
2. Lock escalation. ALTER TABLE ... ADD COLUMN ... DEFAULT on PostgreSQL < 11 rewrote the entire table under an ACCESS EXCLUSIVE lock. Even today, SET NOT NULL, ALTER TYPE, and ADD CONSTRAINT can scan gigabytes while blocking every write.
3. Replication lag as a correctness bug. A backfill writing 50k rows/second on the primary pushes replicas minutes behind. Reads routed to a replica now return data that violates read-your-writes.
4. Non-idempotent migrations. Orchestrators retry. If your migration is not safe to run twice, a network blip during deploy becomes data corruption.

The governing principle: the database schema must always be a superset of what every live application version needs.

The Expand/Contract Model: Four Phases, Never One

Expand/contract (also called parallel change) replaces a single destructive migration with a sequence of individually safe, individually deployable steps.

Phase 1 — Expand: additive-only schema changes

Add the new structure without touching the old. Every statement here must be metadata-only or near-instant.

-- migrations/0042_expand_orders_currency.sql
BEGIN;

-- Bound lock acquisition so a busy table cannot queue the whole app behind us.
SET LOCAL lock_timeout = '3s';
SET LOCAL statement_timeout = '0';

-- PostgreSQL 11+: non-volatile DEFAULT means no table rewrite.
ALTER TABLE orders
  ADD COLUMN IF NOT EXISTS currency_code char(3);

-- Enforce NOT NULL without a blocking full-table scan.
-- Step 1: NOT VALID takes ACCESS EXCLUSIVE only for a catalog update.
ALTER TABLE orders
  ADD CONSTRAINT orders_currency_code_not_null
  CHECK (currency_code IS NOT NULL) NOT VALID;

COMMIT;

-- migrations/0043_validate_orders_currency.sql
-- SHARE UPDATE EXCLUSIVE: concurrent reads and writes proceed.
ALTER TABLE orders VALIDATE CONSTRAINT orders_currency_code_not_null;

-- PostgreSQL 12+ promotes the validated CHECK to a real NOT NULL
-- without re-scanning the table.
ALTER TABLE orders ALTER COLUMN currency_code SET NOT NULL;
ALTER TABLE orders DROP CONSTRAINT orders_currency_code_not_null;

Phase 2 — Dual write and idempotent backfill

The application writes both the legacy and the new column. The legacy column remains the source of truth — reads do not change yet.

async function updateOrderCurrency(orderId: string, currency: string, db: Pool): Promise<void> {
  const client = await db.connect();
  try {
    await client.query('BEGIN');
    // Legacy column stays authoritative until Phase 3.
    await client.query(
      `UPDATE orders
          SET currency = $1,
              currency_code = $1,
              updated_at = now()
        WHERE id = $2`,
      [currency, orderId],
    );
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Phase 3 — Flip reads behind a flag

Change the read path only after the backfill reports zero remaining NULLs. Guard it with a runtime flag so rollback is a config change, not a deploy.

Phase 4 — Contract: remove the old, only after the fleet is clean

Drop the legacy column in a separate release, after telemetry confirms no live process references it. Contract migrations are one-way doors; treat them with the ceremony of a data deletion.

Making Backfills Safe: Keyset Batching with Checkpoints

Never use OFFSET — it degrades linearly and skips rows when earlier batches mutate the predicate. Use keyset pagination, bounded transactions, and a restartable cursor.

import logging
import time
from dataclasses import dataclass

import psycopg

log = logging.getLogger("backfill")
BATCH_SIZE = 5_000
SLEEP_BETWEEN_BATCHES_S = 0.25

@dataclass(frozen=True)
class Progress:
    total: int = 0
    cursor: int = 0

def backfill_currency(conn: psycopg.Connection, progress: Progress, dry_run: bool = False) -> Progress:
    """Idempotent: re-running resumes from the last committed cursor,
    and rows already populated are excluded by the WHERE clause."""
    state = progress
    while True:
        with conn.transaction():
            rows = conn.execute(
                """
                SELECT id FROM orders
                 WHERE id > %s AND currency_code IS NULL
                 ORDER BY id
                 LIMIT %s
                 FOR UPDATE SKIP LOCKED
                """,
                (state.cursor, BATCH_SIZE),
            ).fetchall()

            if not rows:
                return state

            ids = [r[0] for r in rows]
            if not dry_run:
                conn.execute(
                    """
                    UPDATE orders o
                       SET currency_code = COALESCE(
                             (SELECT s.currency FROM order_snapshots s
                               WHERE s.order_id = o.id LIMIT 1), 'USD')
                     WHERE o.id = ANY(%s) AND o.currency_code IS NULL
                    """,
                    (ids,),
                )

        state = Progress(total=state.total + len(ids), cursor=ids[-1])
        log.info("backfill_progress", extra={"total": state.total, "cursor": state.cursor})
        time.sleep(SLEEP_BETWEEN_BATCHES_S)  # yield to OLTP traffic

FOR UPDATE SKIP LOCKED lets multiple workers share the job without contending. The AND currency_code IS NULL predicate makes the whole routine idempotent under retry.

Replication Lag Is a Correctness Bug, Not a Performance Bug

On PostgreSQL, measure the gap in bytes and time — do not infer it from CPU:

SELECT client_addr,
       state,
       pg_wal_lsn_diff(pg_current_wal_lsn(), replay_lsn) AS replay_lag_bytes,
       replay_lag
  FROM pg_stat_replication;

For read-your-writes guarantees during the migration window, either pin the user's session to the primary, or pass the write's LSN forward and block on the replica:

-- On the replica, after receiving the primary's LSN from the write path
SELECT pg_last_wal_replay_lsn() >= $1::pg_lsn AS caught_up;

Online DDL: Know What Your Engine Actually Does

On MySQL 8.0, INSTANT and INPLACE algorithms avoid table copies — but MODIFY COLUMN type changes and many index rebuilds still force COPY. Use an external tool for those, and always throttle against replica lag:

gh-ost \
  --host=primary.internal --database=app --table=orders \
  --alter="MODIFY COLUMN currency_code CHAR(3) NOT NULL DEFAULT 'USD'" \
  --allow-on-master --assume-rbr \
  --max-load='Threads_running=40' --critical-load='Threads_running=200' \
  --chunk-size=1000 \
  --throttle-control-replicas=replica-1.internal,replica-2.internal \
  --postpone-cut-over-flag-file=/tmp/gh-ost.postpone \
  --execute

pt-online-schema-change \
  --alter "ADD COLUMN currency_code CHAR(3) NULL" \
  D=app,t=orders --max-lag=2s \
  --critical-load Threads_running=200 --chunk-time=0.5 --execute

The --postpone-cut-over-flag-file is the critical detail: it lets you defer the final atomic table swap to a low-traffic window you control.

Guardrails: Linters, Shadow Rehearsals, and Timeouts

Catch unsafe DDL in CI, not in production. Static linters encode your team's hard-won rules:

# .github/workflows/migrations.yml
- name: Lint migrations
  run: squawk --config .squawk.toml migrations/*.sql

- name: Rehearse against a shadow database
  run: |
    atlas migrate apply \
      --url "$SHADOW_DB_URL" \
      --dir "file://migrations"

Pair linters with three runtime settings on every migration connection: lock_timeout (fail fast rather than queue), statement_timeout (bound runaway backfills), and one transaction per migration file (partial application is worse than a failed run).

Pre-Flight Operational Checklist

• Migration is additive; no DROP, RENAME, or type narrowing in the same release as code changes.
• Every ALTER TABLE is wrapped in SET LOCAL lock_timeout and verified against a production-sized dataset clone.
• Backfill uses keyset pagination, bounded batches, SKIP LOCKED, and is safe to re-run from any cursor.
• Replica lag alerting is armed with a threshold that pauses the backfill automatically.
• Old and new code paths both tolerate the schema (dual-write confirmed by integration tests on both versions).
• Rollback plan is a code/flag revert, never a reverse migration.
• Contract phase scheduled at least one release after read flip, gated on zero-reference telemetry.

Pitfalls to Avoid

• `NOT NULL` without a `NOT VALID` check. On large tables this is a silent multi-minute outage.
• Backfill and DDL in the same transaction. The lock is held for the entire scan.
• `ADD COLUMN ... DEFAULT volatile_fn()`. Volatile defaults force a full rewrite; use NULL plus backfill instead.
• *Trusting `SELECT count()` progress.** It is a full scan on every poll — use the cursor value or an approximate counter.
• Running migrations inside the deploy pipeline for every pod. Elect a single migration job with a leader lock (advisory lock or flyway_schema_history-style table).
• Dropping the legacy column in the same release as the read flip. Any straggler pod or batch job will throw immediately.

Sources & Technical References

• PostgreSQL Global Development Group — ALTER TABLE (locking behavior, NOT VALID, VALIDATE CONSTRAINT): https://www.postgresql.org/docs/current/sql-altertable.html
• PostgreSQL 11 Release Notes — non-volatile DEFAULT no longer rewrites tables: https://www.postgresql.org/docs/11/release-11.html
• PostgreSQL — Explicit Locking and lock conflict matrix: https://www.postgresql.org/docs/current/explicit-locking.html
• PostgreSQL — Monitoring Stats, pg_stat_replication and pg_stat_wal_receiver: https://www.postgresql.org/docs/current/monitoring-stats.html
• Oracle — MySQL 8.0 Online DDL Operations (INSTANT / INPLACE / COPY matrix): https://dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-operations.html
• GitHub — gh-ost: GitHub's Online Schema-Migration Tool for MySQL: https://github.com/github/gh-ost
• Percona — pt-online-schema-change documentation: https://docs.percona.com/percona-toolkit/pt-online-schema-change.html
• Martin Fowler — ParallelChange bliki entry: https://martinfowler.com/bliki/ParallelChange.html
• Scott Ambler & Pramod Sadalage — Refactoring Databases: Evolutionary Database Design, Addison-Wesley, 2006.
• AWS Builders' Library — Ensuring Rollback Safety During Deployments: https://aws.amazon.com/builders-library/ensuring-rollback-safety-during-deployments/
• Google SRE — Site Reliability Engineering (release engineering and canarying): https://sre.google/sre-book/table-of-contents/
• Squawk — PostgreSQL migration linter: https://squawkhq.com/
• Atlas — Migration Linting Analyzers: https://atlasgo.io/lint/analyzers