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 COLUMNon 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 ... CONCURRENTLYin 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
Phase 1 — Expand: Add new column/table without removing old.
-- 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.
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.
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)
The rest of this guide is part of Pro
Free covers every fresher guide in full. Pro unlocks Senior, Staff and Architect guides across every track.
Still to come · 4 sections
- Verification before cutover
- Online schema changes
- Cross-database migration (MySQL → PostgreSQL)
- Feature flags for safe cutover
Test yourself
Answer these before moving on — recall is what makes it stick.