MongoDB Mastery lessons / Module 13: Schema Design Patterns

Schema anti-patterns and tree structures

Level 3: Advanced26 min

The six schema anti-patterns from the MongoDB manual, how to detect each one on a running database, and the five ways to store a hierarchy: parent references, child references, an array of ancestors, materialized paths and nested sets.

This lesson is interactive. Run every example against an in-browser MongoDB, see explain() plans drawn as trees, and take the quiz.

Open the interactive lesson

You will learn to

Recognise unbounded arrays, too many collections, unnecessary indexes, bloated documents, separated data and case-insensitive regex
Measure document size, array length and index usage on a live collection
Choose a tree model from the queries you need
Query each tree model with an index

conceptThe concept

Anti-patterns are designs that work on day one and fail at scale:

Unbounded arrays. Comments, events or followers pushed into one document forever. The document grows towards 16 MB, every update rewrites a large document, and multikey index entries multiply.
Too many collections. One collection per customer or per day. Every collection and index is a separate WiredTiger file with its own handles and cache overhead; tens of thousands slow down startup, replication and backups.
Unnecessary indexes. Indexes that no query uses still cost memory and slow every write.
Bloated documents. Large documents where most queries need a few fields. The working set stops fitting in cache.
Separating data that is accessed together. A $lookup on every request to rebuild what could be one document.
Case-insensitive queries without a case-insensitive index. /^pune$/i cannot use an index efficiently; an index with a collation of strength 1 or 2 can.

Trees. There are five standard models: parent references (each node stores its parent), child references (each node stores an array of children), an array of ancestors (each node stores the path from the root as an array), a materialized path (the path as a string such as ,electronics,audio,), and nested sets (left and right numbers from a depth-first walk).

whyWhy it matters

Schema problems are the most expensive kind to fix, because the fix is a data migration. Knowing the anti-patterns lets you catch them in design review. Knowing how to detect them lets you find them in an existing system before they cause an incident. Hierarchies such as categories, org charts, folders and comment threads appear in almost every product, and the wrong model turns a simple "show the breadcrumb" into a recursive query on every page view.

Use caseReal-world scenario

A product catalogue stores categories with parent references. The home page needs every subcategory under "electronics" and every product page needs its breadcrumb. With parent references alone, both need $graphLookup on each request. Adding an ancestors array makes "all descendants of electronics" a single indexed equality query ({ ancestors: "electronics" }), and the breadcrumb is already in the document. Category renames are rare, so the extra update work is acceptable.

Try itExamples

Run and edit these in the interactive lesson.

Find the largest documents

db.orders.aggregate([
  { $project: { orderNo: 1, bytes: { $bsonSize: "$$ROOT" }, items: { $size: "$items" } } },
  { $sort: { bytes: -1 } },
  { $limit: 3 }
])

$bsonSize (4.4) measures each document. Run it with a $sample stage first on large collections.

Spot growing arrays

db.products.aggregate([
  { $project: { n: { $size: { $ifNull: ["$tags", []] } } } },
  { $bucket: { groupBy: "$n", boundaries: [0, 2, 4, 8, 1000], default: "other", output: { docs: { $sum: 1 } } } }
])

A long tail in the top bucket is the first sign of an unbounded array.

Unused indexes since the last restart

db.lab_ix.drop()
db.lab_ix.insertMany([{ a: 1, b: 1 }, { a: 2, b: 2 }]).acknowledged
db.lab_ix.createIndex({ a: 1 })
db.lab_ix.createIndex({ b: 1 })
db.lab_ix.find({ a: 1 }).toArray().length
db.lab_ix.aggregate([{ $indexStats: {} }, { $project: { name: 1, ops: "$accesses.ops" } }, { $sort: { name: 1 } }])

b_1 has 0 accesses. Check every member and a full business cycle before dropping; hide it first with hideIndex.

Parent references: walk the tree with $graphLookup

