Indexes and schema design patterns

Compound index ordering with ESR, the index types MongoDB offers, and the patterns that keep documents bounded as data grows.

Choosing an index

A compound index is a sorted structure over several fields in a fixed order, and like any B-tree it is usable only from the left. Order the fields with the ESR rule: Equality fields first, then Sort fields, then Range fields.

// equality: status + customer.id, sort: placedAt, range: total
db.orders.createIndex({ status: 1, "customer.id": 1, placedAt: -1, total: 1 });

// multikey: automatically used for array fields
db.orders.createIndex({ tags: 1 });

// unique, partial and TTL variants
db.users.createIndex({ email: 1 }, { unique: true });
db.orders.createIndex({ placedAt: 1 }, { expireAfterSeconds: 60 * 60 * 24 * 365 });
db.orders.createIndex({ status: 1, placedAt: -1 },
                      { partialFilterExpression: { status: "queued" } });

db.orders.getIndexes();
db.orders.find({ status: "paid" }).explain("executionStats");

Index	Use
Single field	One field, many reads — the simplest case
Compound	Multi-field predicates; covers the left prefix
Multikey	Array fields; created automatically when the field is an array
Text	Word matching with `$text`; one per collection
Wildcard	Unknown or varying field names in a subdocument
TTL	Delete documents after a date or a number of seconds
Partial	Index only the subset your query touches — smaller and faster
2dsphere	Geospatial queries with `$near` and `$geoWithin`

What indexes cost

Every index is maintained on write. Ten indexes make an insert roughly ten times more index work.
One compound index with the fields in the right order usually beats several single-field indexes; MongoDB's index intersection is a fallback, not a strategy.
A compound index may contain only one array field — two arrays in the same index produces a cannot index parallel arrays error.
A covered query is answered entirely from the index; add the projected fields to the end of the index to make it happen.
Indexes live in RAM as much as possible. db.collection.stats() and $indexStats show size and usage counts — drop indexes with zero accesses.

💡

db.orders.aggregate([{ $indexStats: {} }]) reports how many times each index has actually been used since the last restart. Deleting the never-used ones is usually the cheapest performance win available.

Schema patterns

Pattern	Problem it solves
Embedded document	One-to-few: read the child with the parent every time
Extended reference	Copy the two or three fields you always need instead of joining for a name
Bucket	Unbounded time-series growth: group readings into documents holding 100 values
Computed	Expensive aggregates stored and refreshed on write instead of recomputed per read
Subset	Keep the recent or hot items in the parent, the rest in another collection
Outlier	A few documents with thousands of children that would blow the 16 MB limit
Schema versioning	Old documents missing new fields; migrate lazily with a `schemaVersion` key

// bucket pattern: one document per hour instead of one per reading
db.readings.updateOne(
  { sensorId: "s-12", hour: ISODate("2026-09-18T09:00:00Z") },
  {
    $push: { values: { t: new Date(), v: 21.4 } },
    $inc: { count: 1, sum: 21.4 },
    $min: { minV: 21.4 },
    $max: { maxV: 21.4 },
    $setOnInsert: { sensorId: "s-12", hour: ISODate("2026-09-18T09:00:00Z") }
  },
  { upsert: true }
);

Design around the queries your application issues, not around an entity diagram inherited from a relational model.
Bounded arrays can be embedded; anything that grows with time needs a bucket, a separate collection, or the outlier pattern.
The hard limit is 16 MB per document, and a document that grows without limit will reach it.
Use $jsonSchema validation plus a schemaVersion field to make changes to the shape explicit and migratable.

FAQ

Should I keep the document small?

Yes — smaller documents mean more of the working set fits in RAM and fewer cache misses. Watch for arrays that grow forever and for fields you never read being copied into every document.

How do I pick a shard key?

It needs high cardinality, even distribution, and it should appear in most of your queries so they can be routed to a single shard. A monotonically increasing key concentrates all writes on the last chunk.

Documents, collections and queries The aggregation pipeline

Last refreshed 2026-09-18.

Indexes and schema design patterns

Choosing an index

What indexes cost

Schema patterns

FAQ

Related