DynamoDB paginated data retrieval

Question

I have the following columns and its purpose.

1. id -> stores the timestamp of modification
2. authorName -> saves the user name who have modified
3. authorEmail -> saves the user email who have modified
4. version -> value denoting the version number
5. Data -> a JSON object

Here, version can be considered as auto increment field which is incremented when modification happens.

The DynamoDB has partition key as id which is timestamp.

I want to create a GET API which orders all the records by version in descending order and applies a limit governed by pageSize constant.

Currently the architecture I thought of was by sending LastEvaluatedKey in response along with the data and next api call would pass this LastEvaluatedKey which will be my ExclusiveStartKey in the scan operation.

The issue as per my understanding is that the records cannot be ordered across multiple partitions.

Is there a way through which this can be achieved?

Answer 1

If you want to get all items for a given id and sorted in desc order by version then you must use partition key: id and sort key: version

SELECT * FROM myTable WHERE id=123 LIMIT 5 DESC

If you would like to maintain global order (order across all items) by version, then you will have to create a Global Secondary index and use a static partition key:

gsipk	version	id	other
1	0	123	data
1	1	376	data
1	2	292	data
1	5	101	data
1	6	123	data
1	10	403	data
1	13	191	data
1	17	403	data

SELECT * FROM myTable.myIndex WHERE gsipk=1 LIMIT 5 DESC

Now that we use a static value for GSI partition key (1) I want to make you aware that this will limit your throughput to 1000 WCU per second, as that is the limit for a single partition. If you require more throughput than that, then you will have to make use of GSI partition key sharding.