Chapter 12

Migrations at scale

📖 7 min read · 12 sections · May 2026

Why this matters

The single most common Staff-level project: "we need to move data / change a schema / replace a service / split a monolith without downtime." Every meaningfully old system has migrations in its future. Getting them right separates Staff engineers from everyone else.

Migrations are uniquely difficult because:

They're inherently risky (you're modifying production-critical state)
Rollback is often impossible (you can't un-migrate data)
They're long-running (weeks to months for big ones)
They require coordination across teams
Mistakes are visible to users

The discipline of doing migrations safely is mostly: break them into reversible, observable steps; verify each step before proceeding; have a rollback plan for each phase.

The migration playbook

The five-phase model that applies to almost any non-trivial migration:

Phase 1: Build new alongside old. The new system / schema / service exists but isn't used. Writes go only to old.
Phase 2: Dual-write. Writes go to both old and new. Reads still come from old. New has the data but is "trailing" the old (potentially with bugs).
Phase 3: Backfill. Historical data is copied from old to new. Continues during ongoing dual-writes.
Phase 4: Dual-read with verification. Reads come from old (authoritative) but also from new; results compared. Discrepancies logged and investigated. This is the verification gate.
Phase 5: Cutover. Reads switch to new (now authoritative). Old becomes read-only / backup. Wait for confidence period.
Phase 6: Cleanup. Stop writing to old. Eventually delete old.

Each phase is a checkpoint with rollback to the previous phase. You don't commit forward until you're confident.

Online schema changes

The simplest migration: change the schema of a table without downtime.

Naive approach: ALTER TABLE users ADD COLUMN preferences JSONB. For a small table, fine. For a 100GB table:

Postgres: ADD COLUMN with DEFAULT NULL is fast (metadata-only). With non-null default, varies. Older versions rewrite the table (long, locks).
MySQL: depends on engine and operation. Many ALTER TABLE operations rewrite the whole table while blocking writes.

For risky ALTERs, use shadow-table tools:

pt-online-schema-change (Percona, MySQL): creates a new table with the desired schema, copies data in chunks, syncs via triggers, swaps tables atomically.
gh-ost (GitHub, MySQL): same idea but uses binlog instead of triggers — lighter overhead.
Postgres: native approach via partitioned tables (decompose, then re-aggregate), or third-party tools like pg_repack.

These tools are mature. For any ALTER on a table > 10GB or with high write traffic, use them.

Schema evolution patterns

Add a column

Add column nullable (or with default). Fast metadata-only operation on modern PG.
Deploy code that writes the new column (still reads from old).
Backfill the column for existing rows.
Deploy code that reads the new column.
If made NOT NULL, do it only after backfill is complete and verified.

Rename a column

Don't rename directly. Do an expand-contract pattern:

Add new column with new name.
Deploy code that writes to both old and new column.
Backfill new column from old.
Deploy code that reads from new column.
Deploy code that writes only new column.
Drop old column.

This is 6 deploys but each is safe and reversible. A single "RENAME COLUMN" is a coupled deploy nightmare — old code references one name, new code references another; if you roll back the deploy without rolling back the schema, things break.

Change a column type

Similar to rename: add new column with new type, dual-write, backfill, dual-read, swap.

Service migrations — replacing a system

Same five-phase playbook applied to a service.

Example: replacing a legacy order service

Build new alongside old: new service exists, exposing same API. Routes traffic to old.
Dual-write: API gateway sends every write to both old and new services. Reads still go to old.
Backfill: historical orders are copied from old DB to new DB.
Shadow read: for each read, query both old and new; compare responses; log differences. Investigate any discrepancy.
Percentage rollout: 1% of reads use new service as authoritative; 99% still use old. Monitor errors. Gradually increase: 10%, 50%, 100%.
Cutover: 100% reads on new. Old kept warm in case of emergency rollback.
Cleanup: stop dual-writing; remove old service.

Backfilling — the operational concern

Copying millions or billions of rows from one place to another takes time.

Pace the backfill: don't slam the source DB. Use batched reads with sleep between batches. Throttle to fit in available IO budget.
Resumable: store progress markers (last processed id). If the backfill crashes, it resumes from where it left off, not from the start.
Idempotent: each row's write is idempotent (use the source's primary key as the destination's key). Re-running the same range is safe.
Monitor: track rows processed, rate, errors. ETA matters — for big migrations, "this will take 3 weeks" is critical to know upfront.
Validate: row counts match? Checksums of corresponding rows match? Sample rows manually-verified?

Verification — the critical phase

Dual-read with comparison is the gate that catches bugs in the new system. Without it, you discover migration bugs after cutover, in production, with affected users.

What to compare:

Counts: did both systems return the same number of items?
Content: are the items themselves identical (or equivalent modulo expected differences)?
Latency: is new system meeting the latency budget?
Error rate: is new system erroring at acceptable rate?

Expected differences need explicit handling — sometimes new system has slightly different semantics by design (e.g., new sorting). Don't ignore differences; categorise them.

Rollback planning

Every phase needs an answer to "what if this is bad?"

Phase	Rollback
Phase 2 dual-write	Stop writing to new. Old continues unaffected.
Phase 3 backfill	Pause/cancel. Verify old not modified.
Phase 4 dual-read	Stop reading from new (still doing it for verification, not authoritative). Old unchanged.
Phase 5 cutover (initial %)	Reduce % back to 0. Reads return to old.
Phase 5 cutover (100%)	Switch reads back to old. New may have writes old doesn't have — reconcile.
Phase 6 cleanup	Once you drop the old, no rollback. Don't drop until weeks of stable operation on new.

Real-world examples

Stripe's online migrations

Stripe has written extensively about their migration framework. Multi-month migrations of foundational data, with continuous verification and graceful rollout. stripe.com/blog/online-migrations is the canonical writeup.

GitHub's MySQL → Vitess

GitHub used logical replication + a phased migration to move from monolithic MySQL to sharded Vitess. Multiple years from start to completion of all tables. github.blog.

Notion's sharding migrations

Notion sharded from 1 to 32 to 96 logical shards. Each transition followed the playbook above. notion.so/blog/sharding-postgres-at-notion.

Common migration pitfalls

"We'll do it in a maintenance window": works for small migrations; fatal for big ones. Maintenance windows scale to hours, not weeks. Treat downtime as a privilege not a default.
No verification phase: discovered bugs in production. Always have dual-read for at least days before cutover.
No throttling on backfill: source DB saturated; serving traffic suffers. Always rate-limit.
Coupled schema + code changes: deploy is reversible only if both can be rolled back together. Decouple via expand-contract.
Cleanup too early: old system removed before confidence period. If new has a latent bug, you can't recover.
Backfill consumes all the WAL: replication lag explodes; replicas fall behind. Throttle.
Missing fields in dual-write: new schema has fields old doesn't. Make sure dual-write populates them.

Applied scenarios

Scenario: You need to add a foreign key constraint on an existing 500GB table. The simple ALTER would take 6 hours and lock writes. Design the migration.

Use NOT VALID + VALIDATE pattern in Postgres. Step 1: ALTER TABLE ADD CONSTRAINT ... FOREIGN KEY ... NOT VALID — fast, doesn't validate existing rows but enforces on new writes. Step 2: Fix any pre-existing rows that violate the constraint (background job). Step 3: ALTER TABLE VALIDATE CONSTRAINT ... — scans the table but does NOT take a strong lock; concurrent writes continue. Pattern documented in Postgres docs. For MySQL, use gh-ost or pt-online-schema-change which handles this transparently.

Scenario: You're migrating a 200M-row table to a new shard. How long, and what's the rollout?

Backfill rate: depends on source throughput and throttle. Realistic: 1-5K rows/sec sustained. 200M rows / 3K rps = ~18 hours of pure copy time. With throttling and pauses: 2-3 days actual elapsed. Then dual-read for at least 3-5 days. Then percentage rollout: 1% (24h), 10% (24h), 50% (48h), 100% (1 week). Then cleanup after another 2 weeks. Total: 3-4 weeks for the whole migration. Bigger tables: months.