Zero-Downtime Database Migration: Schema Changes and Data Migration Strategies

The Bottom Line: “Dual-Running” Is the Key to Zero-Downtime Migration

The core tension in database migration is: Schema must change, but the service cannot stop. The solution is not “finish faster” — it is letting the old and new structures coexist for a period, with the application layer compatible with both, and switching over only after all data has been safely migrated.

This article covers four scenarios, each with a strategy template and rollback plan.

---

Scenario 1: Adding a Column (The Simplest Zero-Downtime Operation)

Strategy: Three-Phase Progressive

-- Step 1: Add column, allow NULL (metadata-only, no table lock)
ALTER TABLE users ADD COLUMN phone varchar(20);

-- Step 2: Backfill in batches via application layer
UPDATE users SET phone = '' WHERE phone IS NULL LIMIT 1000;

-- Step 3: After confirming all rows are filled, add NOT NULL
ALTER TABLE users ALTER COLUMN phone SET NOT NULL;

Why this is safe:

• Step 1: On MySQL 5.6+/PostgreSQL, adding a nullable column is an O(1) metadata operation
• Step 2: Batch processing avoids long transactions and replication lag
• Step 3: Verify with SELECT count(*) FROM users WHERE phone IS NULL first

Rollback: Simply DROP COLUMN — no data loss.

---

Scenario 2: Changing Column Type (The Riskiest)

Strategy: New Column + Dual-Write + Atomic Cutover

-- Step 1: Add new column
ALTER TABLE orders ADD COLUMN total_amount_cents bigint;

-- Step 2: Application enables dual-write (writes both old and new)
// code: orders.totalAmount = amount; orders.totalAmountCents = Math.round(amount * 100);

-- Step 3: Background job backfills historical data
UPDATE orders SET total_amount_cents = ROUND(total_amount::numeric * 100)
  WHERE total_amount_cents IS NULL AND id BETWEEN ? AND ?;

-- Step 4: Switch read path
// code: const amount = orders.totalAmountCents / 100;

-- Step 5: After verifiation, drop old column
ALTER TABLE orders DROP COLUMN total_amount;

Longer dual-write = safer: Run dual-write for at least 48 hours to observe data consistency before switching the read path.

Rollback: Anytime before switching the read path — just disable dual-write and drop the new column.

---

Scenario 3: Splitting a Table (Horizontal Partitioning)

Strategy: Proxy + Dual-Write + Full Sync

State 0: App → old table (orders)
State 1: App → dual-write to old + shadow table (orders_2026)
State 2: Background job fully syncs old table to shadow (batched)
State 3: App reads from shadow table, old table read-only backup
State 4: After confirmation, remove old table

Production-ready middleware:

Solution	Database	Features
ProxySQL + pt-archiver	MySQL	Flexible routing rules
pg_partman	PostgreSQL	Native partitioning, auto sub-table
Vitess	MySQL	Full distributed solution
Citus	PostgreSQL	Distributed with SQL compatibility

---

Scenario 4: Heterogeneous Migration (Switching Database Types)

Strategy: Dual-Write + Verification + Gradual Traffic Shifting

State 0: App → MySQL
State 1: App → dual-write MySQL + PostgreSQL (write both, read from MySQL)
State 2: Historical full + incremental sync (pgloader, Debezium)
State 3: Full verification → gradual read cutover (1% → PostgreSQL first)
State 4: Scale traffic (1% → 10% → 50% → 100%)
State 5: After confirmation, remove MySQL read dependency

Gradual cutover is the most important safety measure — running 1% of traffic through the new system first allows you to observe errors and performance before scaling up.

---

Recommended Toolchain

Tool	Scenario	Approach
gh-ost	MySQL Online DDL	Binlog streaming, no triggers
pt-online-schema-change	MySQL DDL	Trigger-based shadow table
pgroll	PostgreSQL lock-free	Shadow table + triggers
pgloader	Heterogeneous (any DB → PG)	Streaming batch load
Debezium	CDC (Change Data Capture)	binlog/WAL → Kafka
pt-table-checksum	MySQL verification	Chunked CRC32 comparison

---

Migration Timeline Example (gh-ost)

17:00  Create shadow table (empty)
17:01  Start binlog listener, begin incremental tracking
17:02  Start full copy (3M rows, estimated 2 hours)
19:05  Full copy complete, entering "catch-up delay" mode
19:08  Replication lag reaches 0
19:08  🔒 Brief table lock (milliseconds)
19:08  Atomic swap: shadow ↔ original table rename
19:08  Unlock

~2 hours total migration, of which only tens of milliseconds is the actual downtime window (the rename lock).

---

Checklist

Before migration:

• Verify sufficient disk space (shadow tables need extra space)
• Disable cron jobs and triggers on the target table
• Backup the original table
• Run a full migration rehearsal in staging first

During migration:

• Monitor replication lag (should stay under 5 seconds)
• Monitor disk IOPS and CPU (full copy phase is the heaviest)
• Rollback plan is written down, not “we will figure it out”

After migration:

• Data verification: row count + checksum + sampled full-field
• Run at least 24 hours before cleaning up the old table
• Update documentation and monitoring dashboards

---

Related Reading

• Redis Caching Strategies & Common Pitfalls — Cache layer acceleration after database migration
• OpenAPI Best Practices — API versioning for post-migration services
• Docker Compose in Practice — Containerized database deployment

Need help designing a database migration strategy? Contact us for a free consultation and fixed-price quote.