Adjusting Database Indexes

Lesson: Mastering Indexing Strategies in Azure Cosmos DB

Introduction: Why Indexing Matters

When working with Azure Cosmos DB, you are interacting with a globally distributed, multi-model database service designed for high-scale applications. One of the most common reasons developers experience performance degradation or unexpected cost spikes in Cosmos DB is a misunderstanding of how the indexing engine works. Unlike traditional relational databases where you might manually define indexes on specific columns to speed up JOIN operations or WHERE clauses, Cosmos DB uses an automatic indexing policy by default. While this "index everything" approach is excellent for getting started and handling unknown query patterns, it can become a significant bottleneck as your data volume grows and your throughput requirements increase.

Indexing is the process of creating a secondary data structure that allows the database engine to locate specific records without scanning the entire collection. In Cosmos DB, every item inserted into a container is automatically indexed. By default, the indexing engine includes every property of your JSON documents. While this makes your queries fast out of the box, it consumes Request Units (RUs)—the currency of Cosmos DB—every time you perform a write operation. Each write requires the engine to update these indexes, which means the more indexes you have, the more expensive your writes become.

Understanding how to tune, restrict, or optimize these indexes is the difference between a high-performing, cost-effective application and one that suffers from high latency and bloated RU consumption. In this lesson, we will explore the mechanics of the Cosmos DB indexing engine, learn how to modify indexing policies, and examine strategies to balance read performance against write efficiency.

The Mechanics of Cosmos DB Indexing

To optimize your database, you must first understand how the index is structured. Cosmos DB uses a B-tree based index for range queries and a hash-based index for equality queries. When you save a JSON document, the engine decomposes the tree structure of your document into a flat representation and creates entries in the index for every path.

The Default Indexing Policy

