Indexing Policies

Mastering Indexing Policies in Azure Cosmos DB for NoSQL

Introduction: Why Indexing Matters in NoSQL

When you are building applications that require high performance and massive scale, the way your database stores and retrieves data is the single most important factor in your success. Azure Cosmos DB for NoSQL is a globally distributed, multi-model database service that prides itself on low latency and high availability. However, even the fastest database can grind to a halt if it is forced to perform a "full collection scan" every time a query is executed. This is where the indexing policy comes into play.

An indexing policy is the blueprint that tells the Cosmos DB engine how to structure its internal index to satisfy your application's queries. Think of it like the index at the back of a textbook: without it, you would have to read every single page to find a specific term. With it, you can jump straight to the relevant information. In a NoSQL environment where schemas are flexible and data is often hierarchical, indexing is not just about speed; it is about balancing query performance against the cost of write operations and the storage overhead of the index itself.

Understanding indexing policies is critical for any developer working with Azure Cosmos DB. If you do not configure your indexing policy correctly, you might find that your application performs well during early development but encounters significant latency issues or unexpected cost spikes once your data volume grows into the millions or billions of documents. This lesson will guide you through the mechanics of indexing, how to customize it for your specific needs, and how to avoid the common pitfalls that catch many developers off guard.

The Fundamentals of Cosmos DB Indexing

By default, Azure Cosmos DB is "index-everything." When you create a container, the system automatically indexes every property within every document by default. This approach is designed to provide a "just-works" experience for developers who want to get started quickly without worrying about underlying mechanics.

How the Indexing Engine Works

The indexing engine in Cosmos DB creates an inverted index. When you insert a document, the engine traverses the JSON structure, identifies every property, and creates an entry in the index that maps the value of that property to the document ID. This allows the database to perform point reads (looking up a document by its ID) and range queries (finding documents where a value is greater than or less than a certain point) incredibly quickly.

However, this convenience comes at a price. Every write operation—whether it is an insert, update, or delete—requires the database to update the index. If you have a document with hundreds of properties, every update triggers a cascade of index modifications. For write-heavy workloads, this can increase your Request Unit (RU) consumption significantly.

Callout: Indexing vs. Schema-less Nature It is a common misconception that because Cosmos DB is schema-less, it does not need an index. In reality, the schema-less nature of the database makes the index even more vital. Because the database does not know the structure of your data beforehand, the index acts as the primary mechanism for discovering and retrieving data efficiently. Without an index, the database engine would have to perform a full scan of every document in the collection to determine if it matches your criteria, which is prohibitively expensive at scale.

Components of an Indexing Policy

An indexing policy is a JSON document that defines how the database should handle indexing for a specific container. It consists of three primary components: the indexing mode, included paths, and excluded paths.

1. Indexing Mode

The indexing mode determines whether the index is updated synchronously or asynchronously.

• Consistent: The index is updated synchronously as you perform write operations. This ensures that the index is always in sync with the data, meaning your queries will always return the most recent information. This is the default and recommended mode for most applications.
• None: The index is effectively disabled. This is useful if you only ever access documents by their unique ID or partition key, as it eliminates the overhead of index maintenance entirely.

2. Included Paths

Included paths allow you to explicitly define which properties should be indexed. You can use wildcard characters to index all properties, or you can specify exact paths to keep the index lean.

3. Excluded Paths

Excluded paths are the opposite of included paths. They tell the engine to ignore specific properties. This is highly effective for reducing storage costs and write RUs when you have large, nested objects or binary data that you know you will never query against.

Practical Examples of Indexing Policies

Example 1: The Default Policy

The default policy is quite generous. It indexes everything, including range indexing for strings and numbers, which allows for equality, range, and ORDER BY queries.

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

