Database Migration at Scale

Zero-downtime schema changes, dual writes and backfill strategies for live production data.

ExpertOps12 min read

Read these first

Multi-Tenant SaaS

Why this matters

StreamHub migrated 2.4B rows from MySQL to PostgreSQL over 6 weeks with zero downtime — the dual-write + backfill pattern made it possible.
ALTER TABLE ADD COLUMN on a 500M-row table locks the table for minutes in MySQL; in PostgreSQL it is fast but backfills are still slow.
Getting migration wrong means data loss, extended downtime, or weeks of rollback pain.

Zero-downtime migration toolkit

Expand-contract — add new column/table → dual-write → backfill → switch reads → drop old.
Online schema change — pt-online-schema-change, gh-ost, or native ALTER ... CONCURRENTLY in Postgres.
Dual writes — application writes to both old and new stores during transition.
Backfill workers — batch-process historical rows from old to new with rate limiting.
Verification — row-count checks, checksum comparisons, shadow reads before cutover.

Expand-contract phases

Zero-downtime DB migration

DMS CDC → new Aurora → dual-write → cutover → decommission old RDS.

Phase 1 — Expand: Add new column/table without removing old.

Java

-- Postgres: instant metadata change, no table rewrite
ALTER TABLE users ADD COLUMN display_name_v2 VARCHAR(255);

-- Create new table for major restructure
CREATE TABLE users_v2 (
  id          UUID PRIMARY KEY,
  email       VARCHAR(255) UNIQUE NOT NULL,
  profile     JSONB,
  created_at  TIMESTAMPTZ DEFAULT now()
);

Phase 2 — Dual write: Application writes to both old and new.

Java

def update_user(user_id: str, display_name: str) -> None:
    mysql.execute(
        "UPDATE users SET name = %s WHERE id = %s",
        [display_name, user_id]
    )
    postgres.execute(
        "UPDATE users SET display_name_v2 = %s WHERE id = %s",
        [display_name, user_id]
    )

Phase 3 — Backfill: Batch-copy historical data.

Java

BATCH_SIZE = 1000
RATE_LIMIT_PER_SEC = 5000

def backfill_display_names():
    last_id = load_checkpoint()
    while True:
        rows = mysql.query(
            "SELECT id, name FROM users WHERE id > %s ORDER BY id LIMIT %s",
            [last_id, BATCH_SIZE]
        )
        if not rows:
            break
        postgres.executemany(
            "UPDATE users SET display_name_v2 = %s WHERE id = %s",
            [(r.name, r.id) for r in rows]
        )
        last_id = rows[-1].id
        save_checkpoint(last_id)
        time.sleep(BATCH_SIZE / RATE_LIMIT_PER_SEC)

Verification before cutover

Aspect	Check	How
Row count	COUNT(*) on both sides	Must match exactly
Checksum	MD5 of sorted column values per batch	Detects silent data corruption
Shadow read	Read from new, compare with old	Validates read path before switch
Latency	p99 on new store under production load	Ensures performance acceptable
Rollback plan	Feature flag to revert reads to old	Tested before cutover

Row count
CheckCOUNT(*) on both sides
HowMust match exactly
Checksum
CheckMD5 of sorted column values per batch
HowDetects silent data corruption
Shadow read
CheckRead from new, compare with old
HowValidates read path before switch
Latency
Checkp99 on new store under production load
HowEnsures performance acceptable
Rollback plan
CheckFeature flag to revert reads to old
HowTested before cutover

Never cut over without all five checks passing. StreamHub's shadow-read phase caught a timezone encoding bug that would have corrupted 200K rows.

Online schema changes

For in-place changes without dual-write (adding an index, renaming a column):

Java

-- Postgres: non-blocking index creation
CREATE INDEX CONCURRENTLY idx_users_email ON users(email);

-- Postgres: add column with default (fast in PG 11+)
ALTER TABLE streams ADD COLUMN quality_tier VARCHAR(20) DEFAULT 'standard';

Java

# MySQL: gh-ost for zero-downtime ALTER
gh-ost \
  --host=mysql-primary \
  --database=streamhub \
  --table=users \
  --alter="ADD COLUMN display_name_v2 VARCHAR(255)" \
  --max-load=Threads_running=25 \
  --critical-load=Threads_running=50 \
  --chunk-size=1000 \
  --execute

Cross-database migration (MySQL → PostgreSQL)

StreamHub's largest migration followed this timeline:

Week	Phase	Risk
1	Create PostgreSQL schema; deploy dual-write code	Low — old path still primary
2–3	Backfill 2.4B rows at 50K rows/sec	Medium — monitor replication lag
4	Shadow reads: compare PG results with MySQL	Low — reads still from MySQL
5	Switch reads to PostgreSQL (feature flag)	High — rollback flag ready
6	Stop MySQL writes; verify; decommission	Medium — final data audit

1
PhaseCreate PostgreSQL schema; deploy dual-write code
RiskLow — old path still primary
2–3
PhaseBackfill 2.4B rows at 50K rows/sec
RiskMedium — monitor replication lag
4
PhaseShadow reads: compare PG results with MySQL
RiskLow — reads still from MySQL
5
PhaseSwitch reads to PostgreSQL (feature flag)
RiskHigh — rollback flag ready
6
PhaseStop MySQL writes; verify; decommission
RiskMedium — final data audit

Total migration window: 6 weeks. Actual cutover (read switch) took 15 minutes.

Feature flags for safe cutover

Java

def get_user(user_id: str) -> User:
    if feature_flags.is_enabled("read_from_postgres", user_id=user_id):
        return postgres.get_user(user_id)
    return mysql.get_user(user_id)

Gradually increase the percentage: 1% → 5% → 25% → 100%. Roll back instantly by disabling the flag.

Quick recall

Everything you need if you only revisit this box.

Zero-downtime migration = expand → dual-write → backfill → switch reads → contract (drop old).
Backfill in batches with rate limiting and checkpoints for resumability.
Verify with row counts, checksums, and shadow reads before cutover.
Use gh-ost or CREATE INDEX CONCURRENTLY for in-place schema changes.
Feature flags enable gradual cutover with instant rollback.

Test yourself

Answer these before moving on — recall is what makes it stick.