Moving to Single-Table NoSQL Design with DynamoDB
Since its introduction nearly 50 years ago database technology has remained relatively the same, consisting of a very structured and tabular way of storing data. At the same time, application technology evolved at a rapid pace. With advancements like artificial intelligence, machine learning, blockchain, and Web 3.0, more sophisticated ways of representing data are becoming more essential. This has resulted in the rise in popularity of NoSQL databases, like Amazon’s DynamoDB, which provides a way of storing both structured and unstructured data.
Amazon DynamoDB
Amazon DynamoDB offers a fully managed NoSQL database service that is fast and predictable while offering a consistent response time even as the database scales to enormous sizes.
However, data modelling with DynamoDB requires a different mindset coming from a relational database design. With a relational database, it is easy to extend a data model as new requirements arise. This is not so the case with a NoSQL database.
To effectively design a NoSQL data model with DynamoDB, Amazon recommends limiting the number of tables and having a solid understanding of the application's access patterns beforehand.
In this post, we will go over an approach to single-table design using DynamoDB, some considerations to evaluate, and how to implement it. The full source code can be found on my GitHub repo.
Let’s Get Started
With a relational database, data is generally normalized with each individual table responsible for each type of entity. In this example, we would have a Users table and an Accounts table to model the relationship between users and accounts. A foreign key in the Accounts table represents this record belongs to the specific user in the Users table.
To satisfy 1NF normalization, we would also create an AccountType table that is used to reference the Account Types. Managing these relationships can start to become complex as the number of tables increases.
When querying for data the joining of data across multiple tables also becomes expensive. It requires scanning large portions of multiple tables, comparing different values, and potentially filtering the result set.
While it is possible to stream the normalized data into a de-normalized data set like a data lake, this process is typically asynchronous and doesn’t provide real-time access to the de-normalized data set like a NoSQL data model.
DynamoDB Considerations
The way to model the previous dataset with DynamoDB is to pre-join the data into item collections in a single table. Each item collection in DynamoDB shares a partition key. The primary key in a DynamoDB table consists of the composition of the partition key and the optional sort key.
In addition to the partition key and sort key, DynamoDB also consists of two very important types of indexes that are important to understand in the design of a table.
Local Secondary Index (LSI)
In some situations, an application may only need to query data using the base table’s partition key and require a way to sort the data in an alternative way. With a Local Secondary Index, an alternate sort key can be created using the same partition key. This allows the sorting of different attribute values within a table.
Take an example of a table containing games and results. To sort the results by the most recent game, an LSI can be created on the game_ts attribute.
Keep in mind an LSI must be defined at the table creation time. To learn more about LSIs, see more from the AWS documentation:
Global Secondary Index (GSI)
A Global Secondary Index (or GSI) consists of a new partition key and an optional sort key. This new partition key is different from the initial partition key defined in the table effectively leading to the creation of a new “table” that we can further project attributes on. This is useful in situations when an application may need to query data using a different set of attributes as query criteria.
In the example of our games, we can query the results of a particular game by projecting a new GSI with the game_id attribute.
The use of a GSI allows the ability to query non-keyed attributes in a table. To learn most about GSIs, see the following AWS documentation:
Solving This With DynamoDB
When we take this understanding of DynamoDB to our initial example, here we manage 3 accounts for the user Bonnie Ellis through the item collection. Since each of the accounts has the same partition key, they are maintained within the same item collection.
When we extend this to the entire data model we have a single-table design that looks like the following:
With this single-table design, we are now able to fetch data from the database with a single request without requiring a costly join operation across multiple tables. With an understanding of how data will be accessed by our application, we are now able to provide fast and predictable response times for data access that remains constant even as our data grows. The data model accurately represents our application's access patterns!
For example, to get all the positions within an account of a user (GetPositions), we are able to query using the PK= userId, SK that begins with accpos, and include a filter condition where the aggregateId = aggregateId.
Putting it together
By understanding an application's access pattern upfront and designing a single-table design with DynamoDB around this, there is a significant performance and cost advantage to traditional relational data models.
- With only a single request needed to retrieve all the required data for an access pattern, performance is significantly improved over having to perform many complex joins across tables.
- Having a single table can also reduce costs by having to only provision RCUs and WCUs once compared to having to capacity plan across multiple tables.
While the single-table DynamoDB design is extremely performant and scalable, it does have some drawbacks.
- Without fully understanding your application's access patterns upfront, it is very difficult to design a data model that will work.
- Further to this, adding new access patterns is also extremely difficult. There is a lot of inflexibility once a single-table DynamoDB design is made.
- DynamoDB is exceptionally great for OLTP cases but provides challenges for any OLAP access patterns, such as analytical queries that often transform the data into a re-normalized form.
While it may not be the use case for every application that you build, I hope you consider a single-table DynamoDB design the next time you build an application that makes use of a NoSQL database!
Eric Bach is a Senior Software Developer @ Alberta Motor Association who enjoys learning, reading, and writing about leadership principles, event-driven microservices, and all things AWS.
