Cosmos DB (DocumentDB API): Efficient way to query most recent document by partition ID?

6
votes

I have a Cosmos DB collection with numerous partitions based on a device ID. I frequently have use cases that require retrieving the most recent document by a specific device ID. I'm currently using the SELECT TOP 1 functionality available in the DocumentDB API as shown below to accomplish this:

SELECT TOP 1 *
FROM c
WHERE c.deviceId = 5
ORDER BY c.timeStamp DESC

This approach results in increased RU/s consumption and decreased performance as the collection and individual partitions grow in size, as one would expect . As a temporary remedy to this issue, I have added additional where clauses to limit the scope of the query by timestamp:

SELECT TOP 1 *
FROM c
WHERE c.deviceId = 5
 AND c.timeStamp >= 1506608558 --timestamps are unix/epoch based to optimize indexing
 AND c.timeStamp <= 1506694958
ORDER BY c.timeStamp DESC

I would like to know if there's a better way to select the latest document by partition id, as the addition of this where clause could result in unexpected or missing results.

1
azure-cosmosdb

1 Answers

2
votes

I had a similar scenario where the Id of the asset I am tracking forms my partition key, and within that partition there are 2,880 events per day per asset, and that will continue to grow over time.

While full event history was required for other use cases, this particular use case required the latest event to be extracted. So an alternate Collection was created which uses the same partition key, but contains the CURRENT state i.e. latest event for that asset.

When an event is written to the WRITE Side, being the collection which persists all events for an asset, a trigger updates the READ side with the latest value.

While this may appear to being doubling up the effort on writes, in our use case the performance increase on the read side made up for this.

I found this MS article to be of use Working with the change feed support in Azure Cosmos DB