Read-Heavy vs Write-Heavy Indexing

Module: Optimize Azure Cosmos DB Solution

Lesson: Indexing Strategy - Read-Heavy vs Write-Heavy Workloads

Introduction: Why Indexing Strategy Defines Your Cosmos DB Performance

When you deploy a solution on Azure Cosmos DB, you are essentially building on top of a highly distributed, multi-model database system that relies heavily on its internal indexing engine. Unlike traditional relational databases where you might manually define indexes to speed up specific queries, Cosmos DB indexes every single property of every document by default. While this "index everything" approach makes it incredibly easy to get started and run ad-hoc queries, it is rarely the most efficient path for production environments operating at scale.

Understanding the balance between read-heavy and write-heavy indexing strategies is the difference between a system that remains responsive under load and one that incurs unnecessary costs while suffering from latency spikes. In a read-heavy workload, your goal is to minimize the Request Units (RUs) consumed by query operations by ensuring the engine has precisely the right data structures to find your records. Conversely, in a write-heavy workload, your objective is to minimize the overhead of updating those indexes every time a document is created, updated, or deleted.

This lesson explores how to configure indexing policies to align with these two fundamental workload patterns. By moving away from the default indexing policy, you can significantly reduce your RU consumption and lower your monthly cloud bill while maintaining the performance levels your application requires.

Understanding the Cosmos DB Indexing Engine

To master indexing, you must first understand what actually happens under the hood. Cosmos DB uses an inverted index structure. Every property path in your JSON document is stored in a way that allows the engine to quickly locate documents containing specific values. When you write a document, Cosmos DB must update these internal structures. When you read, it traverses them.

The default indexing policy is "Consistent," meaning indexes are updated synchronously with every write operation. This ensures that every read operation reflects the most recent write. However, this comes at a cost: every index update consumes RUs. If your document has 50 fields and you index all of them, a single write operation incurs the overhead of updating 50 individual index paths.

Callout: The Trade-off Matrix

Workload Type	Primary Goal	Indexing Strategy	RU Impact
Read-Heavy	Minimize Query Latency	Include specific paths; use range indexes	Higher on Writes
Write-Heavy	Maximize Throughput	Exclude unused paths; use lazy/none	Lower on Writes
Balanced	Consistency & Speed	Selective indexing of common filters	Moderate

Optimizing for Read-Heavy Workloads

In a read-heavy scenario, your application spends most of its time searching for data. You might be running complex filters, sorting results, or performing aggregations. To optimize this, your indexing policy must be tailored to include the specific paths used in your WHERE, ORDER BY, and JOIN clauses.

The Strategy of Precision

Instead of indexing everything, you should explicitly define the paths that your application queries. By excluding paths that are never queried, you reduce the write overhead without sacrificing read performance.

Consider an e-commerce catalog application. You frequently query products by category, price, and manufacturer. You rarely query by internal_metadata or legacy_description.

{
    "indexingMode": "consistent",
    "includedPaths": [
        { "path": "/category/?" },
        { "path": "/price/?" },
        { "path": "/manufacturer/?" }
    ],
    "excludedPaths": [
        { "path": "/*" }
    ]
}

