Relational vs Document vs Key-Value.

The quickest way to spot a beginner's design is a database picked before anyone explained how the data gets used. Tonight we flip that: first decide how the data is read and written, then the database almost picks itself.

The run sheet.

You'll be able to…

The data model follows the access pattern.

Nobody picks a store in a vacuum. You pick it from how the data is written and read, the invariants you must enforce, and the scale you must hit. Name those first and the shortlist writes itself.

Four questions, asked out loud, before any store name.

Ask these four in order. By the fourth, the answer is usually obvious, and you can explain it instead of guessing.

The trap is jumping straight to the fourth one. "We need scale" decides nothing until you've answered the first three.

Six families. Each is a different bet on those four questions.

Normalize once, query a hundred ways you didn't predict.

You keep each fact in one place, in tidy tables, and the database stitches them back together with joins when you ask. The big win: you can ask questions you never planned for. It only struggles when one table grows too big for a single machine.

erDiagram
  AUTHOR ||--o{ POST : writes
  POST ||--o{ COMMENT : has
  AUTHOR ||--o{ COMMENT : makes
  AUTHOR { uuid id PK }
  POST { uuid id PK }
  COMMENT { uuid id PK }
        

How to build an ERD, in order.

A normalized model, read in crow's-foot.

Same example, drawn properly. Note the M:N between products and orders is resolved by order_item, and the line ends tell you the cardinality at a glance.

erDiagram
  CUSTOMER ||--o{ ORDER : places
  ORDER ||--|{ ORDER_ITEM : contains
  PRODUCT ||--o{ ORDER_ITEM : "appears in"
  CUSTOMER {
    uuid id PK
    string email UK
  }
  ORDER {
    uuid id PK
    uuid customer_id FK
    timestamp placed_at
  }
  ORDER_ITEM {
    uuid order_id FK
    uuid product_id FK
    int qty
  }
  PRODUCT {
    uuid id PK
    string name
    int price_cents
  }
        

PostgreSQL: the boring, correct default.

Open-source, fiercely ACID, with SQL plus JSONB, full-text, and extensions (PostGIS for geo, pgvector for embeddings). It will carry you much further than you expect.

If you can't name a reason to leave Postgres, don't. Start here, earn the exit.

MySQL / MariaDB: the other default.

The most-deployed relational engine, replication-first, everywhere. The honest choice mostly when it's already in your stack or your team knows it cold.

Same family as Postgres. Pick on ecosystem and team, not on a benchmark blog.

Store the aggregate you read, as one shape.

If a screen always loads an order with all its items, store them together and grab them in one read. No joins, and each document can have its own shape. The tricky part is data shared in two places: do you copy it in or link to it?

flowchart LR
  Q(["How is it read?"])
  Q -- "always together,
read as a unit" --> EMB["EMBED
order + items in one doc · 1 read · duplication"]
  Q -- "shared, changes
independently" --> REF["REFERENCE
store an id · app-side join · no duplication"]
  classDef e fill:#D6F5E3,stroke:#1F2937,color:#0E1726;
  classDef r fill:#FEE4E2,stroke:#1F2937,color:#0E1726;
  classDef a fill:#ffffff,stroke:#1F2937,color:#0E1726;
  class EMB e;
  class REF r;
  class Q a;
        

MongoDB: the document default.

Stores flexible JSON-like documents, with proper indexes, an aggregation pipeline, and built-in sharding. Quick to change when your data shape is still moving.

Multi-document transactions exist now, but if you need them everywhere, ask if you wanted relational.

DynamoDB: managed scale, zero ops, on AWS.

Amazon's fully managed key-value and document store: very fast reads at any size, and no servers to run. You shape your keys around the exact queries you'll make.

The discipline DynamoDB forces, patterns up front, is exactly tonight's lesson made mandatory.

If you always know the key, nothing is faster.

Think of a giant dictionary spread across many machines. You give it a key, it hands back the value in basically one hop. The catch: you can only look things up by the exact key, no searching, sorting, or joining.

flowchart LR
  K(["get( session:8f2a )"]) --> H["hash(key) → node"]
  H --> N1["node A"]
  H -.-> N2["node B"]
  H -.-> N3["node C"]
  N1 --> V(["value · one hop"])
  classDef hot fill:#DCEBFE,stroke:#1F2937,color:#0E1726;
  classDef cold fill:#fff,stroke:#9CA3AF,color:#6B7280;
  class K,H,N1,V hot;
  class N2,N3 cold;
        

Redis: in-memory, sub-millisecond, more than a cache.

More than strings: it has lists, hashes, sorted sets, streams, and pub/sub. It lives in memory (RAM), which is why it's so fast, and also its main limit.

Treat it as a derived store by default. The truth lives somewhere durable; Redis makes it fast.

Model around the query, then absorb enormous writes.

Data is grouped into partitions; inside each, rows are kept sorted. You design a separate table for each query and happily store the same data more than once. In return: huge write speeds and no single bottleneck.

Cassandra / ScyllaDB: masterless write firehose.

No single master, every machine takes writes, consistency you can dial per query, and it scales across regions. ScyllaDB is a faster rewrite with the same model.

Most teams that "need Cassandra" actually have a Postgres they haven't tuned. Earn this one.

Full-text and analytics are not your primary store's job.

Two jobs quietly need their own engine. Cramming them into your main database is a classic cause of slow, painful nights.

Elasticsearch / OpenSearch: relevance and logs.

A search engine with ranking, typo tolerance, filters, and aggregations. Also powers most "search your logs" setups. Always a copy of your real data, never the original.

If you ever query it for the truth, you've made it the source of truth by accident. Don't.

ClickHouse / BigQuery: billions of rows, fast aggregates.

Analytics engines that store data by column: scan only what a query needs, compress hard, and add up enormous tables in seconds. Easy to append to, painful to update.

DuckDB is the same idea, embedded, when "big data" actually fits on one laptop.

When the relationships are the point, traverse, don't join.

Things (nodes) and the links between them (edges) are first-class, so "friends of friends who bought X" is a short walk, not a pile of self-joins. Cypher is the usual query language.

Neo4j is the server-grade default; Kuzu is an embedded, file-based graph engine, the one we'll see in the real system shortly.

The same four questions, six answers.

No row is "best." Read down the columns, not across: each column is a question you should already have answered.

Answer "how is it read?" and the store falls out.

flowchart TB
  R{"How is it
primarily read?"}
  R -- "by exact key" --> KV["Key-value
+ wide-column if writes are huge"]
  R -- "as one whole aggregate" --> DOC["Document"]
  R -- "ad-hoc, joined,
with invariants" --> REL["Relational"]
  R -- "by relationships" --> GR["Graph"]
  R -- "full-text" --> SRCH["Search index
derived"]
  R -- "heavy aggregates" --> OLAP["OLAP / columnar
derived"]
  REL --> INV{"More than one
access pattern fights?"}
  DOC --> INV
  INV -- "yes" --> POLY["Polyglot:
1 source of truth + derived stores"]
  INV -- "no" --> ONE["One store. Stop here."]
  classDef rel fill:#FEF3C7,stroke:#1F2937,color:#0E1726;
  classDef doc fill:#E6E8FF,stroke:#1F2937,color:#0E1726;
  classDef kv fill:#D6F5E3,stroke:#1F2937,color:#0E1726;
  classDef der fill:#EEE6FF,stroke:#1F2937,color:#0E1726;
  classDef ask fill:#ffffff,stroke:#1F2937,color:#0E1726;
  class REL rel;
  class DOC doc;
  class KV,GR kv;
  class SRCH,OLAP,POLY der;
  class R,INV,ONE ask;
        

One source of truth, many derived stores, kept in sync on purpose.

Polyglot persistence is not "use five databases." It is: pick one store that owns the truth, then add read-optimized derivations (a cache, a search index, an analytics copy) that you rebuild from it. Every extra store is a sync problem you now own.

Polyglot in miniature: files, a log, and a derived graph.

My personal knowledge base is plain Markdown. No app, no server. Yet it runs three stores at once, each chosen from an access pattern, and it shows the whole lesson in one picture.

flowchart LR
  subgraph SOT["Source of truth"]
    MD["Markdown + YAML
notes, strategy, facts"]
    LOG["graph.jsonl
append-only decision log"]
  end
  MD --> BUILD["graph-build
derivation step"]
  LOG --> BUILD
  BUILD --> KUZU["Kuzu graph DB
derived · drop & rebuild"]
  KUZU --> QQ(["relational / graph queries
'all docs about a venture'"])
  MD --> GREP(["grep
raw phrase match"])
  classDef sot fill:#FEF3C7,stroke:#1F2937,color:#0E1726;
  classDef der fill:#EEE6FF,stroke:#1F2937,color:#0E1726;
  classDef read fill:#D6F5E3,stroke:#1F2937,color:#0E1726;
  class MD,LOG sot;
  class BUILD,KUZU der;
  class QQ,GREP read;
        

"Use Kuzu first, grep second." Same data, two stores.

The rule in the repo's own docs is an access-pattern decision written down. The question decides the tool, not familiarity.

Grep can't answer "everything connected to this venture" without me re-deriving the joins by hand. So the relational questions get a graph store, the text questions get text search. Same bytes, the access pattern split the work.

Access pattern → Invariant → Store → Sync cost.

Four short sentences. Run them on any database choice and you sound like you decided it, because you did.

Four ways engineers pick the wrong store.

The fix for all four is the same: start from the access pattern and name the invariant.

Pick a store from one feature's access pattern.

Take one feature from your app. Don't name a database yet. Answer the four questions out loud, let the answer fall out, and say the one rule the database has to protect.

Drop your store + the invariant it protects in the chat. We'll react to a few.

Access pattern first, store second, invariant always.

Relational for invariants and ad-hoc queries; document for aggregate reads; key-value and wide-column for scale with fewer guarantees; graph for relationships; search and OLAP as derived stores. Mix only when one store genuinely can't serve a pattern, and name the sync cost when you do.