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

Question

Accepted Answer

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 . ```javascript
// Example 1: Index only documents with rating greater than 5
db.restaurants.createIndex(
  { cuisine: 1, name: 1 },
  { partialFilterExpression: { rating: { $gt: 5 } } }
)

// Example 2: Index only documents with a specific field value
db.movies.createIndex(
  { title: 1 },
  { partialFilterExpression: { genres: "Drama" } }
)

// Example 3: Index only documents where email field exists (sparse-like behavior)
db.contacts.createIndex(
  { name: 1 },
  { partialFilterExpression: { email: { $exists: true } } }
)

// Example 4: Partial index with unique constraint on active users
db.users.createIndex(
  { username: 1 },
  { 
    unique: true, 
    partialFilterExpression: { status: "active" } 
  }
)
``` **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 . **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 . **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 }` . **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. **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 . **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 .

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.

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.

Scenario Questions

Follow-up Questions