Designing Zero-Downtime Database Migrations in Distributed Production Environments

Why Zero-Downtime Migrations Fail in Distributed Systems

Every failed migration I have post-mortemed shares one root cause: the team treated a schema change as a SQL exercise when it was a distributed-systems coordination problem. ALTER TABLE is rarely what pages you. What pages you is the overlap window — the interval where application version N and schema version N+1 are both live, and one of them makes an assumption that is no longer true.

That window is structurally unavoidable. A rolling deploy means dozens of pods running mixed binaries for minutes. Blue/green means two complete stacks sharing one database. Read replicas replay WAL seconds behind the primary. Async workers drain queues full of messages serialized by code you deleted last Tuesday. Your database is the only stateful, shared component in the system, and it does not participate in your atomic deploy.

The consequence is a hard architectural invariant:

The database must simultaneously satisfy every application version currently running, and no destructive change may ship in the same deploy as the code that stops using the old shape.

Everything below is a consequence of that invariant.

The Expand / Migrate / Contract Protocol

Also called parallel change, this three-phase protocol is the backbone of safe schema evolution. Each phase is a separate deploy, independently reversible.

The single most common violation: collapsing phases 2 and 3 into one deploy. If the same release stops writing email and drops email, any pod still on the previous ReplicaSet will throw column does not exist the moment the rollout reaches 50%.

Renaming a Column Without Breaking Anyone

ALTER TABLE ... RENAME COLUMN is an atomic metadata operation — and a guaranteed outage. Renaming is a contract-phase operation, never an expand-phase one. Instead:

1. Expand: ADD COLUMN email_normalized text
2. Migrate: dual-write to both email and email_normalized; backfill; shadow-read to verify parity.
3. Contract (release N+2): drop email.

Adding NOT NULL Without a Full-Table Lock

A naive ALTER TABLE users ALTER COLUMN email SET NOT NULL takes an ACCESS EXCLUSIVE lock and scans the whole table. On a 400 GB table behind a busy connection pool, that is a multi-minute write outage while the lock queue backs up. Use the validated-check trick, which takes SHARE UPDATE EXCLUSIVE and does not block DML:

-- Step 1: cheap metadata change, no scan
ALTER TABLE users
  ADD CONSTRAINT users_email_not_null
  CHECK (email IS NOT NULL) NOT VALID;

-- Step 2: scan without blocking writes (separate transaction)
ALTER TABLE users VALIDATE CONSTRAINT users_email_not_null;

-- Step 3: PostgreSQL 12+ reuses the validated CHECK and skips the scan
ALTER TABLE users ALTER COLUMN email SET NOT NULL;
ALTER TABLE users DROP CONSTRAINT users_email_not_null;

The Lock Queue Is the Real Outage

Here is the mechanism most engineers miss. In PostgreSQL, a pending ACCESS EXCLUSIVE request does not merely wait — it blocks every lock request queued behind it, including plain SELECTs. A migration that waits 30 seconds for a long-running analytics query stalls the entire table for 30 seconds, burning through your connection pool and cascading into a full outage.

The mitigation is not "run migrations at night." It is to make the migration give up:

BEGIN;
SET LOCAL lock_timeout = '3s';        -- fail fast instead of queuing
SET LOCAL statement_timeout = '30s';  -- cap the work
SET LOCAL idle_in_transaction_session_timeout = '10s';

ALTER TABLE orders ADD COLUMN settlement_currency char(3);

COMMIT;

With lock_timeout, a contended migration aborts and retries rather than holding the queue hostage. Your migration runner must treat that abort as a retryable condition.

A Production Migration Runner

Advisory locks prevent two pods from racing the same migration during a rolling deploy — the scenario where autoscaling spins up three replicas and each one independently runs the pre-deploy hook.

import type { Pool, PoolClient } from 'pg';

const MIGRATION_LOCK_ID = 8123456; // arbitrary, cluster-unique bigint

interface Migration {
  id: string;
  sql: string;
  /** true for CREATE INDEX CONCURRENTLY etc. — those cannot run in a transaction */
  transactional: boolean;
}