In this policy, the /* wildcard indicates that every property is indexed. The _etag is excluded because it is a system-generated property that is rarely needed for user-facing queries.

Example 2: The Minimalist/Performance Policy

If you are running a high-volume application where you only query by a specific set of fields, you should switch to an "exclude all" strategy. This involves setting the default to excluded and then explicitly adding the paths you need.

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

By switching the logic, you drastically reduce the index size and the cost of write operations, as only userId and orderDate will be maintained in the index.

Step-by-Step: Updating an Indexing Policy

Updating an indexing policy is a background operation. Cosmos DB does not take the container offline; instead, it re-indexes the data in the background.

1. Open the Azure Portal and navigate to your Cosmos DB account.
2. Select the Container you wish to modify.
3. Navigate to the "Settings" tab in the left-hand menu.
4. Click on "Indexing Policy" at the top of the settings page.
5. Edit the JSON directly in the editor provided.
6. Click "Save".

Note: When you update an indexing policy, the container will enter a "re-indexing" state. You can monitor the progress of this operation in the portal. While the re-indexing is happening, your queries might be slower, or some queries might not return full results until the process is complete. Always perform major policy changes during off-peak hours.

Advanced Indexing Configurations

Composite Indexes

Composite indexes are essential for queries that involve multiple properties in the ORDER BY clause or queries that filter by multiple properties. If you have a query like SELECT * FROM c WHERE c.name = 'John' ORDER BY c.age DESC, a single-property index on name or age will not be enough to satisfy the query efficiently.

A composite index combines multiple paths into a single index entry.

"compositeIndexes": [
    [
        { "path": "/name", "order": "ascending" },
        { "path": "/age", "order": "descending" }
    ]
]

Spatial Indexing

If your application deals with geographical data, you need to enable spatial indexing. Without it, queries using built-in spatial functions like ST_DISTANCE or ST_WITHIN will fail or perform poorly.

"spatialIndexes": [
    {
        "path": "/location/*",
        "types": ["Point"]
    }
]

Best Practices for Indexing

To ensure your database remains performant as it scales, follow these industry-standard guidelines:

• Keep the Index Lean: Only index what you actually query. If you have large text fields or binary blobs in your JSON, exclude them from the index.
• Use Composite Indexes Wisely: Only create composite indexes for the specific queries that require them. They increase the storage footprint and the cost of every write operation.
• Monitor RU Consumption: If you see high RU consumption on write operations, investigate your indexing policy. You might be indexing unnecessary fields.
• Automate via Infrastructure as Code (IaC): Always define your indexing policy in your Terraform, Bicep, or ARM templates. Never rely on manual changes in the portal for production environments.
• Test with Representative Data: Do not test your indexing policy with 10 documents. Use a dataset that represents the size and complexity of your production data to get an accurate reading on performance and costs.

Common Pitfalls and How to Avoid Them

1. The "Everything is Indexed" Trap

Many developers leave the default indexing policy untouched. While this is fine for small projects, it is a significant waste of resources in production. If you have a 100GB container, the index itself could be another 50GB+. You are paying for that storage and the throughput to update it.

2. Ignoring the Order in Composite Indexes

When defining a composite index, the order of the fields matters. The index must match the order of the fields in your ORDER BY clause exactly. If your index is defined as (name, age) but your query is ORDER BY age, name, the engine cannot use the composite index.

3. Forgetting to Index 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 index. Ensure that your queries always include the partition key to avoid cross-partition queries, which are much more expensive and slower.

4. Over-indexing Large Documents

If you store large JSON documents with many nested objects, the default /* index will index every single nested path. This can lead to an explosion in index size. Explicitly exclude deep paths that don't need to be searched.

Comparison: Default vs. Custom Indexing

Tip: The "Query Explorer" is your best friend. If you are unsure which index your query is using, use the "Query Stats" tab in the Azure Portal's Data Explorer. It will show you exactly how many RUs your query consumed and whether it performed a full collection scan or utilized an index. If you see "Index Hit: False," you have a problem.

Deep Dive: The Cost of Indexing

Every time you perform a write operation in Cosmos DB, the database performs a "write tax." Part of this tax is the cost of updating the index. If you have an index with 10 paths, and you update a document, the engine must update 10 index entries. This is why minimizing the number of indexed paths is one of the most effective ways to lower your RU consumption.

Consider the scenario of a logging application. You are writing millions of log entries per day. Each log entry has a timestamp, level, message, and metadata. If you index the message field (which might be a long string), you are wasting massive amounts of RUs for an index that is likely rarely used for searching. By excluding the message field from the index, you could potentially cut your write costs by 30% or more.

Handling Schema Changes

NoSQL databases are flexible, but your indexing policy is not always as flexible as the data. If you add a new property to your documents, it will be indexed by default if you use the wildcard /* path. If you use a strict custom index, the new property will not be indexed until you update your policy.

This is a double-edged sword. On one hand, the "index-everything" approach protects you from forgetting to index a new field. On the other hand, it can lead to "index creep," where your index grows uncontrollably as your data model evolves.

Strategy for Evolving Schemas:

1. Start with a moderately restrictive policy.
2. Use the /* wildcard but add specific exclusions for known heavy/unnecessary fields.
3. Periodically audit your query patterns using Azure Monitor and Log Analytics.
4. If you find a new field is being queried frequently, update the indexing policy to explicitly include it.

Advanced Query Optimization Techniques

While the indexing policy is the foundation, your query patterns also dictate how the index is used.

Equality vs. Range Queries

Cosmos DB indexes support both equality (e.g., WHERE c.status = 'active') and range (e.g., WHERE c.price > 100). Range indexing consumes more storage than equality indexing. If you know you will only ever perform equality checks on a specific field, you can optimize the indexing policy to use a "Hash" index instead of a "Range" index, though this is a more advanced configuration typically managed by the system.

The Impact of Data Types

The indexing engine treats strings and numbers differently. String indexing is particularly expensive because of the way the engine handles collation and string comparisons. If you have a property that contains a unique identifier (like a GUID or a hash), you should ensure it is indexed as a string, but be aware that it will consume more index space than a numeric field.

Troubleshooting Common Indexing Issues

"Query is too complex"

If you receive this error, it usually means your query is trying to perform an operation that the index cannot support. This often happens with complex JOIN operations or cross-partition queries that involve multiple filters that are not properly indexed.

High RU Usage on Simple Queries

If a simple SELECT * FROM c WHERE c.id = '...' is consuming a high number of RUs, check if you have accidentally disabled indexing for the ID field, or if there is a massive amount of indexing happening on other fields that is bloating the cost of the write operation that preceded the read.

The "Re-indexing" Lag

If you notice that your application is not finding newly inserted data, check the portal to see if a background indexing operation is still in progress. During this time, the index is "eventually consistent." If your application requires "strong consistency" for queries, you must ensure the index is fully updated before the query is executed.

Key Takeaways

1. Indexing is a Balancing Act: You are constantly balancing query performance against write cost and storage. There is no "perfect" policy that works for every application.
2. Default is for Development: The default "index-everything" policy is perfect for prototyping, but it is rarely the best choice for a production application that needs to be cost-efficient.
3. Know Your Queries: You cannot build an efficient indexing policy if you do not know how your application will query the data. Analyze your most frequent and most expensive queries before finalizing your policy.
4. Use Exclusions: The most effective way to reduce costs is to identify the fields you don't need to query and explicitly exclude them from the indexing policy.
5. Monitor Regularly: Indexing is not a "set it and forget it" task. As your application's query patterns change, your indexing policy should evolve with them.
6. Composite Indexes are Necessary for Complex Queries: Do not expect single-field indexes to solve every problem. When you have filters and sorting on multiple fields, look to composite indexes.
7. Automation is Key: Use infrastructure-as-code to manage your indexing policies. Manual changes are prone to error and difficult to track, audit, and replicate across environments.

By mastering these concepts, you move from being a developer who "uses" Cosmos DB to one who "optimizes" it. This level of control is what separates high-performance, cost-effective cloud applications from those that suffer from unpredictable performance and ballooning Azure bills. Take the time to audit your current containers, understand your query patterns, and prune your indexing policies—your future self (and your budget) will thank you.