Part 5 · 2 chapters · ~12 min
MongoDB Querying and Indexing
The query planner, plan cache and explain, index types (single, compound, multikey, text, geospatial, hashed, wildcard), the ESR rule, covered queries, the aggregation pipeline and $lookup's real cost, partial and TTL indexes, embedding versus referencing, the 16 MB limit and the bucket pattern, and the schema design pattern catalogue.
8
Indexes and the ESR rule
code
db.transfers.createIndex({ owner_id: 1, status: 1, created_at: -1, amount: 1 })
db.transfers.find({ owner_id: 7, status: "pending", amount: { $gt: 1000 } }).sort({ created_at: -1 }).explain("executionStats")
db.sessions.createIndex({ created_at: 1 }, { expireAfterSeconds: 86400 }) // TTL index
db.transfers.createIndex({ status: 1 }, { partialFilterExpression: { status: "pending" } }) // partial index
// aggregation pipeline: daily volume per merchant
db.transfers.aggregate([
{ $match: { created_at: { $gte: ISODate("2026-10-01") } } }, // match early: uses indexes
{ $group: { _id: { m: "$merchant_id", d: { $dateTrunc: { date: "$created_at", unit: "day" } } }, total: { $sum: "$amount" } } },
{ $sort: { total: -1 } }, { $limit: 20 } ])THE ESR RULE FOR COMPOUND INDEXES
Equality, then Sort, then Range
swipe the figure sideways, or tap expand for full screen
1/4
equality first
Fields matched by equality go first: they narrow the index to one contiguous range.
equality fields firstnarrow to one range
9
Schema design: embed or reference
| pattern | use when |
|---|---|
| embed | data is read together and bounded (an account's limits, a KYC submission's documents metadata) |
| reference | unbounded or shared data (transactions of an account: never an ever-growing array) |
| bucket | time series: one document per account per day with an array of readings (bounded growth) |
| computed | store running totals updated on write instead of aggregating on every read |
| subset | embed the 10 most recent items, reference the rest |
| outlier | handle the rare huge case (a merchant with millions of items) separately |
Documents are limited to 16 MB; unbounded arrays are the most common MongoDB modelling mistake. $lookup (a join) exists but runs per document; if you need many joins, the data is relational.