Partial indexes with filter in MongoDB - Deep Dive

Overview - Partial indexes with filter

What is it?

Partial indexes in MongoDB are special indexes that only include documents matching a specific condition or filter. Instead of indexing every document in a collection, they index only those that meet the filter criteria. This makes the index smaller and faster for queries that target those filtered documents.

Why it matters

Without partial indexes, every query that uses an index must scan the entire index, even if only a small subset of documents is relevant. This can slow down queries and waste storage. Partial indexes solve this by focusing only on relevant documents, improving query speed and saving space. Imagine searching a phone book that only lists people in your city instead of the whole country.

Where it fits

Before learning partial indexes, you should understand basic MongoDB indexing and how queries use indexes. After mastering partial indexes, you can explore more advanced indexing strategies like compound indexes, wildcard indexes, and index intersection.

Mental Model

Core Idea

A partial index is like a filtered shortcut that only covers the documents you care about, making searches faster and indexes smaller.

Think of it like...

Think of a partial index like a VIP guest list at a party. Instead of checking everyone at the door, the bouncer only checks people on the VIP list, speeding up entry for those guests.

Collection Documents
┌───────────────┐
│ Doc 1        │
│ Doc 2        │
│ Doc 3        │
│ Doc 4        │
│ Doc 5        │
└───────────────┘
       │
       ▼
Partial Index Filter: { status: 'active' }
       │
       ▼
Indexed Docs
┌───────────────┐
│ Doc 2        │
│ Doc 5        │
└───────────────┘

Build-Up - 7 Steps

1

Foundation

What is an index in MongoDB

Concept: Introduce the basic idea of an index as a tool to speed up searches.

An index in MongoDB is like a table of contents in a book. It helps the database find documents faster by keeping a sorted list of values from a specific field. Without an index, MongoDB must look at every document to answer a query, which is slow.

Result

Queries using indexed fields run faster because MongoDB can quickly locate matching documents.

Understanding indexes is essential because partial indexes build on this idea by adding filters to reduce index size.

2

Foundation

How filters work in queries

Concept: Explain how MongoDB uses filters to find documents matching conditions.

Filters in MongoDB queries specify conditions documents must meet to be returned. For example, { status: 'active' } finds documents where the status field equals 'active'. Filters help narrow down results from the whole collection.

Result

Queries return only documents matching the filter, reducing data to what you need.

Knowing how filters work is key to understanding how partial indexes use filters to include only certain documents.

3

Intermediate

What is a partial index

Concept: Introduce partial indexes as indexes built only on documents matching a filter.

A partial index is created with a filter expression. MongoDB indexes only documents that satisfy this filter. For example, an index on { age: 1 } with filter { status: 'active' } indexes only active users' ages, ignoring others.

Result

The index is smaller and faster for queries that include the filter condition.

Partial indexes optimize performance by focusing indexing effort only where it matters.

4

Intermediate

Creating a partial index in MongoDB

🤔Before reading on: do you think you can create a partial index without specifying a filter? Commit to your answer.

Concept: Show how to create a partial index using the 'partialFilterExpression' option.

Use db.collection.createIndex() with the 'partialFilterExpression' option to define the filter. Example: db.users.createIndex( { age: 1 }, { partialFilterExpression: { status: 'active' } } ) This creates an index on the 'age' field but only for documents where 'status' is 'active'.

Result

MongoDB builds an index that includes only active users' ages.

Knowing the syntax lets you create efficient indexes tailored to your query patterns.

5

Intermediate

How queries use partial indexes

🤔Before reading on: do you think a query without the filter condition can use a partial index? Commit to your answer.

Concept: Explain when MongoDB can use a partial index to speed up queries.

MongoDB uses a partial index only if the query's filter matches or includes the partial index filter. For example, a query with { status: 'active', age: { $gt: 30 } } can use the partial index on age with filter { status: 'active' }. But a query without 'status' cannot use it.

Result

Queries that match the partial index filter run faster; others do not benefit from the partial index.

Understanding this helps you design queries and indexes that work well together.

6

Advanced

