$lookup against embedding, measured on 10 million documents

The advice is everywhere and it is correct as far as it goes: store together what you read together, reference what changes on its own. It is also unfalsifiable as stated, because it never says what the wrong choice costs. A number changes the conversation. If referencing costs 3% you stop worrying about it; if it costs 40x you redesign the collection.

So I built both shapes and measured them. The result was not what I expected, and the part I expected to be a footnote turned out to be the whole finding.

What was measured

Ten million orders across 200,000 customers, in two shapes of the same information:

• Referenced. orders.customerId points at a separate customers collection. Reading an order with its customer means a $lookup.
• Embedded. Each order carries a customer subdocument with the four fields the read path uses: name, email, tier, country. Four fields, not the whole customer.

Both collections have the indexes their queries need, and the $lookup runs against an indexed _id, which is the best case for referencing rather than a strawman.

The customer assignment is deliberately skewed rather than uniform, because a uniform one makes the write comparison meaningless. The mean customer has 50 orders. The busiest has 22,441. Real data has a tail and the tail is where the interesting number lives.

The generator is seeded, so the same command produces the same distribution and the same busiest customer on any machine.

The read path

Shape	Median	95th
Embedded	1 ms	1 ms
Referenced, $lookup on indexed _id	1 ms	2 ms

That is the finding I did not expect. Fetching 50 orders with their customers through a $lookup on an indexed key was indistinguishable from having the data already embedded, at ten million documents.

Both numbers are at the floor this harness can resolve, so the honest statement is not “they are equal” but “the difference is smaller than a millisecond and this cannot measure it”. For the embedded side the plan agrees: nReturned 50 against totalDocsExamined 50, executionTimeMillis 0, so there is no wasted work there to find. The harness prints that plan for the embedded queries only, so the same claim is not made about the referenced side: what supports that one is the timing, not a plan I have shown you.

If somebody has told you $lookup is slow, this is the case they are wrong about. A join on an indexed key, for one page of results, is not the thing costing you anything.

The read path that actually hurts

Now sort by a field that lives on the joined side, “orders by customer tier”, and ask for the same 50 rows:

Shape	Median	95th
Embedded, sorted on customer.tier	1 ms	1 ms
Referenced, $lookup then $sort on the joined field	9.02 s	9.24 s

Nine seconds against one millisecond. Not a percentage, not a multiple worth writing down: a different category of thing.

The mechanism is the whole point. $lookup produces its output after the index scan, so nothing about the joined field exists when the planner picks a plan. It cannot use an index for that sort because the field is not in one yet. Every candidate document has to be joined, then collected, then sorted, and only then can 50 be taken off the top.

The embedded shape sorts on customer.tier from an index and stops after 50. Same 50 rows, same data, nine seconds apart.

The write path, where embedding sends the bill

Denormalising moves the cost, it does not remove it. Changing one customer’s tier:

Shape	Documents written	Median	95th
Referenced	1	1 ms	1 ms
Embedded, mean customer	39	1 ms	1 ms
Referenced, busiest customer	1	1 ms	1 ms
Embedded, busiest customer	22,441	341 ms	403 ms

The mean case is free. Rewriting 39 documents did not register against rewriting one.

The tail is not. The busiest customer’s 22,441 orders take 341 ms to update, and that is the number to plan around, because the busiest customer is exactly the one whose tier changes when somebody upgrades your largest account. Referencing has no busiest customer: it is one document every time.

The write cost scales with fan-out and not with collection size. Ten times the orders with the same distribution does not change the mean case at all; it changes the tail.

What this actually says

Three things, and only one of them is the usual advice.

A $lookup on an indexed key is not your problem. It measured the same as embedded on the read path. Restructuring a schema to avoid one, on the general belief that joins are expensive, is work spent on the wrong thing. Plenty of collections should stay normalised.

Sorting on the joined side is a completely different question. That is where the nine seconds is, and it is the case that appears in production and not in development, because it needs enough intermediate documents to matter. If a read has to order by something living on the other collection, embedding that one field is usually the entire fix. You do not need to denormalise the relationship, only the field you sort on.

Filtering on the joined side was not measured here and should not be assumed to behave the same way. The planner can coalesce a $lookup followed by $unwind and $match into the lookup itself, so a filter may be handled far better than a sort is. If that is your case, measure it rather than taking this post’s word for it.

Embed fields, not documents. The embedded shape here carries four fields. That keeps the write amplification proportional to what the read path needed and keeps the order document nowhere near the 16 MB limit. Copying the whole related document is how denormalising earned its bad reputation, and it is a different decision from the one measured here.

What this does not say

These numbers came from a container on a laptop, and they are a comparison between two shapes on one machine rather than a claim about what your cluster will do. The absolute milliseconds are not transferable, and neither is the exact multiple: what should carry is the mechanism, that a sort on a field produced by $lookup cannot use an index because the field does not exist when the plan is chosen. Expect that to cost you somewhere. Do not expect it to cost you 9,000 times.

The read numbers sit at the timer’s floor, so the correct reading is “too close to separate” rather than “identical”. If you need to separate them, the harness takes --runs and you will want a larger page size than 50.

Reproducing it

git clone https://github.com/Kontsedal/monghoul-public
cd monghoul-public/benchmarks/lookup-vs-embedding
npm install
docker run -d -p 27017:27017 mongo:8.3.8
node generate.mjs --uri mongodb://localhost:27017 --orders 10000000
node run.mjs --uri mongodb://localhost:27017

--orders defaults to a million, because ten million takes several minutes to insert. run.mjs prints the table above for your hardware, along with the plan statistics behind each read, so you can check the timings against totalDocsExamined rather than taking them on trust.