DynamoDB how to get items count for a partition keys using .net core?

Question

How can I get items count for a particular partition key using .net core preferably using Object Persistence Interface or Document Interfaces?

Since I do not see any docs any where, currently I get the number of items count by retrieve all the item and get its count, but it is very expensive to do the reads.

What is the best practices for such item count request? Thank you.

Answer 1

dynamodb is mostly a document oriented key-value db; so its not optimized for functionality of the common relation db functions (like item count).

to minimize the data that is transmitted and to improve speed you may want to do the following:

Create Lambda Function that returns Item Count

To avoid transmitting data outside of AWS; which is slow and expensive.

query options

use only keys in your projection-expression, reducing the data that is transmitted from db

max page-size, reducing number of calls needed

Stream Option Streams could also be used for keeping counts; e.g. as described in https://medium.com/signiant-engineering/real-time-aggregation-with-dynamodb-streams-f93547cfb244

https://docs.aws.amazon.com/amazondynamodb/latest/developerguide/bp-gsi-aggregation.html

Related SO Question

Complexity of finding total records count with partition key in nosql dynamodb table?

Answer 2

I just realized that using low level interface in QueryRequest one can set Select = "COUNT" then when calling QueryAsync() orQuery() will return the count only as a integer only. Please refer to code sample below.

private static QueryRequest getStockRecordCountQueryRequest(string tickerSymbol, string prefix)
        {
            string partitionName = ":v_PartitionKeyName";
            string sortKeyPrefix = ":v_sortKeyPrefix";

            var request = new QueryRequest
            {
                TableName = Constants.TableName,
                ReturnConsumedCapacity = ReturnConsumedCapacity.TOTAL,
                Select = "COUNT",
                KeyConditionExpression = $"{Constants.PartitionKeyName} = {partitionName} and begins_with({Constants.SortKeyName},{sortKeyPrefix})",
                ExpressionAttributeValues = new Dictionary<string, AttributeValue>
                {
                    { $"{partitionName}", new AttributeValue {
                        S = tickerSymbol
                    }},
                    { $"{sortKeyPrefix}", new AttributeValue {
                        S = prefix
                    }}
                },
                // Optional parameter.
                ConsistentRead = false,
                ExclusiveStartKey = null,
            };
            return request;
        }

but I would like to point out that this still will consumed the same read units as retrieving all the item and get its count by yourself. but since it is only returning the count as an integer, it is a lot more efficient then transmitting the entire items list cross the wire.

I think using DynamoDB Streams in a more proper way to get the counts for large project. It is just a lot more complicated to implement.