Optimizing Large-Scale MongoDB Aggregation Pipelines: A Deep-Dive into Performance, Memory, and Sharding Strategies

Originally published on tamiz.pro.

MongoDB’s aggregation framework is a powerful tool for transforming and analyzing large datasets, but as collections grow into the hundreds of millions or billions of documents, poorly written pipelines can bring a cluster to its knees. This deep-dive unpacks how aggregation stages execute under the hood, identifies the bottlenecks that kill throughput, and walks through concrete optimization strategies—from pipeline reordering and index exploitation to sharding and memory management—that production teams use to keep queries fast and resource usage predictable.

Table of Contents

• 1. How Aggregation Pipelines Execute
• 2. Common Performance Bottlenecks
• 3. Pipeline Reordering and Early Filtering
• 4. Indexing Strategies for Aggregation
• 5. Memory Limits and the AllowDiskUse Option
• 6. Sharding Considerations
• 7. Real-World Case Study
• 8. Monitoring and Profiling
• 9. Frequently Asked Questions

1. How Aggregation Pipelines Execute

Every aggregation pipeline is compiled into an internal execution plan, much like a query optimizer in a relational database. MongoDB’s aggregation engine processes documents stage by stage, passing the output of each stage as input to the next. Each stage operates as a stream: documents flow through, get transformed, filtered, or grouped, and are passed downstream. Understanding this streaming model is critical because it determines whether a stage can leverage an index or whether it must perform a full collection scan or an in-memory sort.

The execution engine distinguishes between two types of stages:

• Streaming stages process documents one at a time and can begin emitting output immediately. Examples include $match, $sort, $skip, $limit, and $project.
• Blocking stages must consume the entire input before producing any output. Examples include $group, $sort (when no index is available), $facet, and $bucket.

A pipeline like db.orders.aggregate([{$match: {...}}, {$sort: {...}}, {$group: {...}}]) forces MongoDB to scan, sort everything in memory, and then group. If the $sort can use an index, it becomes a streaming operation, dramatically reducing memory pressure.

2. Common Performance Bottlenecks

Before diving into fixes, it helps to recognize the patterns that cause slowdowns:

2.1 Late $match Stages

Plating a $match stage after $project, $group, or $lookup means the pipeline processes every document in the collection before filtering. The fix is to push $match as close to the beginning as possible.

2.2 Unindexed $group Operations

Grouping by a field without a supporting index forces MongoDB to build an in-memory hash table of all group keys. On collections with high cardinality, this can exhaust the 100 MB memory limit per pipeline stage.

2.3 Expensive $lookup Joins

$lookup performs a nested loop join by default. Without an index on the foreign collection’s join field, each lookup degenerates into a collection scan. Even with an index, lookups inside a $unwind or $group can multiply the cost.

2.4 In-Memory Sorts Without Indexes

A $sort stage without a matching index must load all documents into memory and perform an in-memory sort. If the result exceeds 100 MB, the pipeline fails unless allowDiskUse is enabled.

2.5 Unnecessary $project and $addFields

Including fields that are never used downstream increases document size, memory consumption, and network I/O. Pruning early reduces the working set.

3. Pipeline Reordering and Early Filtering

The single most impactful optimization is restructuring the pipeline so that filtering happens as early as possible. MongoDB’s query planner can only use an index for a $match stage if that $match appears before any blocking stage.

Consider this anti-pattern:

// ❌ Inefficient: processes all documents before filtering
db.orders.aggregate([
  { $project: { total: { $sum: "$items.price" } } },
  { $match: { total: { $gt: 1000 } } },
  { $group: { _id: "$customerId", count: { $sum: 1 } } }
])

The $project and $sum execute on every document. A better approach pushes $match first:

// ✅ Efficient: filters early, reduces working set
db.orders.aggregate([
  { $match: { status: "completed" } },
  { $project: { total: { $sum: "$items.price" }, customerId: 1 } },
  { $match: { total: { $gt: 1000 } } },
  { $group: { _id: "$customerId", count: { $sum: 1 } } }
])

