All writing
A commit graph. The main line starts at a commit labelled postgres and passes four green commits: reads, scale, consistency and operations. From each, an amber branch rises to a label: key-value and wide-column, sharding, distributed SQL, and what you can run.

commit 40390078 min

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.

From a commit labelled a write, two lines split. The top line reaches a green commit marked partition and splits into availability and consistency. The bottom line reaches a green commit marked else and splits into latency and consistency. The caption maps it to the shopping list: with no signal, keep editing or wait for the other phone, with signal, answer now or wait to be sure.

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

  1. Reads: appointments by doctor and day, a patient's history, an invoice joined to its visits. Mixed queries, lots of joins.

  2. Scale: a few thousand appointments a month. One small machine will last years.

  3. 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.

  4. 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

  1. Reads: the last hour of one truck's positions, and daily distance per driver. Always by truck and by time range.

  2. Scale: 5,000 trucks sending a position every 5 seconds is 86.4 million rows a day, almost all writes.

  3. What can go wrong: a missing point is an annoyance. The map smooths it over.

  4. 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

  1. Reads: fetch and update one cart by its ID. Nothing else.

  2. Scale: traffic jumps 50x for a few hours, then falls back.

  3. 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.

  4. 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

A commit graph. The main line starts at a commit labelled postgres and passes four green commits: reads, scale, consistency and operations. From each, an amber branch rises to a label: key-value and wide-column, sharding, distributed SQL, and what you can run. The caption says a branch happens only when the answer is hard.

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

Merged into main on

Comments

Loading comments