What’s the fuss about DynamoDB Single Table Design — My Take
As someone who comes from a SQL background, believe me when I say, working with a NoSQL Database was at the very least of my desires.
In this article, I give my take on the Why, How, and Benefits of using a Single Table Design in DynamoDB. I am assuming my audience has a good knowledge of what DynamoDB is, and how it works, however, let me do a quick overview.
Amazon DynamoDB is a fast, flexible, and fully managed serverless NoSQL database service that allows auto-scaling, in-memory caching, backup, and restore options. Due to its design, its performance is consistent no matter how large your database grows.
That being said, the crux of this article: Single Table Design
Single Table Design(STD) essentially means using a single table in a database to build a particular project.
Unlike creating multiple tables, we would have only a single table in the database and all parts of the app would interact with this single table. This approach is mostly been used in NoSQL databases of which DynamoDB is one.
NoSQL databases can be flexible, highly scalable, and have extremely high performance, but most sacrifice some benefits found in relational databases such as the beautiful join clause and normalization.
Why the need for a Single Table Design?
Note: Familiarity with DynamoDB essentials is important before deciding on the design architecture to use.
A classic example of data modeling in SQL DBs can be seen below. We can see we have two tables namely: Club and Players, we also see the relationship between the tables.
Supposing we needed to get a list of clubs with their players, we would need to use join statements to get related data between the tables. We do this with the help of the foreign key ClubId in the Players table which acts as a pointer:
This approach works well and is used a lot in SQL databases, however, SQL joins are quite expensive and they require scanning multiple tables in the database before actually getting the needed result. This is time-consuming and a very expensive operation, it even gets worse cos as the data in the database increases the response time of queries increases leading to more hours spent creating new indexes as well as renormalizing the tables, thereby making scalability a very tedious task.
DynamoDB tries to achieve high scalability and consistency, hence the concept of joins is removed from it.
Since DynamoDB intentionally removed the concept of joins to achieve high scalability, how then do we handle related data/relations our applications may use.
Solution: How to use Single Table Design in DynamoDB?
We pre-join our data using item collection as opposed to a post-join when we use SQL databases join. An item collection in DynamoDB refers to all the items in a table or index that share a partition key.
To achieve a good design you need to do the following before coming up with a good STD:
- Create an entity-relationship diagram to see how all entities interact, this initial process would help us see all possible interactions that could happen in our application.
- List all access patterns the application could use, after the ER diagram, we would have a clear picture of possible access patterns that the application would need.
In the example below, we have a DynamoDB table that contains players and the clubs in which they play. The primary key used is a composite primary key where the partition key has a format: CLUB#clubname and the sort key: PROFILE#clubname or PLAYERS#playerId.
This approach makes it easy to both stores the club details as well as all players in a particular club in a single item collection.
We also make use of a generic Partition key: PK and sort key: SK since we would be having different value types in them:
Now when we want to fetch the Clubs and Players, we can do it in a single request without needing a costly join operation nor needing to make multiple requests to get both data.
Supposing we want to query and get all players where their country is Spain, we would need to create a secondary index since we don’t have an access pattern with that ability. We can create a GSI on the ClubCountry attribute. Then we can read the needed data off the table using this GSI.
GSI simply means an index with a partition key and a sort key that can be different from those on the base table.
LSI means An index that has the same partition key as the base table, but a different sort key.
When not to use STD in DynamoDb?
Now, that we see the need for this, we need to know that not every scenario would need us creating an STD or even using DynamoDb for instance.
As Alex Debrie noted in an article of his, “whenever one needs query flexibility and/or easier analytics more than I need blazing fast performance, this approach may not be the best fit for that project”.
Pros of using STD in DynamoDb
Below are some of the key benefits of using the Single Table Design approach:
- Fewer tables to manage
- Reduced query time as we don't do joins
- Less number of requests made to retrieve data
- Reduced cost since we only make requests to a single table
- Easier monitoring as you only have to monitor one resource, alarms, metrics
Cons of STD use in DynamoDb
It is not all rosy, there exist some drawbacks when going with the single table design approach:
- Steep Learning curve: Using myself as an example, it took quite an amount of effort and time before I got the underlying concept involved.
- The difficulty of exporting your tables for analytics.
My key Takeaways
In this article, we saw the need for using a Single Table Design in DynamoDB, how to achieve it, as well as how organizations could benefit from it.
For further reading on this concept, I recommend reading Alex Debrie's article where he wrote a detailed write-up on this, as well as AWS ReInvent 2020 talk he gave here.
