MongoDB Performance: Multiple find() Queries vs Aggregation with $unionWith

Working with Data
, ,
1

I need to fetch data from multiple collections, and each collection contains millions of documents. I’m evaluating two different approaches to retrieve this data:

Context:

  • Each collection has millions of documents.
  • Proper indexes are in place for the queried fields.
    *$unionWith provides a unified result, but I’m concerned about its efficiency under load.
  • The use case involves APIs that may be hit tens of thousands of times per day.

Option A: Multiple find() Queries

const result1 = await db.collection("CollectionA").find(queryA).toArray();
const result2 = await db.collection("CollectionB").find(queryB).toArray();
const result3 = await db.collection("CollectionC").find(queryC).toArray();

These queries are executed either sequentially or in parallel using Promise.all.

Option B: Single Aggregation with $unionWith

db.CollectionA.aggregate([
  { $match: { ...queryA } },
  { $addFields: { source: "A" } },
  {
    $unionWith: {
      coll: "CollectionB",
      pipeline: [
        { $match: { ...queryB } },
        { $addFields: { source: "B" } }
      ]
    }
  },
  {
    $unionWith: {
      coll: "CollectionC",
      pipeline: [
        { $match: { ...queryC } },
        { $addFields: { source: "C" } }
      ]
    }
  },
]);

I want to understand:

  • Which option is faster?
  • Which is more performant overall (in terms of resource usage like memory and CPU)?
  • Which is more suitable for high-throughput APIs or large-scale production systems?

Any recommendations or best practices would be appreciated.

2

I am not aware of any comparison study between those 2 approaches. And most likely one is better in some cases and the other best in other cases.

It is hard to tell without testing with real or made-up data, traffic and site characteristics.

I do not know about best practices but I would strongly recommend you to test both approaches with the data you already have, the traffic you already and the exact queries you already have.

And please share your finding.

1 Like
3

If you were able to test what is best, it would be nice if you could share the result with us.