Databases: A Field Guide

A database is where your system’s truth lives — the durable record that survives restarts, crashes, and deploys. But “database” is not one thing. Over decades, different data shapes and access patterns produced different families of database, each making a deliberate trade to be excellent at something and mediocre at the rest. Choosing the wrong family is one of the most expensive mistakes in system design, because data is sticky: by the time you know it’s wrong, terabytes have piled up in the wrong shape.

This page is a map of the families and the question that selects each one: what is the shape of my data, and how will I query it? The deeper relational-vs-everything-else debate lives in SQL vs NoSQL; here we build the field guide.

What does choosing the right database buy us, and what does it cost? The right family buys natural modeling, fast queries on your actual access patterns, and the right consistency guarantees. The cost of every choice is the things that family gives up — there is no database that is best at everything, and any vendor who claims otherwise is selling you future pain.

The first split: relational vs the rest

For decades the relational database (SQL) was the only serious answer, and it’s still the right default for most systems. Everything else — the “NoSQL” families — emerged to handle a specific case where the relational model strained: extreme scale, a flexible schema, a graph-shaped query, or a firehose of timestamped data. So read the rest of this page as “here is the relational default, and here are the four shapes that justify reaching past it.”

The six families

   RELATIONAL    rows & columns, joins, ACID        →  the default; structured, related data
   KEY-VALUE     key → opaque blob                  →  fastest possible lookups by key
   DOCUMENT      key → rich JSON-like document       →  flexible schema, nested objects
   WIDE-COLUMN   row key → sparse columns, huge scale→  massive write/read at scale
   GRAPH         nodes & edges                       →  relationships ARE the query
   TIME-SERIES   timestamp → measurements            →  append-only metrics/events over time

Relational (PostgreSQL, MySQL)

Data lives in tables of rows and columns with a fixed schema; tables relate via foreign keys and are combined at query time with joins. Relational databases give you ACID transactions (atomic, consistent, isolated, durable) and a powerful, declarative query language (SQL). They are the right choice whenever your data is structured and related, you need correctness guarantees (money, inventory, anything that must never be half-updated), and your access patterns aren’t all known in advance — SQL lets you ask new questions later. The classic cost is that scaling writes horizontally is hard, and a rigid schema can chafe against fast-changing data.

Key-value (Redis, DynamoDB, Memcached)

The simplest model: a dictionary. You store a value under a key and get it back by that key — nothing else. Because the model is so constrained, key-value stores can be blisteringly fast and trivially partitioned by key. They’re ideal for caches, session stores, feature flags, and any “look it up by ID” workload. The cost is the flip side of the speed: you generally can’t query by value or do joins — if you don’t have the key, the store can’t help you.

Document (MongoDB, Couchbase)

A document store keeps self-contained, nested documents (typically JSON) under a key, and — unlike pure key-value — it can index and query the fields inside the document. The schema is flexible: different documents in the same collection can have different fields, which suits evolving data and aggregates that are naturally read together (a whole order with its line items in one document). The cost is that data spanning many documents (joins across entities) is awkward, and the flexible schema pushes the discipline of consistency onto your application code.

Wide-column (Cassandra, HBase, Bigtable)

Despite looking table-like, this family is really a giant, sparse, distributed map: a row key maps to a set of columns, and rows can have wildly different columns. It’s built for enormous scale and high write throughput across many machines, with tunable consistency. The defining constraint: you must design your tables around your queries up front, because there are no flexible joins or ad-hoc queries — the data layout is the query plan. Reach for it when you have write-heavy, massive-scale data with known access patterns (event logs, time-stamped activity at billions of rows).

Graph (Neo4j, Amazon Neptune)

Here the relationships are first-class: data is nodes connected by edges, and queries traverse those connections (“friends of friends who like X,” “the shortest path of approvals”). In a relational database, such queries become deep, slow, recursive joins; a graph database makes traversal the native, fast operation. Use it when the connections are the point — social graphs, recommendation networks, fraud rings, dependency graphs. The cost is that it’s a specialized tool, weaker for plain tabular reporting and harder to scale than simpler models.

Time-series (InfluxDB, TimescaleDB, Prometheus)

Optimized for data that is append-only and timestamped: metrics, sensor readings, events. These databases exploit the fact that writes arrive roughly in time order and old data is queried in ranges and rolled up — so they compress aggressively and make “average CPU per minute over the last 7 days” cheap. Use them for monitoring, IoT, and analytics over time. The cost is narrowness: they’re great at time-range queries and poor at general-purpose relational work.

