Four questions before you pick a database
Four questions, asked in order, three made-up systems walked through them, and a table of where the usual databases fit.
In November 2015, Discord had around 100 million messages in MongoDB, and the data and its index no longer fit in memory. Latency became unpredictable, and the team moved to Cassandra. Diaspora, an open-source social network, had already left MongoDB too, for a relational database. Two teams left the same document database in opposite directions, and both were right, because they read their data in different ways.
Before choosing a database, I work through four questions, in order: how the data is read, how big it really gets, what you can afford to get wrong, and who will run it. The cases below show how different teams answered them.
Step 1: write down how the data is read
List the five or ten queries your app will run most, including the ones the roadmap will add. Next to each, note three things: does it fetch one key or many rows, does it read a record alone or join it to others, and in what order does it want the results?
That list is what split Discord and Diaspora. Discord's main query fetched a channel's messages in order. Cassandra, a wide-column database, stores rows grouped by a key (the channel, split into time buckets) and sorted inside it (by message ID), so the data sat on disk in the order the app asked for it. Diaspora's data was the opposite. Sarah Mei described in 2013 how users, friends, posts and comments all point at each other. A document store, which keeps each record as one nested blob, made them copy the same user into many documents and join things back by hand. (People argued the fault was their schema, not MongoDB. Either way, the shape didn't fit how they read it.)
If you can't write the list yet, you're not ready to choose a database.
Step 2: measure the scale you actually have
Write down three numbers: how much data you have today, how fast it grows per month, and how many reads and writes per second you see at peak. Then ask whether one large machine runs out within a year.
A lot of choices are made for traffic that never arrives. Figma is a useful correction. In 2020 its whole product ran on a single Postgres database, on the largest instance AWS offered. Its data then grew almost 100x. When one machine finally wasn't enough, the team looked at distributed databases and chose to split Postgres themselves, first by groups of tables, then by rows, because moving to anything else meant a complex migration and they had only months of runway. By the time one database ran out, they knew exactly which part was hurting.
Step 3: decide what you can afford to get wrong
Picture two people who share a shopping list on their phones and walk into a supermarket with no signal. Each can keep editing and merge later, accepting that milk might get bought twice. Or the app can refuse edits until both phones reconnect, so the list is never wrong but nobody can use it in the store.
That's the CAP theorem, stated by Eric Brewer in 2000 and proved by Seth Gilbert and Nancy Lynch in 2002. When the network between copies of your data breaks (a partition), a distributed database has to choose between answering anyway and making every answer reflect the latest write. Amazon chose the first phone for its shopping cart: its Dynamo paper (2007) says "add to cart" must work even while servers fail, and accepts that a deleted item can come back after a merge.
Most teams get the second phone without asking. One Postgres primary is one list, so a read from it is always current. The choice comes back when you add copies: a read replica trails the primary a little, a second region trails more. Daniel Abadi's PACELC adds the ordinary days: with a Partition, choose Availability or Consistency, Else choose Latency or Consistency, because waiting for the other phone to confirm makes every write slower.
So go through your data kind by kind and mark each one: is a stale or duplicated answer an annoyance or an incident? A profile picture a few seconds old is an annoyance. Selling the last item twice is an incident. (Martin Kleppmann asked people to stop labelling whole databases "CP" or "AP", since most meet neither definition. The label belongs on your data, not on the product.)
Step 4: name who will run it
Write down who takes the backups and has tested restoring them, how schema changes ship, and who gets paged at night.
In 2016 Uber moved most of its databases from Postgres to MySQL, two relational databases with the same data model. The reasons were how Postgres wrote rows and indexes to disk, how replication behaved, and how painful major upgrades were. Postgres developers answered with rebuttals, but the point stands: the choice that bit Uber was about operations. Discord met the same question five years after choosing Cassandra. With 177 nodes, garbage-collection pauses and frequent pages, they kept the kind of database and changed the engine to ScyllaDB, which is compatible with Cassandra and written in C++. The cluster went from 177 nodes to 72.
A database your team can't operate is the wrong one, however well it fits on paper.
Three systems through the four steps
The next three systems are hypothetical.
A booking app for a small clinic
Reads: appointments by doctor and day, a patient's history, an invoice joined to its visits. Mixed queries, lots of joins.
Scale: a few thousand appointments a month. One small machine will last years.
What can go wrong: two patients in the same slot is an incident. A unique constraint on doctor and slot, on one primary, prevents it.
Who runs it: two developers, no one on call at night. A managed database with automatic backups.
Lands on: managed Postgres. The workload is small, and the team needs joins, constraints and managed backups.
Location tracking for a delivery fleet
Reads: the last hour of one truck's positions, and daily distance per driver. Always by truck and by time range.
Scale: 5,000 trucks sending a position every 5 seconds is 86.4 million rows a day, almost all writes.
What can go wrong: a missing point is an annoyance. The map smooths it over.
Who runs it: a team that already runs Postgres well.
Lands on: a time-series database. Step 4 picks which one: TimescaleDB is a Postgres extension that partitions tables by time, so the team keeps the tools and skills it has. A team without Postgres experience could weigh a dedicated time-series engine instead.
Shopping carts during a big sale
Reads: fetch and update one cart by its ID. Nothing else.
Scale: traffic jumps 50x for a few hours, then falls back.
What can go wrong: a lost "add to cart" costs a sale. An item that comes back after deletion is an annoyance, as Amazon decided.
Who runs it: a team already on AWS, with nobody who wants to run a cluster.
Lands on: a managed key-value store such as DynamoDB for the carts only. Orders and stock stay in Postgres, where selling the last item twice can't happen.
Where each database fits
For this exercise, Postgres is the starting point. Each branch shows a requirement that could lead to another database.
Database | Kind | Fits when reads are | Grows by | You give up |
|---|---|---|---|---|
Postgres, MySQL | Relational | Mixed, with joins and transactions | A bigger machine, read replicas, then sharding by hand | Writes beyond one primary without extra work |
MongoDB | Document | Whole records by ID, read together | Built-in sharding | Joins across documents |
DynamoDB | Key-value | One key at a time, at huge volume | Automatic partitions, managed by AWS | Any query you didn't design the key for |
Cassandra, ScyllaDB | Wide-column | Writes pouring in, read in order per key | Adding nodes | Ad-hoc queries, and a cluster to run |
CockroachDB, TiDB | Distributed SQL | SQL writes in several regions that must agree | Adding nodes, across regions | Latency on every write, and a harder system to run |
TimescaleDB | Time-series on Postgres | Time ranges and rollups | Partitions by time | Write volume beyond one primary |
Redis | In-memory key-value | Sessions, counters, cache | More memory or more nodes | Data bigger than memory. Keep the real copy elsewhere |
Elasticsearch | Search and analytics engine | Full-text search, filters and aggregations | Shards distributed across nodes | Immediate search visibility. Keep transactional data in the primary database and sync the search index |
OpenSearch | Search engine | Text search and filters | Shards | Being the source of truth. Feed it from the database |
For example, a product catalog can keep prices and stock in Postgres while Elasticsearch indexes descriptions for search and filters. Search is near real-time, so a recent change may not appear immediately. Confirm stock on the primary database at checkout. OpenSearch fills the same search role. Add either when search requirements justify running and synchronizing another system.
If every answer in your four steps is small, stay where you are.
What I run
This blog runs on one plain Postgres. Posts, revisions, images and comments all live there, and moving to another host means changing one connection URL. It's a personal site, small on purpose, and one Postgres is more than enough for it. Many small projects and MVPs can start the same way. Adding a distributed database, a cache and a queue before they're needed gives the team more to build and more to pay for. Which of the four questions has the hardest answer in the system you're working on?
References
Renato Augusto, Como Escolher o Banco de Dados Correto pra sua Aplicação (video, Portuguese)
Seth Gilbert and Nancy Lynch, Brewer's Conjecture and the Feasibility of Consistent, Available, Partition-Tolerant Web Services, 2002
Daniel Abadi, Consistency Tradeoffs in Modern Distributed Database System Design, IEEE Computer, 2012
Martin Kleppmann, Please stop calling databases CP or AP, 2015
Discord, How Discord Stores Billions of Messages, 2017
Discord, How Discord Stores Trillions of Messages, 2023
Sarah Mei, Why You Should Never Use MongoDB, 2013
Giuseppe DeCandia et al., Dynamo: Amazon's Highly Available Key-value Store, SOSP 2007
Figma, How Figma's databases team lived to tell the scale, 2024
Evan Klitzke, Why Uber Engineering Switched from Postgres to MySQL, 2016
Timescale, TimescaleDB, source and README
Elastic, Elasticsearch index fundamentals and Near real-time search
Comments
Loading comments