In this configuration, we explicitly include the paths we need for our queries. The /* in the excludedPaths tells Cosmos DB to ignore everything else. Note the ? at the end of the path; this indicates a range index, which is required for equality comparisons, inequality comparisons, and order-by clauses.

Best Practices for Read-Heavy Optimization

1. Use Range Indexes for Filters: If you use inequality operators (e.g., > or <), you must ensure the property has a range index. Hash indexes only support equality.
2. Composite Indexes: If your application frequently sorts by multiple properties or filters by a combination of fields, composite indexes are essential. They allow the engine to evaluate multiple conditions in a single pass.
3. Monitor Query Metrics: Always use the x-ms-documentdb-query-metrics header to see how many RUs your queries consume. If you see high "index hit" costs, your indexing strategy might need refinement.

Note: When you change an indexing policy, Cosmos DB performs an "online index transformation." This process happens in the background and does not take your database offline, but it does consume RUs while it runs. Monitor your RU consumption during these transitions.

Optimizing for Write-Heavy Workloads

Write-heavy workloads are common in IoT telemetry, logging systems, and high-frequency event ingestion. In these scenarios, the cost of indexing can become the primary bottleneck for your throughput. Every write operation must wait for the index to be updated. If you are writing 10,000 events per second, the overhead of updating 20 indexes per document can lead to significant RU spikes and latency.

Reducing the Indexing Footprint

If your write-heavy workload only needs to retrieve data by id (Point Reads), you don't need secondary indexes at all. Point reads (reading a document by its ID and Partition Key) do not require secondary indexes.

You can set your indexing policy to "None" if you only perform point reads:

{
    "indexingMode": "none"
}

If you still need to query by a few specific fields, you should keep your index as lean as possible. Every excluded path is a win for write throughput.

Step-by-Step: Configuring a Lean Index

1. Analyze Query Patterns: Use the Azure Portal or diagnostic logs to identify which fields are actually used in WHERE clauses.
2. Audit the Document Schema: Identify large fields (like blobs or long descriptions) that are never queried. These should always be excluded.
3. Update the Index Policy: Apply a policy that includes only the bare minimum fields.
4. Test Throughput: Use a load testing tool to measure the number of writes per second before and after the change.

Warning: Setting your indexing mode to "none" is a permanent decision for your queries. If you change your mind later and need to query by properties other than the ID, you will have to re-index the entire collection, which can be an expensive and time-consuming process for large datasets.

Composite Indexes: The Advanced Performance Lever

Composite indexes are a powerful tool for complex queries. They allow you to define an index on multiple paths, which significantly speeds up queries that filter by multiple fields or sort by multiple fields.

Imagine a query: SELECT * FROM c WHERE c.status = 'active' ORDER BY c.timestamp DESC. Without a composite index, Cosmos DB might have to load many documents into memory to sort them. With a composite index, the results are already ordered.

"compositeIndexes": [
    [
        { "path": "/status", "order": "ascending" },
        { "path": "/timestamp", "order": "descending" }
    ]
]

When to use Composite Indexes

• Multi-Property Sorts: When your ORDER BY clause contains more than one property.
• Filter + Sort: When you filter by one property and sort by another.
• Complex Filters: When your query uses multiple properties in the WHERE clause that are highly selective.

Common Pitfalls and How to Avoid Them

Even experienced architects fall into traps when managing Cosmos DB indexes. Here are the most frequent mistakes:

1. Over-Indexing

The most common mistake is sticking with the default "Index Everything" policy for massive datasets. Over-indexing increases storage costs and significantly slows down write performance.

• How to avoid: Periodically review your query logs. If you see queries that aren't being used, remove them from your indexing policy.

2. Ignoring Range Indexes for Inequality Queries

Developers often assume that because a field is indexed, it will work for any query. However, if a field is only indexed with a hash index, a query using a greater-than (>) or less-than (<) operator will fail or result in a full collection scan.

• How to avoid: Always use range indexes for fields that are part of range queries, sorting, or grouping.

3. Forgetting the Partition Key

While the partition key is indexed by default, it is important to remember that it is the most critical part of your query strategy. If your query does not include the partition key, it becomes a "cross-partition query," which is significantly more expensive.

• How to avoid: Design your schema so that your most frequent queries always include the partition key in the WHERE clause.

Callout: The "Hidden" Cost of Indexing

Many developers view storage costs as separate from RU costs. However, index storage is billed as part of your total database storage. Furthermore, every time an index is updated, that write operation consumes RUs. For high-volume systems, the RU cost of indexing is often higher than the RU cost of the data write itself.

Practical Implementation: A Scenario-Based Comparison

To illustrate the difference, let’s look at a hypothetical scenario: A sensor data collection system.

The Workload:

• Ingestion: 500 documents per second (IoT sensor data).
• Reads: Occasional dashboard queries (e.g., "Show me the last 10 readings for Sensor X").
• Data Structure: { "sensorId": "S1", "timestamp": 1625, "value": 23.5, "metadata": { "firmware": "1.0", "location": "Warehouse A" } }

Scenario A: Default Indexing (The "Easy" Way)

If you leave this at default, Cosmos DB indexes every field, including the nested metadata. Every write consumes roughly 3-4 RUs.

• Total Daily RUs: 500 writes/sec * 3 RUs * 86,400 seconds = ~129 million RUs.

Scenario B: Optimized Indexing (The "Efficient" Way)

We realize we only ever query by sensorId and timestamp. We exclude the entire metadata object and value field.

• New Indexing Policy:

{
    "includedPaths": [
        { "path": "/sensorId/?" },
        { "path": "/timestamp/?" }
    ],
    "excludedPaths": [
        { "path": "/metadata/*" },
        { "path": "/value/?" }
    ]
}

• Result: By excluding the bulk of the document, the write cost drops to ~1.5 RUs.
• Total Daily RUs: 500 writes/sec * 1.5 RUs * 86,400 seconds = ~64.8 million RUs.

You have effectively cut your database costs by 50% simply by changing the indexing policy. This is the power of a deliberate indexing strategy.

Step-by-Step: Updating Your Indexing Policy

You can update your indexing policy through the Azure Portal, the Azure CLI, or the SDK. Here is the process using the Azure CLI, which is often the most reliable method for production environments.

1. Create a JSON file named indexingPolicy.json containing your desired configuration.
2. Verify the policy syntax. Use a JSON validator to ensure your paths and structure are correct.
3. Apply the policy using the Azure CLI:

az cosmosdb sql container update \
    --resource-group MyResourceGroup \
    --account-name MyCosmosAccount \
    --database-name MyDatabase \
    --name MyContainer \
    --indexing-policy @indexingPolicy.json

4. Monitor the progress. You can check the status of the transformation in the Azure Portal under the "Scale & Settings" tab of your container. Cosmos DB will show you the progress percentage of the index update.

Managing Indexing Through the Life Cycle of an Application

Indexing is not a "set it and forget it" task. As your application evolves, your query patterns will change. You must treat your indexing policy as part of your infrastructure-as-code (IaC).

• Development Phase: Keep the default policy to allow for rapid development and ad-hoc testing.
• Staging/Performance Testing: Identify the actual queries the application will run. Build the indexing policy based on these queries.
• Production Deployment: Apply the optimized policy.
• Monitoring Phase: Use Azure Monitor to track query performance. If a specific query starts taking too long, analyze the execution plan and determine if a new composite index is needed.

Handling Large-Scale Changes

When you have terabytes of data, changing an indexing policy can be a long process. The index transformation happens in the background, but it is throttled to ensure your production traffic is not impacted. If you need to make a massive change, consider the following:

• Create a new collection: Sometimes it is faster and safer to create a new collection with the correct indexing policy and use a migration tool (like Azure Data Factory or a custom script) to move the data.
• Staggered updates: If you have multiple containers, update them one by one rather than all at once.

Summary Table: Indexing Configuration Guide

Key Takeaways

1. Default is not optimal: The default "Index Everything" policy is convenient but rarely efficient for production workloads. Always tailor your indexing policy to your actual query patterns.
2. Write-Heavy = Less Indexing: If your system is write-heavy, minimize the number of indexed fields to reduce RU consumption and latency. Focus on indexing only what is required for Point Reads or essential filters.
3. Read-Heavy = Precision Indexing: For read-heavy systems, prioritize range indexes for filters and composite indexes for multi-field sorts to minimize query RU costs.
4. Composite Indexes are powerful: When queries involve multiple WHERE or ORDER BY conditions, composite indexes are the most effective way to optimize performance.
5. Monitor the RU cost: Use the query metrics headers to verify your indexing decisions. If a query consumes more RUs than expected, check if the index is being effectively utilized.
6. Infrastructure as Code: Treat your indexing policy like your application code. Keep it in version control and automate its deployment through your CI/CD pipeline.
7. Transformation awareness: Remember that changing an indexing policy triggers an online transformation. Plan for this during off-peak hours if you are working with very large datasets.

By mastering these indexing strategies, you move from treating Cosmos DB as a "black box" to treating it as a precision instrument. You gain control over your application's performance profile, ensuring that your solution remains scalable, cost-effective, and reliable as your data grows. Always prioritize the path of least resistance for your most frequent operations, and do not be afraid to prune your index policy to keep the system lean.

Frequently Asked Questions (FAQ)

Q: Does excluding a path from the index prevent me from querying it? A: No, you can still query by that property, but it will result in a "full collection scan." This means Cosmos DB must load every document in the collection to check the value of that property, which is extremely expensive in terms of RUs and slow for large datasets.

Q: How do I know if my query is using an index? A: Look at the x-ms-documentdb-query-metrics response header. It will contain details on the index hit ratio and the cost of the query. If you see high "index lookup" costs, your query is likely inefficient.

Q: Can I index nested JSON properties? A: Yes. You can define paths like /address/zipcode/? to index nested properties. The syntax follows the standard JSON path convention.

Q: What is the maximum number of composite indexes I can have? A: There are limits on the number of composite indexes and the number of properties within each composite index (typically 8). Always check the official Azure documentation for the current service limits, as they can change.

Q: Is there any reason to use "Lazy" indexing mode? A: "Lazy" indexing mode was designed to allow writes to proceed faster by delaying index updates. However, it is largely deprecated in favor of "Consistent" indexing, as it can lead to inconsistent query results. It is generally recommended to stick with "Consistent" and optimize your index paths instead.

Q: How do I handle schema-less data where properties change frequently? A: If your schema is highly dynamic, you might consider using a wildcard index (e.g., /*). This indexes everything. While this is the "default" approach, if you know certain properties are never queried, you should still explicitly exclude them to save costs.

By applying these principles, you ensure that your Azure Cosmos DB solution is built on a foundation of performance and efficiency. Remember that every index is a commitment—ensure that the performance gains for your reads outweigh the RU cost of your writes.