Choosing a Database
A decision tree, not a brand loyalty
Database choice is a requirements conversation: query shape, write volume, latency, scale, freshness, and team skill decide Postgres vs document vs wide-column vs search — in roughly that order.
What you will learn
- Turn a feature request into storage requirements
- Map each requirement to a database family
- Articulate the trade-offs you accepted
What is this?
Choosing a database starts with the workload, not the branding: what do reads look like, how do writes scale, how fresh is fresh, what consistency must hold, and which developer skills the team has. For most products the answer is still a relational engine, and the exotic choices earn their place only for measurably different requirements.
Why do you need it?
- A wrong pick costs years of migration and feature delay.
- Most 'I need a NoSQL DB' requests are really 'I don't want to model a relational schema'.
- A short decision framework turns a debate into a checklist the team can own.
Mental model
Choosing a vehicle by trip type
Choosing a database
A sedan (Postgres) handles every commute; a van (document store) wins for cargo of mixed shapes; a race car (Redis) is for bursts of speed; a bus (warehouse) is for moving volumes of history. Nobody picks a van because they like the brand.
How it works
- Score against: data shape, access patterns, write volume, read latency, consistency, scale, operational skill, cost.
- Relational (Postgres/MySQL) answers 80%+ of workloads; its MVCC, JSONB, and extensibility cover a wide field.
- Document stores fit self-contained nested records; search fits full-text scoring; key-value fits cache/shape-locked speed.
- Wide-column fits event/fact-scale writes where high-cardinality partition keys spread load.
- Undifferentiated 'scale' fears rarely need exotic engines; profiling and indexing earn more first.
A lean selection flow.
Examples
Turning a requirement into a storage pick
# Request: "Show a user's last 500 order events instantly."# Shape: 1:many nested, append-heavy, per-user reads -> relational ordered by PK,# or document store with user+ts key, or key-value if memory-bound. # Request: "Search products by description and rank by relevance."# Shape: full-text scoring -> a search engine (Elasticsearch), fed by CDC.Requirements first, engine second; the two requests map to different shapes and different stores.
In production
- A saas company keeps Postgres as OLTP and adds Redis for sessions and Elasticsearch for product search.
- An IoT fleet is wide-column (Cassandra) once profiling proves per-device write rates swamp a relational bolt-on.
- A legal-records product stays relational because of tight referential integrity and SAS-style joins.
Best practices
- Write the decision as a one-page RFC: workload, SLOs, the chosen engine, and the reasons two main alternates lost.
- Prototype with realistic volume instead of guessing; every engine 'works' on 10k rows.
- Revisit the choice only when a requirement changes, not as engineering mood swings.
Common mistakes
- Choosing an engine to avoid designing a schema; the schema work appears later, in worse form.
- Scaling by adding a second store when the first was misused (no index, wrong pool).
- Acknowledging consistency SLOs, then picking a store that violates them on the write path.
Debugging tips
- When a new store looks necessary, measure the specific query that the current one cannot do under SLO.
- Prototype the load path and read path; bottlenecks show up only at production-ish scale.
- Keep a 'why we rejected X' list — it prevents arguing the same decision twice.
Interview checkpoint
Quick quiz
Postgres is a reasonable default for most new workloads until a measured requirement argues otherwise.
Which store family wins for full-text relevance ranking?