LLI Wiki — My Digital Knowledge Base

Cosmos DB: choosing a partition key

Logical and physical partitions, limits, hot partitions, cross-partition queries, synthetic and hierarchical keys, with good and bad key examples.

In my Cosmos DB training everyone repeats the same thing: "choosing the partition key is the most important decision". Coming from SQL Server, where you can add an index after the fact, that is unsettling. Here is what I take away, with examples of good and bad keys (an orders container in the AppDb database).

Logical and physical partitions

flowchart LR
    A["Item (customerId = C42)"] --> B["Hash of the partition key"]
    B --> C["Logical partition C42 (max 20 GB)"]
    C --> D["Physical partition 1 (max 50 GB, 10,000 RU/s)"]
    E["Logical partition C77"] --> D
    F["Logical partition C99"] --> G["Physical partition 2"]

From the SQL Server side, the partition key is closer to a sharding key: it is for routing, not a secondary index.

What a good key looks like

Per the docs, the property should:

The path accepts letters, digits and underscores; the value is a string or a number (the docs advise strings if numbers may exceed IEEE 754 precision).

Good and bad examples

Key Verdict Why
/customerId (many customers, balanced traffic) Good High cardinality, "orders of a customer" queries hit a single partition
/id Good for point reads Perfect spread, efficient point reads, but any query on another property becomes cross-partition
/status (3 values) Bad Low cardinality, hot partitions and the 20 GB limit reached fast
/orderDate on a write-heavy workload Bad All of the day's writes land on one partition (the docs' IoT example)
/tenantId with one huge tenant Risky The big tenant becomes a hot partition and may exceed 20 GB
Random GUID nobody filters on Trap Writes spread well, but every read by another criterion is cross-partition

Hot partitions and cross-partition queries

A hot partition appears when a few key values concentrate traffic: the physical partition involved exceeds its RU budget and returns 429s while the container as a whole has headroom. In the portal: Insights, Throughput, "Normalized RU Consumption (%) By PartitionKeyRangeID". One partition at 100% while others sit at 30% is suspicious. With diagnostic logs you can see the most expensive keys:

CDBPartitionKeyRUConsumption
| where TimeGenerated >= ago(24h)
| where CollectionName == "orders"
| where isnotempty(PartitionKey)
| summarize sum(RequestCharge) by PartitionKey, OperationName, bin(TimeGenerated, 1s)
| order by sum_RequestCharge desc

A query without the partition key in its filter is cross-partition: it fans out to all physical partitions, with an overhead of about 2 to 3 RU per physical partition. On a small container (one or two partitions) that doesn't hurt. The docs put the point of attention around 30,000 provisioned RU/s or 100 GB of data.

Synthetic and hierarchical keys

A filter on only the second or third level is still a full cross-partition query.

Lessons learned

Further reading

Mastodon LLIMastodon SocialFosstodon