Part 9. How to Store Data

A relational database stores data in tables, which represent a collection of related items. Each item is stored in a row, and each row in a table has the same columns. For example, if you were working on a website for a bank and needed to store data about its customers, you might have a customers table with each row representing one customer as a tuple of id, name, date_of_birth, and balance, as shown in Table 16.

Relational databases require you to define a schema to describe the structure of each table before you can write any data to that table. You’ll see how to define the schema for the customers table in Table 16 a little later in this blog post. For now, let’s imagine the schema already exists and focus on how to read and write data. To interact with a relational database, you use Structured Query Language (SQL).

The SQL to write data is an INSERT INTO statement, followed by the name of the table, the columns to insert, and the values to put into those columns. Example 144 shows how to insert the three rows from Table 16 into the customers table.

How do you know whether these INSERT statements worked? One way is to try reading the data back out. To read data with a relational database, you use the same language, SQL, to formulate queries. The SQL syntax for queries is a SELECT statement, followed by the columns you wish to select, or the wildcard * for all columns, then FROM, followed by the name of the table to query. Example 145 shows how to retrieve all the data from the customers table.

As you’d expect, this query returns the three rows inserted in Example 144. You can filter the results by adding a WHERE clause with conditions to match. Example 146 shows a SQL query that selects customers born after 1950, which should return just two of the three rows.

SQL is ubiquitous in the world of software, so it’s worth taking the time to learn it, as it will help you build applications, do performance tuning, perform data analysis, and more. That said, going into all the details of SQL is beyond the scope of this blog post series; see Part 9 recommended reading if you’re interested. All I’ll say for now is that SQL and the relational model are exceptionally flexible, allowing you to query your data in countless ways; for example, you can use WHERE to filter data; ORDER BY to sort data; GROUP BY to group data; JOIN to query data from multiple tables; COUNT, SUM, AVG, and a variety of other aggregate functions to perform calculations on your data; indices to make queries faster; and more. If I had used a relational database for that load-generator app when I was a summer intern, I could’ve replaced thousands of lines of custom query code with a dozen lines of SQL.

The flexibility and expressiveness of SQL is one of the many reasons most companies use relational databases as their primary data stores. Another major reason is ACID transactions, as discussed next.

A transaction is a set of coherent operations that should be performed as a unit. In relational databases, transactions must meet the following four properties:

These four properties taken together form the acronym ACID, and it’s one of the defining properties of just about all relational databases. For example, going back to the bank example with the customers table, imagine that the bank charged a $100 annual fee for each customer. When the fee was due, you could use a SQL UPDATE statement to deduct $100 from every customer, as shown in Example 147.

A relational database will apply this change to all customers in a single ACID transaction. Either the transaction will complete successfully, and all customers will end up with $100 less, or no customers will be affected at all. This may seem obvious, but many of the data stores you’ll see later in this blog post do not support ACID transactions, so it would be possible for those data stores to crash partway through this transaction and end up with some customers having $100 less and some unaffected.

Relational databases also support transactions across multiple statements. The canonical example is transferring money, such as moving $100 from the customer with ID 1 (Brian Kim) to the customer with ID 2 (Karen Johnson), as shown in Example 148.

All the statements between START TRANSACTION and COMMIT will execute as a single ACID transaction, ensuring that one account has the balance decreased by $100, and the other increased by $100, or neither account will be affected at all. If you were using one of the data stores from later in this blog post that don’t support ACID transactions, you could end up in an in-between state that is inconsistent. For example, the first statement completes, subtracting $100, but then the data store crashes before the second statement runs, and as a result, the $100 simply vanishes into thin air. With a relational database, this sort of thing is not possible, regardless of crashes or concurrency. This is a major reason relational databases are a great choice as your company’s source of truth. Another major reason for using relational data stores is the support for schemas and constraints, as discussed next.

Relational databases require you to define a schema for each table before you can read and write data to that table. To define a schema, you again use SQL, this time with a CREATE TABLE statement, followed by the name of the table and a list of the columns. Example 149 shows the SQL to create the customers table in Table 16.