In some cases, you can even move a $match that references a computed field into an indexed field on the collection. For example, if you frequently filter orders by total > 1000, consider maintaining a total field on each document and indexing it, then matching on that field directly.

4. Indexing Strategies for Aggregation

Indexes are the primary lever for accelerating aggregation pipelines. Here are the key strategies:

4.1 Compound Indexes for Multi-Stage Pipelines

If your pipeline starts with { $match: { status: "shipped", region: "us-east" } } followed by { $sort: { createdAt: -1 } }, a compound index on { status: 1, region: 1, createdAt: -1 } allows MongoDB to both filter and sort using the index, eliminating the in-memory sort entirely.

db.orders.createIndex({ status: 1, region: 1, createdAt: -1 })

4.2 Indexes on $lookup Join Fields

Every $lookup that joins orders to customers on customerId requires an index on customers.customerId. Without it, each lookup triggers a full collection scan of the customers collection.

// Foreign collection index
db.customers.createIndex({ _id: 1 })

// Pipeline with indexed lookup
db.orders.aggregate([
  { $lookup: {
    from: "customers",
    localField: "customerId",
    foreignField: "_id",
    as: "customer"
  } },
  { $match: { "customer.tier": "premium" } }
])

4.3 Partial Indexes for Filtered Workloads

If 80% of your queries filter on status: "active", create a partial index that only covers active documents:

db.orders.createIndex(
  { customerId: 1, createdAt: -1 },
  { partialFilterExpression: { status: "active" } }
)

This keeps the index smaller and faster while still supporting the most common query pattern.

4.4 Wildcard Indexes for Schemaless Fields

When aggregating on fields that don’t exist in every document (common in flexible schemas), a wildcard index can cover those sparse fields:

db.orders.createIndex({ "$**": 1 })

Use sparingly, as wildcard indexes can be significantly larger than targeted indexes.

5. Memory Limits and the AllowDiskUse Option

MongoDB imposes a 100 MB memory limit on each pipeline stage. If a $group, $sort, or $lookup exceeds this limit, the pipeline throws an error unless allowDiskUse is enabled:

db.orders.aggregate(
  [/* pipeline stages */],
  { allowDiskUse: true }
)

While allowDiskUse prevents failures, it comes at a performance cost: disk I/O is orders of magnitude slower than in-memory operations. The goal should be to stay within memory limits rather than relying on disk spilling.

Strategies to reduce memory usage:

1. Filter early: Push $match stages to the front of the pipeline to reduce the number of documents processed.
2. Group in batches: For large $group operations, consider pre-sorting on the group key so MongoDB can use a streaming group algorithm instead of a hash-based one.
3. Limit intermediate results: Use $limit or $sample to cap the number of documents flowing through expensive stages.
4. Avoid unbounded $unwind: If unwinding an array, follow immediately with a $match or $limit to reduce the document count.

For example, this pipeline is memory-efficient because $sort uses an index and $group operates on a pre-sorted stream:

db.orders.aggregate([
  { $match: { status: "completed" } },
  { $sort: { customerId: 1, createdAt: 1 } },
  { $group: {
    _id: "$customerId",
    totalSpent: { $sum: "$amount" }
  } }
])

6. Sharding Considerations

When a single collection exceeds the capacity of a single server, sharding becomes necessary. However, sharding introduces new constraints on aggregation performance:

6.1 Shard Key Selection

The shard key determines how data is distributed. A good shard key ensures even distribution and allows queries to target specific shards. If your aggregation pipeline filters on the shard key, MongoDB can route the query to a subset of shards (targeted routing). If not, it must scatter-gather across all shards, which is significantly slower.

6.2 $merge and $out Stages

Stages like $merge and $out write the aggregation result back to a collection. In a sharded cluster, these stages require all data to be merged on a single shard, creating a potential bottleneck. Consider writing results to a sharded collection instead of an unsharded one.

6.3 $group with Accumulators Across Shards

When a $group stage spans multiple shards, MongoDB first groups locally on each shard, then merges the partial results on a merge shard. This two-phase grouping can be slow for high-cardinality group keys. To mitigate:

• Use a compound shard key that includes the group key.
• Pre-aggregate data using a cron job or change stream.
• Consider using $bucket or $facet to split the workload.

For example, if you shard orders on customerId, grouping by customerId benefits from targeted routing:

db.orders.aggregate([
  { $match: { customerId: { $in: ["c1", "c2", "c3"] } } },
  { $group: {
    _id: "$customerId",
    totalSpent: { $sum: "$amount" }
  } }
])

MongoDB routes this query to only the shards holding c1, c2, and c3, then performs a local group on each shard before merging.

6.4 Distributed $lookup

In sharded clusters, $lookup between sharded collections requires the local and foreign fields to be compatible shard keys. Otherwise, MongoDB performs a broadcast lookup across all shards, which is extremely expensive.

7. Real-World Case Study

Problem: An e-commerce company runs a daily aggregation to compute revenue per product category. The orders collection has 500 million documents, and the pipeline currently takes 45 minutes to complete, often timing out.

Original Pipeline:

db.orders.aggregate([
  { $project: {
    categoryId: 1,
    amount: { $sum: "$items.price" },
    status: 1
  } },
  { $match: { status: "completed" } },
  { $group: {
    _id: "$categoryId",
    totalRevenue: { $sum: "$amount" }
  } },
  { $sort: { totalRevenue: -1 } }
])

Optimizations Applied:

1. Reordered stages: Moved $match before $project to reduce the working set.
2. Added a compound index: Created an index on { status: 1, categoryId: 1 } to support both filtering and grouping.
3. Pre-computed totals: Added a total field to each order document and indexed it, eliminating the $sum inside $project.
4. Enabled allowDiskUse: As a safety net, though the pipeline now stays well under the 100 MB limit.

Optimized Pipeline:

db.orders.createIndex({ status: 1, categoryId: 1, total: 1 })

db.orders.aggregate([
  { $match: { status: "completed" } },
  { $group: {
    _id: "$categoryId",
    totalRevenue: { $sum: "$total" }
  } },
  { $sort: { totalRevenue: -1 } }
], { allowDiskUse: true })

Result: Runtime dropped from 45 minutes to 3 minutes. The index allows MongoDB to scan only completed orders and group them using the index order, eliminating the in-memory sort.

8. Monitoring and Profiling

Optimization is an ongoing process. Use MongoDB’s built-in tools to identify slow pipelines:

8.1 Database Profiler

Enable the profiler to capture slow operations:

db.setProfilingLevel(1, { slowms: 100 })

Queries that appear in system.profile with high millis or keysExamined values are candidates for optimization.

8.2 Explain Plans

Always run explain() on suspicious pipelines:

db.orders.explain("executionStats").aggregate([
  { $match: { status: "completed" } },
  { $group: { _id: "$categoryId", total: { $sum: "$total" } } }
])

Look for:

• stage: "COLLSCAN" when an IXSCAN was expected.
• keysExamined much higher than docsExamined.
• stage: "SORT" indicating an in-memory sort.

8.3 Atlas Performance Advisor

If you're on MongoDB Atlas, the Performance Advisor automatically suggests indexes based on query patterns. Review its recommendations regularly.

9. Frequently Asked Questions

Q: When should I use $facet versus separate queries?

A: $facet runs multiple pipelines in parallel within a single aggregation, which is useful for dashboard-style queries that need multiple summary statistics. However, it forces all sub-pipelines to share the same working set. If the sub-queries can run independently, separate queries with parallel execution in your application layer may be more efficient.

Q: Does allowDiskUse always fix memory limit errors?

A: Yes, it allows MongoDB to spill to disk, but at a significant performance cost. It should be a last resort, not a default. The real fix is to reduce memory usage through pipeline reordering, indexing, and early filtering.

Q: How do I handle $lookup performance with large foreign collections?

A: Ensure the foreign collection has an index on the join field. If the join is still slow, consider denormalizing related data or maintaining a pre-joined materialized view updated via change streams.