Data Models · 1 of 1

Data Models & Query Languages

The same facts can be a tree, a set of tables or a graph. The shape you choose decides which questions are cheap, which are painful, and what breaks when the data changes.

≈ 25 min

The whole idea

Document: ada.jsonDocument: ben.jsonuserspositionscompanies
Press ← → to step, or Space to play and pause, whenever a diagram has focus.

Why data models matter

Software is built in layers of models. Your code thinks in objects. Below it, the database thinks in tables, documents or graphs. Below that, a storage engine thinks in pages and sorted files, and below that, the disk thinks in blocks. Each layer hides the mess of the one beneath it behind a cleaner picture.

The data model is the layer you choose most deliberately, and it shapes everything above it. Take one small set of facts: Ada worked at Acme and at Globex, and Ben worked at Acme. As the hero diagram shows, you can store that as two nested documents, as three tables linked by IDs, or as a graph of people and companies. All three hold the same information. What differs is which questions are one cheap read and which need a join, a scan or a rewrite, and that’s the question to ask of any model.

The relational model

In 1970 Edgar Codd proposed storing data as relations: unordered sets of tuples, which everyone now calls tables of rows. A query names the rows it wants by value (WHERE company_id = 7), never by where they live.

That sounds obvious today. It wasn’t then. IBM’s IMS, built in the late 1960s to track parts for the Apollo program, stored records in a fixed hierarchy, and the CODASYL network standard that followed, based on Charles Bachman’s IDS, let a record have several parents. In both, a program navigated: it followed pointer chains along access paths chosen when the database was designed. A new question that didn’t match an existing path meant new code, and often a new database layout.

Codd’s move was to separate the logical model from the physical one. The database decides how to reach the rows, so you can add an index, change the layout or move to new hardware without touching a single query. Joins let any table relate to any other, which makes many-to-one and many-to-many relationships natural.

That design has absorbed every challenger for fifty years. In 2024 Michael Stonebraker and Andy Pavlo reviewed two decades of new data models (key-value, document, column-family, graph, vector) and concluded that the relational model is still dominant, because SQL keeps borrowing the good ideas from its rivals.

Objects vs tables

Application code holds an object graph: a User with a list of Jobs, each pointing to a Company. A relational database holds flat rows. Something has to translate between the two, and the awkward fit is called the object-relational mismatch, or impedance mismatch.

ORMs (Hibernate, ActiveRecord, Django’s ORM, Prisma) write that translation for you, and they’re genuinely useful. But they make a database look like local memory, and the illusion leaks exactly where it costs most.

The N+1 query problem

Each round trip is cheap. Fifty of them on every page view is not.

The N+1 problem is the classic leak: one query for the list, then one more per item as the code touches a lazily loaded field. It hides well, because each query is fast and the code reads naturally. The fixes are to fetch the shape you need up front, with a join or the ORM’s eager loading (includes in Rails, select_related and prefetch_related in Django), or to have SQL build the nested result itself: Postgres’s json_agg can return each user with their jobs as one JSON value.

Or you store the data in the shape the code already wants.

The document model

A document database stores each record as a self-contained tree, usually JSON (MongoDB uses a binary form called BSON). A user profile, with its jobs, education and links nested inside, is one document. Three things follow:

  • Locality. Everything about the profile sits together, so one read fetches it, with no join.
  • A natural fit for one-to-many trees. A profile owns its jobs. The tree in the document is the tree in the code, so the mismatch mostly disappears.
  • Flexibility. Two documents in a collection don’t have to share fields, which suits data whose shape varies.

The cost is that the tree is the only cheap direction. Fetching a profile is fast. “Everyone who worked at Acme” cuts across documents and needs an index on a nested field, or a scan. And documents are read and written whole: MongoDB caps them at 16 MiB, and Postgres’s docs warn that every update to a JSON value locks the whole row, so they advise keeping each document to a single “atomic datum” of business data.

Embed or reference?

posts collection{ _id: 42,title: "Launch day",comments: [ben: congrats on the launchchen: nice workdev: when is v2 out?] }document size16 MiB limitone read renders the whole pagecomments collection{ post_id: 42, by: "ben", … }{ post_id: 42, by: "chen", … }{ post_id: 42, by: "dev", … }{ post_id: 42, by: "eve", … }{ post_id: 42, by: "fay", … }… 84,113 small documentsindex: post_id → commentsposts collection{ _id: 42,title: "Launch day",comments: [ben: congrats on the launchchen: nice workdev: when is v2 out?] }document size16 MiB limitone read renders the whole pagecomments collection{ post_id: 42, by: "ben", … }{ post_id: 42, by: "chen", … }{ post_id: 42, by: "dev", … }… 84,113 small documentsindex: post_id → comments
Embedding is right when the nested part is bounded and read together with its parent. Comments are neither.