This code creates a table called customers with columns called id, name, date_of_birth, and balance. Note that the schema also includes integrity constraints to enforce business rules, such as the following:

Foreign-key constraints are another defining characteristic of relational databases—another major reason they are a good source of truth. These constraints allow you to express and enforce relationships among tables (this is what the "relational" in "relational database" refers to), which is essential to maintaining the referential integrity of your data.

In addition to using CREATE TABLE to define the schema for new tables, you can use ALTER TABLE to modify the schema for existing tables (e.g., to add a new column). Carefully defining and modifying a schema is what allows you to evolve your data storage over time without running into backward-compatibility issues, as I did with my load-generator app.

Initially, you might manage schemas manually, connecting directly to the database and executing CREATE TABLE and ALTER TABLE commands by hand. However, as is often the case with manual work, this becomes error prone and tedious. Over time, the number of CREATE TABLE and ALTER TABLE commands piles up, and as you add more and more environments where the database schema must be set up (e.g., dev, stage, prod), you’ll need a more systematic way to manage your database schemas. The solution, as you saw in Part 2, is to manage your schemas as code.

In particular, schema migration tools can help, such as Redgate Flyway, Liquibase, and Knex.js (full list). These tools allow you to define your initial schemas and all the subsequent modifications as code, typically in an ordered series of migration files that you check into version control. For example, Flyway uses standard SQL in .sql files (e.g., v1_create_customers.sql, v2_create_accounts.sql, and v3_update_customers.sql), whereas Knex.js uses a JavaScript DSL in .js files (e.g., 20240825_create_customers.js, 20240827_create_accounts.js, and 20240905_update_customers.js). You apply these migration files by using the schema migration tool, which keeps track of which of your migration files have already been applied and which haven’t, so no matter what state your database is in, or how many times you run the migration tool, you can be confident your database will end up with the desired schema.

As you make changes to your app, new versions of the app code will rely on new versions of your database schema. To ensure that these versions are automatically deployed to each environment, you will need to integrate the schema migration tool into your CI/CD pipeline (something you learned about in Part 5). One approach is to run the schema migrations as part of your app’s boot code, just before the app starts listening for requests. The main advantage of this option is that it works not only in shared environments (e.g., dev, stage, prod), but also in every developer’s local environment, which is not only convenient, but also ensures your schema migrations are constantly being tested. The main disadvantage is that migrations sometimes take a long time, and if an app takes too long to boot, some orchestration tools will think there’s a problem and try to redeploy the app before the migration can finish. Also, if you are running serverless apps, which already struggle with cold starts, you shouldn’t add anything to the boot code that makes it worse. In these cases, you’re better off running migrations as a separate step in your deployment pipeline, just before you deploy the app.

Now that you’ve seen the concepts behind relational databases, let’s see those concepts in action with a real-world example.

In this section, you’ll go through an example of deploying PostgreSQL, a popular open source relational database, using Amazon’s Relational Database Service (RDS), a fully managed service that provides a secure, reliable, and scalable way to run several types of relational databases, including PostgreSQL, MySQL, Microsoft SQL Server, and Oracle Database. You’ll then manage the schema for this database by using Knex.js and deploy a Lambda function to run a Node.js app that connects to the PostgreSQL database over TLS and runs queries.

Here are the steps to set this up:

Let’s start with creating the OpenTofu module.

To get a sense of how document stores work, let’s use MongoDB as an example. MongoDB allows you to store JSON documents in collections, somewhat analogously to the way a relational database allows you to store rows in tables. MongoDB does not require you to define a schema for your documents, so you can store JSON data in any format you want. To read and write data, you use the MongoDB Query Language (MQL), which is similar to JavaScript. Example 167 shows how you can use the insertMany command to store JSON documents in a collection called bank.

This is the same bank example you saw with relational databases earlier in this blog post, with the same three customers as in Table 16. To read data back out, you can use the find command as shown in Example 168.

You get back the exact documents you inserted, except for one new item: MongoDB automatically adds an _id field to every document, which it uses as a unique identifier, similar to a primary key. You can look up a document by ID as shown in Example 169.

