What is the difference between a sparse index and a partial index in MongoDB?

## Why either exists Indexes cost storage and write throughput on every document they cover. If your queries only ever touch a slice of the collection — the unfinished orders, the users who actually set a nickname — indexing the whole collection is waste. Both options shrink the index to the interesting subset. They differ in how the subset is described. ## Sparse: presence only ```javascript db.users.createIndex({ nickname: 1 }, { sparse: true }) ``` A sparse index skips every document that does **not contain** the field. Note the precise rule: it is about the field's presence, not its value. A document with `nickname: null` does contain the field, so it *is* indexed. A document with no `nickname` key at all is not. On a compound sparse index the rule loosens in a way that surprises people: a document is indexed if it contains **at least one** of the indexed fields, not all of them. So a compound sparse index is far less selective than it looks. Combined with `unique: true`, sparse solves the classic optional-unique problem: many documents may omit the field, and uniqueness is enforced only among those that have it. ## Partial: an actual filter ```javascript db.orders.createIndex( { customerId: 1, createdAt: -1 }, { partialFilterExpression: { status: "open" } } ) ``` A partial index indexes only documents matching `partialFilterExpression`. The filter language is deliberately restricted — equality on a field, `$exists: true`, the range operators `$gt`, `$gte`, `$lt`, `$lte`, `$type`, and `$and` at the top level, plus a small number of others depending on version. It is not a general query language; anything that would require evaluating the whole document arbitrarily is refused at creation time. Because `{ field: { $exists: true } }` is expressible, a partial index can do everything a sparse index does and more. MongoDB's own guidance is to prefer partial indexes for new work. You cannot specify both `sparse: true` and `partialFilterExpression` on the same index — the server rejects it. ## The planner rule that trips everyone up An index that does not cover the whole collection can only answer a query when the planner can **prove** that no document outside the index could match. Formally, the query predicate must guarantee a subset of the index's filter. With the partial index above on `status: "open"`: - `find({ customerId: 7, status: "open" })` can use it — the predicate pins `status` to the filter value. - `find({ customerId: 7 })` cannot — documents with `status: "closed"` might match and are not in the index, so the result would be incomplete. You get a collection scan. - `find({ customerId: 7, status: { $in: ["open", "closed"] } })` cannot use it either, for the same reason. The practical consequence: the filter has to be a literal in the query. Applications that build the filter dynamically, or that pass `status` as a variable that is *usually* `"open"`, silently lose the index. Always confirm with `explain("executionStats")` that you get an `IXSCAN` and not a `COLLSCAN`. Sparse indexes have the mirror-image restriction. MongoDB will not use a sparse index for an operation that would need documents lacking the field — for example a plain `sort({ nickname: 1 })` over the whole collection, or a query for `{ nickname: { $exists: false } }` — because the answer would be missing documents. You can force it with `hint()`, and then you get the incomplete result you asked for, which is a good way to create a subtle bug. ## Unique plus partial The most valuable combination in practice is uniqueness scoped to a subset: ```javascript // At most one active subscription per customer; // any number of cancelled ones. db.subscriptions.createIndex( { customerId: 1 }, { unique: true, partialFilterExpression: { status: "active" } } ) ``` Documents outside the filter are not in the index at all, so they are not constrained. This expresses a business rule that has no other clean home in a document store, and it is a very common interview follow-up. ## Choosing Use partial by default. Reach for sparse only when you are maintaining an existing index or when presence really is the whole condition and you like the shorter spelling. Size the win honestly: if the filter matches 90% of the collection, the smaller index buys you very little and the planner restriction is pure downside. The pattern pays when the indexed slice is a small minority of a large collection — open orders in a table of years of closed ones is the canonical case.