Zero-Downtime Database Migration Playbook

Context and Goals

Database migrations are where application velocity meets immovable state. A careless ALTER during peak traffic can lock tables, corrupt partial writes, or force a multi-hour outage. Teams that treat migrations as afterthoughts learn expensive lessons when renaming a column takes down checkout.

Zero-downtime migration does not mean zero planning. It means sequencing changes so old and new code—and old and new schema—coexist safely during a bounded transition window. The goal is predictable promotion with measurable checkpoints, not heroic midnight DDL.

This playbook is for backend engineers and DBAs shipping schema changes in continuously deployed systems. You will learn expand-contract workflows, backfill discipline, and operational guardrails that scale beyond one-off heroics.

Implementation Blueprint

Adopt expand-contract (parallel change) as default. Expand: add new columns, tables, or indexes without removing old shapes; deploy application code that writes to both or reads with fallback. Migrate: backfill data in batches with idempotent jobs, throttled to protect production IO. Contract: stop writing to legacy fields, remove reads, then drop obsolete schema only when telemetry proves zero dependency.

Classify migrations by risk—additive index, nullable column, type widening, destructive drop—and map each to required steps. Destructive changes never ship in one release. Use online schema tools appropriate to your engine (pt-online-schema-change, gh-ost, native online DDL) and benchmark on production-sized fixtures.

Pair every migration with application feature flags or version gates so code paths activate only after schema is ready. Run pre-flight checks: replication lag, disk headroom, long-running transactions, and lock wait thresholds. Define automatic abort criteria before applying change two of a multi-phase plan.

Depth: Backfills, Replication, and Verification

Backfills should be resumable, keyed by primary key ranges, and observable—rows processed per minute, error counts, and lag versus live writes. Handle concurrent updates with updated_at comparisons or merge rules documented in the runbook. Never assume a one-shot UPDATE is safe on billion-row tables.

On replicated topologies, verify changes propagate before contract phase. Failover during dual-write windows needs explicit policy: which writer is authoritative and how conflicts reconcile. Test failover mid-migration in staging at least quarterly.

Verification includes row counts, checksum samples, and application-level smoke tests on read paths using new schema. Keep migration audit logs: who approved, which phase, start/end times, and rollback executed or not.

Trade-offs and Pitfalls

Expand-contract lengthens calendar time but shrinks blast radius—resist pressure to combine phases for speed. A common pitfall is nullable new columns without application defaults, causing subtle NULL bugs in downstream analytics. Another is dropping an index before query plans are validated, spiking CPU company-wide.

Do not rely on maintenance windows as the primary strategy unless your product truly tolerates hard stops. Even then, rehearse the migration; undocumented steps become outages.