Optimizing Large-Scale MongoDB Aggregation Pipelines: A Deep Dive

Large-scale MongoDB aggregation pipelines can become slow and memory-hungry long before you notice. A $lookup that scans a million documents, a $group that spills to disk, or a $sort that ignores an index — these are the silent killers of query performance. In this deep dive, we'll peel back the hood of the aggregation framework, understand how the query planner makes decisions, and arm you with concrete, production-tested techniques to make pipelines fast and predictable.

• 1. The Anatomy of an Aggregation Pipeline
• 2. Indexing Strategies for Aggregation
• 3. The $match and $sort Optimization
• 4. Memory and the 100MB Limit
• 5. Optimizing $lookup and Joins
• 6. Using Facets, Buckets, and $merge
• 7. Real-World Optimization Workflow
• 8. Anti-Patterns to Avoid
• 9. Frequently Asked Questions

1. The Anatomy of an Aggregation Pipeline

An aggregation pipeline is a sequence of stages that transform documents. Each stage receives a stream of documents from the previous stage and outputs a new stream. The MongoDB query planner treats the entire pipeline as a single query and attempts to optimize it as a whole — but only within certain boundaries.

How the Query Planner Works

The planner examines the pipeline from the first stage to the last. It looks for opportunities to:

• Push $match stages earlier (they filter documents, reducing work downstream).
• Push $project stages earlier (they limit fields, reducing memory per document).
• Convert $sort followed by $limit into a top-k sort (much cheaper).
• Reorder stages when the result is semantically identical (e.g., moving $match before $unwind).

However, the planner will not reorder stages that have side effects or data dependencies. For example, a $group cannot be moved before a $match that filters on the grouped field, because the group would compute the wrong result.

Why Pipelines Slow Down

There are four primary culprits for slow aggregation:

1. Full collection scans — a $match or $lookup that cannot use an index forces the engine to read every document.
2. Memory spills — $group, $sort, and $bucket that exceed the 100 MB memory limit spill to disk, causing I/O spikes.
3. Expensive stages — $unwind on large arrays, $lookup on unindexed collections, and $facet with heavy sub-pipelines multiply work.
4. Blocking stages — stages that must see all documents before emitting output (like $sort and $group) prevent streaming and increase latency.

Understanding these root causes is the first step. The rest of this article provides concrete strategies to address each one.

2. Indexing Strategies for Aggregation

Indexes are the single most impactful optimization for aggregation pipelines. But not every index helps every stage. You need to understand which stages can use which indexes.

Indexes That Match $match

A $match stage at the beginning of a pipeline behaves like a find() query. It can use any index that supports the filter predicates. For example, given this pipeline:

db.orders.aggregate([
  { $match: { status: "shipped", total: { $gt: 100 } } },
  { $group: { _id: "$customerId", sum: { $sum: "$total" } } }
])

A compound index on { status: 1, total: 1 } would be ideal. The planner will use it to satisfy the $match and then pass the matching documents to $group.

Indexes That Match $sort

If a $sort stage is preceded by a $match, the sort can often be satisfied by an index if the index order matches the sort order. For example:

db.events.aggregate([
  { $match: { userId: "abc123" } },
  { $sort: { timestamp: -1 } }
])

An index on { userId: 1, timestamp: -1 } allows MongoDB to retrieve documents in the correct order without a blocking sort.

Indexes That Help $group

A $group stage cannot directly use an index to avoid grouping — it must accumulate all documents in memory. However, if the $group is preceded by a $match that narrows the input, an index on the matched fields reduces the number of documents the group must process. In some cases, a $group followed by a $sort on the _id field can use an index if the group's _id is indexed, but this is rare.

Compound Indexes and Index Prefixes

MongoDB compound indexes follow the leftmost prefix rule. An index on { a: 1, b: 1 } supports queries on a, and queries on a + b, but not on b alone. When designing indexes for aggregation, always consider the order of fields in the $match and $sort stages.

Using $indexStats

To discover which indexes are actually being used, run:

db.collection.aggregate([{ $indexStats: {} }])

This returns usage statistics for every index on the collection. Look for indexes with accesses counts near zero — those are candidates for removal, reducing write overhead.

3. The $match and $sort Optimization

The first stages of a pipeline determine its performance ceiling. Getting them right can mean the difference between scanning 10 million documents and 10 thousand.

Early Filtering

Always put the most selective $match stage as early as possible. The planner will try to push $match stages earlier, but it cannot move one past a $project that removes the matched field, or past a $limit that changes semantics. As a developer, you should manually order stages so that filtering happens before expansion ($unwind) or grouping.

// BAD: unwinds first, then filters
// Processes every array element before filtering
db.orders.aggregate([
  { $unwind: "$items" },
  { $match: { "items.sku": "A123" } }
])

