Learn System Design Choosing a database
BackendHow to choose a database: seven laws
Nobody picks the wrong database on purpose. They pick it in the wrong order: engine first, queries later. These seven laws run the decision the other way round, and each one is really just a question you have to answer out loud.
The seven laws at a glance. Save it, or keep scrolling for the walkthrough.
The choice is rarely about features
Ask how to choose a database and you will get a comparison table. Rows for transactions, documents, horizontal scaling, and a tick in most of the boxes for most of the options. The table is accurate and it will not help you, because the databases people regret were almost never missing a feature. They were fine at the thing the team measured and awkward at the thing the team did every day.
That is what these laws are aimed at. Every one of them replaces a product decision with a question about your own system, and the questions are ordered. What you read and write comes before what rules must hold, which comes before where the time is going, which comes before what "scale" means in your sentence. Answer them in that order and the shortlist writes itself.
The central lesson is unglamorous: the goal is not the database with the most capabilities. It is the one that fits the workload, chosen by someone who can describe the consequences of the fit.
Interactive
Walk the chain
Seven links, in order. Pick one to see the law, the question it forces, and the move it saves you from.
Law 1 · 00:00 in the talk
Start with the queries, not the database
What does this application actually read and write?
Most database arguments start in the wrong place, which is the shape of the data. The shape that decides things is the shape of the access. Write down the ten reads and writes the app performs most often, how frequently each one runs, and how much it returns. An e-commerce app whose every page ties customers to orders to products to payments to stock is describing a relational database whether or not anyone says the word. An app that only ever fetches one document by one key is describing something else entirely.
What usually happens
Choosing PostgreSQL, MongoDB or DynamoDB from the data type, the conference talk, or what the last team happened to use.
Do this instead
List your top ten access patterns before you shortlist anything. That list rules out more options than any feature comparison will.
Law 2 · 01:45 in the talk
Set consistency from the business rules
Which rules must never be wrong, even for a second?
Not all of your data deserves the same guarantees. A recommendation strip that is thirty seconds stale costs nothing at all. An account balance that is thirty seconds stale costs money, and possibly a phone call from someone in legal. Sort your tables by that one question and a pattern usually appears: a small core that needs real transactions, and a much larger tail that is perfectly happy being slightly behind.
What usually happens
Buying strong guarantees for the whole system because one table needs them, or settling for eventual consistency everywhere because one table can live with it.
Do this instead
Name the invariants out loud. Stock never goes negative. A payment is recorded exactly once. Those get transactions. Everything else gets whatever is cheapest.
Law 3 · 02:54 in the talk
Find the bottleneck before replacing the database
Where is the time actually going?
The database is slow is a symptom, not a diagnosis. Before you shortlist a replacement, read the query plan on your slowest endpoint. Look for the sequential scan where you assumed an index, the query that returns forty thousand rows so the app can display twenty, the innocent-looking loop that fires the same query once per row, and the connection pool that has been at its limit since Tuesday. Swapping engines carries every one of those problems across the migration intact.
What usually happens
Migrating to something faster, then finding the same N+1 query waiting on the other side with a new accent.
Do this instead
Turn on slow query logging, read the plans, count the round trips. A new engine cannot fix a query that asks for the wrong thing.
Law 4 · 03:57 in the talk
Know which axis you are scaling on
Reads, writes, volume, spikes, or distance?
We need to scale is half a sentence. The five things people mean by it have almost nothing in common: more reads, more writes, more stored data, sharper traffic peaks, or users who are physically far away. Read pressure often yields to a cache and a replica by Thursday. Write pressure eventually forces partitioning, which is a much larger conversation. The useful question is never whether a database scales, because the vendor has a benchmark proving it does.
What usually happens
Asking "can this database scale?" Everything scales in a chart that somebody else produced.
Do this instead
Ask "how does it scale for my workload?" then pick the lens below that matches the pressure you actually have.
Law 5 · 04:59 in the talk
One database until a second solves a real problem
What is the source of truth, and what is a copy?
Every store you add is another thing to back up, monitor, upgrade, secure and pay for. The real cost is not any of those, though. It is the synchronisation: two systems now hold versions of the same fact, and they will disagree at the worst possible moment unless somebody decided in advance which one is allowed to be right. Caches, search indexes and analytics stores should be derived copies, rebuildable from the store of record, and everyone on the team should be able to say so without thinking.
What usually happens
Running Redis, Elasticsearch and a warehouse before the first database has had an index added to it.
Do this instead
Write down one store of record. Everything else is derived, rebuildable, and disposable in an incident.
Law 6 · 06:03 in the talk
Fast is not the same as durable
What happens if this system loses everything right now?
In-memory systems are fast partly because they skip the step where they promise to remember. That is a fine trade for a cache and a terrible one for an order. The test is a single question, asked per store: if this process restarted empty, could I rebuild its contents from something durable? If yes, enjoy the speed. If the answer involves the word "orders" or "payments", the thing needs replication, backups and a restore you have actually performed.
What usually happens
Promoting the cache to source of truth because it won the load test, then discovering which writes were only ever in memory.
Do this instead
Ask the loss question per store and write the answer next to the connection string.
Law 7 · 07:04 in the talk
Choose for 3am, not just for the sprint
Who restores the backup, and have they ever done it?
Development is a few weeks. Operations is every week after that: backups, restores, monitoring, failover, version upgrades, access control, and a bill that grows in a shape you did not predict. Managed services move most of this work rather than deleting it. You still own the schema, the queries, the access rules and the pricing model. The part teams skip is rehearsal, and it is the only part that matters at 3am.
What usually happens
Averages in the benchmark, and a nightly backup job nobody has ever restored from.
Do this instead
Restore a backup into a scratch environment. Trigger a failover on purpose. Benchmark P95 and P99 on realistic data, not averages on an empty table.
Read as one sentence: Queries → Consistency → Bottleneck → Scaling → Complexity → Durability → Operations.
Law 4, up close
Five things people mean by "scale"
They are five different projects with five different first moves. Pick the pressure you actually have.
How it shows up
Traffic doubled, the same few hundred rows are being fetched over and over, CPU is pinned.
What is really happening
The working set is small and the database is answering the same question repeatedly. This is the friendliest kind of growth.
First moves, in order
Cache the hot answers, then add read replicas and route reporting queries away from the primary.
The expensive mistake
A cache turns a read problem into an invalidation problem, which is a consistency problem. Law 2 decides how stale each cached thing is allowed to be.
How it shows up
Write latency climbs steadily, locks and queue depth grow, replicas fall behind.
What is really happening
One primary accepts every write, and you are approaching what a single machine can commit per second.
First moves, in order
Batch what can be batched, drop indexes nobody queries, move append-heavy tables out, then consider partitioning.
The expensive mistake
Sharding is close to a one-way door. Pick the partition key from the access patterns in law 1, because the wrong key makes ordinary queries cross every shard.
How it shows up
Fine at 10GB, unpleasant at 2TB, on the same queries and the same traffic.
What is really happening
Indexes no longer fit in memory, so lookups that used to be free now hit disk.
First moves, in order
Partition by time, archive cold rows to cheaper storage, and stop keeping data no query has asked for since 2023.
The expensive mistake
Buying a distributed database to hold history that nobody reads. Storage is the cheap part; the cluster is not.
How it shows up
Tuesday is calm. The launch, the sale or the email send falls over in ninety seconds.
What is really happening
Connection limits and pool exhaustion arrive long before CPU does. The database is refusing new work, not doing it slowly.
First moves, in order
Put a connection pooler in front, cap concurrency, queue the non-urgent work and add backpressure at the edge.
The expensive mistake
Autoscaling the app tier multiplies connections into a database whose limit did not move. More app servers make this failure arrive faster.
How it shows up
Snappy in Ohio, sluggish in Sydney, and no query is slow in the logs.
What is really happening
You are paying for the speed of light, once per round trip. The engine is not the problem.
First moves, in order
Cut round trips first, then put read replicas or a CDN near the readers.
The expensive mistake
Multi-region writes. This is where the consistency conversation from law 2 stops being theoretical and starts being a design constraint.
Laws 5 and 6, up close
Which of your stores could you lose?
Run every store you operate through one question: if it emptied itself during lunch, where would the data come back from? The honest answers sort your architecture into copies and originals faster than any diagram.
| Store | Exists for | Rebuild it from | If it vanishes |
|---|---|---|---|
| Cache | Repeat reads of the same hot answers | The primary, on demand, as requests arrive | A latency spike while it refills. Nothing is lost. |
| Search index | Text search, facets, fuzzy matching | A reindex job over the primary | Search is down for the length of the reindex. Checkout is not. |
| Read replica | Read capacity and reporting queries | Replication from the primary | Read capacity, plus anything pinned to that replica. |
| Analytics warehouse | Aggregates across history | A replay of the primary and the event log | Dashboards go stale. Revenue keeps working. |
| Queue or event log not derived | Work that has been accepted but not finished | Only if every producer can re-emit, which is rarer than people assume | In-flight jobs disappear silently. This one is usually a store of record wearing a costume. |
Watch the last row
Queues get filed under "infrastructure" and treated like caches, but a job that has been accepted and not yet done exists in exactly one place. If the producer cannot re-emit it, the queue is a source of truth, and law 6 applies to it in full: replication, backups, and a restore somebody has rehearsed. This is the row that quietly breaks the pattern in most systems we have looked at.
Test yourself
Four calls a senior makes differently
Each one maps back to a law above. Nothing is saved or sent anywhere.
Your score
0 / 4
The short version
Start general, specialise on evidence
The senior move is not knowing more databases. It is starting with a general-purpose one that fits what you currently understand, then measuring, optimising, naming the actual bottleneck, and only then adding specialised infrastructure with a reason attached to it. Specialisation bought early is complexity you pay for daily and benefit from hypothetically.
One question belongs in the decision and rarely makes it: how hard would this be to leave? You will not get it perfectly right, which is fine, so long as being wrong is survivable.
Data portability
Rows and documents move. The shape you gave them to satisfy one engine's quirks does not, and that reshaping is most of the migration.
Engine-specific features
Every stored procedure, vendor extension and clever index type is a small rope tying you to the choice. Some are worth it. Count them anyway.
Application coupling
If query building leaks across the codebase instead of living behind a data layer, changing databases means changing everything that touches one.
Keep reading
INNER JOIN vs OUTER JOIN
Law 1 in practice: what your reads actually cost once relationships are involved.
HashMap
Why a key-value lookup is instant, and why that shape suits some access patterns and not others.
The API Lifecycle
Law 7 at a larger scale: the stages where operations quietly become somebody's job.
The seven laws are adapted from 7 Database Laws of a Senior Backend Developer. The scaling lenses, the store table and the quiz are ours.