By default, every container is created with an indexing policy that includes every path (/*) for every data type. It also includes support for range queries for both strings and numbers. This is a "greedy" policy. It ensures that any query you write against your document structure will be served by an index, preventing expensive full-container scans. However, this convenience comes with a cost: storage overhead and increased write latency.

Callout: The Trade-off of Automatic Indexing Automatic indexing is a double-edged sword. It provides immediate query performance for any property in your document, but it creates a "write tax." Every time you insert, replace, or delete a document, the index must be updated. For write-heavy workloads, this can significantly increase your RU usage compared to a more surgical indexing approach.

Indexing Paths

An indexing path is the sequence of keys that leads to a specific value in your JSON document. For example, in a document like {"user": {"name": "Alice"}}, the path is /user/name. You can define how the indexing engine treats these paths by specifying:

• Included Paths: Paths that you want the engine to track.
• Excluded Paths: Paths that you do not need to query, which should be ignored to save resources.

By explicitly defining these paths, you tell the engine exactly what it needs to focus on. If you have a massive document with nested metadata that you never query, you should exclude that path to reduce the index size and improve write performance.

Implementing Custom Indexing Policies

Modifying the indexing policy is done via the Azure Portal, the Azure CLI, or the SDKs. The policy is defined as a JSON document that resides within the container configuration. Let's look at how to construct and apply these policies.

Step-by-Step: Updating the Indexing Policy via Azure Portal

1. Navigate to your Azure Cosmos DB account in the portal.
2. Select the Data Explorer tab from the left-hand menu.
3. Choose your database and then select the specific container you wish to optimize.
4. Click on Settings in the top menu bar.
5. Locate the Indexing Policy tab.
6. Here, you will see the JSON representation of the current policy. You can edit this directly.
7. Once you have made your changes, click Save.

Warning: Modifying an indexing policy is a potentially long-running operation. When you change the policy, Cosmos DB must rebuild the index based on the new rules. During this time, your container will continue to be available, but query performance may fluctuate, and the index rebuild will consume RUs. Always perform these changes during off-peak hours if possible.

Example: A Selective Indexing Policy

Suppose you have a document structure where you only ever query by email and createdDate. You do not need to index the biography or profilePictureUrl fields. Your custom policy would look like this:

{
    "indexingMode": "consistent",
    "automatic": true,
    "includedPaths": [
        {
            "path": "/email/?"
        },
        {
            "path": "/createdDate/?"
        }
    ],
    "excludedPaths": [
        {
            "path": "/*"
        }
    ]
}

In this example, we have set the excludedPaths to the wildcard /*, which means "exclude everything." We then add back specific includedPaths for email and createdDate. The /? suffix indicates that the index should support range queries for those specific properties.

Indexing Modes: Consistent vs. Lazy

Cosmos DB offers two primary indexing modes that dictate how and when index updates occur:

1. Consistent: This is the default. When you perform a write, the index is updated synchronously. Your queries will always return the most up-to-date results. This ensures strong consistency in your query results.
2. None: This disables indexing entirely. You might choose this if you are using Cosmos DB as a simple key-value store where you only ever retrieve documents by their id and partition key. This provides the highest possible write performance because there is no index overhead.

Note: Previously, Cosmos DB supported a "Lazy" indexing mode, but this has been deprecated in favor of Consistent indexing. Always default to Consistent unless you have a specific, high-scale write scenario that requires disabling indexing entirely.

Best Practices for Query Performance

Optimizing indexes is not just about excluding paths; it is about writing queries that the index can actually use. Even with a perfect index, a poorly written query can force a full collection scan.

1. Avoid Functions in the WHERE Clause

When you use a system function in your WHERE clause, the indexing engine often cannot use the index for that property. For example, if you have an index on /name and you write: SELECT * FROM c WHERE UPPER(c.name) = 'ALICE' The database engine must scan every document to apply the UPPER function before checking for equality. Instead, store the name in uppercase in your document and query against that field directly.

2. Use the Partition Key

The partition key is the most critical component of a Cosmos DB query. If your query includes the partition key in the filter, the engine can route the query directly to the relevant physical partition. If you omit the partition key, the query becomes a "cross-partition" query, which is significantly more expensive and slower. Always include the partition key in your WHERE clause whenever possible.

3. Favor Equality over Range Queries where Possible

Equality queries (using =) are generally faster and cheaper than range queries (using >, <, BETWEEN). If your business logic allows for equality lookups, structure your documents to support them. If you must use range queries, ensure that you have explicitly enabled range indexing for that specific path in your indexing policy.

4. Indexing for Sorting

If you frequently use ORDER BY in your queries, you must ensure that the property you are sorting by is included in the index. Furthermore, if you are sorting by multiple properties, you may need to implement a Composite Index.

Advanced Technique: Composite Indexes

A composite index is required when your query filters by multiple properties or sorts by multiple properties. While individual indexes are helpful, they are not sufficient for multi-property queries.

For example, consider this query: SELECT * FROM c WHERE c.category = 'Electronics' ORDER BY c.price DESC

To optimize this, you need a composite index that covers both category and price. Without it, the engine has to retrieve all documents matching "Electronics," then sort them in memory, which is highly inefficient.

Defining a Composite Index

You add composite indexes in the compositeIndexes section of your indexing policy JSON:

"compositeIndexes": [
    [
        {
            "path": "/category",
            "order": "ascending"
        },
        {
            "path": "/price",
            "order": "descending"
        }
    ]
]

This configuration tells the engine to pre-sort the data based on these two fields. Now, when your query runs, the database can retrieve the data already in the correct order, drastically reducing the RU cost and latency.

Common Pitfalls and How to Avoid Them

1. The "Everything is Indexed" Trap

Many developers leave the default policy in place for years. As the document size grows, the index becomes massive, consuming significant RU budget on every write.

• Fix: Audit your queries using the Query Metrics in the Azure Portal. If you see a query that is not using the index (or performing a full scan), add the necessary path. If you have paths that are never queried, remove them.

2. Oversized Indexing Policies

Adding too many composite indexes can also be problematic. While they make queries faster, they increase the storage cost and the complexity of the write path.

• Fix: Only create composite indexes for the most critical, high-frequency queries. Use the "Query Stats" feature in the Portal to identify which queries are consuming the most RUs and target those specifically.

3. Ignoring the Partition Key

This is the most frequent mistake. A query that ignores the partition key is a "fan-out" query. It must hit every physical shard in your cluster, collect the results, and aggregate them.

• Fix: Re-evaluate your container design. If your queries frequently need to aggregate data across partitions, you might be using the wrong partition key.

4. Forgetting to Re-index

When you change an indexing policy, the background index rebuild process starts. If you have a multi-terabyte collection, this can take a long time.

• Fix: Always check the status of your indexing progress. You can monitor this in the Azure Portal under the "Indexing Policy" tab. Do not assume that your new policy is fully active the second you click save.

Comparison: Indexing Strategies

Practical Example: Optimizing a Shopping Cart

Imagine a shopping cart container. The documents look like this:

{
    "id": "cart123",
    "userId": "userA",
    "items": [...],
    "lastUpdated": "2023-10-01T10:00:00Z",
    "status": "active"
}

If your application frequently runs a query like: SELECT * FROM c WHERE c.userId = 'userA' AND c.status = 'active' ORDER BY c.lastUpdated DESC

The Optimization Steps:

1. Partition Key: Ensure userId is the partition key.
2. Composite Index: Create a composite index for (status, lastUpdated).
3. Selective Paths: Exclude the items array from the index if you never perform a WHERE clause check on the contents of the cart. This will significantly reduce index size if the carts are large.

By making these changes, you transform a cross-partition, expensive sort operation into a targeted, indexed, and pre-sorted retrieval.

Summary of Best Practices for Production

• Start with the default, then prune: It is safer to start with the default index and disable paths you don't need than to start with a blank slate and guess what you might need later.
• Monitor RU Consumption: Use the Azure Monitor metrics to track RU usage per request. If you see a sudden spike in RUs for a specific query, check if it is performing a full scan.
• Use the SDK for Testing: When testing new indexing policies, use the Cosmos DB SDK to run your queries and inspect the RequestCharge property in the response headers. This gives you exact data on how your changes affect cost.
• Documentation: Keep a record of why specific composite indexes exist. If a developer removes a "useless-looking" index later, they might inadvertently tank the performance of a critical report.
• Automation: Use Infrastructure as Code (IaC) tools like Bicep or Terraform to manage your indexing policies. This ensures your production environment remains consistent and reproducible.

Key Takeaways

1. Indexing is a Trade-off: Every index you add makes reads faster but makes writes more expensive. Always balance these two needs based on your application's specific workload.
2. Default Policy is Greedy: The default indexing policy covers everything. This is great for flexibility but usually inefficient for large-scale production workloads.
3. Exclude Unnecessary Paths: If you never query a field, exclude it from the indexing policy. This reduces the index size and lowers the RU cost of write operations.
4. Composite Indexes are Power Tools: Use them for queries that filter or sort by multiple fields. They are essential for performance but should be used sparingly to avoid excessive write overhead.
5. Partition Key is King: No amount of indexing can compensate for a missing partition key. Always include the partition key in your queries to avoid cross-partition scans.
6. Background Rebuilds: Be aware that changing an indexing policy triggers a background rebuild. Monitor the progress to ensure the new policy is fully applied before expecting performance gains.
7. Audit Regularly: Indexing requirements change as your application evolves. Periodically review your query metrics to see if your indexing policy still aligns with your actual query patterns.

Frequently Asked Questions (FAQ)

Q: Can I index an array? A: Yes, Cosmos DB allows you to index arrays. If you have an array of tags, you can index the individual elements to support queries like SELECT * FROM c WHERE 'Electronics' IN c.tags.

Q: What happens if I make a mistake in my indexing policy? A: If you exclude a path that you later need to query, your queries will simply stop working (they will return no results or require a full scan). You can always add the path back to the policy, and the index will rebuild.

Q: Do I need to index the id field? A: The id field and the partition key are indexed by default and cannot be removed from the index. This ensures that lookups by ID and partition key are always fast.

Q: How do I know if my query is using an index? A: You can use the "Query Stats" in the Azure Portal. Look for "Index Utilization" metrics. If the index utilization is low or zero, your query is likely performing a full scan, and you need to investigate your indexing policy or query structure.

Q: Does indexing affect the cost of read operations? A: Yes, efficient indexing lowers the cost of read operations by reducing the number of documents the engine must scan. A well-indexed query will consume fewer RUs than a query that requires a full collection scan.