antipattern-excessive-lookups.md

title	Reduce Excessive $lookup Usage
impact	CRITICAL
impactDescription	Can reduce query cost on hot paths by avoiding repeated cross-collection joins
tags	schema, lookup, anti-pattern, joins, denormalization, atlas-suggestion

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 $lookup stages

For Atlas M10+ use $queryStats. See Query Stats Use codebase if available, ask the user.

// Check if $lookup foreign fields are indexed

// Example A - $indexStats
db.categories.aggregate([
  { $indexStats: {} }
])

// Example B - getIndexes()
db.categories.getIndexes()

// Look for index supporting the query (either a direct index on the foreign field or a compound index that has the foreign field as a prefix, note the collation)

// 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