Index Type Selection

Module: Optimize Azure Cosmos DB Solution

Lesson: Index Type Selection

Introduction: The Hidden Engine of Database Performance

When you first start working with Azure Cosmos DB, it is easy to view it as a "black box" where you simply push JSON documents in and retrieve them later. However, as your data volume grows and your application demand increases, the way Cosmos DB organizes and retrieves that data becomes the primary factor in your system’s latency and cost. At the heart of this organization lies the Indexing Strategy. Specifically, the choice of index type determines how the database engine traverses your data to satisfy queries.

Choosing the right index type is not just a technical detail; it is a fundamental architectural decision. If you index too much, you increase the Request Unit (RU) cost of every write operation because the database must update the index for every modification. If you index too little, your read queries will perform full scans of your collection, leading to high latency and massive spikes in RU consumption. This lesson focuses on the nuances of index type selection, helping you balance the trade-off between write-heavy workloads and read-intensive requirements.

Understanding the Default Indexing Policy

By default, Azure Cosmos DB indexes every single property within every document you insert. This "everything-indexed" approach is excellent for developers who are just starting or building prototypes because it allows you to run almost any query without prior configuration. However, in a production environment, this is rarely the most efficient path.

When the database engine receives a document, it creates an inverted index for each property. An inverted index is a mapping from content (the value of a field) to its location (the document ID). Because Cosmos DB stores these indexes in a way that supports efficient range scans and equality filters, it is quite powerful. Yet, the cost of maintaining this index for every single field—including large strings or deeply nested objects—can become prohibitive. As you scale, you must move from the default policy to a customized policy that reflects the actual query patterns of your application.

The Three Pillars of Indexing: Types and Modes

Before we dive into the specific index types, we must distinguish between the Indexing Mode and the Index Type. The Indexing Mode tells the system how to update the index, while the Index Type tells the system how to store the data for a specific field.

Indexing Modes

• Consistent: Updates to the index happen synchronously with the write operation. This ensures that a query immediately reflects the most recent write. This is the standard for most transactional applications.
• Lazy: Updates to the index happen asynchronously when the system has spare capacity. While this lowers the write cost, it risks returning stale data, as the index might not include the latest changes at the moment a query is executed.

Callout: Consistent vs. Lazy Indexing In modern production environments, the use of 'Lazy' indexing is extremely rare. Because distributed systems require strict consistency for many business processes, 'Consistent' is the default and recommended mode. Only consider 'Lazy' indexing if you are dealing with a non-critical analytical workload where eventual consistency is acceptable and you must prioritize minimizing write latency at all costs.

Index Types

Azure Cosmos DB provides three primary index types that you can apply to your paths:

1. Hash: This index type is used for equality comparisons. If your queries frequently use WHERE c.customerId = '123', a hash index is highly efficient. It provides a constant-time lookup for equality.
2. Range: This is the most versatile index type. It supports equality comparisons, range comparisons (>, <, >=), and ORDER BY operations. If you need to sort data or filter by a range of dates or numbers, this is the mandatory choice.
3. Spatial: Used specifically for geospatial data. It allows you to query based on proximity, distance, or containment within shapes (e.g., "Find all stores within 5 miles of this coordinate").

Practical Application: Selecting the Right Type

To select the right index, you must perform a query analysis. Start by listing all the queries your application executes against a specific container. Categorize them by their filter criteria.

• Scenario A: High-Concurrency Lookups. If your application is a user profile service where the most common query is retrieving a user by their userId, you should use a Hash index on the userId field. This minimizes the index size and the overhead of maintaining a range index.
• Scenario B: Time-Series Data. If you are storing IoT sensor data, you are likely filtering by timestamp and device ID. You will need a Range index on the timestamp field to support queries like SELECT * FROM c WHERE c.timestamp > '2023-01-01'.
• Scenario C: Geospatial Tracking. If you are building a delivery app, you need a Spatial index on the location field to perform proximity searches like ST_DISTANCE.

