Custom Indexing Policies

Mastering Custom Indexing Policies in Azure Cosmos DB

Introduction: The Power of Targeted Indexing

When you first start working with Azure Cosmos DB, it is easy to assume that the default indexing policy is sufficient for every use case. After all, the service automatically indexes every property of every item in your container, allowing you to run queries immediately without any configuration. However, as your data volume grows and your query patterns become more complex, this "index everything" approach often becomes a liability rather than an asset. This is where custom indexing policies come into play.

A custom indexing policy is essentially a set of instructions you provide to the Cosmos DB engine that dictates exactly how it should structure its internal index. By default, Cosmos DB creates a range index for all strings and numbers, which consumes significant storage and Request Units (RUs) during write operations. When you take control of these policies, you move from a "one-size-fits-all" model to a precision-engineered architecture. You decide which properties need to be searchable, which types of indexes (Hash, Range, or Spatial) are appropriate, and which paths should be ignored entirely.

Understanding how to optimize your indexing policy is critical for two main reasons: cost and performance. Every write operation in Cosmos DB incurs a cost proportional to the number of properties indexed. By reducing the number of indexed fields, you lower your write RU consumption. Simultaneously, by choosing the correct index type for your specific query patterns, you ensure that read operations remain fast and efficient. This lesson will guide you through the technical nuances of designing and implementing custom indexing policies that make your data layer both economical and performant.

Understanding the Indexing Architecture

To master custom policies, you must first understand the fundamental components that make up an index in Cosmos DB. The indexing engine is designed to handle schema-agnostic data, meaning it treats your JSON documents as trees of nodes. When you define an indexing policy, you are essentially defining a set of rules that traverse these trees to determine which nodes should be stored in the inverted index.

The Anatomy of an Indexing Policy

An indexing policy consists of several key elements that define how the system behaves:

• Indexing Mode: This determines whether the index is updated synchronously as you write data (consistent) or if it is disabled entirely (none).
• Included Paths: These are the specific property paths you want the engine to track. You can be as broad as the root (all paths) or as narrow as a single nested property.
• Excluded Paths: These are paths that the engine should explicitly ignore. This is vital for large, deeply nested objects or binary data that you never intend to query.
• Composite Indexes: These are special structures that allow the engine to optimize queries that filter or sort by multiple properties simultaneously.

Callout: The "Consistent" vs. "None" Dilemma

The consistent indexing mode ensures that your queries always reflect the most recent data written to the database. This is the standard for most production applications. Conversely, the none mode turns off indexing completely. While this makes write operations extremely cheap, it renders the container effectively unqueryable via standard SQL. Use none only for write-heavy logging scenarios where you retrieve data exclusively by its unique identifier (ID).

Step-by-Step: Implementing a Custom Policy

Implementing a custom policy is not just about writing code; it is about analyzing your application's query logs. Before you change your policy, you should identify the common filters, sorts, and projections used by your application.

Step 1: Analyze Query Patterns

Before writing a single line of JSON, use the Azure Portal or the Cosmos DB SDK to capture the most frequent queries. Look for WHERE clauses, ORDER BY statements, and JOIN operations. If you have a query that filters by status and timestamp, you have a strong candidate for a composite index.

Step 2: Draft the JSON Policy

The policy is defined as a JSON document. Below is a foundational example of a policy that excludes everything by default and only includes specific fields. This is often called a "white-listing" approach, which is the safest way to minimize RU consumption.

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

Step 3: Apply the Policy via SDK

Once your JSON is ready, you can apply it using the Azure Cosmos DB .NET SDK. Note that changing an indexing policy is an asynchronous operation. If your container is large, the engine will perform a background transformation to rebuild the index.

ContainerResponse response = await container.ReadContainerAsync();
ContainerProperties properties = response.Resource;

