Walk through migrating a live database to a different engine with no downtime. Where does reversibility end?
Show the full answer Hide the answer
What is being tested
Whether you verify before switching and know precisely where the one-way door is.
The sequence
1. Stand up the target and establish change capture from the source.
2. Backfill history in batches — rate-limited, resumable from a cursor, without locking the source.
3. Verify. Row counts, checksums, sample comparison including rows written during the backfill. Not optional. This is what catches the type conversion that silently truncated something, and skipping it is how migrations corrupt data invisibly.
4. 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 the behavioural differences a schema comparison never reveals: collation, null handling, precision, sort order, transaction semantics, SQL dialect.
6. Shift reads gradually behind a flag, with instant revert.
7. Shift writes.
8. Decommission after a soak period measured in weeks.
Where reversibility ends
Step 7. 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, taken alone rather than bundled with other changes, and followed by a period in which the source is still updated via reverse replication — so a revert remains possible for a while afterwards.
Steps 1–6 are all reversible with a flag flip or by stopping a job.
What differs by migration type
- Same engine, new version or location — native replication does most of the work.
- Different engine — type mappings, collation and dialect differ. Shadow reads are essential.
- Restructuring — the transformation is code that needs tests, and it is the most likely error source.
- Splitting for sharding — every consumer must learn to route, which is an application change.
The failures to name
Verification skipped, so corruption is found months later. A backfill locking the source. Dual-write without reconciliation, so the two diverge unnoticed. And behavioural differences discovered after cutover, when the source is already stale.