Why Legacy SQL Breaks Under Load
Relational databases built in the 2000s were optimised for correctness and consistency in single-server, low-concurrency environments. When you throw 50 000+ concurrent requests at a traditional RDBMS, three things collapse first:
- Row-level locking contention — heavy
UPDATEandDELETEpatterns cause lock queues that cascade across dependent transactions. - JOIN amplification — deeply normalised schemas with 6-8 table joins produce query plans that execute in milliseconds in dev (1 000 rows) but seconds in production (100 million rows).
- Vertical scaling ceiling — adding CPU and RAM buys time, not architecture. Once you hit the largest available RDS instance, the only path is horizontal — which RDBMS sharding handles poorly.
Step 1 — Schema Audit & Workload Classification
Before writing a single migration script, classify every table by access pattern. This determines which tables move to documents, which stay relational, and which get cached.
Access Pattern Matrix
| Pattern | Example | Recommended Target |
|---|---|---|
| Read-heavy, flexible schema | Product catalogue, user profiles | MongoDB document store |
| Write-heavy, append-only | Event logs, audit trails | MongoDB time-series or DynamoDB |
| Complex aggregations, reporting | Analytics dashboards | Keep in SQL + read replica |
| Multi-entity ACID transactions | Order + Payment + Inventory | AWS Aurora / CockroachDB |
| Key-value lookups, session data | Auth tokens, feature flags | Redis or DynamoDB |
SQL Query Profiling
Enable slow query logging and capture one week of real traffic before making any decisions:
-- MySQL: enable slow query log
SET GLOBAL slow_query_log = 'ON';
SET GLOBAL long_query_time = 0.5; -- log queries slower than 500ms
SET GLOBAL slow_query_log_file = '/var/log/mysql/slow.log';
-- After capture, analyse with pt-query-digest
pt-query-digest /var/log/mysql/slow.log \
--order-by Count:sum \
--limit 20 \
> slow_query_report.txt
Sort by total execution time, not just individual query time. A query running in 200ms but called 2 million times per day costs 400 000 seconds of DB time — far more impactful than a 2-second report query run once daily.
Step 2 — Document Modelling for MongoDB
The most common mistake is a 1:1 translation — taking each SQL table and creating a MongoDB collection with the same fields. This loses all the benefits of a document store.
Embed vs Reference
The core design decision in MongoDB schema design:
- Embed when the child data is always read with the parent and doesn't exceed ~16 MB per document (MongoDB's document size limit).
- Reference when the child data is queried independently, shared across multiple parents, or grows unboundedly.
A classic example — an e-commerce orders table with line items. In SQL you have three tables: orders, order_items, products. In MongoDB:
// MongoDB document — embed line items, reference product
{
"_id": ObjectId("..."),
"orderId": "ORD-20261001-8821",
"customerId": ObjectId("..."), // reference — queried separately
"status": "shipped",
"placedAt": ISODate("2026-10-01T09:14:00Z"),
"shippingAddress": {
"line1": "12 MG Road",
"city": "Bangalore",
"pincode": "560001"
},
"items": [ // embedded — always read together
{
"productId": ObjectId("..."), // reference for product detail pages
"name": "Wireless Keyboard", // denormalised for read performance
"sku": "WK-BLK-US",
"qty": 1,
"unitPrice": 2499,
"subtotal": 2499
}
],
"totals": {
"subtotal": 2499,
"tax": 450,
"shipping": 99,
"grand": 3048
}
}
name and unitPrice inside the order item avoids a $lookup (MongoDB's equivalent of JOIN) on every order fetch — at the cost of slightly larger documents. For most e-commerce workloads, this tradeoff strongly favours embedding.
Index Strategy
MongoDB's default _id index is not enough. Create compound indexes that mirror your most frequent query shapes:
// Create indexes aligned to query patterns
db.orders.createIndex({ customerId: 1, placedAt: -1 }); // customer order history
db.orders.createIndex({ status: 1, placedAt: -1 }); // fulfilment queue
db.orders.createIndex(
{ "items.productId": 1 },
{ name: "idx_order_product" }
); // product sales lookup
// Use explain() to verify index usage
db.orders.find({ customerId: ObjectId("...") })
.sort({ placedAt: -1 })
.explain("executionStats");
Step 3 — Zero-Downtime Migration Phases
Never attempt a big-bang cutover on a live system. The industry-standard approach is a four-phase process that keeps both databases in sync until you are confident in the new system.
Shadow Write
All writes go to SQL (primary). The application also writes asynchronously to MongoDB via a background queue. Reads still come from SQL. No user impact. This phase validates that your document schema is correct and your write path is stable.
Dual Read with Comparison
Reads are issued to both databases. Results are compared in a background process and discrepancies are logged. This catches transformation bugs and schema edge cases before any user ever touches the new DB.
MongoDB Primary for Reads
Flip the read path to MongoDB for a subset of traffic (feature-flag a 5% canary). Monitor error rates, latency P95, and query plans. Gradually increase the percentage over days or weeks.
Full Cutover & SQL Decommission
All reads and writes go to MongoDB. SQL becomes a read-only audit replica for 30 days. After the observation window with zero incidents, the SQL instance is decommissioned.
Dual-Write Implementation (NestJS)
// dual-write.interceptor.ts
@Injectable()
export class DualWriteInterceptor implements NestInterceptor {
constructor(
private readonly mongo: MongoOrderRepository,
private readonly queue: BullQueue,
) {}
async intercept(ctx: ExecutionContext, next: CallHandler): Promise> {
const result = await lastValueFrom(next.handle());
if (result?.orderId) {
// Fire-and-forget async write to MongoDB
await this.queue.add('sync-order', { orderId: result.orderId }, {
attempts: 5,
backoff: { type: 'exponential', delay: 1000 },
removeOnComplete: true,
});
}
return of(result);
}
}
// sync-order.processor.ts
@Processor('sync-order')
export class SyncOrderProcessor {
@Process('sync-order')
async handle(job: Job<{ orderId: string }>) {
const order = await this.sqlRepo.findById(job.data.orderId);
const doc = this.transformer.toMongoDocument(order);
await this.mongoRepo.upsert(doc);
}
}
Step 4 — Backfill Historical Data
Migrating years of historical data requires a batched, resumable job — not a single INSERT INTO ... SELECT that locks your SQL server for hours.
// backfill.ts — paginated, resumable backfill with cursor checkpointing
async function backfillOrders(fromId: number) {
const BATCH_SIZE = 500;
let cursor = fromId;
let processed = 0;
while (true) {
const rows = await sqlDb.query(
`SELECT * FROM orders WHERE id > ? ORDER BY id ASC LIMIT ?`,
[cursor, BATCH_SIZE]
);
if (rows.length === 0) break;
const docs = rows.map(transformToMongoDoc);
await mongoCollection.bulkWrite(
docs.map(doc => ({ replaceOne: { filter: { orderId: doc.orderId }, replacement: doc, upsert: true } })),
{ ordered: false }
);
cursor = rows[rows.length - 1].id;
processed += rows.length;
// Checkpoint so the job is resumable after a crash
await redisClient.set('backfill:orders:cursor', String(cursor));
console.log(`Backfilled ${processed} orders. Last ID: ${cursor}`);
await sleep(50); // throttle — avoid saturating prod DB
}
}
Step 5 — Data Validation & Consistency Checks
Before cutover, run a full consistency check that compares row counts, checksums, and spot-samples between your SQL and MongoDB datasets:
// validation.ts
async function validateConsistency(sampleSize = 1000) {
const sqlCount = await sqlDb.queryOne<{ count: number }>(
'SELECT COUNT(*) as count FROM orders'
);
const mongoCount = await mongoCollection.countDocuments();
console.log(`SQL rows: ${sqlCount.count} | Mongo docs: ${mongoCount}`);
if (Math.abs(sqlCount.count - mongoCount) > 10) {
throw new Error(`Count mismatch: delta ${sqlCount.count - mongoCount}`);
}
// Random spot-sample
const sampleIds = await sqlDb.query<{ id: number }>(
`SELECT id FROM orders ORDER BY RAND() LIMIT ${sampleSize}`
);
let mismatches = 0;
for (const { id } of sampleIds) {
const sqlRow = await sqlDb.queryOne('SELECT * FROM orders WHERE id = ?', [id]);
const mongoDoc = await mongoCollection.findOne({ sqlId: id });
if (!deepEqual(transform(sqlRow), mongoDoc)) {
mismatches++;
logger.warn(`Mismatch on order id=${id}`);
}
}
console.log(`Spot-check: ${sampleSize - mismatches}/${sampleSize} matched`);
}
Performance Results: Before & After
Results from a real production migration of a 40M-row order management system (MySQL 8 → MongoDB 7 on AWS):
| Metric | MySQL (Before) | MongoDB (After) | Improvement |
|---|---|---|---|
| Order fetch (P95) | 340 ms | 18 ms | 18.9× faster |
| Write throughput | 1 200 TPS | 18 000 TPS | 15× higher |
| DB CPU at peak | 94% | 31% | −67% |
| Query count per request | 7 (joins) | 1 (document fetch) | 7× fewer |
| Monthly DB cost (AWS) | $2 400 | $680 | 72% reduction |
Common Pitfalls to Avoid
- Unbounded arrays: Never embed arrays that grow forever (e.g., all comments on a post). After ~10 000 elements, document rewrites become expensive. Use a separate collection and reference.
- Skipping write concern: Default MongoDB write concern is
w:1— acknowledged by primary only. For financial data, usew:"majority"to ensure replication before ACK. - No transaction strategy: MongoDB 4.0+ supports multi-document ACID transactions, but they come with performance overhead. Only use them where genuinely required; design around single-document atomicity where possible.
- Migrating indexes after data: Always create indexes on an empty collection before backfilling. Building a compound index on 40M documents takes far longer and blocks reads.
- Skipping the 30-day SQL archive window: Keep your SQL instance alive in read-only mode. Teams have been burned by edge-case discrepancies discovered three weeks post-cutover.
Conclusion
A successful database migration is 20% schema work and 80% process discipline. The dual-write, gradual-read-shift approach means you can run the entire migration in production — across weeks — with zero user-facing downtime and a clean rollback path at every stage.
The key principles: audit before you model, model for your query patterns not your SQL schema, backfill in throttled batches, validate obsessively, and keep the old system alive long enough to trust the new one.
If you are planning a migration and want to talk through your specific schema and workload, feel free to book a free 30-minute consultation.