MongoDB Partial Indexes: Surgical Query Planning for High-Cardinality Sparse Fields

Full collection scans on a 200-million-document collection surface in two distinct failure modes: latency spikes visible in your APM, and silent queue buildup when the slow query holds a read ticket longer than your thread pool tolerates. The instinct is to add an index. The mistake is adding the wrong one.

Partial indexes—indexes built over a filtered document subset—are the least-used lever in MongoDB query planning despite being available since 3.2. The design tradeoff is precise: you trade index completeness for index size, working-set footprint, and write amplification on the hot path. Getting that tradeoff right requires understanding how the query planner selects a partial index, when it refuses to, and what happens at the driver layer in Go when the planner's decision changes under you.

Why Sparse Cardinality Is the Problem Worth Solving

Consider an orders collection where status takes values pending, processing, completed, and cancelled. In a mature system, 97% of documents carry completed or cancelled. Only 3% are in the operationally relevant states. A standard index on status encodes all 200 million entries. Your application queries overwhelmingly touch the 6 million live documents.

The full index costs you on three axes:

1. Working set pressure. MongoDB's WiredTiger cache must hold frequently accessed index pages in memory. A full status index bloated with completed-order keys competes with the BTree pages your application actually traverses.
2. Write amplification. Every insert and every status transition writes to the index regardless of business relevance. A completed order that will never be queried again still pays the write cost.
3. Plan cache pollution. The query planner scores candidate indexes by sampling. If the planner's winning plan involves a full index scan over a status value with poor selectivity, the cached plan degrades all queries sharing that plan cache key until the cache entry expires or gets evicted.

Defining the Partial Index Correctly

The filter expression on a partial index is evaluated at write time, not at query time. A document enters the index when it matches the filter at insert or update; it leaves the index when an update causes it to no longer match.

db.orders.createIndex(
  { customerId: 1, createdAt: -1 },
  {
    partialFilterExpression: {
      status: { $in: ["pending", "processing"] }
    },
    name: "idx_active_orders_by_customer"
  }
)

This index holds only the 6 million live documents. The size reduction is roughly 97%. More importantly, the WiredTiger pages backing this index fit in cache without displacing your hot document pages.

The constraint that trips teams: the query must include the partial filter expression, or a superset of it, as a query predicate for the planner to consider the index eligible. A query that filters only on customerId without constraining status cannot use this index, because the planner cannot guarantee the index covers all matching documents.

Query Planner Eligibility: The Exact Rule

MongoDB's planner checks eligibility by asking whether the query predicate logically implies the partial index filter. The implication must be provable from the query shape alone—the planner does not evaluate actual documents.

This query is eligible:

db.orders.find({
  customerId: "cust_abc",
  status: "pending"
})

This query is not eligible, even though at runtime all results would have pending status:

db.orders.find({
  customerId: "cust_abc",
  createdAt: { $gte: ISODate("2026-01-01") }
})

The planner has no logical proof that all documents with createdAt >= 2026-01-01 carry pending or processing status. The index is skipped. The query falls back to a COLLSCAN or a less selective index.

This has an operational consequence that stings in microservices: a seemingly innocuous query refactor—removing the status filter because "the service only processes active orders anyway"—silently degrades from an index scan to a collection scan. Without explain() output in your observability pipeline, you won't catch it until p99 latency climbs.

Go Driver Integration: Forcing the Filter Into the Query

The Go MongoDB driver (go.mongodb.org/mongo-driver/v2) gives you typed filter construction through bson.D. The discipline here is encoding the partial filter requirement as a typed predicate rather than a runtime string, so a refactor cannot accidentally remove the status constraint without a compile-time signal.

package orders

import (
	"context"
	"time"

	"go.mongodb.org/mongo-driver/v2/bson"
	"go.mongodb.org/mongo-driver/v2/mongo"
	"go.mongodb.org/mongo-driver/v2/mongo/options"
)

type ActiveOrderFilter struct {
	CustomerID string
	Before     time.Time
}

// activeStatuses mirrors the partial index filter expression.
// Changing one without the other is a schema migration, not a code change.
var activeStatuses = bson.A{"pending", "processing"}

