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:

⚠️ Warning: NoSQL is not a silver bullet. If your workload is heavily transactional — think financial ledgers or inventory reservations — a distributed SQL platform (CockroachDB, AWS Aurora, PlanetScale) is often the right target, not a document store.

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:

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
  }
}
✅ Tip: Denormalise fields you read on every query. Storing 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.

Phase 1

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.

Phase 2

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.

Phase 3

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.

Phase 4

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
  }
}
✅ Tip: Run the backfill during off-peak hours and throttle at 50–100ms between batches. A 10M-row table with 500-row batches at 50ms intervals completes in roughly 17 minutes while keeping SQL CPU under 20%.

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

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.