// GOOD: filters first, then unwinds
// Only matching orders are unwound
db.orders.aggregate([
  { $match: { "items.sku": "A123" } },
  { $unwind: "$items" }
])

$sort with $limit

When a $sort is immediately followed by a $limit, MongoDB uses a top-k sort algorithm that only keeps the top k documents in memory. This is dramatically faster than sorting the entire result set. The planner does this automatically, but only if there is no intervening stage that breaks the pattern.

// Efficient: top-k sort
db.sales.aggregate([
  { $sort: { amount: -1 } },
  { $limit: 10 }
])

// Inefficient: sort all, then skip and limit
// The $skip prevents the top-k optimization
db.sales.aggregate([
  { $sort: { amount: -1 } },
  { $skip: 100 },
  { $limit: 10 }
])

In the second example, MongoDB must sort all documents because it cannot know which 10 documents to keep after skipping 100.

Using hint()

Sometimes the query planner chooses a suboptimal index. You can force a specific index with the hint() method:

db.orders.aggregate(
  [
    { $match: { status: "shipped" } },
    { $sort: { createdAt: -1 } }
  ],
  { hint: { status: 1, createdAt: -1 } }
)

Use hint() sparingly — only after confirming with explain() that the chosen index is indeed better. Index selection can change as data grows, so a hard-coded hint can become a liability.

4. Memory and the 100MB Limit

MongoDB limits each aggregation stage to 100 MB of memory by default. Stages like $group, $sort, $bucket, and $facet are blocking — they must accumulate all input documents before emitting output. If the accumulated data exceeds 100 MB, the stage throws an error unless allowDiskUse is enabled.

Enabling allowDiskUse

db.largeCollection.aggregate(
  [
    { $group: { _id: "$category", count: { $sum: 1 } } }
  ],
  { allowDiskUse: true }
)

When allowDiskUse is true, MongoDB spills intermediate data to temporary files on disk. This avoids the error but introduces significant I/O overhead. It's a safety net, not a performance strategy.

Reducing Memory Footprint

Before resorting to disk, try these techniques:

• Project early: Use $project or $addFields to drop fields you don't need before a $group or $sort. This reduces the size of each document in memory.
• Use $group with $sum instead of $push: Accumulating arrays ($push) is memory-hungry. If you only need counts or sums, use $sum.
• Use $sort with $limit: As noted, top-k sort uses far less memory than full sort.
• Use $bucket instead of $group for histograms: $bucket is more memory-efficient for fixed-range groupings.

Monitoring Memory Usage

Use explain("executionStats") to see the peak memory usage of each stage:

db.collection.aggregate(pipeline, { explain: "executionStats" })

Look for totalDocsExamined, totalKeysExamined, and maxMemoryUsage in the output. If maxMemoryUsage is close to 100 MB, you need to optimize or enable allowDiskUse.

5. Optimizing $lookup and Joins

$lookup performs a left outer join with another collection. It is often the most expensive stage in a pipeline because it runs a separate query for each input document.

Indexing the Foreign Collection

The foreign collection (the one being joined to) must have an index on the localField or foreignField that matches the join condition. Without an index, $lookup performs a collection scan for every input document — catastrophic for large inputs.

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",
      localField: "customerId",
      foreignField: "_id",
      as: "customer"
    }
  }
])

Ensure there is an index on customers._id (it's the primary key, so it's already indexed). For non-primary-key joins, create an explicit index:

db.customers.createIndex({ email: 1 })

$lookup with Pipeline vs. localField/foreignField

The pipeline form of $lookup is more flexible but also more expensive. It allows you to filter and transform the joined documents before returning them:

db.orders.aggregate([
  {
    $lookup: {
      from: "customers",
      let: { custId: "$customerId" },
      pipeline: [
        { $match: { $expr: { $eq: ["$_id", "$$custId"] } } },
        { $project: { name: 1, email: 1 } }
      ],
      as: "customer"
    }
  }
])

The pipeline form cannot use the foreign collection's index as effectively as the simple form, because the $match uses $expr which may not be index-friendly. If you don't need the extra flexibility, prefer the simple form.

Denormalization Alternatives

For extremely large datasets, consider denormalizing the joined data into the source collection. This eliminates $lookup entirely. For example, store customerName directly on the order document. This trades write complexity for read performance — a common pattern in high-throughput systems.

6. Using Facets, Buckets, and $merge

$facet and Parallel Execution

$facet allows you to run multiple sub-pipelines on the same input stream. Each sub-pipeline is executed independently and can be processed in parallel on different cores. This is ideal for dashboards that need multiple aggregations on the same data:

db.orders.aggregate([
  {
    $facet: {
      byStatus: [
        { $group: { _id: "$status", count: { $sum: 1 } } }
      ],
      byCustomer: [
        { $group: { _id: "$customerId", total: { $sum: "$total" } } },
        { $sort: { total: -1 } },
        { $limit: 5 }
      ]
    }
  }
])

