When building my work-tracking app, one of the first decisions I had to make was what database platform to use. For as long as most of us have been in the industry—at least back to the 1980s—the go-to choice for most projects has been a relational database. But relational databases can be overkill for a simple project, and there are a number of good NoSQL database options, including some very-low-cost cloud-based tools. So I decided to take the plunge and put my data in DynamoDB, a low-cost, fully managed NoSQL database offered by Amazon Web Services (AWS).
The DynamoDB single-table model
Amazon’s guidance has recently shifted, but at the time I was designing my app, the recommendation was a single-table design for most applications. For me, coming from a relational database design background, that was a big mental leap. But I found it to be very productive once I understood its power.
For example, my app consists of two primary entities:
- clients
- work items for those clients
Both clients and work items can have attached documents (notes, basically).
In a relational database, that would have been four tables:
- clients
- client_documents
- work_items
- work_item_documents
In Dynamo, it was a single table with different record types. Think of it as one big spreadsheet holding multiple record types, distinguished by key patterns.
Records in DynamoDB consist of:
- a partition key, which determines where the record is stored
- a sort key, which differentiates records for the same partition key
- an arbitrary number of additional fields
My four record types were:
| Type | PK | SK | Additional |
|---|---|---|---|
| Client | CLIENT#{id} | METADATA | ClientName, IsActive |
| Client Document | CLIENT#{id} | DOC#{id} | Content |
| Work Item | WI#{id} | METADATA | Client ID, Parent, Details |
| Work Item Document | WI#{id} | DOC#{id} | Content |
If I wanted to pull a specific client, I would just query
PK = 'CLIENT#123' and SK = 'METADATA'.
To pull all the documents for that client, it would be
PK = 'CLIENT#123' and SK begins_with 'DOC#'.
Easy enough.
Working across record types was a little more complicated. For example, if I wanted to pull all work items for a client—a very common scenario—I wouldn’t be able to do that efficiently using only the PK and SK. Thankfully, DynamoDB provides another feature, Global Secondary Indexes (GSIs). GSIs essentially store a copy of your data but with a different PK and SK, allowing you to define multiple query patterns for a single record type or set of record types.
I was able to define a GSI using client ID to address the “work items for clients” query pattern. I also needed a GSI to query active work items for a given parent work item, which required me to create a derived, compound field on the work item that I could use as the partition key for the GSI.
Growing pains with DynamoDB
GSIs are a powerful feature. Like many AWS features, there’s no flat fee simply for enabling one. But GSIs consume additional storage and processing resources, and those resources do incur additional costs. Also, there’s a finite number of GSIs per table. I wasn’t close to reaching that limit, but I could see how I someday might.
There were other growing pains.
I added a time-tracking feature, which required additional record types, and additional cross-record-type interactions.
And then I added the weekly client summaries feature. Basically I would track changes to work items (the changes were a separate record type) and then, on demand, would pull:
- all of the changes for a week,
- all the work items associated with those changes,
- all the documents associated with those work items, and
- the most recent weekly summary (another record type) for that client
It would then mush them all together and send them to a large language model (LLM) through an API so that it could generate a summary of the current week’s progress and challenges. It all just got to be too much to manage. I was going to have to redesign my record types. I decided to migrate to a relational database instead.
Migration
So I did. I migrated to PostgreSQL, a hugely popular and widely supported open-source relational database.
Was it a major effort to migrate all those different records? Thankfully, no, not really. Following good design practices— especially separating the database-access code from the rest of the application— made it easy to swap out the database without rewriting the rest of the codebase.
Actually I was able to point an AI coding agent at it, which did most of the heavy lifting. I just had to clean up and tweak a little. Really, a pleasantly anticlimactic ending to what was, for me, a very interesting journey.
Takeaways
- NoSQL options can be a great starting point for prototyping an application, but be sure to follow good design patterns so that you can switch out later if you need to
- For many types of applications, particularly those with nontraditional but well-defined access patterns, NoSQL options like DynamoDB can be an excellent choice
- Tried-and-true relational (SQL) databases are still the default choice for most applications