Benefits and trade-offs of partial indexes

🤔Before reading on: do you think partial indexes always improve performance? Commit to your answer.

Concept: Discuss advantages and limitations of partial indexes in real use.

Partial indexes reduce index size and speed up queries that match the filter. They save storage and improve write performance because fewer documents are indexed. However, queries that don't match the filter cannot use the index, and complex filters may add overhead.

Result

Partial indexes improve performance selectively but require careful design.

Knowing trade-offs prevents misuse and helps balance speed, storage, and query needs.

7

Expert

Partial indexes internals and edge cases

🤔Before reading on: do you think partial indexes index documents missing the filtered field? Commit to your answer.

Concept: Explore how MongoDB handles documents missing filter fields and index updates.

MongoDB includes in the partial index only documents where the filter expression evaluates to true. Documents missing the filtered field are excluded. Updates that change a document's filter match status cause the index to add or remove that document dynamically. This can affect write performance and consistency.

Result

Partial indexes maintain correctness by dynamically tracking documents matching the filter, but this adds complexity.

Understanding this dynamic behavior helps diagnose performance issues and design robust indexes.

Under the Hood

MongoDB evaluates the partialFilterExpression for each document during index build and writes. Only documents where the filter is true are included in the index. When documents are inserted or updated, MongoDB re-evaluates the filter to decide whether to add or remove the document from the index. This selective indexing reduces index size and speeds up queries that include the filter condition.

Why designed this way?

Partial indexes were designed to optimize performance for common query patterns that target subsets of data. Instead of indexing all documents, which wastes space and slows writes, partial indexes focus on relevant documents. This design balances query speed, storage efficiency, and write overhead. Alternatives like full indexes or manual filtering were less efficient or more complex.

┌───────────────┐       ┌─────────────────────────────┐
│ Collection    │       │ Partial Index Filter         │
│ Documents     │──────▶│ { status: 'active' }         │
└───────────────┘       └─────────────┬───────────────┘
                                      │
                                      ▼
                           ┌───────────────────────┐
                           │ Indexed Documents      │
                           │ (status = 'active')    │
                           └───────────────────────┘

Myth Busters - 4 Common Misconceptions

Quick: Can a partial index speed up queries that do NOT include the filter condition? Commit to yes or no.

Common Belief: Partial indexes speed up all queries on the indexed field, regardless of filters.

Reality: Partial indexes only speed up queries that include the filter condition or a superset of it. Queries missing the filter cannot use the partial index.

Why it matters: Assuming partial indexes always help can lead to slow queries and wasted effort creating ineffective indexes.

Quick: Do partial indexes include documents missing the filtered field? Commit to yes or no.

Common Belief: Partial indexes index all documents, even those missing the filtered field.

Reality: Documents missing the filtered field are excluded because the filter condition evaluates to false or undefined.

Why it matters: This can cause unexpected query results or missing index coverage if you assume all documents are indexed.

Quick: Are partial indexes always smaller than full indexes? Commit to yes or no.

Common Belief: Partial indexes are always smaller than full indexes.

Reality: Partial indexes are smaller only if the filter matches fewer documents. If the filter is broad or matches most documents, the partial index size approaches a full index.

Why it matters: Misjudging this can lead to no storage savings and unexpected write overhead.

Quick: Can partial indexes cause write slowdowns? Commit to yes or no.

Common Belief: Partial indexes always improve write performance because they index fewer documents.

Reality: Partial indexes can add overhead because MongoDB must evaluate the filter on every write and update the index dynamically, which can slow writes in some cases.

Why it matters: Ignoring this can cause performance surprises in write-heavy applications.

Expert Zone

1

Partial indexes can be combined with compound indexes to optimize queries filtering on multiple fields selectively.

2

The filter expression supports complex MongoDB query operators, allowing fine-grained control over which documents are indexed.

3

Partial indexes do not support unique constraints directly; unique partial indexes require careful design to avoid unexpected duplicates.

When NOT to use

Avoid partial indexes when your queries do not consistently include the filter condition or when the filter matches most documents, as the benefits diminish. Use full indexes or other index types like wildcard or compound indexes instead.