$facet is memory-intensive because it buffers the entire input for each sub-pipeline. Use it with a selective $match before the facet to reduce input size.

$bucket for Histograms

$bucket groups documents into fixed ranges and is more efficient than $group for histogram-style aggregations:

db.products.aggregate([
  {
    $bucket: {
      groupBy: "$price",
      boundaries: [0, 50, 100, 200, 500],
      default: "other",
      output: {
        count: { $sum: 1 },
        avgPrice: { $avg: "$price" }
      }
    }
  }
])

$merge for Incremental Aggregation

$merge writes the aggregation results into another collection. It can be used to maintain materialized views or rollups. Instead of re-aggregating the entire dataset every time, you can incrementally update a summary collection:

db.dailySales.aggregate([
  {
    $match: { saleDate: { $gte: "2025-01-01", $lt: "2025-01-02" } }
  },
  {
    $group: { _id: { $dateToString: { format: "%Y-%m-%d", date: "$saleDate" } }, total: { $sum: "$amount" } }
  },
  {
    $merge: {
      into: "salesRollup",
      on: "_id",
      whenMatched: "replace",
      whenNotMatched: "insert"
    }
  }
])

This pattern is powerful for large-scale reporting where you can't afford to aggregate the full collection on every request.

7. Real-World Optimization Workflow

Optimizing an aggregation pipeline is not a one-off activity. Follow this workflow to systematically identify and fix bottlenecks.

Step 1: Profile with explain()

Run the pipeline with explain("executionStats") and examine the output:

db.collection.aggregate(pipeline, { explain: "executionStats" })

Key metrics to look for:

• totalDocsExamined — number of documents read. If this is much higher than the result count, you need better filtering or indexes.
• totalKeysExamined — number of index keys examined. If this is high but totalDocsExamined is low, you may be using an index but still scanning many keys.
• executionTimeMillis — total time. Compare this to the time of individual stages.
• maxMemoryUsage — peak memory per stage. Values near 100 MB indicate a spill risk.

Step 2: Use $planCacheStats

To see how the planner is caching query plans, run:

db.collection.aggregate([{ $planCacheStats: {} }])

This shows which shapes have cached plans and which indexes they use. If you see a plan using a full collection scan, you know an index is missing or not being chosen.

Step 3: Monitor with Atlas/Compass

In production, use MongoDB Atlas's Performance Advisor or the Profiler in Compass to track slow queries. Set a slow query threshold (e.g., 100 ms) and review the slow query log. This gives you a real-world view of pipeline performance under load.

Step 4: Iterate

Make one change at a time (add an index, reorder stages, reduce memory) and re-run explain(). Measure the impact before moving on. Avoid making multiple changes simultaneously, as you won't know which one helped.

8. Anti-Patterns to Avoid

$where

The $where operator runs JavaScript on the server and cannot use indexes. It forces a full collection scan and is extremely slow. Use $expr with aggregation operators instead.

// BAD: full scan
db.users.find({ $where: "this.age > 30" })

// GOOD: uses index if available
db.users.find({ age: { $gt: 30 } })

$regex Without Index

A $regex pattern that is not anchored to the beginning of the string cannot use an index. For example, /^abc/ can use an index, but /abc/ cannot. If you need substring search, consider using Atlas Search or a text index.

$unwind of Large Arrays

$unwind on a field that is an array with thousands of elements multiplies the document count. If you only need to filter on array elements, use $elemMatch or an array index instead of unwinding.

$lookup Without Index

As discussed, $lookup without an index on the foreign collection is a performance disaster. Always verify that the join fields are indexed.

$group with $push on Large Fields

$push accumulates entire documents into an array. If the documents are large, this quickly exhausts memory. Use $addToSet only when you need unique values, or project down to smaller fields before pushing.

9. Frequently Asked Questions

Q: How do I know if my aggregation pipeline is using an index?

Run the pipeline with explain("executionStats") and look for winningPlan and totalKeysExamined. If totalKeysExamined is greater than 0 and totalDocsExamined is low, an index is being used. If totalDocsExamined equals the total document count in the collection, it's a collection scan.

Q: Should I always enable allowDiskUse?

No. allowDiskUse masks memory problems and introduces I/O overhead. It should be a last resort. First, try to reduce memory usage by projecting fields, using $limit with $sort, or restructuring the pipeline. Only enable allowDiskUse when you've exhausted those options.

Q: Can I use aggregation pipelines for real-time analytics?

Yes, but for very large collections, consider pre-aggregating with $merge into a rollup collection. Real-time queries against a rollup are much faster than aggregating the raw collection on every request. You can also use change streams to keep the rollup up to date. For more insights on building scalable systems, check out Tamiz's Insights and tamiz.pro.