export async function runMigrations(pool: Pool, migrations: Migration[]): Promise<string[]> {
  const client: PoolClient = await pool.connect();
  const applied: string[] = [];

  try {
    const { rows } = await client.query<{ locked: boolean }>(
      'SELECT pg_try_advisory_lock($1) AS locked',
      [MIGRATION_LOCK_ID],
    );
    if (!rows[0]?.locked) {
      console.log('[migrate] another instance holds the lock; skipping');
      return applied;
    }

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

    for (const m of migrations) {
      const { rowCount } = await client.query('SELECT 1 FROM schema_migrations WHERE id = $1', [m.id]);
      if (rowCount) continue;

      // Retry lock contention with exponential backoff + jitter
      for (let attempt = 0; ; attempt++) {
        try {
          if (m.transactional) {
            await client.query('BEGIN');
            await client.query(m.sql);
            await client.query('INSERT INTO schema_migrations (id) VALUES ($1)', [m.id]);
            await client.query('COMMIT');
          } else {
            await client.query(m.sql);
            await client.query('INSERT INTO schema_migrations (id) VALUES ($1)', [m.id]);
          }
          applied.push(m.id);
          break;
        } catch (err: unknown) {
          await client.query('ROLLBACK').catch(() => undefined);
          const code = (err as { code?: string }).code;
          const retryable = code === '55P03' /* lock_not_available */ || code === '57014' /* query_canceled */;
          if (!retryable || attempt >= 5) throw err;
          await new Promise((r) => setTimeout(r, 2 ** attempt * 250 + Math.random() * 250));
        }
      }
    }
  } finally {
    await client.query('SELECT pg_advisory_unlock($1)', [MIGRATION_LOCK_ID]).catch(() => undefined);
    client.release();
  }

  return applied;
}

Note the transactional flag: CREATE INDEX CONCURRENTLY cannot run inside a transaction block, which is why tools like Flyway expose executeInTransaction=false and why naive runners deadlock themselves.

Backfill Engineering: Batched, Resumable, Idempotent

A backfill is a data pipeline wearing a migration costume. It must be idempotent (safe to replay), resumable (the predicate is the cursor, not an in-memory offset), and throttled against replica lag — because backfill writes flood the WAL, replicas fall behind, and your read path starts serving stale or inconsistent rows to users.

import type { Pool } from 'pg';

interface BackfillConfig {
  batchSize: number;              // 1k-5k rows; smaller = gentler
  pauseMs: number;
  maxReplicaLagSeconds: number;   // abort threshold
  maxBatches: number;             // circuit breaker
}

const LAG_SQL = `
  SELECT COALESCE(EXTRACT(EPOCH FROM max(replay_lag)), 0)::float AS lag_seconds
  FROM pg_stat_replication
`;

export async function backfillNormalizedEmail(
  pool: Pool,
  cfg: BackfillConfig = { batchSize: 2000, pauseMs: 250, maxReplicaLagSeconds: 5, maxBatches: 50_000 },
): Promise<{ processed: number }> {
  let processed = 0;

  for (let batch = 0; batch < cfg.maxBatches; batch++) {
    let rows: number;
    try {
      const res = await pool.query(
        `WITH claimed AS (
           SELECT id FROM users
           WHERE email_normalized IS NULL
           ORDER BY id
           LIMIT $1
           FOR UPDATE SKIP LOCKED
         )
         UPDATE users u
         SET email_normalized = lower(btrim(u.email))
         FROM claimed c
         WHERE u.id = c.id`,
        [cfg.batchSize],
      );
      rows = res.rowCount ?? 0;
    } catch (err: unknown) {
      // Deadlocks and statement timeouts are expected under load.
      const code = (err as { code?: string }).code;
      console.warn('[backfill] batch error', { batch, code, processed });
      await new Promise((r) => setTimeout(r, 1_000));
      continue; // predicate is idempotent — safe to retry the same rows
    }

    if (rows === 0) return { processed };
    processed += rows;

    const { rows: lagRows } = await pool.query<{ lag_seconds: number }>(LAG_SQL);
    const lag = lagRows[0]?.lag_seconds ?? 0;
    await new Promise((r) =>
      setTimeout(r, lag > cfg.maxReplicaLagSeconds ? cfg.pauseMs * 20 : cfg.pauseMs),
    );
  }

  throw new Error(`backfill exceeded maxBatches (${cfg.maxBatches}); investigate before continuing`);
}

FOR UPDATE SKIP LOCKED lets multiple workers (or multiple shards) drain the same table concurrently without contention. Because the cursor is the predicate email_normalized IS NULL, the job can be killed and restarted at any point with zero coordination state.

Orchestrating the Rollout Across a Distributed Fleet

Deploy Ordering Is the Contract

Encode the phase boundary in your pipeline, not in a runbook nobody reads. A CI gate that refuses to apply any DROP or SET NOT NULL statement unless the corresponding feature flag has been at 100% for N hours converts tribal knowledge into an enforced invariant.

Dual-Write and Shadow Reads

During migrate, write both shapes and read the old one. Periodically compute both and emit a diff metric:

const [legacy, modern] = await Promise.all([readLegacy(id), readModern(id)]);
if (legacy.email_normalized !== modern.email_normalized) {
  metrics.increment('migration.shadow_read.mismatch', { entity: 'users' });
  logger.warn('shadow mismatch', { id: mask(id) });
}

Ship the shadow comparison at low sample rates (1-5%) to keep overhead negligible. A nonzero mismatch rate is your abort signal — never flip reads while it is climbing.

Treat Event Schemas as Public APIs

In an event-driven system, the migration surface extends into Kafka/SQS/CDC streams. Adding a column to an outbox payload is safe only if every consumer tolerates unknown fields (Avro/Protobuf with schema registry, or JSON with explicit ignore-unknown semantics). Renaming or removing a field requires the same expand/contract discipline, with the consumer deploy landing before the producer's contract phase.

Sharded and Multi-Region Topologies

• Never DDL all shards simultaneously. A fleet-wide ALTER is a fleet-wide lock window. Roll shard-by-shard with a health gate between each.
• Account for per-shard skew. Shard 3 may hold 60% of your rows; batch sizes tuned on shard 1 will hammer it.
• Cross-region replication adds a second lag dimension. Your abort thresholds must be per-region, not global averages.

Observability and Automatic Abort Criteria

A migration without a rollback tripwire is an unbounded experiment. Instrument and gate on:

Wire these into the migration orchestrator so a breach halts the backfill automatically — the loop above already demonstrates the pattern with maxReplicaLagSeconds.

Testing Migrations Against Production-Shaped Data

Migrations that pass on a 1,000-row fixture and fail on 400 million rows are the norm, not the exception. Minimum viable test harness:

1. Volume test: restore an anonymized production snapshot into a staging cluster; run the full three-phase sequence and record lock wait and duration.
2. Concurrency test: drive synthetic write load (e.g. pgbench) during the migration and assert zero failed transactions.
3. Mixed-version test: run v(N) and v(N+1) binaries simultaneously against the migrated schema. This is the only test that catches backwards-compatibility violations.
4. Rollback rehearsal: execute the reverse of the expand phase and confirm no data loss.

Operational Checklist

• Migration is split into expand / migrate / contract deploys — no single deploy both writes and drops.
• lock_timeout and statement_timeout set on every DDL session.
• Indexes created with CONCURRENTLY, outside a transaction.
• Backfill is batched, idempotent, resumable via predicate, and lag-aware.
• Advisory lock guards the migration runner against duplicate execution.
• Feature flags gate every read-path flip.
• Shadow-read diff metric exists and is near zero before flipping.
• Dashboards for lock waits, replica lag, and write p99 are open during the change window.
• Rollback path documented and rehearsed for the expand phase.
• Contract phase scheduled as a separate release, at least one full deploy cycle later.

Pitfalls to Avoid

• Trusting `ALTER TABLE` speed from the docs. Locking behavior is version- and engine-specific. Verify on your version (PostgreSQL 11+ metadata-only defaults, MySQL 8.0.12+ ALGORITHM=INSTANT) against your actual schema.
• MySQL `ALGORITHM=INPLACE` is not free. It avoids table rebuilds but still takes an exclusive metadata lock at start and end; long-running transactions will block it. gh-ost or pt-online-schema-change remain the escape hatch for large tables.
• Migrations embedded in application startup. In autoscaled fleets this multiplies the blast radius. Run migrations as a discrete, single-writer job before the rollout.
• Ignoring `pg_stat_activity` during the window. One idle-in-transaction session from a forgotten debugger will block your DDL indefinitely.
• Forgetting the ORM cache. Connection-pooled prepared statements and ORM schema caches can hold stale column lists after a contract phase; force a pool recycle.

Sources & Technical References

• PostgreSQL Global Development Group — ALTER TABLE (locking modes, NOT VALID constraints, metadata-only defaults in PG 11+)
• PostgreSQL Global Development Group — CREATE INDEX (CONCURRENTLY semantics and transaction restrictions)
• PostgreSQL Global Development Group — Explicit Locking (lock queue behavior and ACCESS EXCLUSIVE conflicts)
• PostgreSQL Global Development Group — Advisory Locks
• PostgreSQL Global Development Group — Monitoring Replication (pg_stat_replication, replay_lag)
• Oracle — MySQL 8.0 Online DDL Operations
• Martin Fowler — ParallelChange (expand/contract pattern origin)
• GitHub — gh-ost: Online Schema Migration for MySQL
• Percona — pt-online-schema-change
• Redgate — Flyway Documentation (script configuration, executeInTransaction)
• Liquibase — Official Documentation
• Google SRE — Canarying Releases (gradual rollout and abort criteria design)