The big difference between key-value stores and document stores is that document stores can natively understand and process the full contents of each document rather than treating them as opaque blobs. This gives you richer query functionality. Example 170 shows how to find all customers born after 1950, the same query you saw in SQL in Example 146.

You also get richer functionality when updating documents. Example 171 shows how to use the updateMany command to deduct $100 from all customers, similar to the SQL UPDATE you saw in Example 147.

All this richer querying and update functionality is great, but it has two major limitations. First, most document stores do not support working with multiple collections; there is no support for joins.^[38] Second, most document stores don’t support ACID transactions, as discussed next.

The code in Example 171 has a serious problem: most document stores don’t support ACID transactions.^[39] You might get atomic operations on a single document (e.g., if you updated one document with the updateOne command), but you rarely get them for updates to multiple documents. That means it’s possible for that code to deduct $100 from some customers but not others (e.g., if MongoDB crashes in the middle of the updateMany operation).

This is not at all obvious from the code, and many developers who are not aware of this are caught off guard when their document store operations don’t produce the results they expect. This is one of many gotchas with using nonrelational databases, especially as your source of truth. Other major gotchas include dealing with eventual consistency, as you’ll see in Section 9.8.3, and the lack of support for schemas and constraints, as discussed next.

Most document stores do not require you to define a schema or constraints up front. This is sometimes referred to as schemaless, but that’s a bit of a misnomer. The reality is that there is always a schema. The only question is whether you enforce a schema-on-read or a schema-on-write.

Relational databases enforce a schema-on-write, which means the schema and constraints must be defined ahead of time, and the database will allow you to write only data that matches the schema and constraints. Most document stores, such as MongoDB, don’t require you to define the schema or constraints ahead of time, so you can structure your data however you want, but eventually, something will read that data, and that code will have to enforce a schema-on-read to be able to parse the data and do something useful with it. For example, to parse data from the bank collection you saw in the previous section, you might create the Java code shown in Example 172.

This Java class defines a schema and constraints: you’re expecting field names such as name and balance with types String and int, respectively. More accurately, this is an example of schema-on-read, as this class defines the schema you’re expecting from the data store, and either the data you read matches the Customer data structure, or you will get an error. Since document stores don’t enforce schemas or constraints, you can insert any data you want in any collection, as in Example 173.

Did you catch the error? The code uses birth_date instead of date_of_birth. Whoops. MongoDB will allow you to insert this data without any complaints, but when you try to parse this data with the Customer class, you may get an error. And this is just one of many types of errors you may get with schema-on-read. Since most document stores don’t support domain constraints or foreign-key constraints, you will also have to worry about typos in field names, incorrect types for fields, IDs that reference nonexistent documents in other collections, and so on.

Dealing with these errors when you read the data is hard, so it’s better to prevent these errors in the first place by blocking invalid data on write. That’s an area where schema-on-write has a decided advantage, as it allows you to ensure that your data is well formed by enforcing a schema and constraints in one place, the (well-tested) data store, instead of trying to enforce it in dozens of places, including in every part of your application code, every script, and every console interaction.

That said, schema-on-read is advantageous if you are dealing with semistructured or nonuniform data. I wouldn’t use a document store for highly structured bank data, but I might use one for user-generated documents, event-tracking data, and log messages. Schema-on-read can also be advantageous if the schema changes often. With a relational database, certain types of schema changes take a long time or even require downtime. With schema-on-read, all you have to do is update your application code to be able to handle both the new data format and the old one, and your migration is done. Or, to be more accurate, your migration has just started, and it will happen incrementally as new data gets written.

You should consider one other trade-off when choosing between schema-on-read and schema-on-write: performance. With schema-on-write, as with a relational database, the data store knows the schema ahead of time, and the schema is the same for all the data in a single table, so the data can be stored efficiently, both in terms of disk space usage and the performance of disk lookup operations. With schema-on-read, as with a document store, since each document can have a different schema, the data store has to store the schema with that document, which is less efficient. This is one of the reasons that data stores designed for performance and efficiency typically use schema-on-write. This includes data stores designed to extract insights from your data by using analytics, as discussed in the next section.