The rule of thumb that falls out:

  • Embed what the parent owns, what has a known upper bound, and what you always read together with it.
  • Reference what grows without limit, what is shared between many parents, and what is updated on its own.

The moment you reference, you need joins again. MongoDB added the $lookup stage for exactly that in 2015.

Normalization

Look again at the hero. In the document version, the name Acme appeared twice. That’s harmless until the fact changes. Companies merge, cities get renamed, a typo gets fixed, and now every copy must change together.

One rename, two designs

positionspersoncompanysinceAdaFacebook2014BenFacebook2017ChenFacebook2019DevGlobex2020companiesidname7Facebook8Globexquery resultAda · Meta · 2014positionspersoncompanysinceAdaFacebook2014BenFacebook2017ChenFacebook2019DevGlobex2020companiesidname7Facebook8Globexquery resultAda · Meta · 2014
Facebook became Meta in October 2021. How many rows had to know?

Normalization means storing each fact in exactly one place and pointing to it with an ID. The ID carries no meaning to people, so it never needs to change, and everything that refers to it stays consistent. Profiles then point to a companies row instead of repeating a string, which also gives you consistent spelling, one place to add a logo, and a cheap way to find everyone who worked there. The formal normal forms (1NF, 2NF, 3NF, BCNF) make “one fact, one place” precise. In practice, asking “if this fact changed, how many rows would I update?” catches most problems.

The trade-off is plain:

Normalized Denormalized
Writes one row per fact; no anomalies update every copy, or accept drift
Reads need joins to reassemble already assembled
Storage compact repeats data
Good for OLTP with changing facts read-heavy views, analytics, caches

Denormalizing on purpose, by keeping a copy shaped for one read path, is a legitimate and common choice. Precomputed timelines, search indexes and materialized views are all deliberate copies. The difference is that the copy is derived from a single source of truth and can be rebuilt, rather than being five rows that each think they’re the truth.

This is also where documents struggle. Data rarely stays a clean tree: the second version of a product adds “recommended by”, “same company as” and “also bought”, and those are many-to-many relationships. A model with weak joins pushes that work into application code, which is where joins go to become slow and buggy.

Schema-on-read

Document databases are often called schemaless. That’s misleading. Code that reads user.first_name assumes a field exists, so a schema is always there. The real question is when it’s enforced.

  • Schema-on-write: the database checks every write against a declared schema. Data that doesn’t fit is rejected. Relational tables work this way.
  • Schema-on-read: the database accepts any structure. The schema is implicit, and it’s interpreted by the code that reads the data.

It’s the same split as static versus dynamic typing, and neither is simply better. The difference shows up the day the shape of the data changes.

One change, two bills

schema-on-write · users tablenameAda LovelaceAlan TuringGrace Hopperfirstnullnullnulllastnullnullnullschema-on-read · users collection{ "name": "Ada Lovelace" }old shape{ "name": "Alan Turing" }old shape{ "name": "Grace Hopper" }old shape{ "first": "Barbara", "last": "Liskov" }schema-on-write · users tablenameAda LovelaceAlan TuringGrace Hopperfirstnullnullnulllastnullnullnullschema-on-read · users collection{ "name": "Ada Lovelace" }old shape{ "name": "Alan Turing" }old shape{ "name": "Grace Hopper" }old shape{ "first": "Barbara", "last": "Liskov" }
Splitting a name into first and last. The work doesn't disappear; it moves.

Schema migrations have a worse reputation than they deserve. Since Postgres 11 (2018), adding a column even with a non-volatile default no longer rewrites the table, and MySQL 8.0 can add columns instantly. What stays expensive is backfilling data into a new shape, and that cost is the same in any model: a document database that wants every record in the new shape has to rewrite every record too.

Schema-on-read earns its keep when the data really is heterogeneous: many kinds of object that don’t justify a table each, or payloads whose structure is decided by someone else, such as webhooks from a third-party API. Even then, most teams add validation back once things settle. MongoDB has supported $jsonSchema validators on collections since version 3.6 (2017), and a Postgres CHECK constraint can enforce rules on a jsonb column.

Graph models

When many-to-many relationships are the point of the data, as in social networks, road maps, fraud rings, software dependencies or knowledge graphs, a graph is the natural model.

The most common form is the property graph. It has vertices and edges. Each vertex has a label (Person, Company) and properties, and each edge has a type (KNOWS, WORKS_AT), a direction, its own properties, and a vertex at each end. Any vertex can connect to any other, and you can traverse edges in both directions. The other big family, triple stores (RDF, queried with SPARQL), stores every fact as subject–predicate–object and is popular for linked open data.

