Partial, Sparse, Unique, and TTL Indexes

What we're doing: Measure a real size reduction rather than assuming one, using the $in support the earlier, narrower mental model would have missed.

db.orders.countDocuments({})                                    // e.g. 10,000,000
db.orders.countDocuments({ status: { $in: ["pending", "processing"] } })  // e.g. 40,000
// -> a partial index on this filter covers ~0.4% of the collection — a real, worthwhile reduction

db.orders.createIndex(
  { customerId: 1 },
  { partialFilterExpression: { status: { $in: ["pending", "processing"] } } }
)  // $in is supported directly — no need to restructure into repeated $or/$and clauses

1

Measuring both counts directly gives the real reduction percentage, rather than assuming "pending orders are rare" without checking.

5

$in is supported in partialFilterExpression, so the filter can be written directly rather than expanded into several $or branches.

Why this works: The whole point of a partial index is a size/cost reduction that is worth the complexity of remembering to include its filter condition in queries — that trade is only justified if the actual measured reduction is significant, which requires counting, not assuming.

What we're doing: Show the null-vs-missing distinction concretely — both included, only true absence excluded.

db.users.createIndex({ phone: 1 }, { sparse: true })

db.users.insertOne({ name: "Arya" })                 // phone entirely absent — excluded from the index
db.users.insertOne({ name: "Jon", phone: null })      // phone explicitly null — INCLUDED, null is a value
db.users.insertOne({ name: "Sansa", phone: "555-01" }) // included, normal case

1

The index only excludes documents where phone is entirely absent — this is a narrower exclusion than "no meaningful phone number."

5

Explicitly setting phone: null still counts as the field being present — this document is included in the sparse index, which surprises people expecting null to be treated like "missing."

Why this works: The distinction matters because "sparse" is often reached for with the intent "only index documents that have a real value here" — but the actual behavior is narrower (only true absence is excluded), so a field pattern using explicit nulls as a placeholder does not get the exclusion someone might expect.

What we're doing: Show the sparse+unique combination fixing the exact collision section 10 demonstrates with a plain unique index.

db.users.createIndex({ email: 1 }, { unique: true, sparse: true })

db.users.insertOne({ name: "Arya Stark" })     // email absent — excluded from the sparse index entirely
db.users.insertOne({ name: "Jon Snow" })       // also absent — also excluded, no collision this time

1

sparse: true means documents missing email never enter the index at all — there is no "null" entry for them to collide on.

4

Both documents succeed, unlike the plain unique-only case from section 10, because neither one is ever considered by the index.

Why this works: This directly resolves the exact surprise section 10's unique-indexes concept flags — the fix is not to work around the null-collision behavior in application code, but to tell the index to stop considering documents that lack the field in the first place.

What we're doing: Show application logic correctly checking the expiry timestamp instead of trusting TTL to have already removed an expired document.

// Wrong assumption: "if it's still in the collection, it must still be valid"
const session = db.sessions.findOne({ _id: sessionId })
// -> session could be expired but not yet TTL-deleted; using it as-is risks accepting an expired session

// Correct: check the expiry condition explicitly, independent of whether TTL has run yet
const session = db.sessions.findOne({ _id: sessionId, lastActiveAt: { $gte: cutoffTime } })

1

A direct lookup by _id says nothing about whether the document has passed its TTL expiry — it may simply not have been swept yet.

4

Adding the same expiry condition the TTL index itself is based on makes the application logic correct regardless of exactly when the background sweep runs.

Why this works: TTL indexes are a convenience for eventual cleanup, not a synchronization primitive — application logic that needs to know "is this still valid right now" has to encode that check itself, the same way it would need to if TTL did not exist at all.