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.
The whole idea
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
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?
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
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 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
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
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
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
- 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
contentarray 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
CYCLEclause) can run away. - Giant
jsonbblobs 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
- E. F. Codd, “A Relational Model of Data for Large Shared Data Banks” (CACM, 1970): the paper that separated what you ask from how it’s stored.
- Stonebraker & Pavlo, “What Goes Around Comes Around… And Around…” (SIGMOD Record, 2024): a sharp, opinionated tour of every data model since 2005, and why SQL absorbed them.
- PostgreSQL docs: JSON Types:
jsonvsjsonb, containment, GIN operator classes, and honest advice on document design. - MongoDB docs: Avoid Unbounded Arrays: the embedding trap, and the subset and reference patterns that fix it.
- Neo4j Cypher Manual: patterns, variable-length relationships and shortest paths, from the language GQL grew out of.
- Bronson et al., “TAO: Facebook’s Distributed Data Store for the Social Graph” (USENIX ATC 2013): a graph model served from MySQL at a billion reads per second.
- Oracle, “Property Graphs in Oracle Database 23ai: the SQL/PGQ Standard”: what GRAPH_TABLE looks like in a shipping database.
- Polars user guide: Lazy optimizations: the query optimizer hiding inside a DataFrame library.
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).