Is using "Current Date" a good partition key for data that will be queried by date and id?

user_4593682 0
Viewed 253

I'm new to Azure Cosmos DB and I have this new project where I decided to give it a go.

My DB has only one collection where around 6,000 new items are added everyday and each looks like this

{
    "Result": "Pass",
    "date": "23-Sep-2021",
    "id": "user1@example.com"
}

The date is partition key and it will be the date of which the item was added to the collection where the same id can be added again everyday as follows

{
    "Result": "Fail",
    "date": "24-Sep-2021",
    "id": "user1@example.com"
}

The application that uses this DB will query by id and date to retrieve the Result.

I read some Azure Cosmos DB documentations and found that selecting the partition key very carefully can improve the performance of the database and RUs used for each request.

I tried running this query and it consumed 2.9 RUs where the collection has about 23,000 items.

SELECT * FROM c
WHERE c.id = 'user1@example.com' AND c.date = '24-Sep-2021'

Here are my questions

  1. Is using date a good partition key for my scenario? Any rooms for improvements?
  2. Will consumed RUs per request increase over time if number of items in collection increase?

Thanks.

azure-cosmosdb
1 Answers

For a write-heavy workload using date as a partition key is a bad choice because you will always have a hot partition on the current date. However, if the amount of data being written is consistent and the write volume is low, then it can be used and you will have good distribution of data on storage.

In read-heavy scenarios, date can be a good partition key if it is used to answer most of the queries in the app.

The value for id must be unique per partition key value so for your data model to work you can only have one "id" value per day.

If this is the case for your app then you can make one additional optimization and replace the query you have with a point read, ReadItemAsync(). This takes the partition key value and the id. This is the fastest and most efficient way to read data because it does not go through the query engine and reads directly from the backend data store. All point reads for 1KB of data or less will always cost 1RU/s.

user_8849310
Related