A traversal of unknown depth

The degrees-of-connection question every professional network answers, as a breadth-first walk.

Here is that question in Cypher, Neo4j’s query language:

MATCH (me:Person {name: 'You'})-[:KNOWS*1..3]-(p:Person)
      -[:WORKS_AT]->(:Company {name: 'Acme'})
WHERE p <> me
RETURN DISTINCT p.name

*1..3 says “follow between one and three KNOWS edges”, in either direction. In SQL, where each friendship is stored as two rows of a knows table (one per direction), a path of unknown length needs a recursive common table expression. Each iteration adds one more hop:

WITH RECURSIVE reach(person_id, depth) AS (
  SELECT friend_id, 1 FROM knows WHERE person_id = :me
  UNION
  SELECT k.friend_id, r.depth + 1
  FROM reach r JOIN knows k ON k.person_id = r.person_id
  WHERE r.depth < 3
)
SELECT DISTINCT p.name
FROM reach r
JOIN people p    ON p.id = r.person_id
JOIN positions j ON j.person_id = p.id
JOIN companies c ON c.id = j.company_id
WHERE c.name = 'Acme' AND p.id <> :me;

Both work; one says what it means. Graph databases differ in how they store edges, too. Neo4j uses index-free adjacency: each node’s storage leads directly to its relationships (a pointer chain in its older record formats; in the newer block format, the relationships sit in the node’s own block, with very dense nodes spilling into a per-node tree), so a hop doesn’t go through a global index. That makes deep traversals cost roughly the size of the neighbourhood visited rather than the size of the whole graph.

Graphs now have standards. GQL (ISO/IEC 39075), published in April 2024 and based largely on Cypher, is the first new ISO database query language since SQL. SQL:2023 added SQL/PGQ (Property Graph Queries), which lets you declare a graph over ordinary tables and query it with the same pattern syntax inside SQL:

SELECT name FROM GRAPH_TABLE (social
  MATCH (a IS person WHERE a.name = 'You') -[IS knows]- (b IS person)
  COLUMNS (b.name AS name));

Oracle shipped what it calls the first commercial SQL/PGQ implementation in Database 23ai (now succeeded by 26ai). Postgres committed an implementation in March 2026, then reverted it before Postgres 19 shipped, so it’s now expected in a later release.

Declarative queries

Old navigational databases were queried imperatively: a program said how, by walking this pointer and looping over those records in this order. SQL, Cypher and SPARQL are declarative: you describe the result you want, and the database decides how to compute it.

What, not how

The query never changes. The plan does, every time the data or the indexes do.

That indirection is why declarative languages won:

  • The optimizer can choose. Join order, index use and algorithms are picked from statistics, and picked again as the data changes.
  • The engine can improve underneath you. New index types, parallel execution and new storage engines speed up existing queries without anyone rewriting them.
  • Parallelism is the engine’s problem. A loop fixes an order. A declarative query leaves the engine free to split work across cores or machines.

The same idea keeps resurfacing outside SQL. MongoDB’s aggregation pipeline expresses queries as a list of stages ($match, $group, $lookup) that the server can reorder and optimize. DataFrames, the tables of data science, started out eager in pandas: each line runs as soon as it’s called, imperatively. Modern DataFrame libraries have moved to lazy evaluation, which turns code into a query plan first:

(pl.scan_parquet("users.parquet")
   .filter(pl.col("city") == "Oslo")
   .select("name", "signup_date")
   .collect())   # nothing runs until here

Polars’ optimizer then pushes the filter and the column list down into the file scan, orders joins and drops unused work, which is exactly what a SQL planner does. Spark DataFrames go through the same Catalyst optimizer as Spark SQL, and DuckDB will run plain SQL over a pandas DataFrame in place. The line between “DataFrame code” and “a query” is mostly whether evaluation is lazy.

The models converge

For a decade, choosing a database meant choosing a model. That’s much less true now.

Fifty years, two lanes

relational databases add…document and graph databases add…2014–2024: each side adopts the other’s ideas197020102015202020251970 · Coddthe relational model19702009 · MongoDBJSON documents, no joins’092015 · MongoDB 3.2$lookup: joins’152017 · MongoDB 3.6JSON Schema validation’172018 · MongoDB 4.0multi-document transactions’182014 · Postgres 9.4jsonb, indexable JSON’142016 · SQL:2016SQL/JSON in the standard’162024 · Postgres 17JSON_TABLE’242023 · SQL:2023property graph queries’232024 · GQLISO graph query language’24relational databases add…document and graph databases add…2014–2024: each side adopts the other’s ideas197020102015202020251970 · Coddthe relational model19702009 · MongoDBJSON documents, no joins’092015 · MongoDB 3.2$lookup: joins’152017 · MongoDB 3.6JSON Schema validation’172018 · MongoDB 4.0multi-document transactions’182014 · Postgres 9.4jsonb, indexable JSON’142016 · SQL:2016SQL/JSON in the standard’162024 · Postgres 17JSON_TABLE’242023 · SQL:2023property graph queries’232024 · GQLISO graph query language’24
Each side kept adopting the other's best ideas.