A message queue is a data store that can be used for asynchronous communication between producers, who write messages to the queue, and consumers, who read messages from the queue, as shown in Figure 87.

The typical process of using a queue is as follows:

Queues are most often used for tasks that run in the background as opposed to tasks you do during a live request from a user. For example, if you are building an app that lets users upload images, and you need to process each image (e.g., create copies of the image in different sizes for web, mobile, and thumbnail previews), you may want to do that in the background rather than making the user wait for it. To do that, your frontend server stores the original image on a file server and adds a message to a queue with the location of the image. Later, a separate consumer process reads the message from the queue, downloads the image from the file server, processes the image, and when it’s done, deletes the message from the queue. Other common use cases include encoding videos, sending email campaigns, delivering notifications, generating reports, and order processing.

Popular message queues include RabbitMQ, Apache ActiveMQ, and ZeroMQ (full list). Queues provide several key benefits:

Whereas message queues are used for one-to-one communication between a producer and a consumer, event streams are used for one-to-many communication, as discussed next.

Event-streaming tools allow services to communicate asynchronously in a manner similar to a message queue. The main difference is that instead of each message being consumed by a single consumer, streaming allows each message to be consumed by multiple consumers, as shown in Figure 88. Some of the most popular event-streaming tools include Apache Kafka, Amazon Kinesis, and NATS (full list).

The typical process of using event streaming is as follows:

At its most basic level, event streaming could be used as a replacement for a message queue to allow services to communicate asynchronously. However, this is not the primary use case. A message queue is typically used to allow service A to send a message specifically destined for service B. In contrast, in event streaming, every service publishes a stream of events that represent important data points or changes in state in that service but that aren’t necessarily designed for any one specific recipient. This approach allows for multiple other services to subscribe and react to whatever streams of events are relevant to them. This is known as an event-driven architecture.

The difference between messages in a message queue and events in an event stream has a profound impact on the way you build your services. In Figure 86, you saw a simplified diagram showing all systems sending their data to a data warehouse. Figure 90 shows a slightly more realistic image.

As the number of services grows, the number of connections between them grows even faster. With n services, you end up with roughly n² connections across a variety of interfaces and protocols that often require complicated ETL. Setting up and maintaining all these connections can be a massive undertaking. Event streaming offers an alternative solution, as shown in Figure 91.

In Part 7, you saw that a network switch allows you to connect n computers with n cables (each computer has one cable connected to the switch) instead of n². Analogously, an event-streaming platform allows you to connect n services with n connections (each service has one connection to the event-streaming platform) instead of n². Dramatically simplified connectivity is one of the major benefits of an event-driven architecture. Another major benefit, and one that’s less obvious, is that an event-driven architecture allows you to add new services—new consumers—without having to modify any existing producers.

An example can help illustrate the power of this concept. First, consider an architecture where services message each other directly. For example, service A might send the message a new image has been uploaded to location X, please process that image to service B. Six months later, you want to add a new service C to scan images for inappropriate content. For this service to do its job, you have to update service A to send an additional message to service C: a new image has been uploaded to location X, please scan that image for inappropriate content.

Now compare this to an event-driven architecture, where service A doesn’t have to know about the existence of other services at all. Service A merely publishes important events, such as a new image has been uploaded to location X. Perhaps on day one, service B subscribes to this event stream and is able to process each image. Six months later, when you add service C, it can subscribe to the same event stream to start scanning images for inappropriate content—without any need to modify service A. You could add dozens more services that consume service A’s event stream, again, with no need for A to be aware of them at all.

In an event-driven architecture, every service publishes important events, such as a new user has registered, a user clicked a button, an order has been placed, a server is down, and so on. Any other service can subscribe to any of these event streams to perform a variety of actions (e.g., update a search index, detect fraudulent activity, generate a report, or send out a notification). Moreover, each time a service subscribes to an event stream, it can choose to start at offset 0 in that stream (refer to Figure 89), effectively "going back in time," and processing all the historical events from that event stream (e.g., all images that have ever been uploaded) until it catches up to the latest offset, or it can start immediately at the latest offset and just process new events.

Event-driven architectures provide many benefits:

Let’s now move on, not to a specific data storage use case but to the general problem of data store scalability and availability.

Most relational databases are designed to run on a single server. However, as you learned in Part 3, a single server is a single point of failure—a bottleneck to scalability and availability. This isn’t something you have to worry about until you are storing tremendous amounts of data and serving a huge amount of traffic. However, if you get to that point, you should be aware that it’s easy to scale a database vertically by making a single server more powerful (more CPU, more memory, more disk space), but it’s harder to scale a database horizontally across multiple servers. To horizontally scale a relational database—or any data store—there are two primary strategies, replication and partitioning.

In the mid- to late 2000s, the challenges with scalability and high availability for relational databases led to the creation of nonrelational databases, often called NoSQL databases. NoSQL, which at various times stood for Non-SQL or Not-Only-SQL, is a fuzzy term that refers to databases that do not use SQL or the relational model. Over the years, many types of nonrelational databases have been created, most of which failed to gain wide adoption (e.g., object databases in the '90s, XML databases in the early 2000s), but NoSQL in particular refers to databases that were built in the late 2000s, primarily by internet companies struggling to adapt relational databases to unprecedented demands in performance, availability, and data volume.

The early inspirations for NoSQL included Google’s 2006 paper on Bigtable and Amazon’s 2007 paper on Dynamo. The actual term "NoSQL" came after these papers, originating as a Twitter hashtag (#NoSQL) for a 2009 meetup in San Francisco to discuss "open source, distributed, nonrelational databases," which is still the best definition of NoSQL that we have. The primary types of data stores that fall under the NoSQL umbrella are key-value stores, document stores, and columnar databases, all of which you’ve already seen in this blog post.^[41]

Most NoSQL databases were designed from the ground up for scalability and availability, so the default deployment often includes replication and partitioning. For example, MongoDB is typically deployed in a cluster that consists of multiple shards, and each shard has a primary (for writes) and one or more replicas (for reads), plus dedicated servers that handle query routing, auto-sharding, and auto-rebalancing. The benefit is that you get a highly scalable and available data store. The cost is that these are complicated distributed systems, and that comes with challenges, as you’ll see in the next section. For now, I’ll just say that, in the pursuit of scalability and high availability, most NoSQL data stores sacrifice key features from relational databases, such as ACID transactions, referential integrity, and flexible query languages that support joins.

For some use cases, this was too many sacrifices, which led to the creation of a new breed of relational database in the mid- to late 2010s, often called NewSQL, that tried to retain the strengths of a relational database (e.g., ACID transactions, SQL), while providing better availability and scalability. Some of the major players in this space include Google Spanner, Amazon Aurora, and CockroachDB (full list). Under the hood, these are also complex distributed systems that use replication and partitioning to achieve high scalability and availability, but they use new techniques to not sacrifice too many relational database benefits along the way. The approaches they use are fascinating but beyond the scope of this blog post series, especially as many of the early NewSQL players died out, and the ones that remain are still relatively young and immature.

Remember, data storage technology takes at least a decade to mature. As of the writing of this blog post series, most NoSQL data stores are 10–15 years old, so they are just starting to become mature and reliable systems. Most NewSQL systems are still less than 10 years old, so they are still relatively young (at least as far as data storage technologies go). Given that both NoSQL and NewSQL databases are typically complex distributed systems, they face challenges that may take even more than a decade to solve, as discussed next.

As you may remember from Part 6, distributed systems are complicated, and distributed data stores even more so. One of the challenges is that all distributed systems are subject to the CAP theorem, which gets its name from the following three properties:

All three of these are desirable properties for a distributed system, but the CAP theorem says you can pick only two. Moreover, in practice, no network can guarantee there will never be any partitions, so all real-world distributed systems have to provide partition tolerance—they have to pick P—which means you really get to pick only one more. That is, in the presence of a network partition, does your distributed system provide consistency (C) or availability (A)?

Some systems, such as HBase and Redis, pick C, so they try to keep data consistent on all nodes, and in the case of a network partition, they lose availability. If you use a data store that picks C, you have to accept that, from time to time, that data store will be down. Other systems, such as Cassandra, Riak, and CouchDB, pick A, so they are eventually consistent, which means that during a network partition, they will remain available, but different nodes may end up with different data. In fact, even without a partition, eventually consistent systems may have different data on different nodes, at least for a short time. If you use a data store that picks A, you have to deal with an eventually consistent data model, which can be confusing for programmers and users (e.g., you just updated some data, but after refreshing the page, you still see the old data).

Another challenge with distributed systems is that they introduce many new failure modes. At some point, every data store will fail. The question is, how many ways can the system fail, and how easy is it to understand and fix each one? Usually, the number and complexity of failure modes on a single-node system (e.g., a relational database) is far lower than on a distributed NoSQL or NewSQL system that has multiple writers, auto-sharding, auto-rebalancing, eventual consistency, consensus algorithms, and so on. Having to deal with too many failure modes was one of the main reasons Pinterest stopped using Cassandra and Etsy stopped using MongoDB.

One more challenge with distributed systems is figuring out a business model to support them. Building a reliable data store takes a decade or two, and finding a way to sustainably pay developers during all that time is tricky. Many data store companies have shut down (e.g., RethinkDB, FoundationDB, GenieDB, ScaleDB, and many others) which is a huge problem if your company relies on these technologies for storing your most valuable asset! A data store that has been around 20+ years is not only more mature than one that just came out in the last few years, but also more likely to still be around another 20 years from now (this is called the Lindy effect).

This doesn’t mean you should avoid distributed systems, NoSQL, or NewSQL. It just means that you need to understand what they are good at, what they are not good at, and the risks you are taking on. For example, if you have extreme scale and availability requirements that you can’t handle with a relational database, and you have a team willing to put in the time and effort to deploy and maintain a NoSQL or NewSQL database, then by all means, go for it. But if you’re a tiny startup with virtually no traffic, using a complex distributed data store right out of the gate might not be the right way to spend your limited resources.

Note that things can go wrong even with the most mature and battle-tested data store. Therefore, as the final topic of this blog post, let’s talk about how to manage backup and recovery to minimize the risk of losing data.

The following are the most common strategies for backing up data:

These strategies are not mutually exclusive. In fact, each strategy has different advantages and drawbacks, and protects against different types of disasters, so it’s usually a good idea to use several of these strategies to ensure you’re fully covered. Let’s take a closer look.

I recommend a few practices when backing up your data:

Now that you’ve learned about backup strategies and recommended patterns, let’s put it into practice with a real-world example using PostgreSQL.

Earlier in this blog post, you created a lambda-rds root module that could deploy PostgreSQL in AWS by using RDS. Let’s update that example to do the following:

Head over to the lambda-rds module, open up main.tf, find your usage of the rds-postgres module, and update it as shown in Example 178.

One of the benefits of using a managed service like RDS is that it makes it easy to enable common functionality, such as backups:

To add a read replica, use the rds-postgres module a second time, as shown in Example 179.

Again, using RDS makes it easy to use common functionality such as read replicas:

The read replica, as its name indicates, is read-only, whereas the primary database accepts both reads and writes. To run schema migrations, you need both read and write access, so you should continue to use the primary URL for those. However, the Lambda function needs only read access for its one database query, so you can update it to talk to the read replica rather than the primary, as shown in Example 180.

This code updates the DB_HOST environment variable to use the replica hostname instead of the primary. Run init and apply to deploy these changes:

An RDS replica can take 5–15 minutes to deploy, so be patient. When apply completes, head over to the Lambda console, click the lambda-rds-app function, select the Configuration tab, and click "Environment variables" on the left side. You should see something similar to Figure 92.

The Lambda function should now have the DB_HOST set to the replica’s URL, and not the primary’s. If everything looks correct, test out the URL in the function_url output variable one more time:

If you see the exact same results as before, congrats, that means your Lambda function is now reading data from a read replica! Moreover, your database is now backed up, both via the replica and daily snapshots.

When you’re done testing, commit your code, and run destroy to clean up everything. As part of the destroy process, RDS will take one final snapshot of the database, which is a handy fail-safe in case you delete a database by accident.