Choosing a database: the decision walkthrough
What it is
A sequence of questions whose answers eliminate options, rather than a comparison table. The comparison table is the wrong artifact, because every database looks good in its own marketing and the differences that matter are the ones that appear under your specific access pattern.
THE SEQUENCE, in order, because each answer removes options
1. What are the ACCESS PATTERNS?
2. Do you need MULTI-KEY TRANSACTIONS?
3. What is the DATA SIZE and GROWTH?
4. What CONSISTENCY do the invariants require?
5. What is the READ:WRITE ratio and shape?
6. What OPERATIONAL capacity do you have?
7. What do you already run?
Commonly confused with a technology preference. The strongest answer to "which database" is usually "the one we already operate", and the burden of proof is on adding a second one, because a new store is not a schema decision, it is a permanent operational commitment: backups, upgrades, monitoring, on-call expertise, capacity planning and a migration path.
Also commonly confused with SQL versus NoSQL, which is not the axis. Postgres and DynamoDB differ less in query language than in what they make cheap: Postgres makes arbitrary queries cheap and horizontal scale expensive, DynamoDB the reverse.
The problem it solves
Databases are the hardest thing to change and the decision is usually made fastest.
Changing a web framework: weeks
Changing a message broker: months
Changing a database: quarters, and the data has
to move while the system runs
And the failure is asymmetric: the wrong choice does not
show up for a year, when the access pattern you did not
anticipate arrives and the store cannot serve it.
The two failure directions:
PREMATURE DISTRIBUTION
"We need Cassandra for scale." One million writes a day
is twelve per second and ten gigabytes. That is one
Postgres instance with enormous headroom, and you have
bought partition-key design, eventual consistency and a
harder operational story to solve a problem you do not
have.
DEFERRED SPECIALISATION
Running full-text search, time-series and a graph
traversal in Postgres because it can technically do all
three. It can, and each one is worse than the purpose-built
option by enough to matter, at some scale you will reach.
Mechanics
Step 1: access patterns, before anything else
This is the step that determines the answer and the step that gets skipped.
Write them down as queries, not as entities:
"get a customer by id"
"list a customer's orders, newest first, paginated"
"find orders by status for the fulfilment queue"
"full-text search products by title and description"
"aggregate revenue by region and month"
"traverse: which services depend on this one, 3 hops"
For each: expected QPS, latency requirement, and result set
size.
Why it comes first: several databases are chosen by their access pattern rather than their data model. A key-value store is chosen because every access is by key; a graph database because the queries are traversals; a time-series store because the queries are ranges over time with aggregation.
And the test that eliminates most options immediately:
Is there a query here that the candidate store makes
EXPENSIVE rather than merely awkward?
DynamoDB + "aggregate revenue by region" -> a full scan
Postgres + "3-hop traversal over 10M edges" -> recursive
CTE, and it will be slow
Cassandra + "find by any field except the partition key"
-> not supported without a secondary index that has its
own consistency caveats
Step 2: multi-key transactions
A binary question with a large consequence.
Do two or more records need to change atomically, with an
invariant spanning them?
"decrement inventory AND create the order" -> yes
"update the user's name" -> no
"transfer between two accounts" -> yes
"append an event" -> no
IF YES
-> relational, or a distributed database offering
transactions (Spanner, CockroachDB, YugabyteDB), or
DynamoDB transactions with their limits.
-> NOT eventual-consistency stores, and no amount of
application-level compensation is equivalent. See:
sagas vs 2PC.
IF NO
-> the field is wide open, and this is the question that
opens it.
Teams answer "yes" reflexively and are often wrong. The check is whether an invariant spans the records, not whether they happen to be written together. Two writes that can be retried independently do not need a transaction; they need idempotency.
Step 3: size and growth, honestly
THE THRESHOLDS THAT ACTUALLY MATTER
< 100 GB Anything works. Choose on query flexibility
and operational familiarity. Distribution is
pure cost.
100 GB - 1 TB Single-node relational is still comfortable
on modern hardware. Read replicas handle
read scale. This is where most systems live
and where premature distribution happens.
1 TB - 10 TB Single node is possible and getting
uncomfortable: backup and restore times,
vacuum, index maintenance, upgrade windows.
Sharding or a distributed store starts to
earn its cost.
> 10 TB Distribution is not optional. Now the
question is which distribution model, and
the access patterns from step 1 decide it.
And the growth question matters more than the current size: 200 GB growing 10 percent a year and 200 GB growing 30 percent a month are different decisions, and the second one should be designed for the size it reaches in eighteen months rather than today's.
Step 4: consistency, per operation
Not one answer for the system. See the consistency ladder.
For each access pattern, what breaks under concurrency or
staleness?
a user reads their own profile edit -> read-your-writes
a balance -> strong, and
multi-key
a like count -> eventual is fine
a username registration -> uniqueness, so
linearizable
a search index -> seconds of
staleness fine
If any operation needs a real invariant across records, that constrains the primary store, and everything else can be derived. The mistake is choosing the weakest consistency that any operation tolerates, rather than the strongest that any operation requires.
Step 5: read/write shape
READ-HEAVY (100:1 or more)
Read replicas solve most of it, so a single-writer store
goes much further than people expect. Caching is
disproportionately effective.
WRITE-HEAVY
Now the write path matters. LSM-tree stores (Cassandra,
RocksDB-backed, ScyllaDB) convert random writes to
sequential appends and win. B-tree stores do more random
I/O per write.
APPEND-ONLY / TIME-ORDERED
Time-series stores (TimescaleDB, InfluxDB, ClickHouse)
exploit the ordering: compression is far better, and old
partitions are dropped rather than deleted.
ANALYTICAL (few queries, huge scans)
Columnar (ClickHouse, DuckDB, BigQuery, Snowflake). A row
store scanning a billion rows to sum one column reads
every column.
The row-versus-column distinction is the one that produces order-of-magnitude differences, and it is decided entirely by whether queries touch few columns of many rows or many columns of few rows.
Step 6 and 7: the operational reality
QUESTIONS THAT ELIMINATE OPTIONS REGARDLESS OF FIT
Who is on call for it at 3am, and do they know it?
Is there a managed offering in your cloud, and does it
lag the open-source version by how much?
What is the backup and restore procedure, and has it been
tested?
What is the upgrade story? (Some distributed stores have
genuinely painful major-version upgrades.)
How do you monitor it, and does your existing stack cover
it?
If the vendor disappears or changes its licence, what
happens?
AND THE ONE THAT USUALLY DECIDES IT
What do you already run competently?
The bar for adding a second database should be high, because the cost is not the migration, it is the permanent operational surface: another thing to back up, monitor, upgrade, capacity-plan and be woken up by, and another body of expertise the team must maintain.
The honest framing: a purpose-built store must be enough better to justify a permanent operational commitment, and "it would be somewhat faster for this one query" is not.
What each option is actually for
POSTGRES The default, and it is a very good default.
Transactions, arbitrary queries, JSON,
full-text search, geospatial, and now
reasonable vector search. Weak at horizontal
write scale.
MYSQL Similar profile. Choose on team familiarity
and ecosystem rather than on features.
DYNAMODB Predictable single-digit-millisecond access
by key at any scale, with no operations at
all. Requires access patterns known in
advance, and punishes queries you did not
design for.
CASSANDRA / Write-heavy, multi-region, availability over
SCYLLA consistency. Tunable consistency per query.
Costs: no joins, no ad hoc queries, and
tombstones and repair are real operational
work.
MONGODB Flexible documents, good developer
ergonomics, horizontal scale via sharding.
Shard key choice is consequential and hard
to change.
REDIS In-memory, sub-millisecond, rich data
structures. Durability is a configuration
choice and not the point.
CLICKHOUSE Analytical scans over enormous data, orders
of magnitude faster than a row store for
aggregation. Not for point lookups or
frequent updates.
ELASTICSEARCH / Full-text search and log analytics.
OPENSEARCH Not a system of record, and treating it as
one is a recurring mistake.
SPANNER / Global strong consistency with horizontal
COCKROACHDB scale. Costs a cross-region round trip on
writes and, for Spanner, a cloud lock-in.
NEO4J Traversals over a densely connected graph.
Worth it when the queries are multi-hop; a
relational schema handles one hop fine.
A worked example: the decision, run
A B2B SaaS product, 18 months in, on Postgres. Three
proposals on the table: Cassandra "for scale", Elasticsearch
"for search", and a graph database "for the dependency
view".
RUNNING THE SEQUENCE
1. ACCESS PATTERNS (from the query log, not from a meeting)
82% get-by-id and list-by-tenant
11% full-text search over documents
4% aggregations for the dashboard
3% dependency traversal, 1 to 3 hops
2. MULTI-KEY TRANSACTIONS
Yes: billing, entitlements, workspace membership. These
need real invariants.
-> Rules out Cassandra as the primary store immediately.
That proposal is dead on question 2.
3. SIZE
340 GB, growing about 8% a quarter. In 18 months, ~500 GB.
-> Comfortably single-node. The "scale" argument for
Cassandra was never quantified, and once quantified
it disappears.
4. CONSISTENCY
Billing and entitlements need strong. Search results and
the dashboard tolerate seconds.
-> Primary store must be strongly consistent; derived
stores can be eventual.
5. SHAPE
Read-heavy, roughly 60:1. Read replicas cover it.
6/7. OPERATIONS
Two engineers with Postgres experience, nobody with
Cassandra or Neo4j. One managed Postgres, no other
managed store currently running.
THE DECISION
Primary: stays Postgres. Not one of the three proposals
changed that, and question 2 alone eliminated the one that
was framed as a replacement.
Search: Postgres full-text is adequate at 11% of queries
over 340 GB, and it stops being adequate when we need
per-language analysis, which we will within a year for the
German and Japanese customers. So: OpenSearch as a
DERIVED store, fed by change data capture, explicitly not
a system of record.
-> That is a real second database and it earns its
operational cost, because Postgres's per-language
analysis genuinely is not comparable.
Graph: 3% of queries, 1 to 3 hops, over a few hundred
thousand edges. A recursive CTE handles it in single-digit
milliseconds at this size. Measured before deciding.
-> No graph database. Revisit if hops exceed 4 or edges
exceed ~10 million.
Dashboard aggregations: 4% of queries, and they were
locking rows the transactional path needed. Moved to a
read replica. One day of work.
-> Not a database decision at all, which is worth saying,
because it was being presented as one.
Two findings worth extracting. The "we need Cassandra for scale" proposal died on question 2, before size was even discussed, because the product has multi-key invariants. And one of the four problems was not a database problem at all: contention between analytics and transactional work, solved by a read replica in a day.
Production evidence
Postgres's expansion into full-text search, JSON, geospatial and vector search is the practical basis for "the default is a very good default", and the corresponding advice from practitioners to use one database until it demonstrably fails is widespread enough to be a norm rather than a preference.
Segment's "Goodbye Microservices" is the canonical account of operational surface exceeding the isolation benefit, and the same reasoning applies to polyglot persistence: 140 services with shared libraries meant every update was 140 deploys, and N databases means N upgrade paths and N on-call bodies of knowledge.
DynamoDB's design and its documentation are explicit that access patterns must be known in advance and that the key schema derives from them, which is why it is the clearest example of a store chosen by access pattern rather than data model.
Uber's published account of moving from Postgres to MySQL (2016) and the debate it generated is a useful case of a decision driven by specific operational properties (replication and write amplification at their scale) rather than by feature comparison.
The columnar-versus-row performance difference for analytical scans is well documented across the ClickHouse, DuckDB and vectorised-execution literature, and the order-of-magnitude gap for aggregation over few columns is the clearest case where the purpose-built store is not a preference.
The debate
The case for one database: operational simplicity compounds. One backup procedure, one upgrade path, one monitoring stack, one body of expertise, and transactions across everything. Postgres genuinely covers a very wide range, and the cost of a second store is permanent.
The case for polyglot persistence: a purpose-built store can be an order of magnitude better at its purpose, and forcing every workload into one engine means every workload is mediocre. ClickHouse for analytics is not a marginal improvement.
The case for a managed distributed store from the start: avoids a painful migration later, and managed offerings remove much of the operational objection.
My position: default to one relational store, and require a purpose-built second store to clear a high bar, which the analytical and full-text cases usually do and the others usually do not.
The bar I would use is an order of magnitude on a workload that matters, not a marginal improvement on one query. Columnar for analytical scans clears it: a row store reading every column to sum one is not a tuning problem. Per-language full-text analysis clears it, because Postgres's full-text search does not do German decompounding or Japanese segmentation. A graph database for one-to-three-hop traversals over a few hundred thousand edges does not clear it, because a recursive CTE handles that in single-digit milliseconds, and I would measure before deciding rather than assume.
The step I would insist on doing first is writing down the access patterns as queries with their QPS and latency requirements, because it is the step that gets skipped and it is the one that eliminates options. In the worked example, "we need Cassandra for scale" died on the multi-key transaction question before anyone discussed size, and the size figure, 340 GB, made the scale argument disappear entirely once it was quantified rather than asserted.
And the framing I would apply to any proposal for a second store: it is not a schema decision, it is a permanent operational commitment. Another backup procedure, another upgrade path, another thing to monitor, another body of expertise the team must maintain and be woken by. "It would be somewhat faster for this query" does not clear that, and saying so plainly is the useful contribution.
Where I would push back on the one-database position: do not run analytics on your transactional primary. That is not polyglot persistence, it is basic isolation, and in the worked example the dashboard aggregations were locking rows the transactional path needed. A read replica fixed it in a day, and that was not a database choice at all despite being presented as one.
Follow-up Q&A
"How do you choose a database?" By running a sequence of questions in order, because each answer eliminates options, rather than by comparing features. Access patterns first, written as queries with their QPS and latency, because that step is what actually decides it and it is the one that gets skipped. Then multi-key transactions, which is binary and eliminates a whole class. Then size and growth. Then consistency per operation. Then read-write shape. Then, honestly, what you can operate.
"Why are access patterns first?" Because several databases are chosen by access pattern rather than data model: a key-value store because every access is by key, a graph database because the queries are traversals, a columnar store because queries touch few columns of many rows. And the test that eliminates options is whether any listed query is expensive rather than merely awkward in a candidate store: DynamoDB with "aggregate revenue by region" is a full scan, and that is disqualifying rather than inconvenient.
"When does the answer become 'not Postgres'?" When you need horizontal write scale beyond a single node, when the workload is analytical scans where columnar is an order of magnitude better, when you need per-language full-text analysis that Postgres's full-text search does not do, or when you need multi-region active-active writes. Notably not "we might need scale one day": one million writes a day is twelve per second and about ten gigabytes, which is one Postgres instance with enormous headroom.
"How high should the bar be for a second database?" An order of magnitude on a workload that matters, not a marginal improvement on one query. Because a second store is not a schema decision, it is a permanent operational commitment: another backup procedure, another upgrade path, another monitoring integration, another body of on-call expertise. Columnar for analytics clears that bar. A graph database for one-to-three-hop traversals over a few hundred thousand edges does not, because a recursive CTE handles it in single-digit milliseconds, and I would measure that before deciding.
"What sizes actually matter?" Under 100 gigabytes anything works and distribution is pure cost. Between 100 gigabytes and a terabyte single-node relational is comfortable on modern hardware, and this is where most systems live and where premature distribution happens. From one to ten terabytes single node is possible and increasingly uncomfortable because of backup and restore times, vacuum and upgrade windows. Above ten terabytes distribution is not optional and the access patterns decide which model.
"What's the multi-key transaction question really asking?" Whether an invariant spans two or more records, not whether they happen to be written together. Decrementing inventory and creating an order is a yes. Two writes that can each be retried independently is a no; that needs idempotency rather than a transaction. Teams answer yes reflexively and are often wrong, and the distinction matters because a yes eliminates every eventual-consistency store as the primary.
"How do you handle a team that wants a specific database?" By running the sequence with them rather than arguing about the database. In the case I worked, the Cassandra proposal died on question two, because the product had multi-key billing and entitlement invariants, and that happened before size was even discussed. Then the size figure made the scale argument disappear once it was quantified rather than asserted. That is a much better conversation than "I don't think we need Cassandra".
"What gets misdiagnosed as a database choice?" Contention between analytical and transactional work, most often. In the same case, dashboard aggregations were holding locks the transactional path needed, which presented as "Postgres can't handle our analytics" and was fixed by a read replica in a day. That is not polyglot persistence, it is basic isolation, and it is worth checking before any store is evaluated.
Common misconceptions
"It's SQL versus NoSQL." That is not the axis. Postgres and DynamoDB differ in what they make cheap: arbitrary queries versus horizontal scale by key.
"We need X for scale." Quantify it. One million writes a day is twelve per second and ten gigabytes, which is one instance with enormous headroom.
"A purpose-built store is obviously better for its purpose." It has to be enough better to justify a permanent operational commitment. An order of magnitude, not a marginal gain.
"Choose the weakest consistency any operation tolerates." Choose the strongest any operation requires for the primary store, and derive everything else.
"Adding a database is a schema decision." It is another backup procedure, upgrade path, monitoring integration and on-call body of knowledge, permanently.
Interview delivery note
Refuse the comparison-table framing immediately, because the sequence is the answer: "I'd run questions in order rather than compare features, because each answer eliminates options. Access patterns first, written as queries with QPS and latency, then multi-key transactions, then size and growth, then consistency per operation, then the read-write shape, then what we can actually operate."
Show why the ordering matters with a concrete elimination: "In a case I worked, the 'we need Cassandra for scale' proposal died on question two, because the product had multi-key billing and entitlement invariants. That was before anyone discussed size, and the size figure, three hundred and forty gigabytes, made the scale argument disappear entirely once it was quantified rather than asserted."
State the bar for a second store, because that is the judgement: "And I'd hold a high bar for adding a second database, because it's not a schema decision, it's a permanent operational commitment: another backup procedure, upgrade path, monitoring integration and body of on-call expertise. The bar is an order of magnitude on a workload that matters. Columnar for analytics clears it. A graph database for three-hop traversals over a few hundred thousand edges doesn't, and I'd measure the recursive CTE before deciding."
Close on the thing that gets misdiagnosed: "and I'd check first whether it's a database problem at all. In the same case, dashboard aggregations were locking rows the transactional path needed, which presented as 'Postgres can't handle our analytics'. A read replica fixed it in a day."
Further reading
- The PostgreSQL documentation on full-text search, JSONB and its extension ecosystem, for what the default actually covers.
- Amazon's DynamoDB developer guide on modelling from access patterns, as the clearest example of access-pattern-driven design.
- Segment, "Goodbye Microservices", for operational surface exceeding isolation benefit, which applies equally to polyglot persistence.
- Kleppmann, Designing Data-Intensive Applications, chapters 2 and 3, for the storage-engine properties behind the read-write shape question.