How To Satisfy Multiple Access Patterns By Overloading Keys

When the single table design was introduced in DynamoDB, it changed the way many users designed and built databases with DynamoDB.

It solved the issue of being able to query relational data in a NoSQL database while reducing query latency at high scale.

But besides for the single table design, one strategy that improves upon it is overloading primary keys to satisfy multiple access patterns.

In this article, let’s understand what key overloading is, what problems it solves and an example of using it in a single DynamoDB table design.

What is Key Overloading

In DynamoDB, an item is identified by its primary key - a partition key and optionally, a sort key. For this article, we’ll consider the sort key as well.

Traditionally, you would define your partition key as something like “userID” and your sort key as “email”, for example.

Then if you have an access pattern that needs to get all users by location, you can create a GSI with primary keys of “province” for the partition key and “city” for the sort key.

The issue with this design is that if we need to store different related entities on the same table, say user addresses or payment details records, we wouldn’t be able to since an address record can’t have an email attribute.

To solve this issue, we can define our primary key as an abstract name like “pk” and “sk” (for partition key and sort key).

Then, for user items we can use userID and email as the primary keys, for address items we can use city name and address, and for payment details we can use the card brand and expiry date instead. Each entity item has its own primary key attributes.

Satisfying multiple access patterns with Key Overloading

With a key overloaded design we can satisfy various access patterns.

Imagine we have an access pattern that is common that requires us to get a user’s data, their address and their payment info, all in a single request for minimal latency.

Here’s how we can design our primary keys for this table to satisfy this query.

We’ve now used the same partition key for all info type items but each with their own sort keys.

With this design, we can query for all of these info type items, like so:

KeyConditionExpression: “pk = ‘user#101’ AND begins_with(sk, ‘info#’) ”         

This will fetch all items in the “user#101” partition (pk) and whose sort key value (sk) begins with the substring “info#” - which is all 3 items shown above.

Now we can satisfy multiple access patterns with this same design:

We can query for user info only:

KeyConditionExpression: “pk = ‘user#101’ AND sk = ‘info#user’ ”         

We can query for all user addresses:

KeyConditionExpression: “pk = ‘user#101’ AND begins_with(sk, ‘info#address’) ”         

We can query for all user payment cards:

KeyConditionExpression: “pk = ‘user#101’ AND begins_with(sk, ‘info#payment’) ”         

Conclusion

So why do we need key overloading?

Related data is usually fetched together. By storing it on different tables, according to entity, we increase the latency and complexity of queries.

But by storing them on the same table and overloading our keys to enable different key designs for each item we can fetch related data together, more efficiently.

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

?? You can share the article with your network to help others learn as well.

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

https://calendly.com/urielas1/dynamodb-consultations

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