MongoDB schema design and indexes that earn their keep

MongoDB has no schema, which does not mean your data has no shape. It means the shape lives in your application and in the indexes rather than in the server, and that you find out you chose badly in production rather than at migration time. Two decisions cover most of it: embed data you always read together and reference data you read separately, and build one compound index per query shape with the fields ordered equality, sort, range.

Everything below is the reasoning behind those two sentences, plus how to check your work with explain() and the profiler. The examples use a shop: customers, orders, products. If you have not decided between document and relational storage yet, Postgres or MongoDB is the shorter argument; this post assumes the decision is made.

Embedding or referencing#

The question is not which is correct. It is which of the two costs you are willing to pay.

Embedding puts related data inside the parent document. One read gets everything, there is no join, and the write is atomic because a single document update is atomic in MongoDB regardless of how deep the change is. The cost is duplication and growth: the same embedded product name lives in ten thousand orders, so renaming a product means touching ten thousand documents, and a document that grows over time eventually has to be moved on disk.

Referencing stores an identifier and fetches the other document separately, or with $lookup in an aggregation. Nothing is duplicated, updates happen in one place, and the parent document stays small. The cost is a second round trip, or a $lookup that has to be indexed properly to be anything other than slow.

The rules of thumb that survive contact with real applications:

• One to few, read together, changes rarely: embed. Addresses on a customer. Line items on an order. The order's line items are a historical record anyway - if the product price changes, the order should not.
• One to many, where "many" has no ceiling: reference. Comments on a post, events for a user, messages in a channel. The child document holds the parent's _id and is indexed on it.
• Many to many: reference, from whichever side you query. Store an array of tag identifiers on the article if you ask "which tags does this article have", and index the array so the reverse question is answerable too.
• Needed in a list view: duplicate the two or three fields the list shows. An order that stores productName and unitPrice alongside productId renders a basket without touching products at all. This is the extended reference pattern, and the duplication is deliberate.

The failure mode to watch for is the unbounded array. A user document with an events array that grows every time the user does anything is a document that works beautifully for three months and then hits a wall. If you cannot state a maximum for an array, it belongs in its own collection.

The limits that decide the shape#

Sixteen megabytes sounds enormous until an array grows without a bound. The practical threshold is much lower: a document you update frequently should stay small, because every update rewrites it, and a 4 MB document rewritten on every append is a great deal of disk and cache churn for one changed field. Treat a few hundred kilobytes as the point where you should already have moved the array out.

If a single value genuinely needs to be larger than 16 MB, that is what GridFS is for - it splits a file into chunks across two collections. For most applications the better answer is to keep the file in object storage and the reference in MongoDB.

Patterns worth knowing#

Four named patterns cover most of the cases where the simple answer does not work.

Subset. Keep the most recent or most relevant few in the parent, and the rest in another collection. A product holds its ten most recent reviews for the product page; reviews holds all of them for the "see all" page. You accept writing in two places for a single-document read on the page everyone loads.

Bucket. Group many small records into one document by time or by key. A thousand sensor readings a minute stored as one document per minute rather than one per reading turns a million documents a day into 1,440, with dramatically smaller indexes. MongoDB 5.0 and later has time series collections that do this for you, which is the better option when it fits.

Computed. Store the answer, not the inputs, when the answer is read far more often than it changes. A running orderCount and totalSpent on a customer beats an aggregation over every order on every page load. Update it in the same operation that creates the order with $inc.

Schema version. Put a schemaVersion field on every document from day one. Migrating a large collection in place is disruptive; migrating lazily, where the application handles both shapes and rewrites a document when it touches one, is not. Migrations without downtime is the general form of that idea.

You can also make the server enforce a shape, which is worth doing once the schema has stopped moving weekly:

db.runCommand({  collMod: "orders",  validator: { $jsonSchema: {    bsonType: "object",    required: ["customerId", "status", "createdAt"],    properties: {      status: { enum: ["pending", "paid", "shipped", "cancelled"] },      createdAt: { bsonType: "date" }    }  }},  validationLevel: "moderate",  validationAction: "warn"})

Start with validationAction: "warn", which logs violations instead of rejecting writes, and read the log for a week before switching to error.

How MongoDB indexes work#

Every collection has an index on _id. It is unique, it cannot be dropped, and it is the reason a lookup by identifier is always fast. Every other index you create yourself, and every one of them is a B-tree that the server has to update on every write that touches its fields.

