In this lesson, you will learn how to optimize your storage footprint and query performance by using specialized indexes that only target a subset of your document collection.
The Problem of "Index Bloat"
In document-based models, documents are often polymorphic, meaning they contain varying fields. If you create a standard index on a field that only exists in 5% of your documents, the database engine will still allocate space to track the null values for the other 95%. This leads to "index bloat"—where your index takes up more disk space than the actual data it references, slowing down write operations and wasting memory.
Sparse Indexes
A sparse index ignores any document that does not contain the indexed field. It tells the database engine: "If this field is missing, don't bother including this document in the index tree."
Consider a Users collection where only premium members have a subscription_id. Creating a sparse index on subscription_id ensures that your index size only scales with the number of premium users, not the total number of users.
-- Conceptual example of a sparse index
CREATE INDEX idx_premium_sub
ON Users(subscription_id)
WHERE subscription_id IS NOT NULL;
Partial Indexes
A partial index is a more powerful, flexible version of the sparse index. Instead of just filtering out null values, you can define a specific predicate (a condition) to determine which documents get indexed.
Imagine you have an Orders collection. Most queries look for "active" orders, but you have millions of "archived" orders that you rarely touch. You can create a partial index that only covers documents where status = 'active':
CREATE INDEX idx_active_orders
ON Orders(order_date)
WHERE status = 'active';
By excluding the archived data, you keep your index memory-resident and incredibly fast for the queries your application actually runs. You effectively trade a tiny bit of flexibility for a massive gain in storage efficiency and speed.
Key Takeaway: Use sparse and partial indexes to shrink index footprints by only indexing the documents that your application frequently queries, rather than indexing the entire collection.