Data Models Overview — Relational vs Document vs Key-Value vs Columnar vs Graph

A data model is the shape your data takes — in your head and on disk — and that shape decides what is cheap, expensive, or outright impossible. The relational model is just one of five common options, and production engineers pick the model to match the access pattern (how you read and write), never the reverse.

Intermediate data-models nosql polyglot-persistence system-design Created 2026-05-29 · Updated 2026-05-29

Key Components

The unifying tension behind every model is one question: do you store data the way it is logically true (normalized, no duplication → flexible queries, but reads must reassemble via joins), or the way it is physically read (denormalized / aggregated → fast for the target pattern, worse at everything else)?

Relational: Normalized tables connected by foreign keys, queried with SQL. Flexible ad-hoc queries, ACID transactions, engine-enforced integrity — but joins and sharding get hard at scale. The correct default for most apps. (PostgreSQL, MySQL.)
Document: Self-contained JSON aggregates: store together what you read together. One read fetches a whole object, maps cleanly to code, shards well — but duplication creeps in and cross-document joins are weak. (MongoDB, Firestore, Couchbase.)
Key-Value: A giant distributed hash map; the database knows nothing about the value. O(1), blazingly fast, trivially scalable — but you can only query by exact key. A document store is essentially a key-value store that can also index the value's contents. (Redis, Memcached, etcd.)
Columnar: Logically tables, but stored column-by-column. An aggregate over one column touches only that column (far less I/O) and single-type columns compress 10x+. Terrible for single-row OLTP. (ClickHouse, Redshift, Snowflake, Parquet, DuckDB.)
Graph: Nodes and edges where relationships are first-class stored objects. Wins at deep traversal via index-free adjacency — each node stores its own edges. Niche and hard to shard. (Neo4j, Neptune.)

Concrete Example

The sharpest real-world decision is Relational vs Document, because both are general-purpose — almost anything can be modeled in either. So the question is never "which can store this?" but "which makes my dominant access pattern cheap, and my critical invariants safe?" Take one blog domain, modeled both ways.

Relational — every fact stored exactly once:

authors(id, name)
posts(id, author_id, title, body)
comments(id, post_id, author_id, body)
tags(id, name)
post_tags(post_id, tag_id)          -- M:N junction

Document — one post embeds everything you render together:

{
  "_id": "post_42",
  "title": "Why columnar storage is fast",
  "author": { "id": "a_9", "name": "Sam" },   // name copied in
  "tags": ["databases", "olap"],
  "comments": [
    { "author": { "id": "a_3", "name": "Lee" }, "body": "Great post" }
  ]
}

The same operations feel completely different depending on the model:

Operation	Relational	Document
Render one post page	joins across 5 tables	one read by id ✅
All posts by author `a_9`	`WHERE author_id` ✅	hard — author buried in docs
All comments Sam made anywhere	`WHERE author_id` ✅	brutal — scattered across docs
Sam changes his name	update one row ✅	update every doc he appears in ❌
Top 10 tags by count	one `GROUP BY` ✅	awkward cross-doc aggregation
New unplanned query	just write SQL ✅	often reshape the docs

Document is spectacular for the access pattern it was designed around and clumsy for everything else. Relational is mediocre at nothing and stays flexible for queries not yet imagined. That asymmetry is the whole decision.

Visual Model

Think of the five models not as competitors but as specialists. Each one made a trade: it gave up something the relational model has (joins, strict schema, strong consistency) to gain something else (scale, flexibility, raw speed for one pattern). The heatmap below scores each model on the dimensions that matter — greener is stronger. Notice that no row is all-green: every model has a deliberate weak spot, and your job is to match its cheap things to the things you do most.

Model	Ad-hoc queries	Aggregate read by id	Horizontal scale	Deep traversal	Built-in integrity
Relational	Excellent	Needs joins	Hard	Slow w/ depth	Native (ACID)
Document	One pattern	One read	Shards well	Weak joins	App-enforced
Key-Value	Key only	O(1) get	Trivial	None	None
Columnar	Analytics only	Reassembles row	Scales scans	N/A	Limited
Graph	Pattern queries	Node by id	Hard to shard	Index-free adjacency	Relationship-typed

