In a previous post, 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—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).
