Definition
- Expand-and-Contract (Parallel Change) – introduce the new representation alongside the old, transition traffic gradually, then remove the old.
- Additive Schema Change – add new columns or tables without removing anything the current application depends on.
- Dual-Write – write to both old and new representations on every change so new data is correct in both going forward.
- Throttled Restartable Backfill – migrate historical data in bounded batches with checkpointing, adaptive throttling, and observability.
- Continuous Verification – sample rows and assert old and new representations agree before cutting over.
- Staged Cutover – move reads first, then writes, each behind a feature flag, with instant rollback capability.
National Bank of India wants to replace its paper ledger with a digital one for hundreds of millions of past transactions, but cannot close even for an hour. So they install the digital ledger beside the paper one, write every new transaction in both, copy old records in small batches during quiet hours (pausing when busy), cross-check both ledgers continuously, and only switch to reading from the digital one after they fully trust it.
Understanding the Concepts
Why is a single-deployment migration dangerous?
The central mistake is treating a migration as one deployment: change the schema, flip the code, run a big backfill. At hundreds of millions of rows, that either requires downtime or breaks in production.
package com.bank.migration;
public class SingleDeployDanger {
public static void main(String[] args) {
System.out.println("WRONG: one big deploy");
System.out.println(" 1. ALTER TABLE (locks table for hours)");
System.out.println(" 2. Deploy new code (old code incompatible)");
System.out.println(" 3. Backfill 500M rows in one UPDATE (blows transaction log)");
System.out.println(" Result: downtime or corruption");
System.out.println();
System.out.println("CORRECT: four separable phases");
System.out.println(" Each phase leaves a fully working system");
System.out.println(" Each phase is independently reversible");
}
}
WRONG: one big deploy
1. ALTER TABLE (locks table for hours)
2. Deploy new code (old code incompatible)
3. Backfill 500M rows in one UPDATE (blows transaction log)
Result: downtime or corruption
CORRECT: four separable phases
Each phase leaves a fully working system
Each phase is independently reversible
Phase 1: additive schema change
Introduce the new representation without removing anything the current application depends on. Use online, non-blocking DDL.
-- Phase 1: additive only. Old app never references balance_minor.
ALTER TABLE accounts ADD COLUMN balance_minor BIGINT NULL;
CREATE INDEX CONCURRENTLY idx_accounts_balance_minor ON accounts(balance_minor);
-- Old application continues working, completely unaware of the new column.
package com.bank.migration;
public class Phase1Schema {
public static void main(String[] args) {
System.out.println("Added balance_minor column (nullable, no default)");
System.out.println("Created index CONCURRENTLY (no table lock)");
System.out.println("Old application: completely unaffected");
}
}
Added balance_minor column (nullable, no default)
Created index CONCURRENTLY (no table lock)
Old application: completely unaffected
Phase 2: dual-write code
Deploy code that writes to both representations on every change. Reads still come from the old representation. This step is reversible.
package com.bank.migration;
import java.math.BigDecimal;
public class Phase2DualWrite {
static void updateBalance(String accountId, BigDecimal newBalance) {
// Old representation (decimal)
System.out.println("Old: UPDATE accounts SET balance = " + newBalance
+ " WHERE id = '" + accountId + "'");
// New representation (integer paise)
long paise = newBalance.movePointRight(2).longValueExact();
System.out.println("New: UPDATE accounts SET balance_minor = " + paise
+ " WHERE id = '" + accountId + "'");
}
public static void main(String[] args) {
updateBalance("SAV2001", new BigDecimal("75000.50"));
}
}
Old: UPDATE accounts SET balance = 75000.50 WHERE id = 'SAV2001'
New: UPDATE accounts SET balance_minor = 7500050 WHERE id = 'SAV2001'
Phase 3: throttled, restartable backfill
Backfill historical data in bounded batches with checkpointing, adaptive throttling, and observability.
package com.bank.migration;
public class Phase3Backfill {
static long checkpoint = 0;
static void backfill(long totalRows, int batchSize) {
long processed = 0;
for (long id = checkpoint; id < totalRows; id += batchSize) {
long end = Math.min(id + batchSize, totalRows);
System.out.println("Batch: rows " + id + " to " + end);
processed += (end - id);
checkpoint = end;
// Adaptive throttle: check production latency
boolean highLatency = (processed % 50_000 == 0); // simulated
if (highLatency) {
System.out.println(" Production latency high, pausing 5s...");
}
}
System.out.println("Backfill complete. Checkpoint: " + checkpoint);
}
public static void main(String[] args) {
backfill(100_000, 10_000);
}
}
Batch: rows 0 to 10000
Batch: rows 10000 to 20000
Batch: rows 20000 to 30000
Batch: rows 30000 to 40000
Batch: rows 40000 to 50000
Production latency high, pausing 5s...
Batch: rows 50000 to 60000
Batch: rows 60000 to 70000
Batch: rows 70000 to 80000
Batch: rows 80000 to 90000
Batch: rows 90000 to 100000
Production latency high, pausing 5s...
Backfill complete. Checkpoint: 100000
Phase 4: verify and staged cutover
Continuously verify old and new representations agree. Only when verification is clean, move reads behind a feature flag, then writes, then drop the old column much later.
package com.bank.migration;
public class Phase4Cutover {
public static void main(String[] args) {
int sampleSize = 10000;
int mismatches = 0;
System.out.println("Verification: sampled " + sampleSize + " rows");
System.out.println("Mismatches: " + mismatches);
System.out.println();
if (mismatches == 0) {
System.out.println("Step 1: Move READS to new representation (feature flag)");
System.out.println("Step 2: Observe for 1 week");
System.out.println("Step 3: Move WRITES exclusively to new representation");
System.out.println("Step 4: Drop old column in separate deployment (weeks later)");
} else {
System.out.println("STOP: investigate mismatches before cutover");
}
}
}
Verification: sampled 10000 rows
Mismatches: 0
Step 1: Move READS to new representation (feature flag)
Step 2: Observe for 1 week
Step 3: Move WRITES exclusively to new representation
Step 4: Drop old column in separate deployment (weeks later)
Why does staging lie about migration performance?
In staging, the backfill has the database to itself. In production, it competes for CPU, disk I/O, buffer pool, row locks, and replication bandwidth.
package com.bank.migration;
public class StagingVsProduction {
public static void main(String[] args) {
System.out.println("=== Staging ===");
System.out.println("Buffer cache: empty, all for backfill");
System.out.println("Lock contention: none (no live traffic)");
System.out.println("Replication lag: zero");
System.out.println("Backfill time: 1 hour");
System.out.println();
System.out.println("=== Production ===");
System.out.println("Buffer cache: shared with live queries");
System.out.println("Lock contention: live transfers compete for row locks");
System.out.println("Replication lag: backfill generates write load");
System.out.println("Backfill time: 12+ hours (must throttle for live traffic)");
}
}
=== Staging ===
Buffer cache: empty, all for backfill
Lock contention: none (no live traffic)
Replication lag: zero
Backfill time: 1 hour
=== Production ===
Buffer cache: shared with live queries
Lock contention: live transfers compete for row locks
Replication lag: backfill generates write load
Backfill time: 12+ hours (must throttle for live traffic)
What are common mistakes?
Running one giant UPDATE over hundreds of millions of rows, which holds locks, bloats the transaction log, and starves live queries.
-- WRONG: one giant update
UPDATE accounts SET balance_minor = balance * 100;
-- Holds locks on 500M rows, transaction log bloats, live queries time out
-- CORRECT: bounded batches with checkpoint
-- UPDATE accounts SET balance_minor = balance * 100
-- WHERE id BETWEEN ? AND ? AND balance_minor IS NULL;
-- Repeat in batches of 5000, checkpoint after each
What are common interview follow-up questions?
- How do you handle the case where the dual-write fails on one representation but succeeds on the other?
- How do you make the cutover instantly reversible?
- What happens if a new bug is found after cutting over reads but before cutting over writes?
- How do you estimate the backfill duration in production?
Visual Overview
Online Migration: Four Phases
─────────────────────────────
Phase 1: Additive Schema
┌──────────────────────────────────┐
│ ADD COLUMN balance_minor (null) │
│ Old app works, unaware of change │
└─────────────┬────────────────────┘
│
Phase 2: Dual-Write Code
┌─────────────┴────────────────────┐
│ Write to old AND new on every │
│ change. Reads still from old. │
│ Reversible: stop dual-writing. │
└─────────────┬────────────────────┘
│
Phase 3: Throttled Backfill
┌─────────────┴────────────────────┐
│ Bounded batches + checkpoint │
│ Adaptive throttle (watch latency)│
│ Restartable on crash │
└─────────────┬────────────────────┘
│
Phase 4: Verify + Cutover
┌─────────────┴────────────────────┐
│ Verify: 0 mismatches │
│ Move reads → observe → writes │
│ Drop old column weeks later │
└──────────────────────────────────┘
Key Takeaway
A migration of hundreds of millions of rows is never a single deployment. It is four separable phases that each leave a fully working system. First, add the new schema representation additively so the old application keeps working. Second, deploy dual-write code so all new data is correct in both representations. Third, backfill historical data in bounded, throttled, restartable batches with adaptive throttling driven by production latency.
Fourth, continuously verify that old and new agree, then move reads, then writes, each behind a feature flag, and only remove the old representation in a separate deployment weeks later. Until that final step, every phase is reversible.
Staging lies about performance because the backfill has the database to itself. In production it competes with live traffic for CPU, I/O, locks, and replication bandwidth. The throttle must be adaptive, and the whole migration must be pausable and resumable.
Remember one sentence: never let correctness depend on everything happening at once; separate schema, code, backfill, and cutover into four independently reversible phases.
---