DP-900: how to use secondary indexes in Azure Cosmos DB

I will teach you the skill: understanding and using secondary indexes in Azure Cosmos DB. This skill appears in the nonrelational data section of DP-900 and is useful in practice to optimize read performance and RU cost in applications that use Cosmos DB.

What you need to know

Azure Cosmos DB is a multimodel nonrelational database that automatically indexes JSON documents. Secondary indexes (secondary indexes) enable fast queries on properties that are not the primary key. By default, Cosmos DB creates an index for all properties, making most queries fast without additional schema. However, automatic indexing can increase write costs and latency; therefore, it is important to understand how to configure the indexing policy.

Simple example: you have documents with this structure in a container:

{
  "id": "1",
  "nome": "Ana",
  "idade": 30,
  "cidade": "Lisboa",
  "tags": ["clientes", "premium"]
}

If you want to query by city (WHERE cidade = 'Lisboa'), a secondary index on the cidade property speeds up the query. If you rarely query a property, you can exclude it from the index to reduce write costs.

How it works

The indexing policy in Cosmos DB defines which JSON paths are indexed and with which index type (Hash, Range) and precision. Key points:

• Included paths: paths to index (e.g.: "/cidade/?", "/tags/*").
• Excluded paths: paths to exclude from the index to reduce write cost.
• Index types: Range for operators <, >, ORDER BY and range queries; Hash for equality (==), more space-efficient.
• Consistency and RU: indexes increase RU cost on write operations. Adjusting the policy changes the trade‑off between fast reads and more expensive writes.

Example of an indexing policy (simplified) in JSON:

{
  "indexingMode": "consistent",
  "includedPaths": [
    { "path": "/cidade/?" },
    { "path": "/idade/?" }
  ],
  "excludedPaths": [
    { "path": "/comentarios/*" }
  ]
}

In practice

Steps to optimize secondary indexes in a production database:

1. Identify query patterns: analyze the most frequent queries — which properties appear in WHERE, JOIN, ORDER BY or in range filters.
2. Choose the index type according to usage: use Range if you need ORDER BY or range filters (e.g.: idade > 18); use Hash for equality (e.g.: cidade = 'Porto').
3. Exclude low‑use properties: large fields (e.g.: base64 blobs, very large arrays) or rarely queried fields should be excluded.
4. Test and measure RU: before and after changing the policy, measure RU consumed on writes and reads and the latency of queries.

Practical example with Azure Portal / SDK: change the policy via the portal or ARM/SDK — here is a JSON snippet to include a Range index on a text property expected to be used in ORDER BY:

{
  "includedPaths": [
    {
      "path": "/nome/?",
      "indexes": [
        { "kind": "Range", "dataType": "String", "precision": -1 }
      ]
    }
  ],
  "excludedPaths": [
    { "path": "/largeBlob/*" }
  ]
}

After applied, reassess query performance. If necessary, reindex contents (in some scenarios it may be necessary to recreate the container for a full reindexing, depending on the changes).

Common mistakes

1) Assuming automatic indexing is always ideal: having everything indexed increases write cost. Adjust the policy according to read/write patterns.

2) Using Range unnecessarily: Range consumes more space and RU; prefer Hash for equality.

3) Forgetting to measure RU after changes: changing the policy without monitoring can worsen cost or latency. Always test before going to production.

How to practice

Create a test container in Azure Cosmos DB (Core/SQL mode) and run experiments: insert documents with different formats and simulate read/write loads. Use the portal and the SDKs to modify the indexing policy and observe RU and latencies.

For DP-900 exam preparation, take the official Microsoft free Practice Assessment and consult the official study guide (free). These official resources help validate knowledge without resorting to prohibited materials.

Summary

• Secondary indexes in Cosmos DB speed up queries on properties that are not the primary key.
• Configuring included/excluded paths and choosing between Hash and Range helps balance RU cost and performance.
• Always measure RU and latency before and after changes; optimize based on real query patterns.
• Practice in Azure and use the official Microsoft Practice Assessment and study guide, which are free.