Overview
A database is a system that holds an application’s data and answers questions about it. Four kinds appear in most products, and choosing between them is the decision this page covers. It comes before designing what goes inside, and it is harder to reverse than the decisions that follow, because changing it means moving every row and rewriting every query that touched them. The decision is often read as a choice between products. It is more usefully read as a choice between shapes of access: what the application asks of the data, how consistent the answers must be, and how much data there will eventually be. This page covers the four kinds and what each is good at, the two axes that cut across them, why the relational database is the default answer, worked examples of reaching for a second one, and what that second one costs.What each kind is good at
The kind settles the shape of access and no more. The product used within a kind specializes further, and properties such as volatility are settled there rather than by the kind.
Relational database
Data is held as rows and columns, and the relationships between tables are declared as foreign keys. A join answers a question spanning several tables in one query, which means a question nobody anticipated at design time can still be asked later. Two mechanisms come with it and are what make the kind general-purpose. A transaction makes several changes succeed or fail as one unit. Constraints state the conditions the data must satisfy, and the database rejects any write that would break them. MongoDB and DynamoDB offer transactions too, so this is not something the other kinds cannot have. It is that a relational database provides both by default and across a wider range. What it is not built for is write volume beyond what one machine handles, or aggregating years of history. Each of those has a database shaped for it.Document database
Data is held as nested documents, in the shape of JSON. Each document can carry its own set of fields, so the schema does not have to be fixed in advance. The defining difference from a relational database is that things read together can be embedded in one document. An order and its lines kept as a single document are retrieved in one read, with no join. As a matter of implementation, most well-known products are built to spread data across nodes. The model does not require it — document databases run on a single node, and relational databases distribute — but where a distributed setup is chosen, the split is decided by a key, so which key the data is fetched by has to be settled first. The cost is that updating an embedded value reaches many documents. A product name embedded in each order means a rename has to reach every order containing it. The shape rewards knowing how the data will be read before storing it, and asks for a rewrite when the reading changes later. This kind is chosen differently from the other three. A key-value store or a data warehouse sits beside a relational database; a document database replaces it. The decision is usually made early in a product’s life, and it turns on whether the access pattern can be fixed in advance and whether the scale calls for distribution.Key-value store
The structure is the smallest one possible: a key goes in, a value comes out. The database does not interpret the value, so its meaning is entirely the application’s concern. Redis and Memcached hold values in memory, which makes an individual read or write faster by orders of magnitude than a disk-backed database. Each key can carry a time to live, letting the database expire entries instead of the application scheduling deletions. The structure does not require memory, though. DynamoDB carries both a key-value and a document character, writes to disk, and replicates across locations. A sort key even allows range queries, and it is chosen for distribution and managed operations rather than for latency. As the product table above shows, properties like these are settled by the product rather than by the kind. This fits caches, sessions, rate-limit counters, and simple queues. It does not fit any lookup that is not by the key, since the only alternative is scanning every key, nor does it fit data whose records reference each other.Data warehouse
It is separated from the application’s transaction processing (OLTP) to answer analytical queries (OLAP) over the whole history. An analyst scanning several years does not compete for resources with the requests users are waiting on. The implementation that makes this work is storing data by column rather than by row. A query aggregating two columns reads only those two, no matter how many the table has, which puts hundreds of millions of rows within practical reach. The same design makes it poor at single-row reads and writes and at low-latency responses. A data warehouse answers questions about the business, not requests from the application.Two axes that cut across the kinds
Beyond what each kind is good at, two properties decide what may be placed in a given database. One is settled by the data placed there, the other by the product and its configuration. Neither can be read off the table of kinds.Owner or copy
Whether losing the database means losing the business’s records, or means rebuilding them from somewhere else.
Durable or volatile
Whether the data survives a restart of the process, or the database comes back empty.
Owner or copy
The database that owns the data needs durability, backups, and constraints that keep it correct, because losing it means losing records that exist nowhere else. A database holding a derived copy receives its contents from somewhere else, so losing it is an outage while it rebuilds rather than a loss. Which one applies is settled by the data, not by the kind. A data warehouse synchronized from the production database holds copies, but one receiving an event stream directly owns that data. The mistake that hurts is taking the second case for the first, and running data you own without backups or a retention policy.Durable or volatile
Volatility is whether the data survives a restart, and it is settled by the product and its configuration rather than by the kind. Memcached holds values in memory only, so restarting the process empties it. Redis also holds values in memory but writes snapshots to disk by default, so a restart loses only the most recent writes. Both differences sit inside the same kind. Persistence of this sort is still safer to read as a way to shorten the rebuild than as a durability guarantee. Volatility decides what may be placed there. A lost session costs the user one login, and the application recovers by itself. A counter that exists nowhere else cannot be reconstructed once it is gone, so its home is the database that owns the data, with the key-value store holding at most a cached copy of it.The relational database is the default
Starting with a relational database is not conservatism. Three properties make it the one that can be chosen before the requirements are fully known. Queries can be written after the data is stored. A relational database is organized around the structure of the data rather than around the questions asked of it, so a question nobody anticipated can still be answered with a new query. Databases optimized for a known access pattern require the pattern to be known first, and a new question can mean reorganizing the data. Transactions hold multiple changes together. When an order and its payment must both succeed or both fail, a transaction states that directly. Reproducing this across two databases requires the application to handle partial failure, which is work that is easy to underestimate and difficult to verify. Constraints keep invalid data out. Foreign keys, uniqueness, and check constraints are evaluated on every write path, including batch jobs and statements run by hand during an incident. The practical consequence is that the relational database is chosen unless a specific problem rules it out. The other kinds are added when a problem appears that it does not solve.Choosing a database, by example
The question in the middle is the one most often skipped. An index that was never added, a query that scans when it could seek, and a read replica that was never provisioned each solve problems that are otherwise attributed to the database being the wrong kind. Two worked examples show where that question is answered “yes” and where it is answered “no”.A dashboard starts competing with users
A sales dashboard built onGROUP BY is fine over a few months of data. As the history grows to years, that single query occupies enough of the database that the requests users are waiting on slow down.
An index or a revised query sometimes suffices, but what defines this problem is competition with production requests. Sending the aggregate to a read replica stops the heavy query from taking production’s resources, and in most cases the problem ends there.
A data warehouse becomes the answer when the analysis turns ad hoc: analysts writing queries nobody planned for, joining several tables, aggregating hundreds of millions of rows. What brings that workload into a usable time is column-oriented storage and a design aimed at analysis.
Session traffic overwhelms the table
Sessions kept in a relational table are read and written on every request. As the number of signed-in users grows, writes that have nothing to do with the business data take an increasing share of the database. Moving them to a key-value store makes those reads and writes faster by orders of magnitude, and the time to live handles expiry without a cleanup job. The test for whether something belongs there is what happens when it is lost. A session costs one login, and the application recovers on its own. A counter such as “orders placed today” does not: kept only in the key-value store, it is gone at the next restart, which is the volatility described above deciding the placement.What a second database costs
Adding a database is not only the cost of running it. Three costs land on the application and stay there.A second write path
Every change to the source has to reach the copy. That path can fail independently of the write that triggered it.
Divergence
The copy is behind the source, sometimes briefly and sometimes permanently after a failure. The application has to state which staleness is acceptable.
Operations
Monitoring, backups, upgrades, capacity, and someone who can diagnose it at three in the morning.
Related pages
Performance
Measuring before optimizing, and the difference between problems that need faster code and problems that need less work.