Choosing a Database: Model First, Then Engine

"Which database should we use?" is usually answered by habit — the one the team knows, or the one that's fashionable. But database choice is among the least reversible decisions in a system, and the honest position is that there is no single best model: each fits a different relationship shape, and within a model each product fits a different deployment and scale. A Solution Architect should arrive with a shortlist and a rationale, not a favourite. This lab builds that rationale from measurements.

Runnable companion: choosing-a-database on GitHub. One shared dataset (a social network) modelled three ways, with benchmarks and a decision engine.

Level 1 — which model?

The same domain — users, a follow graph, posts — modelled as relational, document, and graph. Each wins a different question.

Traversal (graph vs relational). "Users reachable within N follow-hops":

 depth    result   recursive SQL    Cypher (graph)
--------------------------------------------------
     2        91          0.1 ms            1.5 ms
     3       676          0.6 ms            2.9 ms
     4     1,909          3.6 ms            5.1 ms

recursive SQL   11 lines
Cypher           3 lines   (3.7x more SQL)

Same answer both ways. In-memory SQLite is fast at this scale, but note the trend: its latency grew ~36x from depth 2 to 4, while the graph engine's grew ~3.4x — graph traversal scales with hops far more gracefully, and says it in a third of the lines. Many-to-many, multi-hop → graph, where the relationship is the primitive instead of a join you re-derive each level.

Locality (document vs relational). Fetch a whole entity vs update one field:

operation                       document    relational
------------------------------------------------------
read whole entity                11.3 ms       15.7 ms
partial update (1 post)          21.6 ms        6.7 ms

The document wins read-whole (one keyed read, data sits together) and loses frequent partial updates — it rewrites the entire ~389-byte blob where relational touches one row. Which is the whole lesson on normalization: it's a read/write trade-off, not right vs wrong. Normalize the volatile, denormalize the stable, and keep documents small.

Level 2 — which product?

Choosing "relational" doesn't finish the job. For the user's exact question — SQLite or Postgres or MySQL? — benchmark it. Same write workload under rising concurrent writers:

concurrent writers            SQLite    PostgreSQL
--------------------------------------------------
1                           14,209/s       1,251/s
2                           16,247/s       2,469/s
4                           13,773/s       4,234/s
8                           16,044/s       5,971/s

SQLite is flat — one global write lock serializes writers (its high absolute number is because it runs in-process, no network). PostgreSQL scales with concurrency (MVCC). The measured rule: SQLite for embedded / single-writer; Postgres for concurrent multi-client. MySQL sits near Postgres for this; it differs on replication and ecosystem — and Postgres vs MySQL is often a genuine tie you settle by team.

The product axis that decides most is deployment: embedded (SQLite, DuckDB, Kuzu) vs client-server (Postgres, MySQL, Neo4j) vs distributed (CockroachDB, Cassandra, DynamoDB, ClickHouse). Reach for distributed only when a single primary genuinely can't hold the load — otherwise it buys complexity, not capacity.

Turning it into a decision

The lab ships a decision engine that scores ~16 engines from a catalog against a workload profile and prints a shortlist:

Embedded / edge app, low write concurrency        → SQLite
Web service: relational + JSON + reporting        → PostgreSQL
Social graph, many-to-many multi-hop              → Neo4j / Kuzu
Global high-write key-value, eventual OK          → Cassandra / DynamoDB
Flexible evolving documents, sharding             → MongoDB
Full-text search over content                     → Elasticsearch
Analytics over billions of rows                   → ClickHouse
Time-series metrics at high ingest                → TimescaleDB

It returns a shortlist, not a verdict — because models are converging (Postgres alone does relational, JSON, full-text and, with extensions, time-series and geo). Narrow the field by model and product attributes, then decide on ecosystem, team experience, and operational cost. When one versatile engine covers several needs, that consolidation is itself a strong reason to pick it.

What I'd say in an interview

• No single best database. Choose the model by relationship shape and dominant query; choose the product by deployment, scale and consistency.
• Normalization is a trade-off — normalize the volatile, denormalize the stable, keep documents small; read-time joins aren't automatically a bottleneck.
• "SQLite or Postgres?" is measurable — it comes down to concurrency and deployment, not SQL features.
• Bet on convergence — a versatile multi-model engine (often Postgres) is a strong default until a workload's shape forces a specialist. Bring a shortlist and a reason.