Postgres added a text json type in 9.2 (2012) and binary, indexable jsonb in 9.4 (2014). A GIN index on a jsonb column answers containment queries like “documents whose tags include postgres”. MongoDB, going the other way, added joins, schema validation and multi-document ACID transactions. And SQL/PGQ lets one database serve relational, document and graph queries over the same rows.

Convergence doesn’t make the models identical. What’s left is the default: how data is laid out on disk, and which access path the engine was built to make fast. A jsonb value is rewritten in full whenever any key in it changes. $lookup exists, but MongoDB still recommends designing so that you rarely need it. And SQL/PGQ runs its patterns as joins over ordinary tables, so it doesn’t give you index-free adjacency for deep walks. Choose the model that matches your main access pattern, and use the bolted-on features for the rest.

Trade-offs

Relational Document Graph
Natural shape tables of rows self-contained trees vertices and edges
Best fit many-to-one, many-to-many; changing facts one-to-many trees read whole highly connected data, paths of unknown depth
Joins first-class, optimized limited ($lookup), discouraged the traversal is the join
Locality row by row; joins reassemble whole document in one place neighbourhood via pointers
Schema on write (declared) on read (implicit), optional validation usually flexible; labels and types
Query language SQL aggregation pipeline, MQL Cypher, GQL, SPARQL, SQL/PGQ
Weak spot object mismatch, deep recursion cross-document queries, unbounded growth bulk scans and aggregates

In the real world

~2.3 Mpieces of Guardian content moved from MongoDB to Postgres jsonb (2018)
1 billionreads per second served by Facebook’s TAO graph layer on MySQL (2013)
2 millionqueries per second on LinkedIn’s LIquid graph database (2023)
  • The Guardian ran Composer, its publishing system, on MongoDB. After operational trouble running Mongo on AWS, the team moved it in 2018 to Postgres on Amazon RDS, choosing Postgres largely for jsonb: articles stayed documents, and the data model barely changed. A proxy sent traffic to the old and new APIs during the move, so the cutover had no downtime.
  • Uber’s Schemaless (2016) went the other way: a schema-on-read store built on MySQL. Each cell is an immutable JSON blob addressed by a row key, a column name and a version number, so updates are appends, and sharded MySQL handles storage.
  • Facebook’s TAO (USENIX ATC 2013) serves the social graph as typed objects and associations, a property graph by another name, through a graph-aware cache in front of MySQL. The paper reports about a billion reads and millions of writes per second.
  • LinkedIn’s LIquid is a purpose-built graph database for its Economic Graph. In 2023 LinkedIn reported around 2 million queries per second against some 270 billion edges, and expected that load to double within 18 months.
  • The Panama Papers (2016): the ICIJ turned 11.5 million leaked documents into a Neo4j graph of people, companies and intermediaries, explored through Linkurious, so reporters could follow the links without writing queries.
  • Notion models everything on a page as a block: a record with a type, properties, an ordered content array of child block IDs and a parent pointer. A document-shaped product, built entirely from references.

Pitfalls

  • “Schemaless means no schema.” It means the schema lives in your code, unversioned and unchecked. Write it down, and validate at the boundary.
  • Unbounded embedded arrays. Comments, events and log entries inside a parent document grow until reads slow down and writes hit the size limit.
  • Denormalizing without an owner. Every copy needs a single source of truth and a way to rebuild it. Otherwise the copies drift, and you won’t notice until a customer does.
  • Trusting the ORM blindly. Log the SQL in development and count queries per request. N+1 queries are invisible in code review.
  • Recursive SQL without a bound. On cyclic data such as friendships, a recursive CTE without a depth limit or a cycle check (Postgres 14 added the standard CYCLE clause) can run away.
  • Giant jsonb blobs with hot fields. One frequently updated counter inside a large document means the whole value is rewritten and the whole row is locked on every update. Pull hot fields out into columns.
  • Picking a model for flexibility alone. Pick it for the access pattern you’ll run a million times a day.

Recall

Go deeper

Keep going

The atomic notes for this lesson live in the knowledge graph:

Next on the map: Storage Engines (how those tables and documents become bytes on disk) and Indexes & Search (the secondary indexes that make “everyone who worked at Acme” fast).

Done with this lesson?

Mark it learned and the concepts it teaches will light up in your galaxy.

Data Models: roadmapBack to the roadmap