MongoDB Sparse, Partial, and TTL Indexes: Mechanics, Pitfalls, and Production Design Patterns
A SaaS platform stored user records with an optional premiumSince date field. Only premium users — 2% of the collection — had this field set. The team created a standard index on premiumSince to serve premium-only queries. It worked fine until a bulk job set premiumSince: null on all non-premium users as part of a data normalization effort. Overnight, a MongoDB sparse index that was supposed to hold 20,000 entries ballooned to 1 million entries, nullifying every performance benefit and tripling write overhead.
At MinervaDB, this story repeats across client engagements. Sparse, partial, and TTL indexes are powerful tools, but each comes with behavioral contracts that must be understood deeply before deployment. This guide covers the mechanics of each index type, the failure modes we encounter in production, and the design patterns that make each type reliable at scale.
Sparse Indexes: What ‘Missing Field’ Really Means
A sparse index only includes documents where the indexed field exists and is not null. Documents where the field is absent or explicitly set to null are excluded from the index entirely. This contrasts with a standard index, which includes those documents with a null key — making them visible to queries that filter for null values.
The WiredTiger implication: a sparse index’s B-tree contains keys only for the eligible subset of documents. For a collection where 95% of documents lack the indexed field, a sparse index may be 20x smaller than the equivalent standard index. This size advantage is the primary use case: indexing an optional field that appears in a small, bounded fraction of documents.
// Create sparse index on optional field
db.users.createIndex(
{ premiumSince: 1 },
{ sparse: true, name: 'users_premiumSince_sparse' }
);
// This index will NOT include documents where premiumSince is absent or null
// It WILL include documents where premiumSince is any non-null value
// Verify: count indexed documents vs collection documents
var indexedCount = db.users.find(
{ premiumSince: { $exists: true, $ne: null } }
).count();
var totalCount = db.users.countDocuments();
print('Indexed fraction:', (indexedCount / totalCount * 100).toFixed(1) + '%');
// If indexed fraction > 50%, a sparse index provides minimal benefit
// TRAP: sparse index silently skipped for queries that don't reference the field
db.users.find({}).sort({ premiumSince: 1 }).hint('users_premiumSince_sparse');
// ^ Will only return documents WITH premiumSince — non-premium users invisible
// This is intentional behavior but surprises developers expecting all users sorted
The critical behavioral rule: a sparse index is only safe to use with queries that explicitly filter on the sparse field. Any other usage risks returning incomplete result sets. This is why the SaaS client’s bulk null-fill migration was so damaging — it moved documents from the “excluded” bucket into the “included” bucket without anyone anticipating that the index size would grow by 50x.
The Sparse Index Trap: Incorrect Query Results
Sparse indexes can silently return incorrect results when the planner chooses to use them for queries that do not explicitly filter on the sparse field. If the planner selects a sparse index to serve a sort or a different field’s query, documents missing the sparse field are invisible in the result set. This is one of the few cases in MongoDB where index selection affects result correctness rather than just performance.
MongoDB protects against this in most cases by not automatically selecting sparse indexes for queries that don’t include the sparse field in the filter. But if you force a sparse index via hint(), you can produce incorrect results. At MinervaDB, we document all sparse index hints in code with explicit warnings and test coverage that validates document counts against a known baseline.
// Safe usage: query explicitly filters on the sparse field
db.users.find({ premiumSince: { $exists: true } })
.sort({ premiumSince: 1 });
// Planner can safely use the sparse index — only premium users match anyway
// UNSAFE: query does not filter on sparse field, but hint forces the index
db.users.find({ country: 'US' })
.sort({ premiumSince: 1 })
.hint('users_premiumSince_sparse');
// Returns only US users who ARE premium — silently excludes non-premium US users
// No error, no warning — wrong result set
// Check if planner auto-selected sparse index for a non-sparse-field query
db.users.find({ country: 'US' }).sort({ premiumSince: 1 })
.explain().queryPlanner.winningPlan;
// If IXSCAN on premiumSince appears without a premiumSince filter, investigate
The correctness risk is why partial indexes are the recommended replacement for sparse indexes in all new MongoDB 3.2+ deployments. Sparse indexes are maintained for backward compatibility, but every sparse index we encounter in a client audit gets evaluated for conversion to a partial index.
Partial Indexes: Sparse Indexes Done Right
Partial indexes generalize sparse indexes by accepting any filter expression as the inclusion condition. Where a sparse index uses a fixed rule (exclude null or missing), a partial index lets you express exactly which documents to include using $gt, $lt, $eq, $type, and logical operators. Partial indexes are strictly more expressive than sparse indexes, and sparse indexes are a subset of partial indexes.
The key advantage is predictability. A partial index with { status: ‘active’ } as the filter will include exactly the documents you intend, regardless of what other values status might take. The filter is evaluated at index creation and during every write — documents satisfying the filter are indexed, others are not. This eliminates the null-value ambiguity that trips up sparse indexes.
// Partial index: only index active orders
db.orders.createIndex(
{ customerId: 1, createdAt: -1 },
{
partialFilterExpression: { status: 'active' },
name: 'orders_active_customerId_createdAt'
}
);
// This index only contains orders where status = 'active'
// For a collection where 5% of orders are active, this is ~20x smaller
// than a full compound index
// IMPORTANT: Queries MUST include the partial filter in the predicate
// for the planner to select this index
db.orders.find({ customerId: 'c1', status: 'active' })
.sort({ createdAt: -1 });
// ^ Planner can use partial index: query filter subsumes partialFilterExpression
// Planner will NOT use partial index for:
db.orders.find({ customerId: 'c1' });
// ^ Missing status: 'active' — planner cannot guarantee correctness
A logistics platform we support manages a collection of 180 million shipment records where 2% are in active transit at any time. Converting from a full compound index to a partial index on { status: { $in: [‘active’, ‘in_transit’] } } reduced that index from 48GB to under 1GB, cutting write amplification on new shipment inserts by 70%.
Partial Index Design Patterns for Production
The most impactful partial index pattern is the hot data index: index only documents in an active state that the application queries most frequently. Archived, deleted, or completed records rarely appear in hot query paths. A partial index on status: { $in: [‘active’, ‘pending’] } on a billing collection might cover 8% of documents while serving 98% of queries — the index is 12x smaller with equivalent query coverage.
A second high-value pattern is the null-safety partial index: use $type: ‘date’ instead of $exists: true on date fields to exclude documents where the field is present but not a date (which happens in messy legacy data).
// Hot data partial index pattern
db.invoices.createIndex(
{ accountId: 1, dueDate: 1 },
{
partialFilterExpression: {
status: { $in: ['unpaid', 'overdue', 'pending'] }
},
name: 'invoices_hot_accountId_dueDate'
}
);
// Null-safety partial index: better than sparse for optional date fields
db.users.createIndex(
{ lastLoginAt: -1 },
{
partialFilterExpression: { lastLoginAt: { $type: 'date' } },
name: 'users_activeLogin_lastLoginAt'
}
);
// Includes only users who have actually logged in (date type)
// Excludes null, missing, and type-mismatched values explicitly
// Check partial index usage:
db.invoices.find({
accountId: 'acme',
status: 'unpaid',
dueDate: { $lt: new Date() }
}).explain().queryPlanner.winningPlan;
// Should show IXSCAN on 'invoices_hot_accountId_dueDate'
TTL Indexes: Mechanics and the Background Deletion Thread
A TTL (Time-To-Live) index automatically deletes documents once the indexed date field exceeds the specified expireAfterSeconds threshold. Internally, a background thread called TTLMonitor runs every 60 seconds by default and performs a range scan on the TTL index to find expired documents, then deletes them in batches. The TTLMonitor is a single thread per mongod — it processes one TTL index at a time.
The 60-second interval is approximate. TTLMonitor adjusts dynamically if it cannot process all expired documents within one cycle — it processes as many as possible and continues in the next cycle. On a heavily loaded mongod, document deletion may lag by minutes or hours if the backlog grows faster than the monitor can clear it.
// Create TTL index on session expiry field
db.sessions.createIndex(
{ expiresAt: 1 },
{ expireAfterSeconds: 0 } // 0 = expire at the date stored in expiresAt
);
// Alternative: expire N seconds after document creation
db.logs.createIndex(
{ createdAt: 1 },
{ expireAfterSeconds: 2592000 } // 30 days in seconds
);
// Monitor TTL deletion backlog
db.adminCommand({ serverStatus: 1 }).metrics.ttl;
// Output: { deletedDocuments: <total>, passes: <total TTLMonitor runs> }
// deletedDocuments per pass should be stable
// Rising passes count with low deletedDocuments/pass signals TTLMonitor lag
// Tune TTLMonitor sleep interval on high-throughput collections
db.adminCommand({
setParameter: 1,
ttlMonitorSleepSecs: 30 // run every 30 seconds instead of 60
});
At MinervaDB, we treat TTL deletion lag as a capacity metric, not just a correctness concern. A session collection that retains 48 hours of expired sessions doubles the WiredTiger working set, increases COLLSCAN time on full scans, and raises storage costs. Monitoring metrics.ttl.deletedDocuments per pass identifies lag before it becomes a storage or performance incident.
TTL Index Gotchas and Production Pitfalls
TTL indexes only work on fields of type Date. If the field contains a string, integer, or null, the document is never deleted. This is a silent failure mode — the index exists, the TTLMonitor runs, but the documents persist forever. At MinervaDB, we include a TTL field type audit in every collection review.
A second common pitfall: TTL deletions on replica sets happen only on the primary. During an election, the TTLMonitor on the new primary resumes processing after the election completes. Documents that should have expired during the election window are deleted in the next TTLMonitor pass — short elections cause less than 60 seconds of TTL lag.
On sharded clusters, TTL deletions happen on each shard independently, not through mongos. If a shard is lagging or under high write load, TTL deletion may fall further behind on that specific shard.
// Audit TTL field for type correctness
db.sessions.aggregate([
{
$group: {
_id: { $type: '$expiresAt' },
count: { $sum: 1 }
}
}
]);
// Expected: only 'date' type documents
// If you see 'string', 'null', 'missing' — those documents will NEVER be deleted
// Enforce correct type with validation
db.runCommand({
collMod: 'sessions',
validator: {
$jsonSchema: {
required: ['expiresAt'],
properties: {
expiresAt: { bsonType: 'date' }
}
}
}
});
// Verify TTL index definition
db.sessions.getIndexes().filter(i => i.expireAfterSeconds !== undefined);
// Confirm expireAfterSeconds value and field name
A healthcare SaaS platform we support had a sessions collection with 40% of documents missing the expiresAt field entirely — they had been inserted by a legacy API that predated the session expiry feature. Adding the $jsonSchema validator with required: [‘expiresAt’] caught all new non-conforming inserts, and a one-time backfill script added the field to existing documents, reducing the collection size by 35% in the next TTLMonitor cycle.
Choosing Between Sparse, Partial, and TTL
Use a sparse index when: the indexed field is optional, present on a small fraction of documents, and the field is never set to null (only absent). For any new deployment on MongoDB 3.2+, prefer partial indexes over sparse — partial indexes are the safer, more expressive default.
Use a partial index when: you need precise control over which documents are indexed using any filter expression, or when null values are possible and must be explicitly excluded. The hot-data pattern (index only active-state documents) is the highest-value application of partial indexes.
Use a TTL index when: documents have a natural expiration based on a date field and automated deletion is preferable to application-managed cleanup. TTL indexes are not appropriate for data that needs to be archived rather than deleted, or for collections where deletion timing must be precise to the second — the 60-second TTLMonitor cycle makes sub-minute precision unreliable.
Never use sparse indexes where hint() is in the application codebase without explicit correctness verification. Never use TTL on fields that may contain non-Date values without first enforcing type validation.
Key Takeaways
- Sparse indexes exclude documents where the indexed field is absent or null — a safe choice for truly optional fields, but they can return incorrect results if misused with sort-only queries or forced via hint().
- Partial indexes are more expressive and safer than sparse indexes: the filter expression explicitly defines which documents are indexed, eliminating null-value ambiguity that trips up sparse indexes.
- For the planner to select a partial index, the query predicate must logically subsume the partialFilterExpression — queries missing the filter condition will not use the index and fall back to a full scan.
- TTL indexes delete documents via the background TTLMonitor thread (default 60-second interval) — deletion is approximate, not guaranteed to occur exactly at expiry time.
- TTL fields must be of BSON Date type; string, integer, or null values silently prevent deletion with no error or warning — validate TTL field types at both the application and database level.
- Monitor db.adminCommand({ serverStatus:1 }).metrics.ttl for deletedDocuments per passes ratio to detect TTLMonitor lag before it becomes a storage capacity problem.
- The hot-data partial index pattern — indexing only active-state documents — frequently produces indexes 10-20x smaller than full collection indexes while serving the same query workload.
How MinervaDB Can Help
At MinervaDB, we design specialized index architectures — sparse, partial, and TTL — tailored to your collection growth patterns and query workload. Our MongoDB consulting engagements include TTL lag analysis, partial filter expression audits, array size projection, and index size forecasting that prevents capacity surprises. We provide 24/7 MongoDB support, MongoDB remote DBA services, and hands-on performance engineering for production clusters of all sizes.
If your cluster is struggling with bloated indexes, unexpected TTL deletion backlogs, or sparse index correctness concerns, reach out to the MinervaDB team for a targeted index architecture review. Visit MinervaDB to learn more.
Frequently Asked Questions
Q: Can a partial index also be a TTL index?
No. TTL indexes have strict requirements: they must be single-field indexes on a Date field. The TTL and partial index options cannot be combined in the same index definition. If you need TTL behavior with document filtering, implement a scheduled delete job that applies both the date filter and your custom filter condition.
Q: Does a sparse index support unique constraints?
Yes — a sparse unique index enforces uniqueness among documents that have the indexed field while allowing multiple documents to have the field absent. Two documents can both lack the field without violating the unique constraint. This is useful for optional unique identifiers like external API keys or OAuth tokens.
Q: What happens to TTL deletions during a replica set election?
TTL deletions only run on the primary. During an election, the TTLMonitor on the new primary resumes processing after the election completes. Documents that should have expired during the election window are deleted in the next TTLMonitor pass. Short elections (under 15 seconds) typically cause less than 60 seconds of TTL lag — within normal operational tolerance.
Q: Can I change the expireAfterSeconds value on an existing TTL index?
Yes, using the collMod command. The change takes effect on the next TTLMonitor pass. You cannot change the indexed field without dropping and recreating the index — which creates a deletion gap during the rebuild if documents expire during index creation. Plan TTL index changes during low-write periods.
Q: Are partial indexes replicated to secondaries?
Yes. The index definition, including the partialFilterExpression, is written to the oplog during createIndex and replicated to all replica set members. All members maintain identical index structures, and the partialFilterExpression is evaluated on each member independently during writes — ensuring consistent index coverage across the replica set.
Sources:
MongoDB Sparse Indexes Documentation
