Databases look like a sprawling product catalog. Underneath, each one is a small set of bets against a few hard tradeoffs. Learn the tradeoffs and any new engine becomes legible: you can predict its personality before reading the docs. This note maps the durable physics, the data-model families that sit on top, and a decision procedure that reaches a product last, not first.
The tradeoffs you can’t cheat
Consistency vs latency, always on. The CAP theorem says that under a network partition you pick availability or consistency. True, but partitions are rare, so CAP describes an edge case. The more useful frame is PACELC, proposed by Daniel Abadi in 2010: if there’s a Partition, choose Availability or Consistency; Else, choose Latency or Consistency. The second half is the one that bites daily. Even with a healthy network, replication forces a choice, because keeping replicas in agreement costs a coordination round trip, and that round trip is latency. As Abadi argued, the consistency/latency tradeoff is present at all times during system operation, while CAP only matters during the rare partition. Real systems sort cleanly onto the grid: DynamoDB and Cassandra are PA/EL (give up consistency for availability and speed), Spanner and CockroachDB are PC/EC (hold consistency in both cases and pay the latency). See External Consistency for how Spanner buys the strong end of this with atomic clocks.
B-tree vs LSM-tree. Nearly every database is one of these underneath, and knowing which tells you its temperament. A B-tree (Postgres, InnoDB) keeps data sorted in fixed pages and updates them in place: reads traverse root to leaf in a predictable, logarithmic number of I/Os, which makes it read-optimized. An LSM-tree (Cassandra, RocksDB, ScyllaDB) turns every write into a sequential append in memory, then flushes sorted files and compacts them in the background. That makes writes cheap and fast, at the cost of reads that may check several files and periodic compaction spikes. The reason you can’t have both is the RUM conjecture (Athanassoulis et al., 2016): a storage structure can optimize for at most two of read, write, and space amplification. B-trees trade write amplification for low read amplification; LSM-trees do the reverse, with leveled compaction often rewriting data on the order of 50x over its life, but every rewrite sequential and disk-friendly.
Row vs columnar. This is the most durable line in the whole field, because it’s physical. Row storage keeps a record’s fields together on disk, so “fetch this one order” reads one place. Columnar storage keeps each field contiguous, so “average order_value across 100 million orders” reads only that column. On a row store the same aggregate must read every row’s full payload because the values are interleaved with every other field. This is why one engine can’t be excellent at both transactions and analytics, and why the OLTP/OLAP split keeps reappearing under new names. DuckDB and ClickHouse are fast at analytics primarily for this reason, before their vectorized execution and compression add more.
The memory hierarchy. RAM is orders of magnitude faster than SSD, which is faster again than a network hop. Every database is, at bottom, a strategy for what lives where. Redis says keep the working set in RAM. A warehouse says the data sits on cheap object storage and we scan it cleverly. Most of a database’s character falls out of that one decision.
The data-model families
Six shapes. Each exists because some access pattern was awkward in the others.
| Family | Answers well | Pays for it with | Reach for when |
|---|---|---|---|
| Relational (Postgres) | joins, integrity, ad-hoc queries | horizontal write scaling | the default; you don’t yet know every query |
| Key-value (Redis, DynamoDB) | sub-millisecond lookups by key | anything not keyed | caching, sessions, one known access path |
| Document (MongoDB) | nested objects, flexible schema | cross-document consistency and joins | schema varies per record |
| Wide-column (Cassandra) | massive write throughput, always-on | ad-hoc queries, joins | a write firehose across regions |
| Columnar / warehouse (ClickHouse, Snowflake, DuckDB) | aggregating billions of rows | single-record writes and updates | analytics, dashboards, BI |
| Graph (Neo4j) | deep relationship traversal | everything else | the relationships are the product |
Vector, time-series, and search stores are specializations layered on these, not new families. A vector index is a data structure that can live inside Postgres (pgvector) as easily as in a dedicated engine, which is why the standalone vector-database category is contested.
The decision procedure
Architects who are good at this never open with “which database.” They start with the workload and let it eliminate options. In order:
- Read-heavy or write-heavy? (Leans B-tree vs LSM.)
- Transactional or analytical? (Row vs columnar, the biggest fork.)
- What consistency does this field actually need? Most teams demand strong consistency by reflex and pay for it. A like count can be stale; a bank balance cannot. Decide per field, not per app.
- What’s the access pattern? Key lookup, range scan, join, traversal, full-text? The query shape picks the model.
- What’s the honest scale in 18 months? Not Google’s number, yours. This decides whether distributed-systems pain is worth buying.
- What can the team operate at 3am? The best-fitting database you can’t run beats nothing.
The product name never appears in the questions. You derive it.
The laws that keep it honest
Boring by default. Postgres until a workload’s scale makes a wall load-bearing. It absorbs JSON, vector search, time-series, geospatial, and full-text through extensions, so “just use Postgres” is the right first answer for most systems, and you add Redis or Elasticsearch only when a real requirement outgrows it. Every additional datastore is a permanent tax: another thing to monitor, back up, secure, and keep consistent. Polyglot persistence is real, but the hardest part is orchestrating consistent backups and keeping data in sync across the stores, not running any one of them.
The database is the hardest thing to change. You can rewrite the app; migrating the data layer is surgery. So the storage decision deserves more conservatism than any other, and the right target is “won’t regret in five years,” not “fastest in this week’s benchmark.” This is also why the source-of-truth store is defensible: see Systems of Record.
Denormalize for reads, normalize for writes. A normalized schema keeps one authoritative copy, which is safe to write but expensive to read across joins. A denormalized copy is shaped for a specific read and fast, but now two copies can disagree. Read replicas, caches, and CQRS are all the same move: keep a second, read-shaped copy and accept the staleness. Most performance architecture is choosing where on this line to sit.
Run any new database through the four physics questions: storage engine, row or columnar, consistency bet, data-model family. “Cassandra is an LSM-tree, wide-column, PA/EL, write-optimized store” tells you its whole personality before you read a line of its documentation. The marketing evaporates.
The lines are blurring at the product layer, and the model predicts where. HTAP engines keep a row store and a columnar copy in sync to serve both workloads, Postgres extensions bolt on vector and columnar storage, and Spanner has added a columnar engine for analytics. None of this repeals the tradeoffs; it pays for a second, differently-shaped copy of the data, which is the denormalization law again. When a product claims to be great at two things the physics says are opposed, the question is which copy you’re querying and what it costs to keep them in sync.
Try it
Watch columnar beat row on the same data (1-2 hours, DuckDB + Postgres). Load the same ~10M-row table into Postgres and into DuckDB (duckdb is a single binary, no server). Run SELECT avg(amount) FROM orders on each and time it, then run a single-row primary-key lookup on each and time that. You should see DuckDB win the aggregate by a wide margin while Postgres wins or ties the point lookup. That gap is the row/columnar tradeoff made visible, and it’s the dominant cause (vectorized execution and cache warmth add smaller effects on top).
Make write amplification observable (an afternoon, RocksDB). Use a RocksDB (LSM) binding in any language and bulk-insert a few million keys, then read RocksDB’s compaction statistics (rocksdb.stats) to see how many bytes were physically written versus the logical bytes you inserted. The ratio above 1.0 is write amplification. Push more updates to existing keys and watch it climb as compaction rewrites data, exactly the cost an LSM pays to keep writes sequential.
See also
- External Consistency — the strong end of the consistency/latency tradeoff, and how Spanner buys it.
- Systems of Record — why the authoritative store is the defensible one.
- PACELC — the consistency/latency framing that improves on CAP.
- LSM-Tree — the write-optimized storage engine behind Cassandra and RocksDB.
Sources
- PACELC design principle — the theorem and where real databases land on it.
- B-Tree vs LSM-Tree (TiKV deep dive) — the read/write/space amplification tradeoff, with the two-of-three constraint.
- Columnar storage guide (MotherDuck) — why analytics is faster column-wise, with the I/O math.
- Polyglot persistence and the retrieval gap (Tacnode) — the operational cost of running many stores.