Choosing: a decision sketch

   Is the data highly structured AND need strong correctness (transactions)?
        └─ yes → RELATIONAL (start here, default)

   Pure lookup by key, need raw speed (cache/session)?
        └─ KEY-VALUE

   Flexible, nested objects you read as a unit, schema in flux?
        └─ DOCUMENT

   Write-heavy at massive scale, queries known in advance?
        └─ WIDE-COLUMN

   The relationships themselves are what you query?
        └─ GRAPH

   A firehose of timestamped measurements?
        └─ TIME-SERIES

Start relational; specialize only with evidence

For most systems, a relational database is the right first choice — it’s flexible, correct, and well-understood, and modern Postgres handles far more scale than people assume (and can even store JSON documents). Reach for a specialized store when a specific, measured pressure justifies it: a hot path that needs a key-value cache, a graph query that’s killing your joins, a metrics firehose. “We might need NoSQL someday” is not evidence.

Polyglot persistence has a real cost

Using several databases — the right tool for each job — is common and often correct, but every additional store is another thing to operate, back up, secure, and keep consistent with the others. Data spread across systems can drift out of sync, and a transaction can’t span them. The benefit of a specialized store must outweigh the operational tax of running one more.

Under the hood — the storage engine beneath the families

One level below the six families sits a quieter split that explains much of their behavior: the storage engine. Most relational and document stores use a B-tree — data kept sorted and updated in place, which is excellent for reads and range scans but turns every write into a locate-then-modify. The high-write families (Cassandra, HBase, Bigtable, and embedded engines like RocksDB) use an LSM-tree instead: writes are appended to an in-memory buffer and flushed as sorted files, then merged in the background (“compaction”).

   B-tree:    update in place    → read-optimized; a write = locate + modify
   LSM-tree:  append + compact   → write-optimized; a read may check several files

That single choice is why wide-column stores absorb such enormous write throughput — they turn random writes into sequential appends — and what they pay for it: extra read amplification and constant background compaction work. The trade-off thread, one layer down: write speed bought with read and maintenance cost.

The architect’s lens

Choosing a database family is the stickiest decision in this part — run it through the five questions before terabytes commit you to it:

• Why does it exist? Because a system needs durable truth that survives restarts, crashes, and deploys — and because no single store is excellent at everything, distinct families exist, each making a deliberate trade to be great at one data shape (rows + joins, key lookups, nested documents, massive writes, traversal, time-ranges) and mediocre at the rest.
• What problem does it solve? Matching the store to the shape of your data and how you’ll query it — so you get natural modeling, fast access on your actual patterns, and the right consistency guarantees, instead of fighting the engine on every query.
• What are the trade-offs? Every family buys its strength by surrendering something: key-value is blistering but can’t query by value; document is flexible but cross-entity joins are awkward; wide-column absorbs enormous writes (LSM-tree appends) but you must design tables around queries up front; graph makes traversal native but is hard to scale and weak at tabular reporting.
• When should I avoid a specialized store? When relational would do — Postgres is the right default and scales further than people assume (it even stores JSON). Reach past it only with measured pressure, because polyglot persistence taxes you: every extra store is one more to operate, back up, and keep in sync, and a transaction can’t span two of them.
• What breaks if I choose wrong? Data is sticky — by the time you discover the family is wrong, terabytes have piled up in the wrong shape, and migrating both the data and the access patterns built on it is enormous. The wrong shape compounds with every row, where most early mistakes are cheap to reverse.

Check your understanding

1. Why is choosing the wrong database family so much more expensive than most other early mistakes?
2. What is the single defining capability of a document store that a pure key-value store lacks?
3. Why must you design wide-column tables around your queries up front, and what does that buy you?
4. Give a query that is slow in a relational database but native and fast in a graph database, and explain why.
5. Why is “relational by default, specialize with evidence” good advice, and what is the hidden cost of polyglot persistence?

Show answers

1. Because data is sticky: by the time you discover the family is wrong, terabytes have already piled up in the wrong shape, and migrating that data (and the access patterns built on it) is enormous. Other early mistakes are cheap to reverse; the wrong database shape compounds with every row.
2. A document store can index and query the fields inside the document, not just fetch by key. A pure key-value store only hands back an opaque blob by its key — without the key, it can’t help you, and it can’t query by value.
3. Because a wide-column store is a giant sparse distributed map with no flexible joins or ad-hoc queries — the data layout is the query plan. Designing tables around your queries up front buys enormous scale and high write throughput with predictable, fast access on exactly those patterns.
4. “Friends of friends who like X” (or shortest-path / dependency traversal). It’s slow in a relational DB because it becomes deep, recursive self-joins that explode with depth; a graph database makes traversal the native operation, so following edges is the fast primitive instead of a join.
5. Relational is flexible, correct, well-understood, and modern Postgres scales further than people assume — so it’s the safe default, and “we might need NoSQL someday” is not evidence. The hidden cost of polyglot persistence is operational: every extra store is one more thing to operate, back up, secure, and keep in sync, data can drift between systems, and a transaction can’t span them.