Code Example: Defining a Custom Indexing Policy

You define your indexing policy in the IndexingPolicy object within your container configuration. Below is an example of how to implement a selective policy using the Azure SDK for .NET.

// Define the container properties
ContainerProperties containerProperties = new ContainerProperties
{
    Id = "OrdersContainer",
    PartitionKeyPath = "/customerId",
    IndexingPolicy = new IndexingPolicy
    {
        IndexingMode = IndexingMode.Consistent,
        Automatic = true,
        IncludedPaths =
        {
            new IncludedPath { Path = "/orderDate/?" }, // Range index for dates
            new IncludedPath { Path = "/customerId/?" } // Hash index for equality
        },
        ExcludedPaths =
        {
            new ExcludedPath { Path = "/*" } // Exclude everything else by default
        }
    }
};

// Apply the policy during container creation
await database.CreateContainerIfNotExistsAsync(containerProperties);

Explanation of the code:

1. ExcludedPaths: We set /* to exclude all properties by default. This is the "Opt-in" strategy, which is the industry standard for optimizing performance and cost.
2. IncludedPaths: We explicitly define the properties we want to index. The /? suffix indicates that we are indexing the value at that path. By default, Cosmos DB applies a range index for strings and numbers.
3. Efficiency: By excluding everything except the fields we actually query, we significantly reduce the amount of storage and compute required for every write operation.

Deep Dive: The Impact of Indexing on RUs

Request Units (RUs) are the currency of Cosmos DB. Every write operation incurs a cost based on the size of the document and the number of indexes that need to be updated. When you have a complex document with many fields, the default "index everything" policy can double or triple the write cost compared to an optimized policy.

Consider a document that contains a large description field or a large tags array. If you index these fields, the database engine must generate index entries for every word or tag in those fields. This results in an enormous index size and slow writes. By excluding these large fields from your indexing policy, you can lower your write RU cost significantly.

Callout: The "Index Everything" Trap Many developers assume that more indexing means faster queries. While this is true for read operations, it creates a massive "Write Tax." Always perform a query audit before moving to production. If a field is never used in a WHERE clause, a JOIN, or an ORDER BY clause, it should be excluded from your index.

Advanced Indexing Strategies

As your application matures, you might encounter scenarios where simple hash or range indexes are insufficient.

Composite Indexes

Composite indexes are required when you have queries that filter by multiple properties or sort by multiple properties. For example, if you frequently run SELECT * FROM c WHERE c.category = 'Electronics' ORDER BY c.price DESC, a single range index on category and a single range index on price will not be enough to optimize the sort. You must create a composite index that covers both.

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

Spatial Indexes

Spatial indexes are configured differently. You must specify the type as Spatial and define the geometry type (Point, LineString, Polygon, or MultiPolygon).

"includedPaths": [
    {
        "path": "/location/?",
        "indexes": [
            { "dataType": "Point", "kind": "Spatial" }
        ]
    }
]

Common Pitfalls and How to Avoid Them

Even experienced architects fall into common traps when managing indexing policies. Here are the most frequent mistakes:

1. Indexing Large Strings: Never index long text fields (like user comments or blog posts) with a range index. These strings can be thousands of characters long. Indexing them will lead to high RU costs and potential errors if the index entry size limit is exceeded.
2. Ignoring the Partition Key: The partition key is automatically indexed. Do not try to manually add it to your includedPaths. Doing so is redundant and adds unnecessary configuration overhead.
3. Forgetting to Update the Policy: As your application evolves, your query patterns will change. A query that was once rare might become the most frequent. You must regularly review your query logs and update your indexing policy to match current usage.
4. Over-indexing Arrays: If you have an array of objects, indexing the entire path can create a massive number of index entries. Be precise about which sub-properties within an array you need to index.

Comparison Table: Index Selection Guide

Step-by-Step: Updating an Indexing Policy

Updating an indexing policy is an online operation. Cosmos DB will transform the index in the background without requiring downtime. However, for very large collections, this transformation can consume a significant amount of RUs.

1. Analyze current queries: Use the Azure Portal or the QueryStats output in your SDK to identify which fields are being filtered.
2. Draft the new policy: Use the JSON structure shown in the code examples above.
3. Test in a development environment: Always verify the impact of the new policy on a staging collection before applying it to production.
4. Apply the policy: Use the Azure SDK or CLI to update the container properties.
   • CLI Command: az cosmosdb sql container update --indexing-policy @policy.json ...
5. Monitor the progress: Use the Azure Portal to check the "Indexing Progress" metric.
6. Verify performance: Once the transformation is complete, check the RU cost of your queries to confirm the improvements.

Note: When updating an indexing policy on a container with millions of documents, the transformation process will consume additional RUs. It is best practice to perform these updates during periods of low traffic to avoid impacting your application's responsiveness.

Best Practices for Long-Term Success

To ensure your indexing strategy remains effective, follow these industry-standard practices:

• The "Opt-in" Philosophy: Always start with an empty index and add only the paths you absolutely need. This keeps your database lean and your write costs predictable.
• Monitor Index Utilization: Use Azure Monitor to track the RUs consumed by indexing. If your write RUs are significantly higher than your read RUs, your indexing policy is likely too aggressive.
• Use Documentation for Policy Changes: Treat your indexing policy as infrastructure-as-code. Store your policy JSON files in a version control system like Git. This allows you to track why certain indexes were added and provides a rollback path if an update causes unexpected issues.
• Be Careful with Wildcards: While the /* wildcard is convenient, it is the enemy of optimization. Use it only during initial development or for very small, non-critical collections.
• Leverage TTL with Indexing: If you are using Time-to-Live (TTL) to automatically expire documents, ensure that the property used for TTL is indexed if you ever need to query against it.

Common Questions (FAQ)

Q: Can I index a property that is deep inside an object? A: Yes, you can use path syntax like /user/address/zipCode/?. This will index the zip code regardless of how deep it is nested in the JSON.

Q: What happens if I make a mistake in my indexing policy? A: If you exclude a field that your application needs, your queries will simply be slower because they will perform a full scan. You can fix this by updating the policy to include the field again.

Q: Are there limits to how many indexes I can have? A: While there is no hard limit on the number of paths, there is a limit on the total size of the index per document. If you index too many fields, you will hit this limit and receive an error when trying to save a document.

Q: Does indexing affect the storage cost? A: Yes. The index is stored as data. A highly complex indexing policy can increase the total storage size of your container by a significant percentage.

Key Takeaways

1. Indexing is a balance: There is a direct trade-off between read performance and write cost. Every index you add makes reads faster but writes more expensive.
2. Default is not optimal: Never use the default "index everything" policy for production applications. Use an "opt-in" approach where you explicitly define only the paths required for your queries.
3. Select the right type: Use Hash for equality, Range for comparisons and sorting, and Spatial for geographical data. Misusing these types will lead to inefficient query execution.
4. Use Composite Indexes wisely: If your queries filter or sort by multiple fields, a composite index is essential for performance. Do not rely on the engine to merge multiple single-field indexes.
5. Monitor and adapt: Your indexing policy is not a "set and forget" configuration. As your application queries evolve, your indexing policy must evolve with them. Use metrics and monitoring to keep your RU consumption in check.
6. Document your changes: Keep your indexing policies in source control. This ensures that your team understands why certain indexes exist and allows for easier troubleshooting when performance issues arise.
7. Avoid over-indexing: Resist the urge to index every single field just in case you might need it later. Only index what you are actively using in your WHERE or ORDER BY clauses to maintain high write throughput.

By following these principles, you ensure that your Azure Cosmos DB solution remains performant, cost-effective, and scalable. The database engine is only as efficient as the instructions you provide it; by mastering index type selection, you are taking full control of your application's performance.