properties.IndexingPolicy.IndexingMode = IndexingMode.Consistent;
properties.IndexingPolicy.IncludedPaths.Clear();
properties.IndexingPolicy.IncludedPaths.Add(new IncludedPath { Path = "/category/?" });
properties.IndexingPolicy.IncludedPaths.Add(new IncludedPath { Path = "/price/?" });

await container.ReplaceContainerAsync(properties);

Warning: The Cost of Re-indexing

When you update an indexing policy, Cosmos DB must re-index the existing data. For large containers (hundreds of gigabytes or terabytes), this process can take significant time and consume additional RUs. Always perform these changes in a non-peak window or on a development/staging environment first to measure the impact.

Advanced Indexing: Composite Indexes and Spatial Data

Standard range indexes work well for single-property lookups, but they fall short when your queries become more complex. If you frequently run queries like 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 avoid a scan.

The Role of Composite Indexes

A composite index creates a "super-index" that stores multiple values together. By defining a composite index for (category ASC, price DESC), the engine can satisfy the filter and the sort in a single operation. This dramatically reduces the number of documents the engine needs to load into memory.

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

Handling Spatial Data

Cosmos DB provides native support for GeoJSON data. If your application handles location-based queries, such as "find all stores within 5 miles," you must use spatial indexing. Unlike range indexes, spatial indexes require you to specify the data type (Point, Polygon, MultiPolygon) for the path.

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

Comparison of Index Types

To help you decide which index type to use for specific scenarios, refer to the table below. Choosing the wrong type can lead to inefficient query plans where the engine is forced to perform a full collection scan.

Best Practices for Optimization

Optimizing indexing is an ongoing process. As your application evolves, so should your indexing policy. Here are the industry-standard best practices to keep your solution lean:

