Choosing a Database
Pick relational, key-value, document, wide-column, graph or time-series from access patterns, guarantees and scale, and defend it in one sentence.
On this page
A team stores readings from 50,000 sensors in a relational table. Each sensor reports every 10 seconds, so that is 50,000 ÷ 10 = 5,000 inserts a second, 5,000 × 86,400 = 432 million rows a day. They keep 30 days. Every night a job deletes the day that just turned 31 days old, 432 million rows one by one, updating every index as it goes, and the dashboards time out while it runs.
The database is not broken; it was built for a different job. A time-series store writes data in chunks by time and expires a whole day by dropping one chunk. The access pattern picks the database.
The idea
Ask three questions, in this order, and the category usually falls out:
- How is the data read and written? By one key, by time range, by following relationships, or with joins and ad hoc filters? Write the top three queries down, as in data modeling.
- What must never go wrong? If two writes must succeed or fail together (take stock, create the order), you need transactions. If a stale read for a second is fine, you have more choices.
- How big and how fast? Rows, bytes and writes a second.
The default is a relational database: transactions, constraints, joins, and queries you did not plan for. Move away from it only when a question above gives you a reason, and say it.
The six categories
Each category is good at one access pattern and pays for it elsewhere. Products blur the lines; treat these as shapes.
| Option | Good at | Bad at | Pick it when |
|---|---|---|---|
| Relational Usual default | Transactions across rows and tables, constraints, joins, ad hoc queries. Examples: PostgreSQL, MySQL. | Scaling writes past one server means sharding by hand. Schema changes on huge tables need care. | Money, orders, inventory, anything with relationships. The default. |
| Key-value | Get and put by key in about a millisecond, and it partitions by key easily. Examples: Redis, DynamoDB. | No queries except by key. No joins. | Sessions, carts, feature flags, lookups by id. |
| Document | Stores a whole nested object (a product with its variants) and reads it in one go. Flexible fields. Example: MongoDB. | Data shared between documents is copied or joined in the app. Multi-document transactions are limited or slower. | Self-contained records read whole, with fields that vary by type. |
| Wide-column | Huge write rates over many nodes. Rows sorted within a partition, so "latest 50 for this user" is one read. Example: Cassandra. | You design tables per query. Weak or no transactions across partitions. | Massive append-heavy data read by partition key: messages, activity logs. |
| Graph | Follows relationships many hops deep without join cost per hop. Example: Neo4j. | Harder to partition. Overkill for one or two hops. | The question is about paths: fraud rings, "friends of friends who like X". |
| Time-series | Appends by time, compresses well, rolls up and expires old data cheaply. Example: InfluxDB. | Poor at updates and at lookups not driven by time. | Metrics, sensor readings, prices over time. |
A lookup to run in your head:
| If you hear | Lean toward | Say why like this |
|---|---|---|
| "Must not double-charge", "stock cannot go negative" | Relational | "These two writes must commit together, so I want a transaction." |
| "Look up by id", millions of reads a second | Key-value | "Every read is by one key, so I don't need a query engine." |
| "Each item has different fields", read whole | Document | "The record is read as one unit and its shape varies." |
| "Billions of messages, newest first per chat" | Wide-column | "Partition by chat, sort by time; one partition read per page." |
| "Paths three or more hops deep" | Graph | "Each hop would be a join; a graph store follows edges directly." |
| "Per-minute averages, keep 30 days" | Time-series | "Writes are appends by time and old data expires in bulk." |
One system, several stores
Real systems often use several stores. Each extra store is one more thing to run, back up and keep in sync, so add one only with a reason.
1A shopper clicks Place order.
- items
- 2
2The service reads the cart by session id from the key-value store: one key, about 1 ms.
- items
- 2
3It opens a transaction in the relational database: take 2 units of stock and insert the order. Both happen or neither does.
- items
- 2
- stock left
- 0
4Another shopper is buying the last unit at the same moment. A constraint that stock stays at 0 or above makes one of them fail cleanly instead of overselling.
5The service deletes the cart key. If this fails, the worst case is a stale cart, not a wrong order.
6It sends "checkout took 180 ms" to the time-series store without waiting. A lost metric hurts nobody.
7The confirmation goes back to the shopper.
8Now the key-value node dies. Carts kept on one unreplicated node are lost: annoying. Orders are untouched, because they live in a replicated store with transactions.
Scale changes less than people think
"It needs to scale" is not a reason by itself. Do the arithmetic. One relational server on good hardware handles thousands to tens of thousands of simple writes a second and several terabytes (a rough rule of thumb), and read replicas multiply read capacity.
Leave it when the numbers do: writes beyond one primary, or data many times one machine, read mostly by one key. Then a store that partitions itself (key-value or wide-column) saves you sharding by hand, and costs you cross-partition transactions and joins.
How much data does the orders table add in one year?
- 2 million orders a day
- About 1 KB per order row, including its index entries
- Nothing is deleted
Show the working
- 2 × 106 × 365 = 7.3 × 108 orders a year
- 7.3 × 108 × 103 B = 7.3 × 1011 B ≈ 730 GB
- Writes: 2 million ÷ 86,400 s ≈ 23 a second on average
Estimate 730 GB (anything from 300 GB to 3 TB is a good answer)
Under 1 TB a year and 23 writes a second is comfortable for one relational primary with replicas. Scale is not a reason to leave it here.
In the interview
Choose the database right after you list the access patterns. Name the category, give the reason in one sentence that cites a query, a guarantee or a number, then say what would change your mind.
Where do you store the messages?
I'd use NoSQL, because it scales better than SQL.
Where do you store the messages?
The main read is "the latest 50 messages in this chat", and we estimated 20,000 writes a second. So I'd use a wide-column store partitioned by chat id and sorted by time: one partition read per page, and writes spread across nodes. Users, billing and group membership go in a relational database, because those need constraints and transactions.
Why not put everything in the relational database?
At this write rate I'd be sharding it by hand within a year. If the numbers were 10 times smaller, I would, and keep one store to operate.
Follow-ups you should expect:
- "Can a relational database scale?" Yes: replicas for reads, partitioning for writes, and caches in front. The cost is operational work and harder cross-shard queries.
- "Document or relational for a product catalogue?" Document if products are read whole and fields vary by type; relational if they join orders and stock in transactions.
- "When would you add a graph database?" When the core query walks three or more hops over a large graph. For "my friends", a join table is enough.
- "Couldn't the sensor table stay relational?" Often, yes: partition it by day, and expiring a day becomes dropping one partition instead of 432 million deletes. A time-series store does that for you and adds compression and roll-ups.
Common mistakes
20,000 delivery vans each report their position every 5 seconds. Dashboards show per-minute averages for the last 7 days. Which store fits best?
A wallet app moves money between two users' balances. Which property decides the store?
Orders add 730 GB a year at about 23 writes a second. A candidate proposes a wide-column store "for scale". Best response?
What to practise next
Whatever store you pick, its speed comes from indexing, which is next. To see a store chosen from access patterns inside a full design, work through Design a Chat App.