Indexing, performance, and scaling

ESR

For a compound index, order the fields: Equality, Sort, Range.

db.orders.find({
  tenantId: t,              // equality
  status: "paid",           // equality
  createdAt: { $gte: cut }  // range
}).sort({ updatedAt: -1 })  // sort

// index
{ tenantId: 1, status: 1, updatedAt: -1, createdAt: 1 }
//  E           E          S              R

Why the order works:

• Equality fields narrow the scan to a contiguous key range immediately.
• Sort fields placed next mean the index is already in the requested order,

so no in-memory sort stage.

• Range fields last, because a range produces a variable-length scan and

anything after it is no longer usefully ordered.

Getting it wrong rarely produces an error. It produces an index that is used for filtering and then a SORT stage in the plan, which fails outright once it exceeds 32MB. That failure arrives with data growth, not at deploy, which is why it tends to happen at the worst time.

One well-ordered compound index almost always beats several single-field indexes that MongoDB has to intersect.

The prefix rule

A compound index serves any prefix of its fields. {a, b, c} serves queries on {a}, {a, b}, and {a, b, c}, but not {b} or {b, c}.

So order fields from most to least commonly filtered, and check whether an existing index already covers a new query before adding another. Most collections have fewer distinct access patterns than they have indexes.

Reading explain()

The only way to know, rather than believe, that an index is used:

db.orders.find({ tenantId: t, status: "paid" })
         .sort({ createdAt: -1 })
         .explain("executionStats")

Three numbers matter:

Field	What it means	Want
nReturned	documents returned	your page size
totalKeysExamined	index entries scanned	close to nReturned
totalDocsExamined	documents read	close to nReturned, or 0 if covered

The ratio is the signal. totalDocsExamined of 200,000 for nReturned of 20 is a missing or misordered index, whatever the wall-clock time says on a small dataset.

Also check the winning plan's stage. COLLSCAN means no index. SORT means an in-memory sort, which is the 32MB time bomb above.

Covered queries

If every field the query needs is in the index, MongoDB answers from the index alone and never touches a document. Enormously faster.

db.vehicles.createIndex({ tenantId: 1, status: 1, make: 1, priceMinor: 1 })
db.vehicles.find({ tenantId: t, status: "listed" },
                 { _id: 0, make: 1, priceMinor: 1 })   // covered

Note _id: 0. _id is returned by default and, unless it is in the index, is what stops the query being covered. This is the most common reason a query that should be covered is not.

Worth chasing for hot list endpoints. Not worth contorting the schema for elsewhere.

Index hygiene

Indexes are not free:

• Every insert and update writes every affected index.
• Indexes compete for RAM with documents. When the working set stops fitting,

performance falls off a cliff rather than degrading smoothly.

• Each index adds to the per-database ceiling, which is what makes

database-per-tenant stop scaling.

So audit them. $indexStats shows access counts since the last restart:

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

An index with near-zero accesses.ops after a representative period is a write tax with no reader. Drop it, in staging first, and watch.

Rules that keep the set small:

• Declare indexes in code, versioned with the app. An index created by hand

in a production shell exists on one cluster, is absent in staging, and vanishes on restore.

• Comment every index with the query it serves. Without that, nobody dares

remove any of them later.

• Build with background semantics on live systems, and prefer a rolling

build on replica sets for very large collections.

Specialised indexes

Partial indexes only cover documents matching a filter. Ideal when queries always target a subset:

db.orders.createIndex(
  { tenantId: 1, createdAt: -1 },
  { partialFilterExpression: { status: "pending" } }
)

Smaller, cheaper to maintain, and often exactly the hot path. The query must include the filter expression for the index to be eligible.

Sparse indexes skip documents missing the field. Useful for optional unique identifiers, so many documents without it do not collide on null.

TTL indexes expire documents automatically. The correct way to handle sessions, password reset tokens, ephemeral events:

db.refresh_tokens.createIndex({ expiresAt: 1 }, { expireAfterSeconds: 0 })

Deletion runs on a background sweep roughly once a minute, so it is eventual, not exact. Do not rely on TTL for a security boundary; check expiry in code too.

Case-insensitive lookups want a collation-backed index rather than a regex. find({ email: /^x@y\.com$/i }) cannot use a plain index efficiently.

Connection and driver settings

Defaults that should be set explicitly in every service:

AsyncIOMotorClient(
    url,
    maxPoolSize=100,               # per process. Size against DB connection limits.
    minPoolSize=0,
    serverSelectionTimeoutMS=5000, # fail fast instead of hanging on an outage
    connectTimeoutMS=5000,
    socketTimeoutMS=30000,
    retryWrites=True,              # default on modern drivers, be explicit
    tz_aware=True,
)

Two things that bite:

• Create the client inside the running event loop. An async client built at

import time can bind to the wrong loop and produce errors that look like network faults.

• Pool size is per process. Multiply by replicas and workers before

comparing against the cluster's connection limit. This is a common way to exhaust a small Atlas tier during a rollout.

Set maxTimeMS per operation on anything user-facing, so a pathological query is killed rather than occupying a connection for minutes.

Read and write concerns

Defaults are usually right; know them anyway.

• w: "majority" for anything that must survive a primary failover. The default

on modern deployments, and worth being explicit about for money and orders.

• readConcern: "majority" to avoid reading data that could be rolled back.
• Reading from secondaries trades consistency for capacity. Fine for

analytics and reports, wrong for read-after-write in a UI flow, where a user updates something and immediately sees the old value.

Sharding

Do not shard early. A replica set on properly sized hardware handles far more than most products ever need, and sharding adds routing, balancing, and a class of operational problems that are hard to reverse. Shard when a single node's working set no longer fits in RAM or write throughput saturates the primary, and not before.

When the time comes, the shard key is the decision:

• High cardinality. Enough distinct values to split into many chunks.
• Even frequency. No single value taking a large share of documents.
• Non-monotonic. This is the one people get wrong. A timestamp or an

ObjectId always increases, so every insert lands in the highest chunk and one shard takes all the writes. You buy a cluster and get a single node.

• Present in common queries. A query without the shard key scatters to every

shard and gathers, which is slower than not sharding.

For monotonic fields use hashed sharding, which distributes writes evenly at the cost of range queries on that field.

For multi-tenant data, a compound key starting with the tenant keeps a tenant's documents together and keeps chunks splittable:

sh.shardCollection("app.orders", { tenantId: 1, _id: 1 })

Test the candidate key against real data before committing: check cardinality and the distribution of the top values. Changing a shard key later means resharding, which is possible on modern versions and still a project.

Aggregation

• Put $match and $limit as early as possible so the pipeline works on the

smallest set. A $match first can use an index; a $match after a $group cannot.

• $project away fields before expensive stages to reduce memory.
• $lookup is a nested loop join. Acceptable on a small driving set, ruinous on

a large one. If a hot read needs $lookup, that is usually a signal to denormalize with the extended reference pattern.

• Stages have a 100MB memory limit; allowDiskUse lifts it and is a warning

sign on an interactive query path.

• $facet is convenient for "list plus total count" but runs each branch

independently. On large collections an approximate count is often the better product decision.