$lookup join strategies: understanding the trade-offs with flexible documents

A developer tested MongoDB $lookup join strategies against DocumentDB for PostgreSQL, an open-source extension implementing the MongoDB API on PostgreSQL. The analysis reveals that while document databases minimize joins through embedding, flexible field semantics like arrays restrict join algorithms, making relational databases more efficient for joins when they are necessary. The developer ran tests with 5 million portfolio documents and 5 FX rate documents, finding that DocumentDB's PostgreSQL optimizer can leverage scalar columns for better join performance compared to MongoDB's limited join strategies.

In a previous post https://dev.to/franckpachot/nested-loop-and-hash-join-for-mongodb-lookup-259d , I explored how MongoDB chooses between nested loop, indexed loop, and hash join strategies for $lookup . Here, I examine what occurs when $lookup runs on DocumentDB for PostgreSQL https://github.com/documentdb/documentdb —an open-source extension implementing the MongoDB API on PostgreSQL. The document model minimizes the need for joins by embedding related data directly within documents. However, when a join is necessary — such as for reference data that updates independently, many-to-many relationships, or dimensional lookups — the flexibility of embedding can complicate join optimization. The goal isn't just to identify "which database is faster"—it's to understand why their behaviors differ, the trade-offs involved, and the options when join performance matters. Relational databases tend to perform more joins because normalized schemas require them, but they also optimize joins more effectively thanks to scalar, well-typed columns. In contrast, document databases perform fewer joins thanks to embedding, but when they do, flexible field semantics—such as arrays—restrict the available join algorithms. I've run all tests in Docker containers with default settings on the same machine. The timings are indicative, not benchmarks — they illustrate the relative cost of different approaches, not absolute performance under production conditions caching, concurrency, hardware, and tuning would all change the numbers . In a document database, you'd typically embed related data to avoid joins. But some data doesn't embed well: rate to usd inside each portfolio document, you'd need to update millions of documents every time a rate moves.This is a classic case where a $lookup join makes sense: a large fact collection portfolios joined to a small, frequently-updated reference collection fxRates . The document model can't avoid this join without accepting stale embedded rates. I created two collections: portfolios : 5 million documents with a currency field 5 distinct values fxRates : 5 documents mapping each currency to its USD exchange rateI used mongosh to create and load the collection with the following commands: js db.portfolios.drop ; db.fxRates.drop ; const currencies = "USD", "EUR", "CHF", "GBP", "JPY" ; currencies.forEach cur = { db.fxRates.insertOne { currency: cur, rate to usd: Math.random 1.5 - 0.5 + 0.5, last updated: new Date } ; } ; const totalPortfolios = 5e6; let bulk = ; for let i = 1; i <= totalPortfolios; i++ { const currency = currencies Math.floor Math.random currencies.length ; bulk.push { portfolioId: i, clientId: Math.floor Math.random 10000 , valuation: Math.round Math.random 1 000 000 , currency: currency, asOfDate: new Date } ; if bulk.length === 10000 { db.portfolios.insertMany bulk ; bulk = ; } } if bulk.length 0 db.portfolios.insertMany bulk ; db.fxRates.createIndex { currency: 1 }, { unique: true } ; The index on a five-document collection is not strictly necessary, but it's good practice and protects my lookup table from duplicates. $lookup This query fetches all portfolios, retrieves the foreign exchange rate for each currency, and converts the valuation to USD. db.portfolios.aggregate {$lookup: { from: "fxRates", localField: "currency", foreignField: "currency", as: "fx" }}, {$unwind: "$fx"}, {$project: { portfolioId: 1, valuation: 1, currency: 1, rate to usd: "$fx.rate to usd", valuation usd: {$multiply: "$valuation", "$fx.rate to usd" } }} MongoDB's $lookup combined with $unwind behaves like a LEFT OUTER JOIN followed by filtering out non-matching rows. In a relational database, portfolios.currency is a VARCHAR column. The optimizer knows it's a single scalar value per row. It can extract it, hash it, sort it, or probe an index with it — all with well-defined operators. In a document database, currency might be: "USD" "USD", "EUR" MongoDB's $lookup compatibility requires the following behavior: localField is an array "USD", "EUR" , it matches any foreign document where foreignField equals "USD" OR "EUR" or contains either, if it's also an array .This means that the join condition is not always a simple equality a = b , but may involve “any element matches” semantics evaluated at runtime. Instead, the matching logic must evaluate each document's field at runtime, determine whether it's a scalar or an array, and match accordingly. The safest general approach is a lateral join — executing the inner query for each outer document and passing the current document's field value into the matching function. This is what both MongoDB and DocumentDB for PostgreSQL do. I use the DocumentDB API in a SQL query rather than the MongoDB-compatible endpoint to view the PostgreSQL execution plan. EXPLAIN ANALYZE ON, BUFFERS ON, COSTS ON, VERBOSE ON SELECT document FROM bson aggregation pipeline 'test', '{ "aggregate": "portfolios", "pipeline": { "$lookup": { "from": "fxRates", "localField": "currency", "foreignField": "currency", "as": "fx" } }, { "$unwind": "$fx" }, { "$project": { "portfolioId": 1, "valuation": 1, "currency": 1, "rate to usd": "$fx.rate to usd", "valuation usd": { "$multiply": "$valuation", "$fx.rate to usd" } } } , "cursor": {} }' ; Since I joined a large collection with a small one and require all documents from both, I would anticipate a hash join. Instead, it uses a nested loop join: php Nested Loop actual time=579..64792 rows=5000000 loops=1 - Seq Scan on documents 11 collection rows=5000000 loops=1 - Seq Scan on documents 10 collection 0 1 rows=1 loops=5000000 Filter: bson dollar lookup join filter ... Rows Removed by Filter: 4 Execution Time: 87750 ms The fxRates table 5 rows, fitting in a single 8kB block is scanned 5 million times. PostgreSQL's cost-based optimizer knows the table is tiny and fits in cache, so a sequential scan is the right choice over an index scan — but the scan is still executed 5 million times because of the LATERAL pattern. The filter function bson dollar lookup join filter is evaluated 25 million times. This function handles array semantics — it extracts the field from the outer document, determines whether it's scalar or an array, and checks for matches in the inner document. Because the inner side is marked as LATERAL, it depends on the current outer row. This prevents PostgreSQL from evaluating both sides independently, which is required for hash or merge joins. As a result, only a nested loop strategy is possible. In MongoDB, the equivalent behavior is the IndexedLoopJoin strategy: for each outer document, probe the index on the foreign field. The algorithm and per-document cost are the same. MongoDB 8.0 can use hash join for $lookup when allowDiskUse: true , no compatible index on the foreign field, the foreign collection is small, and the SBE engine is active. Under these conditions, MongoDB builds an in-memory hash table from the foreign collection, correctly handling array semantics by storing per-element entries. In tests with 5M portfolios and 5 fxRates, MongoDB's native HashJoin finished in ~14 seconds — the fastest of my tests. Without tweaks, it took 170 seconds — the worst. To achieve 14 seconds, I dropped the index on the foreign field, enabled allowDiskUse , and set internalQueryFrameworkControl to trySbeEngine . The default trySbeRestricted mode doesn't push the $lookup and $unwind to SBE, since the optimization depends on feature flags that aren't enabled in this mode. With trySbeEngine , SBE handles the pipeline, using HashJoin: // Setup for hash join db.adminCommand {setParameter: 1, internalQueryFrameworkControl: "trySbeEngine"} ; db.fxRates.dropIndex "currency 1" ; // The query same as all other tests db.portfolios.aggregate {$lookup: {from: "fxRates", localField: "currency", foreignField: "currency", as: "fx"}}, {$unwind: "$fx"}, {$project: {portfolioId: 1, valuation: 1, currency: 1, rate to usd: "$fx.rate to usd", valuation usd: {$multiply: "$valuation", "$fx.rate to usd" }}} , {allowDiskUse: true} .explain "executionStats" ; // Restore db.fxRates.createIndex {currency: 1}, {unique: true} ; db.adminCommand {setParameter: 1, internalQueryFrameworkControl: "trySbeRestricted"} ; DocumentDB for PostgreSQL doesn't currently implement this optimization — it relies on PostgreSQL's native join strategies, which don't understand BSON array semantics. Under normal conditions, both MongoDB and DocumentDB use a Nested Loop join. id as Join Key ~71s The documentDB extension has a special case https://github.com/documentdb/documentdb/blob/v0.113-0/pg documentdb/src/aggregation/bson aggregation nested pipeline.c L2134 when foreignField is id — it uses direct object id equality: // Reshape fxRates to use currency as id db.fxRates.drop ; currencies.forEach cur = { db.fxRates.insertOne { id: cur, rate to usd: Math.random 1.5 - 0.5 + 0.5, last updated: new Date } ; } ; db.portfolios.aggregate {$lookup: {from: "fxRates", localField: "currency", foreignField: " id", as: "fx"}}, {$unwind: "$fx"}, {$project: {portfolioId:1, valuation:1, currency:1, rate to usd:"$fx.rate to usd", valuation usd:{$multiply: "$valuation","$fx.rate to usd" }}} It uses an index scan with the join condition applied as an Index Cond , which is more efficient than a sequential scan with a Filter . It's slightly faster, taking 71 seconds instead of 88 seconds, yet it remains a nested loop with 5 million iterations: php Nested Loop actual time=17..48170 rows=5000000 loops=1 - Seq Scan on documents 11 collection rows=5000000 loops=1 - Index Scan using id on documents 12 rows=1 loops=5000000 Index Cond: object id = ANY bson dollar lookup extract filter array ... Execution Time: 70578 ms This is the same as MongoDB's IndexedLoopJoin — the id field is guaranteed to be scalar, so the extension can use a direct equality lookup on the primary key. However, it doesn't change the join strategy. $lookup + $filter ~68s A minor enhancement involves reading all fxRates at once, using an empty pipeline and no join condition, attaching the data as an array, and then filtering locally: db.portfolios.aggregate {$lookup: {from: "fxRates", pipeline: , as: "allFx"}}, {$addFields: { fx: {$arrayElemAt: {$filter: { input: "$allFx", as: "r", cond: {$eq: "$$r.currency", "$currency" } }}, 0 } }}, {$project: {portfolioId:1, valuation:1, currency:1, rate to usd:"$fx.rate to usd", valuation usd:{$multiply: "$valuation","$fx.rate to usd" }}} The execution plan shows a Nested Loop with a single loop: php Nested Loop actual time=17..20177 rows=5000000 loops=1 - Aggregate rows=1 loops=1 -- reads fxRates ONCE - Seq Scan on documents 11 rows=5000000 loops=1 Execution Time: 67905 ms of which ~48s is $addFields+$project The join itself is fast — fxRates are aggregated once into a single array. But the per-document $filter + $arrayElemAt evaluates BSON expressions 5 million times. We traded "nested loop probe" for "per-row array scan in BSON space". This is conceptually similar to the "nested loop with materialization" approach from the previous MongoDB article https://dev.to/franckpachot/nested-loop-and-hash-join-for-mongodb-lookup-259d — reading the lookup collection once, but matching per-document in the projection. $lookup — No Help Using $lookup with pipeline and let doesn't enhance performance: {$lookup: { from: "fxRates", let: { cur: "$currency" }, pipeline: {$match: {$expr: {$eq: "$currency", "$$cur" }}} , as: "fx" }}, {$unwind: "$fx"}, The extension still creates a LATERAL join all code paths set rightTree- lateral = true , and it introduces additional overhead due to variable resolution. With the MongoDB-compatible API, no solution significantly improves the efficiency of the join. But on DocumentDB, the power of SQL opens new possibilities. Since DocumentDB stores data in standard PostgreSQL tables, we can query the same collections with SQL—within the same transaction and with full ACID guarantees. The trade-off is that we lose flexible-document join semantics and assume scalar join keys. The bson type has a hash operator class bson hash ops used for GROUP BY and DISTINCT . But the = operator https://github.com/documentdb/documentdb/blob/v0.113-0/pg documentdb core/sql/operators/bson btree operators--0.10-0.sql doesn't declare hash join support — it's missing https://github.com/documentdb/documentdb/blob/v0.113-0/pg documentdb core/sql/schema/bson hash operator class--0.15-0.sql HASHES and MERGES properties. This is likely intentional, since bson = bson comparison on full documents has different semantics than field-level equality. But for my investigation comparing extracted scalar field values , it would work: -- Requires superuser — this is a hack, not a supported configuration -- If DocumentDB enables this in the future, it will be part of the extension ALTER OPERATOR documentdb core.= documentdb core.bson, documentdb core.bson SET COMMUTATOR = OPERATOR documentdb core.= , HASHES, MERGES ; Without this, PostgreSQL cannot execute hash join for bson = bson conditions, even in custom SQL. However, note that the SQL hash join method, enabled by this hack, does not replicate MongoDB's "any element matches" behavior when joined fields include arrays. To utilize a SQL join, I first query the two collections within two common table expressions in the WITH clause, then join them in the main query: WITH portfolios AS SELECT document FROM documentdb api.collection 'test', 'portfolios' , fxRates AS SELECT document FROM documentdb api.collection 'test', 'fxRates' SELECT documentdb api internal.bson dollar project documentdb api internal.bson dollar merge documents at path p.document, f.document, 'fx' , '{ "portfolioId" : 1, "valuation" : 1, "currency" : 1, "rate to usd" : "$fx.rate to usd", "valuation usd" : { "$multiply" : "$valuation", "$fx.rate to usd" } }'::bson, '{}'::bson FROM portfolios p JOIN fxRates f ON documentdb api catalog.bson expression get p.document, '{"": "$currency"}'::bson, true = documentdb api catalog.bson expression get f.document, '{"": "$currency"}'::bson, true ; With this query and the operator tweak enabling hash join, I have the following execution plan: Hash Join actual time=7.4..34018 rows=5000000 loops=1 Hash Cond: bson expression get documents 11.document, '{"":"$currency"}'... = bson expression get documents 10.document, '{"":"$currency"}'... - Seq Scan on documents 11 rows=5000000 loops=1 - Hash rows=5 loops=1 Buckets: 1024 Batches: 1 Memory Usage: 9kB - Seq Scan on documents 10 rows=5 loops=1 Execution Time: 38664 ms PostgreSQL creates a small 5-row hash table 9 kB and probes it once per portfolio. It makes a single pass over both collections. Most of the remaining time is spent calling bson expression get 5 million times to retrieve the join key, along with bson dollar merge documents at path and bson dollar project to generate the final output. In the end, this query is only about twice as fast. It requires a complex workaround, breaks document semantics, and still spends most of its time evaluating BSON expressions. Below is a summary of my experiments, run in Docker containers with default configurations, involving 5 million portfolios, 5 fxRates, and a unique index on fxRates.currency : | Approach | MongoDB | DocumentDB | Strategy | |---|---|---|---| $lookup localField/foreignField | ~170s | ~88s | Nested Loop lateral index/filter | $lookup with foreignField: " id" | ~155s | ~71s | Nested Loop index probe | Uncorrelated $lookup + $filter | ~22s | ~68s | Materialize once + per-doc filter | SQL CTE + Hash Join operator tweak | — | ~39s | Hash Join forced | HashJoin SBE, internal tweak | ~14s | — | Hash Join forced | MongoDB's native HashJoin via the Slot-Based Execution engine is fastest, handling hash table build/probe natively with per-element array support and avoiding BSON field extraction overhead, but will not be used without configuration tweaks. The DocumentDB SQL escape hatch uses PostgreSQL's optimizer for the same join strategy but incurs overhead with bson expression get on each row. The other solutions are compatible with standard configurations and use appropriate data models and query code. Remember that the time here reflects reading five million documents, and the difference may be insignificant on small datasets. These experiments show the trade-off clearly. Relational systems rely on joins due to normalization, but they can optimize them effectively thanks to typed scalar columns. Document databases avoid many joins, but when joins are needed, flexible semantics—like arrays—limit the available algorithms. DocumentDB for PostgreSQL sits in the middle. It relies on PostgreSQL storage and execution while preserving MongoDB semantics. As a result, $lookup uses only a subset of the join capabilities available in SQL to preserve this flexibility. The SQL workaround shows that performance improves when you enforce scalar semantics, but this runs counter to the expectations of a document model, where any field in one document can be an array in another. So the real question is not which system is faster, but which trade-off you choose: flexibility with embedded arrays or optimization for scalar values. This was tested on MongoDB 8.0 and DocumentDB 0.112 on PostgreSQL 17.10, and both can improve in the future. Optimization is possible when the field is a known scalar. But if you have a fixed schema, do you still want a document database or switch to SQL? PostgreSQL can also gain optimizations that benefit DocumentDB queries. For example, the lateral join could be memoized in a future version. If you're thinking about using DocumentDB for PostgreSQL https://documentdb.io/ — whether you're migrating from MongoDB or starting fresh — don't stop at the first slow query. Look into the causes, since the trade-off between speed and flexibility can differ. Check execution plans, and file an issue https://github.com/documentdb/documentdb/issues or start a discussion. More feedback from real workloads helps the contributors improve the extension. That's a major advantage of open source.