Consistent Query Performance in Cosmos DB via Automatic Indexing

Unpredictable Latency from Missing Indexes? Use Automatic Indexing.

The primary operational headache in NoSQL databases is query latency spiking because a required filter field lacks an index, forcing a costly full container scan. The useful takeaway is that enabling Cosmos DB's automatic indexing provides a consistent, low-latency query experience without requiring any application code changes, letting the service manage index maintenance.

Default Indexing Behavior

By default, Cosmos DB operates in consistent indexing mode. This mode automatically builds a composite index for nearly every property within a container, unless that property is explicitly excluded in the index policy. This provides broad query coverage but increases index size.

Enabling Automatic Indexing

The automatic indexing feature is a toggle within the container’s index policy. Setting this to true delegates index creation and upkeep entirely to the service. This shifts the burden from manual policy management to configuration.

Worked Example: Product Container Optimization

Consider a Product container queried on price and category. If manual indexing only covered these fields, a query on a third field, say stock, would scan everything.

Enabling automatic indexing ensures the service indexes stock as well. A representative comparison of query estimates illustrates the benefit:

Query	RU Estimate (Manual)	RU Estimate (Automatic)
Filter by Price & Category	150	80

This reduction in estimated Request Units (RU) directly translates to lower operational cost and more predictable latency.

Trade-off: Storage vs. Write Latency

The trade-off is clear: automatic indexing increases storage consumption proportional to the number of indexed fields. Furthermore, during large data ingestion, write operations may experience brief latency spikes as the service synchronizes the new data across all generated indexes. Never rely solely on automatic indexing; review your query patterns to use the index policy's excludedPaths to prune unnecessary indexing and manage storage costs.

Verification and Action

To verify the setting, check the container's index policy via the Azure portal or the REST API. To enable it using Azure CLI (requires appropriate permissions):

az cosmosdb sql container index-policy update \n  --account-name  \n  --resource-group  \n  --database-name  \n  --container-name  \n  --index-policy '{\"indexingMode\":\"consistent\",\"automatic\":true,\"includedPaths\":[{\"path\":\"/\"}], \"excludedPaths\":[]}'

Check the result by running a representative query with the explain trace flag and comparing the returned requestUnits estimate against pre-change metrics. Monitor storage and indexing metrics in Azure Monitor over a week.