The MongoDB Aggregation Pipeline That Killed Our CPU for 3 Hours

It was 2 PM on a Wednesday. Our MongoDB Atlas cluster was humming along at 20% CPU.

Then, within 60 seconds, it shot to 100% and stayed there.

No deploys. No traffic spikes. Just… death.

The Setup

We had an e-commerce MERN stack:

• MongoDB Atlas M40 cluster (4 vCPUs, 16GB RAM)
• Mongoose ODM
• Product catalog with 500k products
• Order history with 2M orders

A new feature required a dashboard showing "top products by revenue per category."

I wrote an aggregation pipeline:

// THE DANGEROUS PIPELINE
const topProductsByCategory = await Order.aggregate([
  // Stage 1: Unwind order items
  { $unwind: '$items' },
  
  // Stage 2: Lookup product details
  { 
    $lookup: {
      from: 'products',
      localField: 'items.productId',
      foreignField: '_id',
      as: 'product'
    }
  },
  
  // Stage 3: Unwind product (always one)
  { $unwind: '$product' },
  
  // Stage 4: Group by category and product
  {
    $group: {
      _id: {
        category: '$product.category',
        productId: '$product._id',
        productName: '$product.name'
      },
      totalRevenue: { $sum: { $multiply: ['$items.price', '$items.quantity'] } }
    }
  },
  
  // Stage 5: Sort within each category
  { $sort: { '_id.category': 1, totalRevenue: -1 } },
  
  // Stage 6: Group to get top 5 per category
  {
    $group: {
      _id: '$_id.category',
      topProducts: { $push: '$$ROOT' },
    }
  },
  
  // Stage 7: Slice to top 5
  {
    $project: {
      topProducts: { $slice: ['$topProducts', 5] }
    }
  }
]);

The Disaster

This pipeline ran once per hour as a scheduled job.

But it had a hidden bug: no indexes on items.productId.

Here's what actually happened when the pipeline executed:

1. $unwind on orders → Created a document per order item. 2M orders × average 3 items = 6M documents.
2. $lookup with no index → For each of 6M documents, MongoDB scanned the entire products collection (500k documents) to find matching product.
3. That's 6M × 500k = 3 trillion document scans.
4. CPU maxed out. Memory exhausted. Disk I/O through the roof.

After 3 hours, MongoDB killed the operation.

But the damage was done: the cache was destroyed, and it took another 2 hours for performance to recover.

The Investigation

I connected to Atlas and ran db.currentOp():

db.currentOp({
  "secs_running": { "$gt": 60 },
  "op": "command"
})

// Result:
{
  "opid": 123456789,
  "command": {
    "aggregate": "orders",
    "pipeline": [...] // our pipeline
  },
  "planSummary": "COLLSCAN",  // ← NO INDEX!
  "numYields": 0,
  "locks": { "Global": "w" }
}

COLLSCAN on a 500k product collection. Repeated 6M times.

The Fixes

1. Add Index on Foreign Key

// Create index on productId inside order items
db.orders.createIndex({ "items.productId": 1 })

// Also on products _id (already exists)

2. Restructure Pipeline Order

// OPTIMIZED PIPELINE
const topProductsOptimized = await Order.aggregate([
  // Stage 1: Match only recent orders (if possible)
  { $match: { createdAt: { $gte: startOfMonth } } },
  
  // Stage 2: Unwind items (still needed)
  { $unwind: '$items' },
  
  // Stage 3: Group FIRST to reduce documents before $lookup
  {
    $group: {
      _id: '$items.productId',
      totalRevenue: { $sum: { $multiply: ['$items.price', '$items.quantity'] } }
    }
  },
  
  // Now only 500k docs (unique products) instead of 6M
  // Stage 4: Lookup product details
  {
    $lookup: {
      from: 'products',
      localField: '_id',
      foreignField: '_id',
      as: 'product'
    }
  },
  
  { $unwind: '$product' },
  
  // Stage 5: Group by category
  {
    $group: {
      _id: '$product.category',
      products: {
        $push: {
          productId: '$_id',
          name: '$product.name',
          revenue: '$totalRevenue'
        }
      }
    }
  },
  
  // Stage 6: Sort and slice per category
  {
    $project: {
      topProducts: {
        $slice: [
          { $sortArray: { input: '$products', sortBy: { revenue: -1 } } },
          5
        ]
      }
    }
  }
]);

3. Use $merge for Materialized Views

Instead of running the heavy aggregation every hour, we stored results:

// Pipeline with $merge to cache results
const pipeline = [
  // ... aggregation stages ...
  {
    $merge: {
      into: "category_top_products",
      whenMatched: "replace",
      whenNotMatched: "insert"
    }
  }
];

// Then query the pre-aggregated collection
const topProducts = await db.collection('category_top_products').find().toArray();

The Prevention

1. Use explain() to Detect COLLSCAN

const explainResult = await Order.aggregate([...]).explain();
if (explainResult.stages.some(s => s.stage === 'COLLSCAN')) {
  console.error('WARNING: COLLSCAN detected in aggregation');
  // Send alert
}

2. Set up Atlas Performance Advisor

Atlas automatically suggests indexes for slow queries.

3. Kill Slow Operations Automatically

// Set maxTimeMS on aggregations
await Order.aggregate([...], { maxTimeMS: 60000 }); // 60 seconds
// MongoDB will kill the operation if it exceeds time

4. Enable Profiling

// Log all slow queries (>100ms)
db.setProfilingLevel(1, { slowms: 100 })

// Check slow logs
db.system.profile.find({ op: "command", "command.aggregate": "orders" }).sort({ ts: -1 }).limit(10)

Commands to Debug Aggregations

Check running aggregations

db.currentOp({
  "command.aggregate": { $exists: true },
  "secs_running": { $gt: 10 }
})

Kill a stuck aggregation

db.killOp(opid)

Analyze pipeline performance

// Use $planCacheStats
db.orders.aggregate([ { $planCacheStats: {} } ])

What I Learned

• Aggregation pipelines need indexes on every join field.
• Reduce document count BEFORE $lookup – group early.
• Never run heavy aggregations on primary – use a secondary read preference or Atlas read-only instance.
• Materialized views are your friend – pre-aggregate once, query many times.

That 3-hour CPU spike taught me to treat aggregations like production code – test, explain, index, and always set timeouts.