DynamoDB Data Modeling For Efficient Date-Based Sorting

Quite often when you query data in your DynamoDB table, you will want the results to be sorted in a certain way.

Sometimes the sorting is by a number or by date. 

Either scenario is simple to model with you sort key.

When you query your data, DynamoDB will return the results sorted by the sort key in lexographic order. Then you can specify the sort order (i.e. ascending or descending) using the ScanIndexForward attribute.

Let’s take an example to demonstrate a data model that leverages date-based sort keys for easy sorting of query results.

What is date-based sorting?

Date-based sorting in DynamoDB is the idea of prefixing your sort keys with a date so that your data is naturally returned in sequential order.

Imagine an online store’s database system that stores customer orders.

In this scenario, we have 2 options:

• Add the order date directly into each order item’s sort key

• Add the order date to a GSI sort key

Which scenario is best? 

Usually the second one, i.e. adding a GSI sort key that contains the date.

This strategy is preferred as it leaves your base table’s sort key a simple orderID key. 

The first scenario also works if you imbed the date as the orderID, although that may sometimes add some querying complexity.

Let’s look at a specific data model to understand this:

Data Model 1 — Using the Base table 

If we were to query all orders made by a given user, we would get the results sorted by the order date — as the date is directly embedded with the sort key.

i.e. orderID = “order#2025–08–21#123”

The added complexity of this approach is that the orderID is not URL-safe. Meaning everytime you use it in a browser, you need to encode and decode it (using encodeURIComponent).

This isn’t a really big deal but it is important to be aware of so that client side functionality works as expected.

Data Model 2— Using a GSI 

The second, cleaner approach, albeit at the expense of adding a GSI, is to offload the date-based sorting to a secondary index.

With this approach you can model your base table order items normally:

Here your data is simple — you don’t need to embed the date into the orderID.

But querying the base table directly will return the orders in order of the ID (typically IDs are not sequential and are usually random e.g. “gjfj83932bb”).

A clever strategy to use is date-based UUIDs. This allows you to have date-based IDs and avoid the extra GSI.

In the case where you do not want to use date-based UUIDs, the typical approach is to then create a GSI with the following data model:

With this approach, you can query single orders using the base table by providing the userID = “101” and the orderID = “123”.

Then all multiple order queries can be fetched using the GSI above by providing the userID = “101” and simply the prefix “order#” using a begins_with() query.

That will fetch all orders made by a user sorted by the order date.

Then in our query we can specify “ScanIndexForward: true” to sort in ascending order or “ScanIndexForward: false” for descending order.

Simple yet efficient and powerful.

Using LSIs to reduce costs 

You may have noticed the partition key of my base table (user#101) is the same as the partition key of my GSI (user#101).

That means I can use an LSI for this access pattern of fetching a user’s orders sorted by date.

The benefit is not having to provision an extra GSI, since LSIs are free to provision, this can provide us with a cost benefit (at scale).

An example order item would look like this using an LSI:

pk: "user#101",
LSI1: "order#2025-08-21#123"

👋 My name is Uriel Bitton and I hope you learned something in this edition of Excelling With DynamoDB.

📅 If you're looking for help with DynamoDB, let's have a quick chat.

🙌 I hope to see you in next week's edition!