Direction on a single-field index does not matter: the server can walk a B-tree either way. Direction on a compound index matters only when you sort on more than one of its fields in mixed directions.

Two behaviours are specific to MongoDB and worth internalising. An index on an array field is a multikey index: it stores one index entry per array element, so a document with fifty tags contributes fifty entries. That is fine, and it is why you can query inside arrays at all, but it is also why a compound index may contain at most one array field. And a partial index with partialFilterExpression: { status: "pending" } indexes only the pending orders, which on a table where 99% of rows are shipped is a tenth of the size and a tenth of the write cost. Prefer partial indexes to sparse ones; they are strictly more capable.

A TTL index is the cheapest way to expire sessions, tokens and logs. The background thread that removes expired documents runs about once a minute, so deletion is approximate, and the field has to be a BSON date rather than a number.

Compound indexes and the ESR rule#

One compound index serves a whole family of queries because of the prefix rule: an index on { a: 1, b: 1, c: 1 } can serve queries on a, on a and b, and on a, b and c, but not on b alone. Order the fields correctly and three indexes become one.

The ordering rule is equality, sort, range:

1. Equality fields first - the ones matched with an exact value. They narrow the scan to a contiguous run of index keys.
2. Sort fields next. If the sort follows the equality fields in the index, the results come out already ordered and there is no separate sort step.
3. Range fields last - $gt, $lt, $in on a span, $regex with a prefix. A range in the middle of an index breaks the ordering of everything after it.

For db.orders.find({ status: "paid", total: { $gt: 100 } }).sort({ createdAt: -1 }) the index is:

db.orders.createIndex({ status: 1, createdAt: -1, total: 1 })

Equality on status, sort on createdAt, range on total. Putting total before createdAt looks more natural and produces a plan with a blocking SORT stage, which is the difference between one millisecond and several hundred.

A covered query is the next step up: if every field the query needs is in the index, the server never touches the documents at all. Because _id is returned by default and is usually not in your index, covering requires projecting it away: .find({ status: "paid" }, { _id: 0, status: 1, createdAt: 1 }). You will see totalDocsExamined: 0 when it works.

Do not index everything. Each index is written on every relevant insert and update, consumes cache that your documents would otherwise use, and has to be rebuilt on a restore. Three good compound indexes beat twelve single-field ones, and MongoDB will rarely combine two indexes for one query even though it technically can.

Reading explain()#

db.orders.find({ customerId: 4821, createdAt: { $gte: since } })         .sort({ createdAt: -1 }).limit(20)         .explain("executionStats")

Before the index:

nReturned: 20executionTimeMillis: 412totalKeysExamined: 0totalDocsExamined: 2400000stage: SORT  sortPattern: { createdAt: -1 }  inputStage: COLLSCAN    filter: { customerId: { $eq: 4821 } ... }

After createIndex({ customerId: 1, createdAt: -1 }):

nReturned: 20executionTimeMillis: 1totalKeysExamined: 20totalDocsExamined: 20stage: LIMIT  inputStage: FETCH    inputStage: IXSCAN      indexName: customerId_1_createdAt_-1

Four numbers tell you almost everything. nReturned is what the query produced. totalKeysExamined is index entries read. totalDocsExamined is documents fetched. You want all three to be roughly equal. Keys far above returned means the index is not selective enough; documents far above keys means the server is fetching documents to apply a filter that should have been in the index; a COLLSCAN with millions of documents examined means there is no usable index at all.

The stage names that matter: COLLSCAN reads the collection, IXSCAN walks an index, FETCH loads documents by their index entries, SORT is a blocking sort in memory, and PROJECTION_COVERED means nothing was fetched. A SORT stage that exceeds the server's sort memory limit fails outright with Sort exceeded memory limit; the limit has been 32 MB and 100 MB depending on version, and the error prints the exact figure. The fix is an index that provides the order, not a bigger limit.

explain("executionStats") runs the query. explain() on its own uses queryPlanner verbosity and only plans it, which is the safe option against production. Aggregations explain too, with db.orders.explain("executionStats").aggregate([...]), and pipeline stages after the first $group or $sort cannot use an index, so put $match first and index what it matches.

Finding slow queries with the profiler#

MongoDB logs any operation slower than slowms, which defaults to 100 milliseconds, to the server log whether or not profiling is on. That log is the first place to look. For something queryable, turn on the profiler, which is per database:

db.setProfilingLevel(1, { slowms: 50 })     // 1 = slow ops only, 2 = everythingdb.system.profile.find().sort({ ts: -1 }).limit(5).pretty()db.setProfilingLevel(0)

system.profile is a capped collection, small by default, so it holds a recent window rather than a history. Level 2 records every operation and is a development tool, not something to leave on.

To find which indexes are worth keeping, ask the server how often each one has been used since the last restart:

db.orders.aggregate([{ $indexStats: {} }])

An index with accesses.ops at zero after a month of uptime is costing you writes and cache for nothing. Check db.orders.stats().indexSizes alongside it to see what it costs. And db.currentOp() shows what is running right now, with db.killOp(opid) for the aggregation somebody started by hand against production.

Building and dropping indexes safely#

From MongoDB 4.2 onwards, index builds take an exclusive lock only briefly at the start and end, and read and write normally in between, so the old background: true option no longer means anything. That does not make a build free: it reads the whole collection and uses CPU and memory throughout. Build during a quiet hour and watch db.currentOp() for progress.

Dropping an index is where people get nervous, and MongoDB 4.4 added the right tool:

db.orders.hideIndex("status_1_total_1")   // invisible to the planner, still maintaineddb.orders.unhideIndex("status_1_total_1") // instant undodb.orders.dropIndex("status_1_total_1")   // once you are sure

Hide it, watch your latency for a day, and drop it if nothing changed. Unhiding is instant; rebuilding a dropped index on a large collection is not.

One note for restores: indexes are rebuilt when a dump is restored, which is often the slowest part of the operation. mongodump and mongorestore covers the flags that control it.

Memory, the working set and the WiredTiger cache#

MongoDB is fast when the working set - the indexes plus the documents you actually touch - fits in memory, and it degrades sharply when it does not, because every miss becomes a disk read. The storage engine reserves a cache of max(50% of (RAM - 1 GB), 256 MB):

The rest of the memory is for connections, the aggregation framework, index builds and the operating system's own page cache, which MongoDB also benefits from. Set storage.wiredTiger.engineConfig.cacheSizeGB explicitly if you want certainty about the figure rather than a calculation from whatever the process believes the machine has.

Size by index, not by data. A 40 GB collection queried only by _id is comfortable on a small server; a 4 GB collection with six indexes and analytical queries is not. db.stats().indexSize gives you the number that matters, and if it is larger than the cache you should expect disk reads on ordinary queries.

Running out is not graceful. On RE:NODE, a container that reaches its memory limit is stopped by the kernel and restarts clean rather than being left to swap, which for a database means dropped connections and a cold cache rather than a machine that slowly stops responding. That is the better failure, but it is still a failure, so leave headroom, keep the pool sizes in your application honest - connection pools and limits has the arithmetic - and move up a tier before the cache is full rather than after. The panel's memory graph against the limit is the number to watch.

FAQ#

Should I embed or reference?

Embed when the child data is read with the parent, belongs only to the parent, and has a known maximum size. Reference when it is queried on its own, shared between parents, or unbounded. When both are defensible, embed - one read is cheaper than two, and you can always move the array out later.

How many indexes is too many?

There is no single number, but if a collection has more indexes than query shapes, some of them are unused. Run $indexStats, find the ones with zero accesses since the last restart, hide them for a week and drop them. Writes get faster and restores get shorter.

Why is my query slow even though I created an index?

Usually field order. An index on { createdAt: -1, status: 1 } cannot efficiently serve a query filtering on status and sorting on createdAt, because the equality field is not the prefix. Run explain("executionStats") and compare totalDocsExamined with nReturned; if the gap is large, the index is not the one the query needs.

Does MongoDB need a schema?

The server does not enforce one unless you add a JSON Schema validator, but your application has one whether you wrote it down or not. Add the validator once the shape settles, starting in warn mode, and add a schemaVersion field so that changing your mind later is a background job rather than an outage.

What does the 16 MB document limit mean for me?

That any array inside a document needs a ceiling you can state. Well before 16 MB, large documents are expensive to update because the whole document is rewritten. Move anything that grows indefinitely into its own collection with an index on the parent identifier.

How much RAM does my MongoDB server need?

Enough for your indexes plus the documents you read regularly. Check db.stats().indexSize per database, add the portion of the data that is actually hot, and compare it with the cache figures in the table above. If the indexes alone are larger than the cache, the next tier is cheaper than the latency you are paying.