Limit number of documents in a partition for cosmosdb

Question

I have a cosmosdb collection with each partition containing a set of documents.I would like to maintain the collection such that a logical partition('id' in this case) does not go over the limit of 5 documents. In the sample below, when a sixth entry(say for 8/11/2020) is added, I want to delete the document created on 7/13/2020 since that was updated the earliest. Basically, I want to make sure for item with id 12345, there are only 5 latest entries and no more. This is to reduce the data in the db and thus avoid querying more data than what's needed.

{
"id": 12345,
"lastUpdated":  8/10/2020
},
{
"id": 12345,
"lastUpdated":  8/3/2020
},
{
"id": 12345,
"lastUpdated":  7/27/2020
},
{
"id": 12345,
"lastUpdated":  7/20/2020
},
{
"id": 12345,
"lastUpdated":  7/13/2020
}

I could do something like this:

Get all documents for id 12345
If count of documents is >=5, get the last document (with instance 5) and delete it.
Insert new document

However, that is a running 3 queries to insert a single document.

Is there a more elegant way to do this?

Thanks!

You're right, in that you'd have to maintain your own list of items to delete. You could perform this action in a stored proc (since sp's are partition-scoped), but you'd still have the multiple operations + related RU cost per operation. That said: I don't understand the need to limit number of items in a partition. Also, I'm assuming you're referring to logical partitions, correct? (since there's no way to control physical partitions without resorting to large amounts of data or artificially increasing RU/sec). Please edit your question to contain more details. — David Makogon
Thanks for the response! By limiting number of items in partition, I meant for a particular id, I only want to maintain 6 items to minimize the amount of data stored. Yes, I am referring to logical partitions (/id) in this case. Is there a way I can leverage TTL somehow? — AgentHunt
Assuming it's just a matter of cleanup to limit growth, seems like a periodic maintenance job run using Azure Function or otherwise would be the simplest and avoid impacting the writes. — Noah Stahl

Jason Pan Jason Pan · Accepted Answer · 2020-08-14T06:02:53

You can use OFFSET 1 LIMIT 5 to get 5 latest entries. For more details, you can read offical document about OFFSET LIMIT clause in Azure Cosmos DB.

You can get the count(assume 100) of data and set ttl, or delete directly. We can query like below.

SELECT f.id, f.lastUpdated FROM yourcosmosdb f ORDER BY f.lastUpdated OFFSET 6 LIMIT 100

Foreach

List<Task> concurrentDeleteTasks = new List<Task>();
while (feedIterator.HasMoreResults)
{
    FeedResponse<response> res = await feedIterator.ReadNextAsync();
    foreach (var item in res)
    {                        
        concurrentDeleteTasks.Add(container.DeleteItemAsync<response>(item.id, new PartitionKey(item.deviceid)));
    }
}
await Task.WhenAll(concurrentDeleteTasks.Take(3));

You also can foreach the collection and set ttl=10, these data will be deleted 10s later.

You can get the latest 5 data：

SELECT f.id, f.lastUpdated FROM yourcosmosdb f ORDER BY f.lastUpdated OFFSET 1 LIMIT 5

Limit number of documents in a partition for cosmosdb

1 Answers