Cosmos DB patterns (1) — partition key design for efficient queries

This is the first part of a series of five articles about advanced patterns in Cosmos DB. We start with the decision with the greatest impact and the hardest to change later: the partition key. The examples come from a real scenario — an AI chat system with sessions, messages, and query logs, similar to what runs behind Oravio.

Why the partition key is the critical decision

Cosmos DB scales by splitting data into physical partitions, distributed based on the partition key. Each logical partition has a 20 GB limit and throughput is distributed among the physical partitions. Practical consequences:

• Queries within a single partition are cheap — Cosmos knows exactly where to look. A few RUs for a point read.
• Cross-partition queries are expensive — they run on all physical partitions and accumulate costs. A query that costs 3 RUs on one partition costs 3 × N on N partitions.
• The partition key cannot be changed — once the container is created, the only way is to migrate data to a new container.

Therefore the golden rule: choose the partition key based on query patterns, not on data structure.

The three criteria

1. High cardinality

The partition key must have many distinct values. A key with 5 possible values (e.g. /status) means a maximum of 5 logical partitions — and the 20 GB per partition limit becomes a real ceiling.

2. Uniform distribution of writes

Avoid hot partitions: if 80% of writes go to the same key value, that physical partition becomes a bottleneck, no matter how much throughput you provision. A classic mistake example: /date as partition key — all today's writes hit the same partition.

3. Alignment with dominant queries

The most frequent query must contain the partition key in the filter. If 90% of queries are "give me the messages of session X", then /sessionId is the natural candidate.

Case study: AI chat system

Let's apply this to a concrete scenario. An AI chat widget stores:

• Chat sessions — one per conversation with a visitor
• Messages — many per session, always read together with the session
• Query logs — for analytics per client (tenant)

Messages: /sessionId

The dominant pattern: "display the conversation" = all messages of a session. With /sessionId as partition key, the entire conversation is a single logical partition — cheap query, no cross-partition:

// Query in a single partition -- cheap and fast
var query = new QueryDefinition(
    "SELECT * FROM c WHERE c.sessionId = @sessionId")
    .WithParameter("@sessionId", sessionId);

using var iterator = _container.GetItemQueryIterator<ChatMessage>(
    query,
    requestOptions: new QueryRequestOptions
    {
        PartitionKey = new PartitionKey(sessionId)  // explicit -- avoids fan-out
    });

A detail that matters: set PartitionKey explicitly in QueryRequestOptions. Without it, even if the filter contains sessionId, the SDK may do unnecessary fan-out.

Query logs: /tenantId... with a catch

/tenantId seems natural for per-client analytics. But if a large tenant generates 50% of the traffic, you have a hot partition. And an active tenant for years can exceed 20 GB.

The solution: synthetic key — combine tenantId with a time component:

public class QueryLog
{
    public string Id { get; set; } = Guid.NewGuid().ToString();

    // Synthetic key: tenant + month -- distribution over time, no hot partition
    public string PartitionKey => $"{TenantId}-{CreatedAt:yyyy-MM}";

    public string TenantId { get; set; } = default!;
    public string Query { get; set; } = default!;
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
}

Monthly analytics per tenant remain single-partition ("query logs for tenant X in March"), and data is naturally distributed over time.

Hierarchical partition keys

The modern alternative to synthetic keys: up to 3 levels of partition key (/tenantId, /sessionId). Queries on prefix (just tenantId) are efficiently routed to the relevant subset of partitions, and the 20 GB limit applies to the full combination, not just the first level:

// Create container with hierarchical partition keys
var containerProperties = new ContainerProperties(
    id: "chat-data",
    partitionKeyPaths: new List<string> { "/tenantId", "/sessionId" });

await database.CreateContainerIfNotExistsAsync(containerProperties);

// Write -- both levels
var partitionKey = new PartitionKeyBuilder()
    .Add(message.TenantId)
    .Add(message.SessionId)
    .Build();
await _container.CreateItemAsync(message, partitionKey);

When to choose hierarchical vs. synthetic: hierarchical if you have queries on both levels (per tenant AND per session); synthetic key if the pattern is fixed and you want simplicity.

The real cost of cross-partition

Sometimes cross-partition is inevitable (e.g. global search in admin dashboard). Survival rules:

• Measure RUs — response.RequestCharge tells you exactly how much each query costs; log it in development
• Don't use cross-partition ORDER BY without composite index — you get an error; for simple cases, sort in memory after fetch
• Watch out for reserved words — ORDER BY c["order"] with brackets for properties like "order"
• Limit with TOP/OFFSET — an unlimited cross-partition scans everything

// Measuring cost -- mandatory in development
var response = await iterator.ReadNextAsync();
_logger.LogInformation("Query cost {RU} RU", response.RequestCharge);

What’s next

The correctly established partition key makes the rest of the patterns possible. In the second part: Change Feed — the stream of changes from the container as an event source, with Change Feed Processor in .NET and practical cases (indexing, cache invalidation, projections).

Questions? Write to me at contact@ludoprogramming.com.