How would you decide whether a $lookup-heavy pipeline can scale in a sharded cluster?

## Frame the cost before optimizing anything `$lookup` is not a set-based operation you can hand-wave about. The foreign side is resolved **per input document reaching the stage**. That single fact drives every decision: > total join cost ≈ (documents entering the $lookup) x (cost of one foreign lookup) So there are exactly two things to attack — the multiplier and the multiplicand — plus a third question specific to sharded clusters: *where* each lookup has to be executed. ## Lever 1 — shrink the left side Every document you eliminate before the `$lookup` is a foreign lookup you never pay for. In practice this means: - Put the selective `$match` **before** the join, and make sure it is index-supported. A `$match` after the join has already paid for every lookup. - If the query is paginated or top-N, get the `$sort` + `$limit` before the join too, so you join 50 documents rather than 500,000. - Beware the pipeline whose left side is a `$group` output: grouping first can *shrink* the left side dramatically, and joining on the grouped key is often orders of magnitude cheaper than joining raw documents and grouping afterwards. The optimizer does move some `$match` stages earlier on its own, but it cannot do so across stages that change the fields being filtered. Do not rely on it — write the pipeline in the order you want executed. ## Lever 2 — make one lookup cheap - **Index the foreign side.** The equality form needs an index on `foreignField`; the pipeline form needs the sub-pipeline's leading `$match` to be index-eligible. Joining on `_id` is free. Joining on an unindexed field means a scan of the foreign collection per input document — this is the single most common cause of a pipeline that ran fine on test data and collapsed in production. - **Trim what comes back.** The equality form returns whole foreign documents. Switch to the pipeline form and `$project` down to the fields you need; on a wide foreign collection this cuts both memory and network cost. - **Bound the fan-out.** A `$limit` inside the sub-pipeline caps how many documents each parent can pull. Remember the joined array lives inside one output document and is still bound by the 16 MB BSON limit — a hub document joining to hundreds of thousands of children will fail outright, not merely run slowly. ## Lever 3 — routing in a sharded cluster This is where the judgment gets genuinely architectural. Current MongoDB supports a **sharded `from` collection** for `$lookup` (earlier versions required it to be unsharded, which is why older material says joins and sharding do not mix). Support, however, is not the same as efficiency. The question is whether each individual lookup can be **targeted**: - If the field named by `foreignField` **is** (or is a prefix of) the foreign collection's shard key, a lookup can be routed to the one shard that owns that key range. - If it is not, every lookup has to be broadcast to every shard, and each shard does work for keys it does not own. Cost now scales with document count **and** shard count, and adding shards makes the join worse rather than better. The left side matters too: a pipeline whose left side is itself scatter-gather starts from a large document count on every shard, and the merging node becomes a bottleneck. A useful heuristic: if neither side of the join is targeted, the pipeline is doing an all-shards-to-all-shards operation, and no amount of index tuning will change its scaling behaviour. `$graphLookup` is more constrained: its `from` collection **cannot be sharded**. If the hierarchy lives in a sharded collection, recursive traversal is simply off the table there and must be solved another way. ## When to stop optimizing and change the shape Signals that the query is the wrong layer to fix: - The join is on the **hot read path** of the product's main screen, not a report. - The left side cannot be shrunk because the query is inherently "all of X". - The foreign lookups cannot be targeted and the cluster is expected to grow. - The pipeline already runs in the analytics window and still misses it. The realistic responses, in increasing order of commitment: **materialize** the joined result with `$merge` into a collection refreshed on a schedule and read that instead; **route** analytical pipelines to secondaries or a dedicated analytics node so they cannot destabilize the transactional workload; or accept the read pattern as a schema signal and change how the data is stored. That last one is not a query decision, and it is the one a lead should be willing to raise: `$lookup` is best understood as the escape hatch for the joins you did not design for, not as the foundation of a high-traffic read path. ## What to measure Before any of this is more than opinion, get the numbers: how many documents actually enter the `$lookup`, whether the foreign lookups are index-driven, and where the time is spent. Run the pipeline's explain output on representative data volumes — a join whose foreign collection fits in cache on a test box behaves nothing like the same join over a production working set.