DynamoDB - how to deal with none unique timestamps as sort key?

Question

I have data that has timestamps that I would like to index as a range key so that I can query on time.

The issue is that the timestamp may not be unique across the partition.

For example:

PK	SK
account	2021-08-06T12:40:32Z
account	2021-08-06T12:48:37Z
account	2021-08-06T12:48:37Z

Which wont work. If I make the PK something unique, like this:

PK	SK
12345	2021-08-06T12:40:32Z
12346	2021-08-06T12:48:37Z
12347	2021-08-06T12:48:37Z

Then I can't query across all my data on timestamp because each record is in a different partition.

How would you go about querying time in DynamoDB? Previous examples Ive seen use SK but this only works if the timestamp is unique.

Scan really isn't an option.

Answer 1

Primary keys need to uniquely identify an item in your base table, but GSIs do not have the same requirement.

If you have a requirement for a unique ID and time sorting, you might want to take a look at KSUIDs (or ULIDs).

A KSUID, or K-Sortable Unique Identifier, is a unique identifier with time-based ordering. This lets you have unique identifiers that are sortable by creation time (or another time if needed). You can read a Brief history of the UUID for more details.

KSUIDs are great when you have a need for unique ID's and time sorting. I've found it especially useful in DynamoDB where I often have the need to sort by creation date.

There are KSUID libraries in several programming languages, so you don't need to implement the algorithm yourself. There's even a KSUID generator website that you can use to quickly interact with KSUIDs.

Answer 2

So it seems like partition and sort keys in a GSI do not need to be unique.

If I create a table with just a PK based on individual ID.

I then create a GSI with PK on account and SK on date, I can query the GSI to get the desired result.