A $group over 300 million documents spills to disk and runs for an hour — how would you speed it up?

## Read the shape of the cost first Two different costs hide behind "the aggregation is slow", and they have different fixes. **Scan cost** is proportional to the number of documents and bytes the pipeline reads. If a `COLLSCAN` feeds 300 million documents into the pipeline, you pay for all of them before any grouping happens. **Group memory** is proportional to the number of *distinct* grouping keys times the size of each accumulator's state. Ten billion input documents grouping into fifty keys is cheap in memory; three hundred million documents grouping into two hundred million keys is not, and that is what spills. Getting this distinction right is most of the senior signal in the question. `explain("executionStats")` tells you which one you have: whether the leading stage was an index scan or a collection scan, and how many documents entered the pipeline. ## Step 1 — cut the input The single highest-leverage change is a selective `$match` as the pipeline's first stage, backed by an index. Only a leading `$match` reaches the query layer and can use an index; a filter written after the `$group` has already paid for everything. Most "grouping the whole collection" pipelines are actually reporting on a window — a date range, a tenant, a status — and simply were not written that way. If the report genuinely needs the full history, the input cannot shrink and you should skip to step 3. ## Step 2 — cut the payload and the accumulator state Documents carry their whole shape through the pipeline unless you narrow them. Projecting to the handful of fields the `$group` actually uses reduces bytes moved per document, which shows up directly in runtime on wide documents. The accumulators matter even more. `$sum`, `$avg`, `$min`, `$max` and `$count` keep a fixed few bytes per group. `$push` and `$addToSet` keep an array per group that grows without bound, and they are the usual reason a `$group` blows its budget at modest cardinality. They can also push a single output document past the 16 MB BSON limit, at which point no amount of disk spilling saves you. If you only need the top few members per group, get them another way rather than pushing everything and slicing afterwards. ## Step 3 — cut, or partition, the key Ask what the grouping key's cardinality actually is. Grouping by `{ userId, minute }` over a year is a vastly bigger key space than `{ userId, day }`, and the report often only needs the coarser one. Rounding a timestamp down before grouping is frequently a hundred-fold memory reduction for identical business meaning. When the key cannot be coarsened, partition the work. Run the pipeline over one time window at a time, so each run's key space is bounded, and combine the partials. ## Step 4 — stop recomputing history An aggregation that reads three hundred million documents on every execution is usually recomputing an answer that barely changed. The durable fix is incremental materialization: run the pipeline over the newest window, end it with `$merge` into a summary collection keyed by the grouping key plus the window, and let reports aggregate the (much smaller) summary. Each run then touches only the new data, and the expensive part of the query disappears from the read path entirely. ## What not to do - **Do not treat `allowDiskUse` as the fix.** It is why the query completes at all. From MongoDB 6.0 it is on by default, which means a spilling pipeline no longer announces itself with an error — it just gets slow and competes for I/O with live traffic. - **Do not add `$limit` after the `$group` and expect relief.** By then all the grouping work is done; the limit only trims the output. - **Do not index the grouping key and assume the group becomes cheap.** An index helps the leading filter and can supply ordering; it does not eliminate the accumulator table. - **Do not tune before measuring.** Confirm from the plan whether you are scan-bound or memory-bound, and change one thing at a time. ## How to present the answer An interviewer is listening for a diagnostic order rather than a list of tricks: identify whether the pain is scan volume or key cardinality, reduce the input with an index-backed leading filter, reduce per-document and per-group state, coarsen or partition the key, and — if the same expensive computation recurs on a schedule — change the shape of the work with incremental materialization instead of optimizing the same full scan forever. Mentioning that heavy analytical passes should be isolated from the nodes serving latency-sensitive traffic rounds it out.