Production Patterns

In production, partial indexes are often used to index only active or recent data, such as indexing only documents with status 'active' or created within the last month, improving performance for common queries while keeping index size manageable.

Connections

Query Optimization

Partial indexes build on query optimization by reducing the search space for queries with specific filters.

Understanding partial indexes deepens your grasp of how databases optimize queries by focusing resources on relevant data subsets.

Set Theory

Partial indexes represent a subset of the collection defined by a filter condition, similar to subsets in set theory.

Recognizing this connection helps understand how filters define the scope of indexing and querying.

Selective Attention in Psychology

Partial indexes mimic selective attention by focusing only on important information and ignoring irrelevant data.

This cross-domain link shows how focusing on relevant data improves efficiency, whether in human cognition or database indexing.

Common Pitfalls

#1 Creating a partial index without matching query filters

Wrong approach:

db.users.createIndex({ age: 1 }, { partialFilterExpression: { status: 'active' } })
db.users.find({ age: { $gt: 30 } })

Correct approach:

db.users.createIndex({ age: 1 }, { partialFilterExpression: { status: 'active' } })
db.users.find({ status: 'active', age: { $gt: 30 } })

Root cause:

The query does not include the partial index filter condition, so MongoDB cannot use the partial index.

#2 Assuming partial indexes index all documents

Wrong approach:

db.users.createIndex({ email: 1 }, { partialFilterExpression: { verified: true } })
db.users.find({ email: '[e-mail]' })

Correct approach:

db.users.createIndex({ email: 1 }, { partialFilterExpression: { verified: true } })
db.users.find({ verified: true, email: '[e-mail]' })

Root cause:

Documents missing the 'verified' field or with verified=false are not indexed, so queries without the filter cannot use the index.

#3 Using partial indexes for unique constraints without care

Wrong approach:

db.users.createIndex({ username: 1 }, { unique: true, partialFilterExpression: { active: true } })

Correct approach:

Use full unique indexes or carefully design application logic to handle uniqueness with partial indexes.

Root cause:

Partial unique indexes only enforce uniqueness on filtered documents, allowing duplicates outside the filter.

Key Takeaways

Partial indexes in MongoDB index only documents matching a filter, making indexes smaller and queries faster for those documents.

They improve performance when queries include the filter condition but do not help queries missing it.

Creating partial indexes requires specifying a filter expression using 'partialFilterExpression' during index creation.

Partial indexes dynamically update as documents change to maintain correctness, which can affect write performance.

Understanding when and how to use partial indexes helps design efficient databases tailored to real query patterns.

Practice

1. What is the main purpose of a partial index in MongoDB?

easy

A. To index only documents that match a specific filter condition

B. To index all documents in a collection

C. To create a backup of the index

D. To speed up writes by disabling indexing

Solution

1. Step 1: Understand what partial indexes do

   Partial indexes only include documents that meet a filter condition, unlike normal indexes that include all documents.

2. Step 2: Compare options

   To index only documents that match a specific filter condition correctly describes this behavior. Options A, B, and D describe other unrelated or incorrect behaviors.

3. Final Answer:

   To index only documents that match a specific filter condition -> Option A

4. Quick Check:

   Partial index = filtered indexing [OK]

Hint: Partial indexes filter documents before indexing [OK]

Common Mistakes:

• Thinking partial indexes index all documents
• Confusing partial indexes with backups
• Assuming partial indexes disable indexing

2. Which of the following is the correct syntax to create a partial index on the status field where status equals 'active'?

easy

A. db.collection.createIndex({status: 1}, {filter: {status: 'active'}})

B. db.collection.createIndex({status: 1}, {partialFilterExpression: {status: 'active'}})

C. db.collection.createPartialIndex({status: 1}, {status: 'active'})

D. db.collection.createIndex({status: 1}, {partialIndex: {status: 'active'}})

Solution

1. Step 1: Recall the correct option name for partial indexes

   The option to specify a filter for partial indexes is partialFilterExpression.

