How to query: filtering on multiple primary partition keys and range on primary sort key

Question

Background I have a dynamoDB table consisting of a Primary Partition Key clientID ("N") and a Primary Sort Key createdAt("S") (as UTC ISO 8601 date) + some additional attributes.

Problem I Want to query back, all items where createdAt BETWEEN two dates and only for around 100 specific clientIDs at a time.

• Dead end 1: First I thought about using BatchGetItem, however though I know what clientIDs I want to query, I cannot specify the sort keys, since I'm looking for anything, that happen within a timeframe.
• Dead end 2: Secondly I though about using Query, however that one does not allow me to look for multiple clientIDs at the same time (in looked at the ´IN´ operator, but that one does not do what I was hoping for). So in other words, how can I achieve this, without having to do a Query for every single ClientID in the timeframe?

Answer 1

Or another viable solution is using PartiQL

Answer 2

I ended up doing something else:

I added a new attribute on called index_date which only holds the date part of created_at (not time). Then I added a new secondary global index on this attribute, with created_at as sort key. In this way, I can efficiently query back all items for one day (in a specific time frame if I want) at a time. Then process them afterwards.

Answer 3

The two options you mention, are actually the only ones you have.

• Option 1: Use BatchGetItem for required clientID, and then do application-side filtering on createdAt.
• Option 2: Fire 1 Query for each clientID and createdAt range, and then merge data on application-side.

You need to choose one of the two, depending on your access pattern, table structure and cost factor.

For e.g., if the average number of createdAt per clientID isn't high, Option 1 would be good enough, as it'll also be frugal on read capacity units.

EDIT: As pointed out by Niels in this answer's comment, Option 1 won't work in this particular scenario, as BatchGetItem needs both partition and sort keys.