Most queries against a big collection only care about a slice of it. A support queue reads open tickets, not the 9 million closed ones. A job scheduler reads pending jobs. If you index the whole collection for those queries, most of the index describes documents nobody asks for. A partial index stores only the documents that match a filter you choose, so it’s smaller, cheaper to maintain, and more likely to stay in memory.
Examples are mongosh for MongoDB 7 and 8 and are illustrative; no live server was available while writing.
Creating one
A tickets collection with 10 million documents, of which about 40,000 are open:
db.tickets.createIndex(
{ assignee: 1, priority: -1, createdAt: 1 },
{ partialFilterExpression: { status: "open" } }
)The index contains keys only for open tickets. When a ticket is closed ($set: { status: "closed" }), its keys are removed from the index. When a new open ticket arrives, it’s added. Inserts and updates to closed tickets don’t touch this index at all.
The size difference is the point. An index over 10 million documents might be several hundred megabytes; one over 40,000 is a few megabytes. On a server where RAM is tight, that can decide whether queries hit memory or disk.
When the planner can use it
Here’s the rule that interview questions test: MongoDB uses a partial index only if the query’s filter implies the partial filter. In other words, every document the query could return must be in the index. Otherwise using the index would silently miss results.
// uses the index: the query requires status "open"
db.tickets.find({ status: "open", assignee: "maya" }).sort({ priority: -1 })
// does NOT use it: no status condition, closed tickets could match
db.tickets.find({ assignee: "maya" }).sort({ priority: -1 })
// does NOT use it: closed is outside the index
db.tickets.find({ status: "closed", assignee: "maya" })The second query is the trap. A developer sees “there’s an index on assignee” and expects it to be used. It isn’t, because the index doesn’t contain Maya’s closed tickets. Explain will show a different index or a COLLSCAN.
The implication works with ranges too. With partialFilterExpression: { amount: { $gt: 100 } }, a query on { amount: { $gt: 500 } } can use the index (anything over 500 is over 100), but { amount: { $gt: 50 } } can’t.
Note that the partial filter field (status) doesn’t have to be one of the index keys. Here it isn’t: the index is on assignee, priority and createdAt, while status only decides membership. That’s a common and efficient design, because all the keys in the index share the same status value anyway, so storing it in each key would be wasted space.
What the filter can contain
partialFilterExpression supports a limited set of operators: equality, $exists: true, $gt, $gte, $lt, $lte, $type, $and, and since MongoDB 6.0 also $or and $in (within limits). You can’t use $ne, $nin, $not or $exists: false. So “index everything that isn’t deleted” has to be written positively: if deleted documents have deleted: true and others have deleted: false, use { deleted: false }. If live documents simply lack the field, you can’t express “missing” in the filter, which is a reason to store an explicit value.
Conditional uniqueness
The other big use is uniqueness that applies only to some documents. A user may have many past subscriptions but only one active one:
db.subscriptions.createIndex(
{ userId: 1 },
{ unique: true, partialFilterExpression: { status: "active" } }
)db.subscriptions.insertOne({ userId: 7, status: "cancelled", plan: "basic" })
db.subscriptions.insertOne({ userId: 7, status: "cancelled", plan: "pro" }) // fine
db.subscriptions.insertOne({ userId: 7, status: "active", plan: "pro" }) // fine
db.subscriptions.insertOne({ userId: 7, status: "active", plan: "team" })
// MongoServerError: E11000 duplicate key error collection: shop.subscriptions
// index: userId_1 dup key: { userId: 7 }Uniqueness is enforced only among documents in the index. That’s exactly the business rule, and it holds under concurrency: two requests racing to activate a subscription can’t both succeed. Doing the same check in application code (“find active, if none, insert”) has a race window.
The same trick fixes the well-known unique-index-with-missing-fields problem. A plain unique index on email treats every document without an email as having email: null, so only one such document is allowed. A partial unique index with { email: { $type: "string" } } indexes only documents that actually have an email.
Partial versus sparse
Sparse indexes are the older feature: they skip documents that don’t have the indexed field. Partial indexes can express that ({ field: { $exists: true } }) and much more, so the MongoDB docs recommend partial indexes over sparse ones for new work. You can’t combine the two options on the same index.
Mistakes to avoid
- Assuming the index is used. Always check explain for the actual query shape. ORMs and query builders sometimes omit the condition you expected.
- Filtering on a value that keeps changing. A partial filter on
createdAt: { $gt: ISODate("2026-01-01") }is fixed at creation time; it doesn’t roll forward. For “recent” data, a TTL or an archival process fits better. - Forgetting the
_idindex is not partial. Only secondary indexes can be partial.
Interview practice
An e-commerce platform has a carts collection with 50 million documents. About 200,000 are active (status: "active"); the rest are abandoned or converted. A background job runs find({ status: "active", updatedAt: { $lt: oneHourAgo } }) every minute to send reminders, and the checkout path runs find({ userId: X, status: "active" }). There’s one index on { status: 1, updatedAt: 1 } and one on { userId: 1 }. Propose an index strategy.
Answer
Both hot queries only care about active carts, which are 0.4% of the collection. That’s a strong case for partial indexes filtered on status: "active".
For the reminder job: { updatedAt: 1 } with partialFilterExpression: { status: "active" }. The query includes status: "active", so the planner can use it, and it holds 200,000 keys instead of 50 million. status doesn’t need to be a key since every entry shares it.
For checkout: { userId: 1 } with the same partial filter, and I’d make it unique: true if the rule is one active cart per user, which also prevents duplicate active carts from a double-click race.
Then I’d check whether anything else uses the existing full indexes. The { status: 1, updatedAt: 1 } index is probably redundant after this. The full { userId: 1 } index might still be needed for an order history page that queries all carts by user; if so, keep it. Finally, I’d verify with explain that both queries pick the partial indexes, and remind the team that a future query without status: "active" won’t use them.