Scores are relative cost profiles, not benchmarks — the point is the shape of each row. "NoSQL" is the umbrella over the bottom four; each traded a relational guarantee for one standout strength.

Deeper — Edge Cases & Gotchas

Why columnar is fast for analytics but bad for single rows

A row store keeps each row's columns contiguous; a column store keeps all values of one column contiguous. For SELECT AVG(salary) over a 50-column employees table, the column store reads only the salary column — roughly 50x less I/O than a row store, which must read every full row to extract one field. Single-type columns also compress extremely well, cutting I/O further. Fetching or updating one full row is the exact opposite: the column store must visit every column file to reassemble or write that one row. So columnar = analytics/OLAP; row = OLTP.

Index-free adjacency: why graph wins at friends-of-friends-of-friends

Relational is a giant phone book: to find a friend's friends you go back to the front and look each one up again — every hop is a fresh search through the entire book. Graph is a contacts app where each person's card has direct tappable links to their friends' cards. Trace it with 10 friends each across 100M people:

Relational friendships table        Graph (index-free adjacency)
Hop1: lookup me → 10                 Hop1: follow 10 pointers
Hop2: 10 lookups → 100               Hop2: follow 100 pointers
Hop3: 100 lookups → 1,000            Hop3: follow 1,000 pointers
  (each lookup indexes 100M rows)      (cost independent of dataset size)

For a single hop relational is perfectly fine — the gap opens with depth.

Anti-pattern: treating Mongoose ref + .populate() as a database join.

// Looks like a join. It is NOT.
const posts = await Post.find().populate('author');
// Query #1: db.posts.find(...)            → posts (each with an author ObjectId)
// Mongoose collects the author ids
// Query #2: db.authors.find({_id:{$in:[...]}})  → those authors
// Mongoose stitches them together IN YOUR NODE PROCESS

The join happens in app memory, not the database. It is one extra query per populated path (batched via $in), not per document — so populate('author') over 100 posts is 2 queries, not 101. But chaining .populate('author').populate('comments').populate('tags') is 4 queries, and a naive populate-in-a-loop is exactly how the N+1 problem appears. MongoDB does have a real server-side join — $lookup (aggregation, v3.2) — but it is a bolt-on that is generally less efficient and historically limited (e.g. in sharded setups); guidance still favors embedding on hot paths.

Diagnostic: if you reach for populate/$lookup on most queries, your access pattern is relational — you picked document and are fighting it. The boundary is genuinely blurring (Postgres JSONB stores documents; MongoDB added transactions and $lookup), but the default cost profile still holds: relational makes joins and integrity cheap while aggregate reads need assembly; document makes aggregate reads cheap while joins and integrity are expensive.

Three principles for an interview or design review

Model follows access pattern. Decide how you read and write first, then pick the model. Picking trendy tech and later discovering you need joins is the classic mistake.
"NoSQL" is not one thing. It is an umbrella over document + key-value + columnar + graph. Always ask "which model, and what did it trade away?"
Polyglot persistence is normal. Real systems mix: Postgres (core transactional) + Redis (cache/sessions) + ClickHouse (analytics) + maybe Neo4j (a recommendation feature) — one app, several models, each matched to its access pattern. Keeping them in sync is what CDC/outbox and cache-invalidation patterns solve.

Senior default: when unsure, choose relational — it adapts to query patterns you did not anticipate, and you can always denormalize later (it is much harder to un-bake a wrong document model).

Test Yourself

What is the one-sentence difference between a document store and a key-value store?

A teammate says "use MongoDB, it scales better." What is your first question?

Why is columnar storage fast for SELECT AVG(salary) but worse for fetching one full row?

Is Mongoose populate a database join, and where does the work happen?