What is a 'Partial Index' and when would you choose it over a 'Sparse Index'?

A Partial Index is an index that only includes documents meeting a specified filter expression, offering greater flexibility than Sparse Indexes and serving as their modern replacement in MongoDB.

A Partial Index in MongoDB is an index that only contains entries for documents that satisfy a specified filter expression, defined using the partialFilterExpression option during index creation . Introduced in MongoDB 3.2, partial indexes provide a superset of the functionality offered by sparse indexes . The key distinction is that while a sparse index selects documents solely based on whether the indexed field exists, a partial index can filter documents based on any field and any condition, offering much more precise control .

Partial Index Examples

When to Choose a Partial Index Over a Sparse Index

1
General preference: According to MongoDB official documentation, partial indexes should be preferred over sparse indexes for MongoDB 3.2 and later versions . They offer a superset of sparse index functionality with greater control over which documents are indexed .
2
Filtering on non-indexed fields: Choose a partial index when you need to filter based on fields other than the index key itself. For example, indexing { name: 1 } but only for documents where email exists—something impossible with a sparse index .
3
Complex filter conditions: Use a partial index when you need conditions beyond simple field existence, such as rating: { $gt: 5 }, status: "active", or age: { $gte: 21 } .
4
Precise query coverage: Choose a partial index when you want the query optimizer to automatically use the index for queries that match the filter expression, without requiring hint() . Partial indexes are considered during query planning when the query predicate includes the filter condition.
5
Conditional uniqueness: Use a partial index with unique: true when you need to enforce uniqueness only on a subset of documents. For example, ensuring usernames are unique only among active users, while inactive users can have duplicate usernames .
6
TTL indexes on filtered documents: Choose a partial index when you need to expire only specific documents based on a filter condition, using partial TTL indexes .

Despite partial indexes being preferred, sparse indexes remain relevant in specific contexts. Certain index types are inherently sparse: 2dsphere, 2d, geoHaystack, and text indexes are always sparse by default regardless of options . Additionally, some legacy applications may still use sparse indexes, and they work perfectly well for their original purpose—indexing only documents that contain a specific field. The key advantage of sparse indexes is their simplicity when your only requirement is "index documents where this field exists" .

Both index types share similar behaviors regarding incomplete result sets. If using either a sparse or partial index would result in an incomplete result set for a query, MongoDB will not use that index unless explicitly hinted with hint() . For example, a query sorting by an indexed field but requesting all documents won't use a sparse or partial index because it would miss documents without the field or not matching the filter. Both index types also support unique constraints, with the same behavior: they prevent duplicate values among indexed documents but allow duplicates in non-indexed documents .

There are two key restrictions to remember: you cannot specify both partialFilterExpression and sparse options on the same index—they are mutually exclusive . Additionally, _id indexes and shard key indexes cannot be partial indexes, and in MongoDB versions prior to 5.0, you could not create multiple partial indexes with the same key pattern but different filter expressions .

Difficulty: 5/10

Topics: indexing, query performance, MongoDB

Scenario Questions

0-2 years experience

1
We have a collection where only some documents have a 'status' field. How would you create an index that only includes documents with 'status' set, and why would you choose that over a regular index?
2
If you run a query filtering on 'status' and notice the index isn’t being used, what could be wrong with the index definition?
3
What happens when you insert a document without the indexed field into a collection that has a partial index on that field?

2-5 years experience

1
Our service added an optional field 'category' and we need fast lookups on it without bloating the index size. Walk me through how you’d implement this and what trade‑offs you consider versus a sparse index.
2
During a performance regression we discovered that a query using a partial index on 'isActive' started scanning the whole collection. What steps would you take to debug and fix it?
3
If a partial index predicate references a field that later becomes required in the schema, how would you handle the migration without downtime?

5-8 years experience

1
Design a sharded MongoDB deployment that uses partial indexes to enforce data‑residency constraints. What are the implications for query routing and index maintenance?
2
At scale we have a mixed workload of reads and writes on a collection with both sparse and partial indexes. How would you decide which index type to keep, and what monitoring would you put in place?
3
Explain how partial indexes interact with TTL indexes and what pitfalls could arise in a high‑throughput logging system.

8+ years experience

1
Our organization is moving legacy collections that use sparse indexes to a new schema that prefers partial indexes for compliance reasons. Outline a migration strategy that minimizes impact on downstream services.
2
When evaluating long‑term storage costs, how would you argue for adopting partial indexes across multiple microservices, considering operational, observability, and data‑governance aspects?
3
If a cross‑team feature requires a compound index that includes a field only present in a subset of documents, how would you coordinate the index definition to satisfy both teams while keeping index size optimal?

Follow-up Questions

❓How does MongoDB evaluate the predicate for a partial index during query planning?
❓What are the main limitations of partial indexes compared to sparse indexes?
❓In what scenarios could a partial index actually degrade write performance?