If you are reading this post, you are probably a developer who leverages Cosmos DB or perhaps another NoSQL service for your application. You've likely witnessed first-hand the complexity of dealing with hierarchical data and the performance impact of this partitioning when your data is sharded and distributed. However, if you are unfamiliar, I will provide a brief background.
NoSQL services store data as JSON documents. If you have multiple nodes in a cluster, queries can either be routed to a distinct node containing your data or scan all nodes. The difference is how you are partitioning or sharding your data. But when is your data "hierarchical"? Let's set the stage with an example data model: your manufacturing organization has many plants and within each plant are many lines that produce widgets. Your use case requires that you can query all lines in a plant, or query data for a specific line. This data is partitioned and spread across the nodes in your cluster. Naturally, you would want to partition by plant and then again by line. But most NoSQL services do not allow for more than one layer of partitioning.
The above use case typically leads to a synthetic key where you concatenate plant and line to create a partitioning key like so: "plant_line" or "DetroitPlant_Line110". If you know your partitioning key, your query will be routed to the specific node where your data resides, no problem. In our example, if you want to get all data for Detroit plant Line 110, the query will be efficiently routed to the right node to serve the data, even with a synthetic key. This is ideal. If you want to get data for all lines in Detroit plant, there is no information to inform your query plan as to where your data resides. In this case your query gets routed to all nodes. This is the most expensive type of query and is called a cross-partition or fan-out query.
Here is an example document:
{ "id": "7027a750-0632-41b3-8ad9-5af8a7ed0e0e", "partitionKey": "DetroitPlant_Line110", "plant": "DetroitPlant", "line": "Line110", "dateTime": "2022-06-16T12:10:30.3207828Z",
...
Say you have 10 partitions and on those partitions you have data from many plants. Chicago plant, Cleveland plant, Houston plant, etc... If you query for all data from the Detroit plant your query might look like this in Cosmos DB:
select value count(1) from c where c.plant = "DetroitPlant"
-- or
select * from c where STARTSWITH(c.partitionKey,"DetroitPlant")Both of these queries will result in a cross-partition query targeting all 10 partitions even though your Detroit data may only reside on a few partitions.
So what does Hierarchical Partitioning Keys in Cosmos DB give us? A path based partitioning mechanism to route queries to only the partitions where our data resides. In Cosmos DB, you can have up to 3 levels of partitioning in your keys. In this case, our partitioning key would be plant and line. Below is an example document:
{ "id": "56979d44-8863-4c54-aff7-8ca1be57dce1", "plant": "DetroitPlant", "line": "Line110", "dateTime": "2022-06-15T20:24:32.4447269Z",
...
It is very similar to the previous document but there is no longer a synthetic partitioning key "partitionKey". On the surface, you may note that there is a benefit to dropping this field from a usability and development standpoint. You no longer have a nebulous key that needs to be built application side for reads and writes, and your queries are greatly simplified. Here is an example query using hierarchical partitioning keys:
select value count(1) from c where c.plant = "DetroitPlant"
-- or, if retrieving line information
select * from c where c.plant = "DetroitPlant" AND c.line = "Line110"On the back end is where the magic is really happening, however. Querying for "DetroitPlant" will produce a query plan that is routed only to the physical partitions that contain "DetroitPlant" data. Here is data showing an example I produced where I created 2 containers, one using a synthetic key and one using hierarchical keys. Both containers have 10 physical partitions and have the same amount of data (5 million documents).
Synthetic key "plant_line":
select value count(1) from c where STARTSWITH(c.pk,"plant1")
-- or
select value count(1) from c where c.plant = "plant1"RU cost: 29.9
Physical partitions hit: 10
Hierarchical Key plant, line:
select value count(1) from c where c.plant = "plant1"RU cost: 21.2
Physical partitions hit: 7
In this example, there is a 30% efficiency gain. This will be highly dependent on your data model.
So, now you see the benefits, but how do you get started? First, you should be using the latest SDK. But you already are, right? In the case of hierarchical keys .NET SDK must be >= 3.17.0-preview. There is currently no way to create a subpartitioned container in the portal, you must use the SDK. Create a list defining your path, create a container properties object, and create the container. This is covered in the documentation (linked below). To create an item, you must use the PartitionKeyBuilder() method to create your key.
This is a very exciting feature for Cosmos DB and is currently in preview. Keep in mind, the more documents you have, the wider the hierarchy, the greater the benefit. Lastly, this allows you to easily scale your partitions in Cosmos DB. Cosmos DB has a 20 GB limit for logical partitions (based on partition key) and hierarchical keys allow you to exceed this limit by partitioning further in your hierarchy.
Happy hacking.