Cosmos DB design and index / partition optimization

Question

Cosmos DB design and index / partition optimization

randomrabbit 116

Hello,

I'm about to use Cosmos DB for storing streaming data where there will be a JSON object that changes in content. Which of the below examples will be a) faster to query and b) more RU efficient?

Version 1:

{
   _id: <string>,
   data: {
     fieldname: <any type>
  }
}

where the "fieldname" will change based on the data sent to the DB. Option 2:

{
   _id: <string>,
   data: {
     name: <string>,
     value: <any type>
  }
}

So in option 1. I would have a data object where the field name can change. In option 2 data.name always exists but has a value that changes.

What if I have to extend the model to store arrays of data, does that change the answer?

Edit: My queries will be based on filtering by the data names.
Edit: Can option 1 support partitioning based on a changing fieldname?

Thanks!

Answer 1

Hi @randomrabbit , welcome to Microsoft Q&A forum.

If I understand correctly you want to know which of these sample document would be faster to update and consume less RUs.

Updating the specific properties for documents you provided would not see any significant differences as long as both the document have similar size. Normally we retrieve entire document and then make changes to properties and update it. If you extend the models then size increases and so do the RU charges.

The RUs depend on multiple factors as mentioned below:

Item size (in your case size looks similar for both)
Item indexing
Type of reads
Indexed properties

There are many others factors to consume less RUs and make query faster (refer article). However if we are doing point reads (based on id or partition key) then query will be faster.

Another way to reduce the RUs is to use the partial document update feature where in we can update specific fields/properties in a single document without needing to perform a full document read-replace operation.

Now this solves the problem specific to cases mentioned in your 2 sample document because for both of them we are just going to retrieve the specific properties and replace that. As an example:

{  
    _id: <string>,  
    data: {  
      fieldname: <any type>  
   }  
 }

For this document we will just need '_id' and 'data/fieldname'.

{  
    _id: <string>,  
    data: {  
      name: <string>,  
      value: <any type>  
   }  
 }

For this document, we will just need '_id' and 'data/value'.

Now to retrieve this document based on id, we can write something like below:

ItemResponse<Object> response = await container.PatchItemAsync<Object>(  
                id: "b864acc7-8cfe-47ff-b04c-c3f2a92a34df",                  
                partitionKey: new PartitionKey("test"),  
                patchOperations: new[] { PatchOperation.Replace("/data/value", "value2") }  
            );

Even if we extend the document to store very complex document this is going to be same request unit charge as we are just patching the properties that we need to update:

Multiple benefits: Partial Document Update

Reduced network call payload, avoiding whole document to be sent on the wire
Avoiding extra “READ “operation for OCC check by the client and hence saving on the extra read RU charges
Significant savings on end-to-end latency for a modifying the document
Avoid extra CPU cycles on client side to read doc, perform occ checks, locally patch document & then send it over the wire as replace API call

Only limitation here is we are querying based on id property of document.

In any case, having a well designed partition key is most important.

Please let us know if this helps or else we can discuss further.

----------

If answer is helpful please click on as it could help other members of the Microsoft Q&A community who have similar questions and are looking for solutions. Thank you for helping to improve Microsoft Q&A!

randomrabbit 116 Reputation points

2022-04-20T18:35:02.41+00:00

Thank you for the detailed answer! Sorry that my writing was not clear. I mostly care about read performance / cost instead of updates.

Here is another way to put the question: if I have data like

{ _id: <string>, data: { fruit: "apple", } } { _id: <string>, data: { vegetable: "carrot", } }

then will I be able to partition the data for "fruit" and "vegetable"?

Thanks again.
Anurag Sharma 17,386 Reputation points

2022-04-21T05:35:20.223+00:00

Thanks for replying back.

Unfortunately partition key cannot be defined on the array elements as partition keys are applicable only for single values. So in your example we would not be able to create partition keys on "fruit" or "vegetables" as they are sub properties of "data" array.

However to control the performance and cost of read operation, we need to choose a partition key that offers high cardinality and balanced distribution of documents, which in turn will prevent us from having hot partitions on the container. Also our RUs will be evenly distributed.

Read operations in Azure Cosmos DB are typically ordered from fastest/most efficient to slower/less efficient in terms of RU consumption as follows:

Point reads (key/value lookup on a single item ID and partition key).

Query with a filter clause within a single partition key.

Query without an equality or range filter clause on any property.

Query without filters.

So we should always try to stick with first 2 points for faster read operations and less cost.

Reference: Reading data: point reads and queries

Please let us know if this helps or else we can continue the discussion.

----------

If answer helps, you can mark it
randomrabbit 116 Reputation points

2022-04-21T10:16:19.56+00:00

I see, thanks. So I would have to have documents like this

{ _id: <string>, food_type: "fruit", food_value: "apple" } { _id: <string>, food_type: "vegetable", food_value: "carrot" }

...in order to have partitioning by food_type? Or is there a nicer way?
Anurag Sharma 17,386 Reputation points

2022-04-21T16:01:10.283+00:00

Yes food_type definitely is one way of defining the partition key. Or we can choose food_value as well for partition key if we know our queries mostly will be using food_value as filters. This will provide better cardinality as well and not giving chances of having hot partitions. Also the read queries should be mostly based on this partition key to have faster responses.

Answer 2

Hasan Savran 321 MVP

Both models you shared are simple and I don't think it will change that much when it comes to RU.
"Faster to query" and "more RU efficient" will mostly depend on your partition key and what is indexed in your situation.
If you are planning to have more than 50 GB data in this container, take your time to pick a correct partition key.
It sounds like name is a good candidate for partition key if its cardinality is high. Just be sure that you will not reach 20GB limit per partition.
If you will not use "value" property in your where clauses, you may exclude it to save some money for storage.

randomrabbit 116 Reputation points

2022-04-20T13:10:11.4+00:00

Can I partition using the fieldname of option 1? Does partitioning support that?
Hasan Savran 321 Reputation points MVP

2022-04-20T13:13:10.987+00:00

Yes in model 1, you can make the fieldname a partition key too. As I said before, you want to be sure that fieldname or name should have high cardinality so you will not reach 20GB limit per partition.

Cosmos DB design and index / partition optimization

1 additional answer

Activity