Choosing a data store
Choose from the access pattern, not the feature list — and why relational is the default that answers questions you have not thought of yet.
"Which database?" is the decision with the longest half-life in a design. Everything else can be changed in an afternoon; the store holds the data, and moving it is a migration project.
So it is worth choosing from the access pattern rather than from a list of features.
Start from the questions, not the technology
Before naming anything, answer four things about the data:
- What are the reads? By primary key? By a range? By arbitrary combinations of fields? Full-text?
- What is the write pattern? Steady, bursty, append-only, heavy updates?
- What consistency does the business need — not what is nice, what would be wrong to get wrong? Money and stock levels are different from view counts.
- How big does it get, and how fast? The estimation lesson's arithmetic, applied here.
Answer those and the shortlist is usually one or two things. Start from "should we use Mongo?" and you have started at the answer.
The shortlist
| Store | It is good at | It is bad at |
|---|---|---|
| Relational (Postgres, MySQL) | related data, ad-hoc queries, transactions, constraints | horizontal write scale past one primary |
| Document (Mongo) | one aggregate read whole, flexible shape | joins, and consistency across documents |
| Key-value (Redis, DynamoDB) | one known key, very fast | anything that is not a key lookup |
| Wide-column (Cassandra) | huge write throughput, time series | anything you did not design the partition key for |
| Search (Elasticsearch) | text, relevance, faceting | being the system of record |
| Object store (S3) | large blobs, cheap and durable | anything you need to query |
| Graph (Neo4j) | many-hop relationship queries | everything else |
The default is boring and it is usually right
Start with a relational database. Not from conservatism — from the fact that it is the only one on that list that does not require you to know your queries in advance.
A relational schema answers questions you have not thought of yet. Every other store on that list is fast because it made an assumption: Cassandra is fast if you query by the partition key you chose a year ago; Redis is fast if you know the key. When the product changes and the question changes, the relational database runs a slower query and the others cannot answer at all.
And the scale argument is weaker than it sounds. A single modern Postgres handles tens of thousands of writes a second and terabytes of data. The honest question is not "will it scale" but "what is our actual number, and what fraction of that is it" — and for most applications being designed in an interview or a startup, the answer is a small fraction.
Polyglot, and its price
Real systems use more than one, and that is fine when each is doing what it is good at:
- Postgres as the system of record.
- Redis as a cache, holding nothing that is not derivable.
- Elasticsearch for search, fed from Postgres.
- S3 for files, with the metadata row in Postgres.
The pattern in each: one store owns the truth, and the others are derived. That keeps the failure modes simple — a derived store can be rebuilt; if two stores both own truth, you have a distributed consistency problem you have to solve forever.
The price is real and worth naming: every additional store is another thing to operate, back up, monitor, upgrade and be paged for, and another consistency lag to explain to somebody. "We added Elasticsearch" also means "search results can be a few seconds behind" and somebody will report that as a bug.
The specific choices that come up
Redis for cache, and for what else? Its data structures make it a reasonable queue, rate limiter and leaderboard. It is not a database: by default it is not durable, and treating it as a store means accepting that a restart can lose data.
When a document store genuinely wins. When the aggregate is read and written whole, has a shape that varies, and is not joined to anything. A product catalogue with per-category attributes is the honest example. An orders system with customers and line items is not — that is relational data in a document store, and you will write the joins in application code.
When Cassandra genuinely wins. Write volume beyond what one primary can take, with queries known in advance and modelled as partitions. Time-series and event logs are the fit. The cost is severe and worth stating: you design the tables from the queries, and a new query often means a new table and a backfill.
Search is not a system of record. Elasticsearch loses documents under some failure modes and its consistency model does not aim at durability. It indexes the truth; it does not hold it.