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"]
- Logical partition: all items sharing the same partition key value. Max 20 GB of data and 10,000 RU/s. It also defines the scope of transactions (stored procedures, triggers, transactional batch).
- Physical partition: the actual storage, fully managed by the service (you don't control it). Max 50 GB and 10,000 RU/s. Several logical partitions fit in one physical partition. As data or throughput grows, the service splits physical partitions without downtime.
- Provisioned throughput is divided evenly across physical partitions. Example from the docs: 18,000 RU/s over 3 physical partitions = 6,000 RU/s each.
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:
- hold a value that never changes (partition key values are immutable: to "move" an item you create a new one and delete the old one, with no atomicity across two partitions);
- have high cardinality (many distinct values);
- spread storage and RU consumption evenly;
- ideally show up in the equality filters of your queries;
- have a value of at most 2048 bytes (101 bytes if large partition keys aren't enabled).
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
- Synthetic key: you build a property by combining several fields (for example customer + date) to get a better spread. Trade-off: a query filtering on only one of the two fields becomes cross-partition again.
- Hierarchical partition key (subpartitioning): up to three levels (for example
/tenantId,/userId,/sessionId). A first-level value can then exceed 20 GB, and queries filtering on the first level (or a prefix) are routed to a subset of partitions. Limits: set at container creation, NoSQL API only, recent SDKs required. The first level should have high cardinality.
A filter on only the second or third level is still a full cross-partition query.
Lessons learned
- The key can't be changed in place: you migrate to a new container (container copy jobs, or the change feed). Global secondary indexes are another path when different queries need different keys.
- Before choosing, list the 3 to 5 main queries and their equality filters.
- Watch logical partition size with an Azure Monitor alert before reaching 20 GB.
- A small container forgives a lot; growth is what reveals bad choices.