2. Step 2: Match syntax with options

   db.collection.createIndex({status: 1}, {partialFilterExpression: {status: 'active'}}) uses partialFilterExpression correctly. Options B, C, and D use incorrect option names or methods.

3. Final Answer:

   db.collection.createIndex({status: 1}, {partialFilterExpression: {status: 'active'}}) -> Option B

4. Quick Check:

   Use partialFilterExpression for partial indexes [OK]

Hint: Use partialFilterExpression option to filter index documents [OK]

Common Mistakes:

• Using 'filter' instead of partialFilterExpression
• Trying to use createPartialIndex method (doesn't exist)
• Using partialIndex option instead of partialFilterExpression

3. Given the following partial index creation:

db.users.createIndex({age: 1}, {partialFilterExpression: {age: {$gte: 18}}})

Which documents will be included in this index?

medium

A. All documents regardless of age

B. Only documents where age is less than 18

C. Only documents where age is 18 or older

D. Documents where age is exactly 18

Solution

1. Step 1: Understand the filter condition

   The filter {age: {$gte: 18}} means age greater than or equal to 18.

2. Step 2: Determine which documents match

   Only documents with age 18 or older satisfy this condition and will be indexed.

3. Final Answer:

   Only documents where age is 18 or older -> Option C

4. Quick Check:

   Filter {$gte: 18} means age ≥ 18 [OK]

Hint: Partial index includes only documents matching filter [OK]

Common Mistakes:

• Thinking all documents are indexed
• Confusing $gte with $lt
• Assuming only exact matches are indexed

4. You tried to create a partial index with this command:

db.orders.createIndex({price: 1}, {partialFilterExpression: {price: {$gt: 100}}})

But you get an error. What is the most likely cause?

medium

A. The filter expression uses an unsupported operator

B. The field name in the index is missing

C. Partial indexes cannot use comparison operators

D. The partialFilterExpression option is misspelled or misplaced

Solution

1. Step 1: Check syntax correctness

   The syntax shown is correct if partialFilterExpression is spelled and placed properly.

2. Step 2: Identify common mistakes

   Errors often happen if partialFilterExpression is misspelled or placed outside the options object.

3. Final Answer:

   The partialFilterExpression option is misspelled or misplaced -> Option D

4. Quick Check:

   Check spelling and placement of partialFilterExpression [OK]

Hint: Check spelling and position of partialFilterExpression [OK]

Common Mistakes:

• Misspelling partialFilterExpression
• Placing filter outside options object
• Assuming comparison operators are unsupported

5. You want to speed up queries on a collection products where only documents with stock greater than zero are frequently queried. Which partial index creation command is best?

hard

A. db.products.createIndex({stock: 1}, {partialFilterExpression: {stock: {$gt: 0}}})

B. db.products.createIndex({stock: 1})

C. db.products.createIndex({stock: 1}, {partialFilterExpression: {stock: 0}})

D. db.products.createIndex({stock: 1}, {filter: {stock: {$gt: 0}}})

Solution

1. Step 1: Identify the filter condition for partial index

   The goal is to index only documents where stock is greater than zero.

2. Step 2: Match the correct syntax and filter

   db.products.createIndex({stock: 1}, {partialFilterExpression: {stock: {$gt: 0}}}) uses partialFilterExpression with {stock: {$gt: 0}}, which matches the requirement.

3. Step 3: Evaluate other options

   db.products.createIndex({stock: 1}) indexes all documents (not partial). db.products.createIndex({stock: 1}, {partialFilterExpression: {stock: 0}}) filters only stock equal to zero, which is wrong. db.products.createIndex({stock: 1}, {filter: {stock: {$gt: 0}}}) uses incorrect option name filter.

4. Final Answer:

   db.products.createIndex({stock: 1}, {partialFilterExpression: {stock: {$gt: 0}}}) -> Option A

5. Quick Check:

   Use partialFilterExpression with correct filter [OK]

Hint: Use partialFilterExpression with correct filter condition [OK]

Common Mistakes:

• Indexing all documents instead of partial
• Using wrong filter values
• Using incorrect option names like 'filter'