1. Start with "Exclude All": It is much easier to add fields to an index than to remove them later. By excluding all paths by default and explicitly adding only the fields you query, you keep your RU costs at the absolute minimum.
2. Monitor RU Consumption: Use the Azure Monitor metrics to track the "Total Request Units" and "Index Transformation" metrics. If you see a sudden spike in RU usage after a code deployment, it is often due to an unindexed query forcing a full collection scan.
3. Use Wildcards Carefully: While /path/* is convenient, it can inadvertently index data you don't need. Be specific with your paths to ensure you aren't paying for storage and write overhead on properties that will never be queried.
4. Avoid Excessive Composite Indexes: While composite indexes are powerful, they also consume additional storage. Only create them for queries that are verified to be "hot" or high-frequency.
5. Review the Query Plan: Always use the "Query Stats" feature in the Data Explorer. If you see "Index Utilization: None" or "Scan," it means your indexing policy is not supporting that specific query, and you need to adjust your strategy.

Note: The "Query Stats" Window

When running a query in the Data Explorer, click the "Query Stats" tab. Look for the "Retrieved Document Count" versus the "Output Document Count." If the retrieved count is much higher than the output count, your index is not optimized, and the database is loading documents only to discard them after filtering.

Common Pitfalls and How to Avoid Them

Even experienced developers fall into common traps when managing indexing policies. Being aware of these pitfalls can save you hours of debugging and significant operational costs.

The "Over-Indexing" Trap

Many developers think that because they have "plenty of RUs," they should just index everything. This is a dangerous mindset. Over-indexing increases the storage footprint of your container significantly because every index entry is stored as a separate document in the system-managed partition. If you have a document with 50 fields, and you index all of them, your storage cost can effectively double or triple.

Ignoring the Order of Composite Indexes

When defining a composite index, the order of the fields matters. If you create a composite index for (A, B), it will work for queries filtering on A and B, or just A. However, it will not be used for queries that filter only on B. Always ensure your composite index definition matches the order of your most common WHERE clauses.

The "Schema Drift" Problem

Cosmos DB is schema-flexible, meaning you can add new properties to documents at any time. If you use a broad indexing policy (like the default), these new properties will be indexed automatically. While this seems helpful, it can lead to "index bloat," where your index size grows uncontrollably as your data evolves. Always audit your indexing policy whenever your document schema undergoes a significant change.

Practical Example: A Retail Order System

Imagine you are building an e-commerce platform. Your document structure looks like this:

{
    "id": "order-123",
    "customerId": "user-99",
    "orderDate": "2023-10-27T10:00:00Z",
    "totalAmount": 150.50,
    "items": [
        { "productId": "p1", "quantity": 1 },
        { "productId": "p2", "quantity": 2 }
    ],
    "metadata": {
        "source": "mobile-app",
        "device": "ios"
    }
}

Common Queries:

1. SELECT * FROM c WHERE c.customerId = 'user-99' ORDER BY c.orderDate DESC
2. SELECT * FROM c WHERE c.totalAmount > 100

Optimized Policy Strategy:

• Excluded: Exclude the items array entirely, as you likely won't query the array contents directly in this container.
• Included: Include customerId (range), orderDate (range), and totalAmount (range).
• Composite: Create a composite index for (customerId ASC, orderDate DESC).

This strategy ensures that the most frequent queries are highly optimized while keeping the storage footprint low by ignoring the large items array.

Key Takeaways

Mastering custom indexing policies in Azure Cosmos DB is a journey from reactive maintenance to proactive performance engineering. By moving away from default settings, you gain granular control over your application's resource consumption and response times. Keep these core principles in mind:

• Precision over Defaults: Never rely on the default indexing policy for production workloads. Always tailor your index to the specific query patterns of your application to reduce RU costs.
• The "Exclude-First" Philosophy: Start your indexing policy by excluding all paths and then explicitly adding the specific fields your application needs to query. This minimizes write overhead and storage costs.
• Composite Indexes are Essential: Use composite indexes for queries that involve multiple filters or a combination of filters and sorting. This is the single most effective way to improve performance for complex queries.
• Spatial Indexing for Location: When dealing with geographic data, ensure you configure spatial indexes correctly, as standard range indexes will not support proximity-based queries.
• Monitor and Re-evaluate: Indexing is not a "set it and forget it" task. Use the Query Stats feature regularly to monitor how your queries are performing and adjust your indexing policy as your data and query patterns evolve.
• Beware of Re-indexing: Remember that changing an indexing policy triggers a background process that consumes RUs. Plan these changes carefully to avoid impacting your application's availability or performance.
• Understand Your Query Plan: Learn to read the query execution plan in the Data Explorer. If a query is performing a full scan, your indexing strategy is the first place you should look for a solution.

By applying these lessons, you will ensure that your Azure Cosmos DB instance remains a high-performance, cost-effective foundation for your applications, regardless of the scale of your data.

FAQ: Common Questions

Q: If I exclude a path from the index, can I still query it? A: Yes, but it will be extremely slow. The database engine will have to perform a full scan of every document in the container to find the data, which will consume a massive amount of RUs and likely time out for large datasets.

Q: Can I change the indexing policy while the application is running? A: Yes, Cosmos DB supports online index updates. The container remains available for reads and writes while the index is being rebuilt in the background. However, be aware of the performance impact on your RU throughput during this process.

Q: Is there a limit to how many paths I can include in an index? A: While there is no hard limit on the number of paths, there is a limit on the total size of the indexing policy document. Additionally, indexing too many paths will negatively impact your write performance and storage costs, so you should always aim for the smallest set of indexed paths necessary.

Q: How do I know if my composite index is actually being used? A: Run your query in the Data Explorer and check the "Query Stats" tab. If the query is using a composite index, you will see it reflected in the query execution plan details. If it isn't being used, double-check that the order of the fields in your query matches the order defined in the index.

Q: What happens if I make a mistake in my indexing policy JSON? A: The Azure Cosmos DB service validates the indexing policy before applying it. If your JSON is malformed or violates policy rules (such as invalid path syntax), the API will return an error, and the policy will not be updated. Your container will continue to use the previous valid policy.