db.employees.aggregate([
  { $match: { _id: 2 } },
  { $graphLookup: { from: "employees", startWith: "$_id", connectFromField: "_id", connectToField: "managerId", as: "reports", depthField: "level" } },
  { $project: { name: 1, reports: { $size: "$reports" } } }
])

employees stores managerId on each person: the parent reference model. Every report below the VP is found recursively.

Array of ancestors: descendants and breadcrumbs without recursion

db.lab_cat.drop()
db.lab_cat.insertMany([
  { _id: "electronics", ancestors: [], parent: null },
  { _id: "audio", ancestors: ["electronics"], parent: "electronics" },
  { _id: "earbuds", ancestors: ["electronics", "audio"], parent: "audio" },
  { _id: "speakers", ancestors: ["electronics", "audio"], parent: "audio" },
  { _id: "laptops", ancestors: ["electronics"], parent: "electronics" },
  { _id: "books", ancestors: [], parent: null }
]).acknowledged
db.lab_cat.createIndex({ ancestors: 1 })
db.lab_cat.find({ ancestors: "electronics" }, { _id: 1 }).sort({ _id: 1 }).toArray()
db.lab_cat.findOne({ _id: "earbuds" }).ancestors

One multikey index answers "everything under electronics", and the breadcrumb is stored in the node.

Materialized paths: an anchored prefix uses the index

db.lab_path.drop()
db.lab_path.insertMany([
  { _id: "earbuds", path: ",electronics,audio," },
  { _id: "speakers", path: ",electronics,audio," },
  { _id: "laptops", path: ",electronics," },
  { _id: "novels", path: ",books," }
]).acknowledged
db.lab_path.createIndex({ path: 1 })
db.lab_path.find({ path: /^,electronics,/ }, { _id: 1 }).sort({ _id: 1 }).toArray()
db.lab_path.find({ path: /^,electronics,/ }).explain("executionStats").queryPlanner.winningPlan.inputStage.stage

The leading comma and the anchor make the prefix exact. A regex without ^ would scan every key.

Nested sets: descendants as one range

db.lab_ns.drop()
db.lab_ns.insertMany([
  { _id: "electronics", lft: 1, rgt: 10 },
  { _id: "audio", lft: 2, rgt: 7 },
  { _id: "earbuds", lft: 3, rgt: 4 },
  { _id: "speakers", lft: 5, rgt: 6 },
  { _id: "laptops", lft: 8, rgt: 9 }
]).acknowledged
const audio = db.lab_ns.findOne({ _id: "audio" })
db.lab_ns.find({ lft: { $gt: audio.lft }, rgt: { $lt: audio.rgt } }, { _id: 1 }).sort({ lft: 1 }).toArray()

Reads are a single range; inserting a node means renumbering every node to its right, so use this only for trees that rarely change.

Case-insensitive lookups: collation index instead of /i

db.lab_ci.drop()
db.lab_ci.insertMany([{ city: "Pune" }, { city: "PUNE" }, { city: "Delhi" }]).acknowledged
db.lab_ci.createIndex({ city: 1 }, { collation: { locale: "en", strength: 2 } })
db.lab_ci.find({ city: "pune" }).collation({ locale: "en", strength: 2 }).toArray().length

The query must use the same collation as the index for the planner to choose it.

The outlier pattern instead of an unbounded array

// Product with a bounded array of recent reviews
{ _id: "P001", name: "Wireless Earbuds", recentReviews: [ /* last 10 */ ], reviewCount: 18412, hasOverflow: true }

// The rest live in their own collection, indexed for the product page
db.reviews.createIndex({ productId: 1, createdAt: -1 })
db.reviews.find({ productId: "P001" }).sort({ createdAt: -1 }).skip(10).limit(20)

// Keep the embedded array bounded on every write
db.products.updateOne({ _id: "P001" }, { $push: { recentReviews: { $each: [newReview], $sort: { createdAt: -1 }, $slice: 10 } }, $inc: { reviewCount: 1 } })

