Problem RestatementProblem
TikTok asked: a team proposes migrating an existing MySQL-backed service to MongoDB. How would you:
- decide whether the migration is justified,
- compare the data models and consistency needs,
- execute the migration safely, and
- prove the claimed benefits?
Step 1: Understand the Motivation
Ask "what problem are we solving?". Common reasons:
- "Schema changes are painful" (the data is naturally nested or varies per record).
- "We need to scale writes beyond one MySQL primary."
- "Our queries always load a whole object with 5 joins."
Step 2: Compare Models Against Real Access Patterns
| Aspect | MySQL (relational) | MongoDB (document) |
|---|---|---|
| Data shape | Normalized tables, joins | Nested documents (an object and its children together) |
| Transactions | Mature multi-row ACID | Multi-document transactions exist, but best to design single-document updates |
| Schema | Enforced, migrations needed | Flexible (validation optional) |
| Scaling writes | Vertical, or manual/Vitess sharding | Built-in sharding by shard key |
| Queries | Rich joins, ad-hoc SQL, reporting | Great for "get this whole object", weaker for cross-entity joins/analytics |
List the top 10 queries by volume and latency, and model them as documents. If most become single-document reads, that's a good sign. If many need $lookup (joins), it's a warning.
Deep Dive — Executing the migration without a bad weekendDeep dive
The decision is made and the document model is designed. Getting there from a live MySQL service is where migrations actually fail.
Big-bang cutover in a maintenance window
Freeze writes, export, transform, load, point the service at MongoDB, unfreeze.
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
FREEZE["Take the service down"] --> EXP["Export terabytes"]
EXP --> XF["Transform to documents"]
XF --> LOAD["Load into MongoDB"]
LOAD --> SW["Switch the service"]
SW --> BUG["A transform bug appears under real traffic"]
BUG --> BACK["Roll back - but MongoDB took hours of writes MySQL never saw"]The window is sized by the largest table and always overruns. Worse, once writes have gone to MongoDB the rollback path is gone, so the only options are fixing forward under pressure or losing data.
Backfill, then switch
Copy historical data ahead of time and cut over with a much shorter freeze.
The window shrinks, which is the main pain point. But the backfill goes stale from the moment it starts, so there is still a gap to reconcile at cutover, and the first time MongoDB serves production reads is the moment everything depends on it. Any modelling mistake is discovered with all the traffic on it.
Backfill, keep in sync with CDC, then shadow before you switch
%%{init: {"look":"handDrawn","handDrawnSeed":7,"theme":"base","fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","themeVariables":{"fontFamily":"Virgil, \"Segoe Print\", \"Comic Sans MS\", cursive","fontSize":"16px","primaryColor":"#fff4e6","primaryBorderColor":"#1e1e1e","primaryTextColor":"#1e1e1e","secondaryColor":"#e7f5ff","tertiaryColor":"#ebfbee","lineColor":"#1e1e1e","textColor":"#1e1e1e","mainBkg":"#fff4e6","nodeBorder":"#1e1e1e","clusterBkg":"#f8f9fa","edgeLabelBackground":"#ffffff","classText":"#1e1e1e"}}}%%
flowchart LR
APP["Service"] -->|"writes"| MY[("MySQL - source of truth")]
MY -->|"CDC from the binlog"| SYNC["Transform to documents"]
SYNC --> MG[("MongoDB")]
BF["Backfill job"] --> MG
APP -->|"shadow reads - result discarded"| MG
CMP["Diff checker"] --> MY
CMP --> MG
CMP --> REP["Mismatch report - fix the transform, replay"]
MG --> RAMP["Ramp real reads: 1% -> 50% -> 100%"]
RAMP --> CUT["Move writes last - MySQL stays readable for rollback"]- CDC removes the gap. Change data capture from the binlog keeps MongoDB continuously current, so there is no stale backfill to reconcile and no long freeze — cutover becomes a routing decision rather than an operation.
- Shadow reads prove the model before it matters. Serve from MySQL, also read from MongoDB, discard the result and diff them. Modelling mistakes and transform bugs surface under real traffic with zero user impact, which is the only way to find them honestly.
- Move reads before writes, gradually. Ramp read traffic and watch latency and diffs; switch writes last, so until that moment MySQL is still the source of truth and rollback is instant.
Pick the shard key from the access patterns before any of this — high cardinality, spreading writes, aligned with the most common query. It is effectively permanent, so it is the one decision worth making slowly.
And keep the diff checker running for a while after cutover. It is the cheapest possible verification that the new model is actually right, and it costs nothing once it is already built.
Step 4: Prove the Benefits
- Before starting: record baselines (p50/p99 latency of key queries, throughput limits, cost, developer time spent on schema changes, incidents).
- During shadow reads: compare latency and resource use under real traffic.
- After: the same metrics, plus cost. If the benefits don't show up, stop at the shadow phase. That's the value of a reversible plan.
Wrap-UpWrap-up
Start from the concrete problem and rule out cheaper fixes, then compare the relational and document models against the service's real top queries and consistency needs (MongoDB fits aggregate-centric, horizontally scaling workloads, while relational fits joins, reporting and multi-entity transactions). If it's justified, migrate with a backfill plus CDC sync, validation, shadow reads, a gradual read-then-write cutover with a reverse-sync rollback path, and prove the gains against recorded baselines.