Database Migration

Definition

Migrating a live database to a new engine, version, structure or location without stopping the application.

The standard sequence

1. Set up the target and establish replication or change data capture from the source.

2. Backfill history, in batches, rate-limited and resumable from a cursor, without locking the source.

3. Verify. Row counts, checksums, and a sample comparison including rows written during the backfill. This step is not optional — it is what catches the type conversion that silently truncated something, and skipping it is how migrations corrupt data invisibly.

4. Dual-write, or keep replication running so the target stays current.

5. Shadow reads. Serve from the source, also read from the target, compare and log differences. This finds behavioural differences — collation, null handling, precision, ordering — that no schema comparison reveals.

6. Shift reads gradually, behind a flag, with the ability to revert instantly.

7. Shift writes, which is the first genuinely one-way step.

8. Decommission, after a deliberate soak period measured in weeks.

The reversibility question

Steps 1 to 6 are reversible with a flag flip. Step 7 is not: once writes go to the target, the source is stale, and reverting requires reconciling the gap.

So step 7 should be preceded by the longest verification you can afford, and followed by a period in which the source is still updated — via reverse replication — so a revert remains possible for a while.

What differs by migration type

• Same engine, new version or location. Native replication does most of the work.
• Different engine. Type mappings, collation, transaction semantics and SQL dialect all differ. Shadow reads are essential.
• Restructuring — normalising, denormalising, splitting. The transformation logic is code that needs tests, and it is the most likely source of error.
• Splitting for sharding. Every consumer must learn to route, which is an application change, not a data one.

Failure scenarios

• Verification skipped, so silent corruption is discovered months later.
• The backfill locking the source, taking the service down.
• Dual-write without reconciliation, so the two diverge and nobody notices.
• Cutover with no revert path, so the first surprise is an incident.
• Behavioural differences discovered after cutover — a sort order, a null comparison, a precision loss.

Interview question

"Walk me through migrating a live database to a different engine with no downtime, and tell me where reversibility ends."