antipattern excessive lookups

Reduce Excessive $lookup Usage

Frequent $lookup operations on hot paths can indicate over-normalization. $lookup is useful, but repeated joins can be slower and more resource-intensive than querying a single collection, especially when supporting indexes or match selectivity are weak. If the same related fields are read together often, consider embedding or extended references.

Incorrect (constant $lookup for common operations):

// Every product page requires repeated joins across collections
db.products.aggregate([
  { $match: { _id: productId } },
  { $lookup: {
      from: "categories",          // Collection scan #2
      localField: "categoryId",
      foreignField: "_id",
      as: "category"
  }},
  { $lookup: {
      from: "brands",              // Collection scan #3
      localField: "brandId",
      foreignField: "_id",
      as: "brand"
  }},
  { $unwind: "$category" },
  { $unwind: "$brand" }
])
// Multiple join stages add planning/execution overhead on hot paths

Join cost depends on cardinality, stage order, index support, and result size. Measure before deciding to embed.

Correct (denormalize frequently-joined data):

Embed data that is always displayed alongside the product directly in the product document: include category fields (_id, name, path) and brand fields (_id, name, logo) as subdocuments. A single indexed query returns complete product data without $lookup. Listing queries (e.g. by category) also run against a single collection.

Managing denormalized data updates:

When category data changes (a rare event), use updateMany to update all products matching that category’s _id with the new field values. For frequently-changing data, keep both a reference ID (brandId) and a cache subdocument (brandCache) with a cachedAt timestamp; refresh the cache when it exceeds a staleness threshold.

When NOT to use this pattern:

• Data changes frequently and independently: If brand logos change daily, denormalization creates update overhead.
• Rarely-accessed data: Don't embed review details if only a small fraction of product views load reviews.
• Many-to-many with high cardinality: Avoid embedding large or fast-growing relationship sets.
• Analytics queries: Batch jobs can afford $lookup latency; real-time queries cannot.

Verify with

// Find pipelines with multiple $lookup stages
db.setProfilingLevel(1, { slowms: 50 }) // Disable afterwards
db.system.profile.find({
  "command.aggregate": { $exists: true },
  "command.pipeline.$lookup": {
    $exists: true
  }
}).sort({ millis: -1 })

// Check if $lookup foreign fields are indexed
db.reviews.aggregate([
  { $indexStats: {} }
])
// Look for index supporting the query in result

// Measure $lookup impact
db.products.aggregate([
  { $match: { category: "electronics" } },
  { $lookup: { from: "brands", localField: "brandId", foreignField: "_id", as: "brand" } }
]).explain("executionStats")
// Check totalDocsExamined in $lookup stage

Atlas Schema Suggestions flags: "Reduce $lookup operations"

Reference: Reduce Lookup Operations