Single-table design is a powerful approach to modeling data in DynamoDB because it allows you to store all your data in one place. This approach can simplify your data model and reduce the number of tables you need to manage.
In the traditional approach, you create a separate table for each entity in your application. For example, you might have a table for characters and a table for quests. Each table would have its own primary key and attributes. However, in DynamoDB, this can lead to inefficient queries and complex data models with high read and write costs.
In this take, we will build upon two existing tables for quests and characters and combine them into a single table. This will show you how to model complex relationships between entities in a single table with many different access patterns.
Ready? Let’s get started!
Getting Started
The source code for this article is in the GitHub repository. We will use TypeScript to write the code and the AWS SDK to interact with DynamoDB.
To spin up a quick TypeScript project, you can use the following commands:
Data Model
Let’s start by defining our data model with separate tables. This is a good starting point to understand the entities and their relationships. We will have two tables: quests
and characters
. To make this concrete, let’s define the attributes for each table using TypeScript interfaces.
The partition keys are the same for both tables, which is the username
. The quest_id
is the sort key for the quests
table.
Note the character has many quests, and each character has many items in their inventory. These relationships are focused on the following access patterns:
- Get all quests for a character
- Get character by username
- Fetch inventory for a character
The key takeaway here is to understand the relationships between entities and access patterns because they will drive design decisions. In DynamoDB, these relationships and access patterns can be exploited to model the data in a single table.
Combining Entities
For single-table design, we can begin by combining the keys into a generic compound primary key. This will allow us to store both entities in the same table.
Think of this interface as metadata that will help us differentiate between the entities while giving us the flexibility to store them in the same table. The partition_key
will have a prefix, like USER#username
, and the sort_key
will have a prefix, like CHARACTER#username
or QUEST#quest_id
. Since both initial tables have the same partition key, the generic partition key will be the same for both entities.
The type
attribute will help us differentiate between the entities, and TypeScript will help enforce type safety. This gives us both flexibility and powerful type checking.
With the generic compound primary key, we can now declare what the character
item and quest
item will look like in the single table.
With these interfaces in place, we can express a character in the single table in this way:
The quest item will look like this:
Each item in the table is optimized specifically for the access patterns we want to support. For example, we can fetch all quests for a character by querying the table with the partition key USER#username
. The sort key gives us the ability to filter by the prefix QUEST#
to fine-tune the query.
Note: With single-table design, we put heterogeneous data in the same table and use a generic compound primary key to differentiate between entities. This helps reduce the number of round trips to the database and simplifies the data model.
Access Pattern with Frequent Updates
In DynamoDB you get charged for read and write capacity units per table. You can break fast-moving attributes like total_play_time
into a separate entity to reduce the cost of updating the entire item.
The character’s inventory likely will not change as frequently as the total_play_time
. This allows us to introduce vertical partitioning.
Instead of having a single CharacterItem
with all the attributes, we can break it into two entities: CharacterItem
and InventoryItem
.
Now a single character item will look like this:
And the inventory item will look like this:
Be sure to go back to the parent interface Game
and update the type
attribute to include Inventory
.
This design allows us to update the total_play_time
attribute without affecting the inventory.
Create the Single Table
With the data model in place, we can now create the single table in DynamoDB. We will use the AWS CLI to create the table with the following command:
Then we can add items to the table using the AWS SDK in TypeScript.
You should see the items added to the table in the AWS Management Console. We recommend having a look at the items in the table to understand how the data is stored in DynamoDB.
Why Single Table Design?
In traditional relational databases, we normalize data to reduce redundancy and improve data integrity. The emphasis is on minimizing pressure on resources like CPU and memory to optimize database performance.
In DynamoDB, the emphasis is on reducing the number of reads and writes to our database to optimize the cost. This is where single-table design shines.
When it comes to fast-moving attributes like total_play_time
, do the math. Say, for example, we update the total play time every 30 seconds. If a character item with its inventory takes up 4KB of space, and there are four target indexes, we would incur a cost of 1,920 write capacity units per hour (4KB x 4 x 120). If we break the inventory
into a separate entity, we would only incur a cost of 480 write capacity units per hour (1KB x 4 x 120). This is a 75% reduction.
By breaking up entities into smaller pieces via vertical slicing within a single table, we can reduce the cost of updating the entire item when only a few attributes need to be updated. Because DynamoDB charges per table and per read/write capacity unit, this can lead to significant cost savings.
One Table, Many Access Patterns
Each DynamoDB table comes with a cost. You need to configure alarms, monitor metrics, and handle backups and restores. DynamoDB charges based on the provisioned capacity, the amount of data stored, and the number of read and write requests.
DynamoDB does not support joins or complex queries across multiple tables. Each table has its own partition key, and you can only query the partition key and sort key. Joins must be done in the application layer. This means more round trips to the database and more read and write capacity units are consumed when there are multiple tables.
Wrapping Up
In this post, we've seen how by using a single table design, you can reduce the number of tables you need to manage. This can lead to significant cost savings. You can also reduce the number of round trips to the database and simplify the data model.
Happy coding!
P.S. If you liked this post, subscribe to our JavaScript Sorcery list for a monthly deep dive into more magical JavaScript tips and tricks.
P.P.S. If you need an APM for your Node.js app, go and check out the AppSignal APM for Node.js.