func FetchActiveOrders(
	ctx context.Context,
	col *mongo.Collection,
	f ActiveOrderFilter,
	pageSize int64,
	afterId *bson.RawValue,
) ([]bson.Raw, error) {
	filter := bson.D{
		{Key: "customerId", Value: f.CustomerID},
		{Key: "status", Value: bson.D{{Key: "$in", Value: activeStatuses}}},
	}
	if !f.Before.IsZero() {
		filter = append(filter, bson.E{
			Key:   "createdAt",
			Value: bson.D{{Key: "$lt", Value: f.Before}},
		})
	}
	if afterId != nil {
		filter = append(filter, bson.E{Key: "_id", Value: bson.D{{Key: "$lt", Value: *afterId}}})
	}

	opts := options.Find().
		SetSort(bson.D{{Key: "createdAt", Value: -1}}).
		SetLimit(pageSize).
		SetHint("idx_active_orders_by_customer") // explicit hint avoids plan cache thrash

	cursor, err := col.Find(ctx, filter, opts)
	if err != nil {
		return nil, err
	}
	defer cursor.Close(ctx)

	var results []bson.Raw
	if err := cursor.All(ctx, &results); err != nil {
		return nil, err
	}
	return results, nil
}

Three decisions in this code carry weight:

activeStatuses as a package-level variable. It creates a single source of truth. The index definition and the query predicate reference the same Go symbol. When someone adds a reviewing state to the workflow, they update this variable, which immediately surfaces the need to update the index filter expression.

Explicit hint via SetHint. The query planner's plan cache key is derived from the query shape. In a microservice that runs many concurrent queries, plan cache entries compete. An explicit hint bypasses the cache selection entirely and pins the execution strategy. This is appropriate when you've verified with explain() that the plan is correct and you need stability across MongoDB version upgrades that may alter planner heuristics.

Cursor-based pagination over skip. skip on MongoDB performs a document count from the index root; for large offsets this re-traverses index entries you've already passed. Cursor pagination using _id or a sort key avoids that traversal entirely. Combined with a partial index, cursor pagination limits each page fetch to a single BTree descent.

Write Path: The Transition Cost

When an order moves from processing to completed, WiredTiger must remove the document from the partial index. This involves a BTree deletion, which triggers page rebalancing and potentially a checkpoint write. The absolute cost is small per operation; the aggregate cost matters at scale.

The key operational point: write amplification from a partial index is strictly lower than from a full index. Documents that are born completed (bulk imports, for instance) never enter the partial index at all. Documents that transition out of active status pay exactly one index deletion rather than one index update. You're paying less at every write path compared to the full index alternative.

The one case where this inverts: if your transition rate is extremely high—think a trading system where orders move through states in milliseconds—the BTree deletions can cause contention on internal WiredTiger pages. Monitor wiredTiger.cache.pages evicted because they exceeded the in-memory maximum and wiredTiger.concurrentTransactions.write.out under load. Sustained high eviction under write pressure indicates the BTree is churning faster than checkpoints can flush clean pages.

Operational Observability Checklist

Partial indexes introduce a failure mode that standard index monitoring misses: a query silently falls off the partial index when the filter predicate is missing. Build these checks into your pipeline:

• Log slow query fingerprints with their winning plan stage. A COLLSCAN or IXSCAN on the wrong index with executionStats.nReturned far below totalDocsExamined is the signal. Atlas has this built in; self-managed clusters require db.setProfilingLevel(1, { slowms: 100 }) and log aggregation.
• Assert index usage in integration tests. Run explain("executionStats") in your test suite against representative queries and assert the winning plan stage is IXSCAN on the expected index name.
• Track index size in your capacity model. A partial index that grows unexpectedly—because the proportion of active documents grew—deserves the same alerting as collection size.

Decision Framework

Apply a partial index when all three conditions hold:

1. The field has low operational cardinality within a high-cardinality collection. A small fraction of documents represent the live working set your queries target.
2. Queries always constrain the filter field. If any code path queries without the partial filter predicate, that path gets a collection scan. Audit before deploying.
3. The filter expression is stable. Changing the partial filter requires dropping and rebuilding the index, which is a background operation but holds an intent lock during the final step on older versions. Plan the migration.

Avoid a partial index when the query surface is broad and unpredictable, when the "hot" subset changes definition frequently, or when you need the index to support queries from multiple services with different predicate shapes. In those cases, a compound index with high-selectivity prefix fields is the safer choice—more write amplification, more memory, but consistent planner eligibility.