Choosing a Database
A decision process that starts from access patterns and operational capacity rather than feature lists, with the trade-offs each choice commits you to.
There is no best database. There are engines whose physical design matches your access pattern and engines whose design fights it, and there is your team's capacity to operate what you pick.
The order the questions should be asked in
1. What are the access patterns? Write them down as concrete queries, including the fields they filter and sort on. This single step eliminates most candidates. A query that filters on a non-partition-key column eliminates wide-column stores. A query that joins five tables eliminates most non-relational engines.
2. Is this the system of record? Systems of record need durability guarantees, backups with a tested restore, and constraints that prevent invalid state. Derived stores — caches, search indexes, analytical copies — can be rebuilt, so they can trade durability for speed.
3. Does it fit on one node, with headroom? A single modern server handles far more than most teams assume. If projected load fits with three years of growth, a single-primary engine is almost always the cheaper decision, in both money and incident hours.
4. What consistency does the business actually require? Distinguish "must be correct" from "must look fresh". Both are legitimate; only the first justifies consensus latency.
5. Who operates it at 3am? An engine your team cannot debug is a liability regardless of its benchmarks. Weigh familiarity, managed-service availability, and the quality of the failure diagnostics.
Access pattern to category
Trade-offs you are committing to
Each choice locks in constraints that are expensive to reverse later.
- Schema flexibility versus enforced invariants. A schemaless store does not remove the schema; it moves it into every service that touches the data, where it cannot be enforced.
- Write scalability versus query flexibility. Engines that scale writes linearly do it by making the partition key mandatory in queries. That is the whole bargain.
- Latency versus durability. Acknowledging a write before it is replicated is faster and loses data on failover. Both are valid choices; only one of them should be a surprise.
- Operational simplicity versus availability. A single primary with a replica is easy to reason about and has a failover gap. A consensus cluster removes the gap and adds a distributed system to your on-call rotation.
Deciding between similar engines
When two candidates are in the same category, the deciding factors are rarely features:
- Failure behaviour. What happens on disk-full, on network partition, on a failed node? Test this before choosing, not after.
- Operational tooling. Backup, restore, upgrade and diagnostics quality. See the relevant comparison.
- Migration path. How you get data out. An engine with good logical replication or a standard wire protocol is much easier to leave.
- Licence and governance. Licence changes have forced real migrations — Redis to Valkey, Elasticsearch to OpenSearch. Know who controls the project.
Validating the choice before committing
Before you build on a decision, prove it with a small load test using your data shape:
- Load a representative dataset — at least the size where the working set stops fitting in memory.
- Run the real query mix, including the write path, at the target concurrency.
- Measure p99, not the average. See Benchmarking Databases.
- Kill a node while the test runs, and observe what the client sees.
- Restore from a backup taken during the test, and verify the data.
Step 4 and step 5 are the ones that change decisions.