4.2 Optimize RUs: Indexing Policies & Consistency Levels

After you can connect and query, AI-200 expects you to optimize performance and cost. In Cosmos DB for NoSQL, cost and capacity are expressed as Request Units (RUs). Indexing policy and consistency level are two of the highest-leverage controls on RU consumption, latency, and correctness for AI applications that read and write JSON at scale.

Request Units: What You Are Paying For

An RU is a normalized measure of CPU, memory, and IOPS required to perform an operation. Approximate intuitions useful for the exam:

• A 1 KB point read costs about 1 RU
• Writes cost more than reads of the same size because indexes and durability work run on the write path
• Query RU charge depends on the number of documents scanned, indexed paths used, page size, and whether the query is single- or cross-partition

Provisioned throughput reserves RU/s on a container or database. Serverless accounts bill RUs consumed per operation. Exceeding provisioned RU/s yields HTTP 429; the SDK retries, but user-facing latency rises. For AI chat logging or embedding-job metadata, poorly filtered cross-partition queries can dominate the RU budget even if individual documents are small.

Strategies that lower RUs without changing business logic:

1. Prefer point reads when id + partition key are known
2. Project only required properties in SELECT
3. Filter on the partition key whenever possible
4. Shape the indexing policy so queries use indexes instead of scans, and unused paths are not indexed
5. Avoid stronger consistency than the app needs

Indexing Policies Overview

Every container has an indexing policy. By default, Cosmos DB uses consistent indexing with a wildcard that indexes most JSON paths. That default is convenient for development but can waste RUs on write-heavy AI pipelines that store large, rarely queried properties (for example, full prompt transcripts or binary-ish base64 blobs embedded in JSON).

Key policy elements:

Indexing modes compared:

There is also a legacy Lazy mode historically discussed in docs; modern guidance for AI-200 focuses on Consistent vs None. Prefer Consistent for any container your AI app queries with SQL.

Excluded Paths: Stop Indexing What You Never Filter

Suppose each chat item stores content (long message text) and embeddingJobLog (verbose string). If you never WHERE or ORDER BY on those paths, exclude them:

{
  "indexingMode": "consistent",
  "includedPaths": [{ "path": "/*" }],
  "excludedPaths": [
    { "path": "/content/?" },
    { "path": "/embeddingJobLog/?" },
    { "path": "/_etag/?" }
  ]
}

Excluding large property paths reduces write RU charges and index storage. Keep paths you filter on — sessionId, userId, createdAt, status, tag arrays — included. The system properties you still need for queries must remain indexed; do not blindly exclude everything.

Composite Indexes: Multi-Property ORDER BY

A single-property index supports many equality filters and simple sorts. Composite indexes are required when you ORDER BY multiple properties, or when the query engine needs a matching compound index for efficient execution.

Example: list messages in a session by time, then by id:

SELECT * FROM c
WHERE c.sessionId = @sid
ORDER BY c.createdAt ASC, c.id ASC

Define a composite index on /createdAt and /id (ascending/ascending) in the container policy. Without it, the multi-property ORDER BY fails or becomes expensive depending on API behavior and policy. Composite indexes also help certain filter + sort patterns common in AI dashboards (status + timestamp).

Design tip: add composite indexes that mirror real query shapes from your prompt-orchestration and admin UIs — not every permutation of properties.

Measuring Query Cost

In Data Explorer or SDK responses, inspect the request charge header (x-ms-request-charge). Compare:

• Query with partition key vs without
• SELECT * vs projected fields
• Before vs after excluding a large path (write charge)
• Before vs after adding a composite index (query charge and success)

For AI-200 case studies, the “best” answer usually pairs correct indexing with single-partition access patterns rather than simply raising provisioned RU/s.

Five Consistency Levels

Cosmos DB replicates data across regions. Consistency level defines the trade-off among freshness, latency, availability, and throughput. The five levels, from strongest to most relaxed:

Session consistency is the default and the right answer for most AI-200 scenarios: a user sends a message (write), then the app immediately reads the transcript for the next prompt — read-your-writes holds within that session token without paying for Strong consistency across every region.

You can set consistency at the account and, in many SDKs, request a weaker level on individual reads to save RUs when staleness is acceptable (you cannot request stronger than the account default).

Putting Indexing and Consistency Together

Consider an Azure OpenAI chat backend on App Service:

• Container partitioned by /sessionId
• Index includes /sessionId, /createdAt, /role; excludes /content if messages are retrieved by id after a metadata query, or keep /content indexed only if you search message text in Cosmos (often better left to AI Search)
• Composite index on (createdAt, id) for ordered history
• Account consistency: Session

This design keeps write RUs predictable, makes history queries single-partition and indexed, and matches user expectations for conversational state. If a global admin dashboard runs heavy cross-partition analytics, isolate that workload on a different container or export to analytics rather than forcing Strong consistency on the hot chat path.

Exam Traps to Avoid

• Raising RU/s instead of fixing a missing partition key filter
• Leaving default indexing on huge rarely queried properties
• Choosing Strong consistency “to be safe” for a single-region chat bot that only needs Session
• Setting indexing mode to None on a container that still runs SQL queries
• Expecting ORDER BY on multiple properties without a composite index

Master these trade-offs and you can answer both the pure Cosmos questions and the integrated “AI app + data store” questions in the Azure data management services domain.