Zero-Downtime Database Migration Framework

The core idea is that the database and application never need to change at the same instant. Each change is split into compatible phases so that both the previous and next application versions work against the schema at every point.

Treat every migration as a small production release with its own plan, owner, and monitoring. Automation helps, since schema migration tools can lint for dangerous operations such as table rewrites or long locks before they reach production.

How it works

Expand and contract has three stages. Expand adds the new column, table, or index without altering existing behavior. Migrate deploys code that writes to both locations, backfills history, and then reads from the new location behind a flag. Contract removes the old structure after a soak period. For engine changes, change data capture streams every write from source to target, a verification job compares row counts and checksums, and cutover moves reads first, then writes, with replication reversed for rollback.

Implementation steps

1. Write the migration plan with each phase, its rollback, and the verification query.
2. Apply additive DDL using online methods, for example concurrent index creation and nullable columns.
3. Deploy application code that writes both forms and tolerates either form on read.
4. Backfill in small batches with throttling, checkpoints, and idempotent updates.
5. Run a continuous parity check and alert on drift.
6. Switch reads using a feature flag, ramping gradually.
7. Stop old writes, wait through a rollback window, then drop old structures.

Rehearse the full sequence on a production-sized copy before the real run. Rehearsal exposes slow backfills, unexpected locks, and parity gaps while the cost of discovering them is still small.

Common pitfalls

• Renaming a column in one step, which breaks old application instances.
• Adding a non-null column with a default on a huge table, locking it.
• Running backfills at full speed and starving production queries.
• Dropping the old structure before the rollback window expires.

Metrics to watch

Replication lag, lock wait time, backfill rows per second, parity mismatch count, p99 query latency during migration, error rates on reads from the new path, and elapsed time between expand and contract.

Define abort thresholds in advance, for example pausing the backfill automatically when replication lag exceeds ten seconds or latency rises by twenty percent. Pre-agreed limits remove hesitation during incidents.

Example migration plan

-- Phase 1 expand
ALTER TABLE customers ADD COLUMN email_normalized text;
CREATE INDEX CONCURRENTLY idx_customers_email_norm ON customers (email_normalized);

-- Phase 2 backfill, repeated in batches
UPDATE customers SET email_normalized = lower(email)
WHERE id BETWEEN 100000 AND 109999 AND email_normalized IS NULL;

-- Phase 3 contract, only after reads moved and soak complete
ALTER TABLE customers DROP COLUMN email_legacy;

Key takeaways

• Never require code and schema to change atomically.
• Backfill gently and verify parity continuously.
• Keep a rollback path until the final contract step.
• Finish the contract phase, or the technical debt stays.