internalsUnder the hood

Arrays are rewritten as a whole when the document changes, and each element of an indexed array is a separate index key. A document with 50,000 array elements and two multikey indexes means 100,000 index entries to maintain on that document's writes.

A materialized path query with an anchored, case-sensitive prefix regex (/^,electronics,/) can use an index as a range scan. An unanchored or case-insensitive regex has to examine every key. Nested sets make "all descendants" a range query on one number, but inserting a node renumbers everything to its right, so they suit trees that rarely change.

Avoid / preferDo this, not that

Avoid

db.posts.updateOne({ _id: postId }, { $push: { comments: comment } })

Prefer

db.comments.insertOne({ postId, ...comment })
db.posts.updateOne({ _id: postId }, { $push: { recentComments: { $each: [comment], $slice: -5 } }, $inc: { commentCount: 1 } })

Comments grow without limit. Keep them in their own collection and embed only a bounded preview with $slice.

Avoid

db.customers.find({ "address.city": /^pune$/i })

Prefer

db.customers.createIndex({ "address.city": 1 }, { collation: { locale: "en", strength: 2 } })
db.customers.find({ "address.city": "pune" }).collation({ locale: "en", strength: 2 })

A case-insensitive regex examines every index key. A collation index of strength 2 compares without case and is used as a normal index.

PitfallsCommon mistakes

Pushing every event, comment or follower into a single document with no bound.
Creating one collection per tenant or per day and ending up with tens of thousands of files.
Keeping indexes "just in case" without checking $indexStats on every member.
Using a case-insensitive regex for lookups instead of a collation index.
Choosing nested sets for a tree that changes often.

LimitsWhen not to use it

Anti-patterns are guidelines, not laws. A few hundred collections is fine. An array that is bounded by the business (the five addresses a customer may store) is not unbounded. And a $lookup on a rarely-run report is cheaper than duplicating data everywhere. Fix the patterns that show up in your measurements.

SecuritySecurity notes

Collection-per-tenant designs are sometimes chosen for isolation, but MongoDB privileges can already be scoped to a collection, and a shared collection with a tenant field needs every query filtered by it. If tenants need hard isolation, use separate databases with their own users, or separate clusters, not thousands of collections under one account.

ChallengeTry it yourself

employees uses parent references (managerId). Return the management chain of the employee with _id: 14 as an array of names, from the direct manager up to the CEO.

Show one solution

One solution

db.employees.aggregate([
  { $match: { _id: 14 } },
  { $graphLookup: { from: "employees", startWith: "$managerId", connectFromField: "managerId", connectToField: "_id", as: "chain", depthField: "d" } },
  { $unwind: "$chain" },
  { $sort: { "chain.d": 1 } },
  { $group: { _id: "$_id", chain: { $push: "$chain.name" } } }
]).toArray()[0].chain

InterviewQuestions you should be able to answer

How do you find schema anti-patterns in an existing MongoDB deployment?

Measure, then read the code. Use $bsonSize with $sample for document size distribution, $size buckets for array growth, $indexStats on every member for unused indexes, listCollections counts per database for collection sprawl, and the slow query log and profiler for case-insensitive regexes and $lookup-heavy pipelines. Then check the write paths that push to arrays without $slice.

Which tree model would you use for product categories, and why?

It depends on the queries. For a catalogue that needs "all products under a category" and breadcrumbs on every page, I would store parent references plus an array of ancestors: one indexed equality query for descendants, the breadcrumb is in the node, and renames are rare so the extra write work is cheap. If arbitrary depth traversal at query time is fine and writes are frequent, parent references with $graphLookup are simpler. Materialized paths suit prefix queries and sorting by path; nested sets only suit trees that almost never change.

QuizCheck your understanding

5 questions with instant feedback in the interactive lesson; a wrong answer goes into your review deck.

← GridFS and storing large files Indexes: COLLSCAN vs IXSCAN →