Database Migration Framework

Online migrations succeed by never asking the system to change everything at once. The framework treats each migration as a sequence of compatible states, where every state works with both the previous and the next application version.

How it works

The expand phase adds new columns, tables, or indexes without removing anything. A backfill copies existing data in small batches while the application writes to both shapes. Reads then move to the new shape behind a flag. After a soak period and verification, the contract phase drops the old structures. For platform moves, change data capture keeps the target in sync while traffic shifts in stages.

Implementation steps

1. Write the plan as ordered steps, each with a verification query and a rollback action.
2. Deploy additive schema changes first, using non-blocking DDL options such as concurrent index creation.
3. Ship application code that writes both shapes and still reads the old one.
4. Backfill in throttled batches keyed by primary key, with resumable checkpoints.
5. Compare old and new data continuously using checksums or sampled row diffs.
6. Switch reads using a feature flag, ramping from a small percentage of traffic.
7. Stop dual writes, wait out the soak period, then drop old structures in a separate release.

Common pitfalls

• Long-held locks from changing a column type or adding a constraint without validating in the background.
• Backfills that saturate replicas and cause replication lag.
• Forgetting writers outside the main application, such as batch jobs.
• Dropping the old column in the same release that stops using it.
• Rehearsing only on small datasets.

Metrics to watch

• Replication lag during backfill.
• Lock wait time and blocked query count.
• Mismatch rate between old and new data.
• Backfill progress and throughput.
• Error rate and latency after each read ramp.

Example configuration

migration: split-customer-name
steps:
  - id: expand
    sql: "ALTER TABLE customers ADD COLUMN first_name text, ADD COLUMN last_name text"
  - id: backfill
    batchSize: 5000
    sleepMs: 200
    maxReplicaLagSeconds: 5
  - id: verify
    check: "mismatch_count = 0 for 24h"
  - id: switch-reads
    flag: customers.read_split_name
    ramp: [1, 10, 50, 100]
  - id: contract
    sql: "ALTER TABLE customers DROP COLUMN full_name"
    requires: "no reads for 14 days"

Practical rollout

Rehearse the whole sequence on a recent production snapshot before touching live systems, and time every step so the plan includes realistic durations. Agree beforehand on abort criteria, such as replica lag above a threshold or any mismatch in verification, and on who may call the rollback. Keep a written log of each executed step. After completion, hold a short review to update tooling and templates for the next migration.

Key takeaways

• Make every step compatible with both neighbouring versions.
• Verify data automatically before removing anything.
• Throttle backfills against replica lag.
• Schedule and complete the contract phase.