A `$lookup` aggregation that spills and times out

allowDiskUse makes the pipeline stop failing, and that is exactly why it is dangerous: it converts a loud error into a quiet, slow one. It does not reduce any of the work.

What the pipeline does now: $lookup runs first, so all 8 million orders are enriched with their customer document - roughly 8 million index seeks into customers plus array assembly - producing an intermediate stream far larger than either input. Only then does $match filter to EMEA. Because cust is an array produced by $lookup, the predicate "cust.region" is not an indexable predicate on orders, so the filter discards most of what was just built. The $group then holds one accumulator per distinct segment; if EMEA still covers millions of orders, the 100 MB per-stage limit is exceeded and it spills.

Fix the order of operations first: filter before you enrich. If the report is really "EMEA revenue by segment", start from customers, match region EMEA there, and $lookup into orders - the driving side becomes 400,000 documents shrinking to a small EMEA set, instead of 8 million. Keep $group last. Only after the pipeline does proportionate work should you consider the memory settings.

Shape A - drive from customers:

[{$match: {region: "EMEA"}},
 {$lookup: {from: "orders", let: {cid: "$_id"},
            pipeline: [{$match: {$expr: {$eq: ["$customerId", "$$cid"]}}}],
            as: "o"}},
 {$unwind: "$o"},
 {$group: {_id: "$segment", total: {$sum: "$o.total"}}},
 {$sort: {total: -1}}]

The $lookup into orders on customerId requires an index on orders.customerId; without it each of the (now few) EMEA customers triggers a collection scan of 8 million orders. If you use the simple localField/foreignField form instead of the sub-pipeline, the same index is required.

Shape B - drive from orders but filter first:

1. Resolve EMEA customer _ids once into a list (a separate, cheap query on customers.region).
2. $match: {customerId: {$in: [...]}} as the first stage of the orders pipeline, so the index on orders.customerId (or {customerId: 1, status: 1}) does the filtering.
3. $group on customer segment - but segment lives in customers, so you either $lookup after the group (few rows) or carry the segment by grouping on customerId first, then $lookup on the ~small grouped result and re-group by segment.

Shape B is usually better for large regions, because $in is indexable and the $lookup happens on tens of rows instead of millions. Shape A is better when EMEA is a small fraction of customers and the customer set is naturally the driver.

First check whether the group is genuinely large: grouping by segment should produce a handful of groups, and a handful of groups never needs 100 MB. A $group that spills usually means the grouping key is effectively unbounded - a typo like grouping on $cust (the whole array) or on a field with one distinct value per document turns a 5-group aggregation into a 400,000-group one. Fix the key before touching memory settings.

If the cardinality is genuinely large, then: set allowDiskUse: true (default in 6.0+ for most stages, with a spill threshold), and more importantly reduce the rows entering $group - filter earlier, project only the fields needed before the group, or pre-aggregate per customer and roll up in a second stage. $group and blocking $sort each have a 100 MB budget; a $sort immediately followed by $limit can use a top-k sort and needs almost no memory, so put $limit where the planner can use it.

Confirm with explain("executionStats") on the aggregation (use explain with verbosity: "executionStats" so you get per-stage totalDocsExamined and timings). What you want to see: the first stage examining thousands instead of millions, IXSCAN rather than COLLSCAN inside $lookup, usedDisk: false on the group, and nReturned collapsing early. If totalDocsExamined is still in the millions, the rewrite filtered late and you have not fixed the root cause.