Relational Databases: The Default for a Reason

Here's a puzzle from the numbers. The most-used database among professional developers is relational — PostgreSQL, at 58.2%, three years running. The most deployed database on Earth is relational — SQLite, sitting in every phone, every browser, every Airbus A350: over a trillion active databases. And on Prime Day 2025, while the exotic engines got the conference talks, Amazon Aurora quietly processed 500 billion transactions.

The core design behind all of this is from 1970. In an industry that reinvents itself every five years, the default data store is old enough to have grandchildren. Either the entire profession is asleep — or this family keeps winning for reasons that matter.

The previous lesson called relational "the only family whose native question is correctness." This lesson opens the box and shows you what that buys, concretely: five gifts you receive the moment you type CREATE TABLE, before you've written a single line of defensive code.

In this lesson: Codd's bargain (the 55-year-old idea that still pays rent), declarative queries and the planner that out-thinks you, why joins exist and why they're not the enemy, constraints as physics, transactions as all-or-nothing — and the honest bill for all of it.

Scope: how the engine physically stores and finds rows is Inside a Database: The Journey of a Write and B-Trees & B+ Trees; the full transaction treatment is ACID, Precisely; when to pick something else entirely is SQL vs NoSQL: The Decision Rubric. Here we're answering one question: why is this the default?

To feel why the relational model landed like a thunderclap, you have to see what came before it. In 1969, data lived in hierarchies and networks — trees of records wired together with pointers. To find an order, your program navigated: start at the customer, follow the pointer to their orders, walk the chain. The code and the storage layout were the same thing. Reorganize the data — add an index, move records to a new disk layout — and every program that navigated the old shape broke.

Edgar Codd, a mathematician at IBM, proposed something that read as almost rude in its simplicity: store everything as flat tables of rows, describe data by "its natural structure only," and let programs say nothing about how to reach anything. He called the payoff data independence — "the independence of application programs from growth in data types and changes in data representation."

That's the bargain. You give up your pointers and your clever navigation code. In exchange, the engine owes you freedom, forever: it can add indexes, reorganize pages, rewrite its storage engine — and your queries keep working. Not one line changes.

Fifty-five years later, the bargain still compounds. A SQL query written in 1992 runs on a 2026 engine — faster, because the engine underneath got better while the query stood still. Let that sink in: which other piece of your 1992 stack still runs, let alone improved?

Tables, in other words, aren't an aesthetic. They're a contract: the model you think in is decoupled from the layout on disk. Every gift in the rest of this lesson falls out of that one separation.

The contract needs a language, and the language has one radical property: it's declarative. You describe the answer you want; you say nothing about how to compute it.

When the marketplace asks "top 5 dishes by city, last 7 days," you write roughly this:

Nowhere does that say scan this index, then hash-join, then sort. Deciding that is the query planner's job: it generates candidate execution plans, estimates each one's cost, and runs the cheapest. The idea comes from IBM's System R project in the 1970s — and at the time, people genuinely did not believe a machine could construct a better plan than a human expert. It could. Every serious optimizer since is a descendant.

Two consequences do quiet, enormous work in production. First: a new business question is one query, not a deploy. Nobody designed the schema for "customers who ordered in May but never since" — you just ask, and the planner figures out how. Second: the plan evolves as your data does. At ten thousand orders, the planner might scan the table; at a hundred million, after you add an index, it switches strategy — and the query text never changes. The application is insulated from its own growth.

That's data independence again, wearing work clothes. (How the planner actually thinks — and how to read its mind with EXPLAIN — is its own lesson: How a Query Executes.)

Look at what the marketplace knows: restaurants, dishes, customers, orders. An order references a restaurant; a dish appears in thousands of orders; a customer favorites many dishes and orders from many restaurants. Facts, cross-referencing other facts.

The relational instinct is to store each fact exactly once. The restaurant's name lives in one row of restaurants — not copied into every order that ever touched it. Rename "Ramen House" to "Ramen House & Bar" and you update one row; every past and future order reflects it instantly, because they reference the fact instead of carrying a copy. Store the name in ten thousand places instead, and someday you'll update nine thousand of them — and then two parts of your own system will disagree about reality. (Database people call these update anomalies; you'll call them a very bad Tuesday.)

But single-copy storage has a read-side price: to display an order — customer name, restaurant, dishes, prices — the engine must recombine facts scattered across tables. That recombination is a join. This is the honest way to understand joins: they aren't a tax the relational model imposes on you; they're the read-side of not lying to yourself on the write side.

And where data is genuinely many-to-many — dishes appear in many orders, orders contain many dishes — the relational model handles the cross-references natively, in both directions, with no duplication. This is exactly the terrain where "just nest everything in one document" quietly falls apart, which is why the trade-off returns, properly, in Document Databases and NoSQL Modeling: Denormalization & Single-Table Design. And no — joins are not "slow"; hold that thought for the corrections at the end.

Now the gift that separates this family from every other on the map. A relational schema doesn't just describe your data — it enforces rules about what data is allowed to exist:

Read those four lines as what they really are: states of the world that can no longer exist. An order pointing at a deleted restaurant? The insert is rejected. The same signup email twice? Rejected. A retried payment charging twice? The second insert hits UNIQUE(idempotency_key) and bounces — you met this exact trick in Idempotency: Safe to Retry, and here's the engine holding the other end of it.

"But my API already validates all that." It probably does — at one door. Here's the distinction this whole lesson turns on: application validation is a guard at one entrance; a constraint is a law of physics for the table. Your validation runs when requests come through your API. It does not run for the nightly import script, the analytics backfill, the second service someone built last quarter, or the 3 a.m. hotfix typed straight into a console. The constraint binds all of them — every writer, every path, forever — because it lives where the data lives.

And the database checks these rules faster than your application can — it's sitting next to the data, with the indexes already in hand. You'll prove all of this to yourself in the widget below: same attacks, two doors, watch what each store lets through.

One gift left, and it's the one you already know you need. When a customer hits Place Order, three writes must happen: create the order, decrement the stock, record the payment. Now remember Shared State & Race Conditions: Why Locks Exist — what happens if the process crashes after write one? Or if two customers grab the last portion of ramen in the same millisecond?

A transaction wraps the three writes into a single all-or-nothing unit: either every write lands, or — on a crash, an error, a conflict — the engine rolls the world back to the moment before you started, as if nothing happened. No half-created orders. No stock that moved without a payment. And when two writers collide on the same row, the engine serializes them: one wins, one waits — not both succeeding into corruption.

This is the family's crown jewel and its native question — "keep many records correct, under concurrent writers." It's also a deep topic with its own machinery (write-ahead logs, MVCC, isolation levels) and its own failure modes, so the course gives it a full section: ACID, Precisely and Isolation Levels & Their Anomalies live in Transactions & Concurrency. Here, feel the shape: correctness isn't something you code around the database — it's something this database is.

Time to stop trusting me. Below are two stores holding the same marketplace data — JSON files guarded by app validation, and a relational database with the constraints from this lesson. Every attack you fire travels through both doors at once: the validated API, and the nightly import script that bypasses the app.

Try this sequence. First, fire "sign up the same email twice" — the app catches it at the API door… and the import door sails right past your validation. Then fire the crash and the race — the attacks no request-validation can even see. Then read the JSON store's "3 months later" bill, and switch to stage ② to feel the other gift: ask both stores a question nobody designed for.

If the gifts were free of charge, there'd be no landscape — one family would have eaten the map. The gifts have prices, and you should know them before a design review does.

Constraints tax writes. Every FK check is a lookup; every UNIQUE is an index the engine must maintain on each insert. At ordinary scale you'll never feel it. At extreme, sharded scale, the checks stop being local — and some of the biggest shops hand parts of the safety net back: Vitess (the sharding layer under PlanetScale) historically didn't support foreign keys at all, and its guidance at large scale is to enforce referential integrity in the application — accepting bug-risk, deliberately, to buy write throughput. Cascading deletes on huge tables can block for minutes; CASCADE actions can even corrupt downstream change-capture pipelines. Notice the shape of that decision: it's the lesson's rule from One Size Fits None again — a measured workload hit the engine's physics, and a team paid a known price. That is nothing like skipping constraints on day one because "the app validates."

Schema changes are the classic self-inflicted outage. The schema is a contract, and renegotiating a contract on a live table costs: ALTER TABLE takes an exclusive lock, and a migration that's milliseconds on your dev machine can freeze a ten-million-row production table for minutes while every query queues behind it. Teams have burned themselves badly enough that there's a whole discipline for changing schemas without downtime — Schema Migrations: Expand-Contract covers it in Architecting Production Systems.

The model won't always match your objects. Application code thinks in nested object graphs; tables are flat. The gap — the impedance mismatch — is why ORMs exist and why they leak. When your data truly is a self-contained aggregate you always load whole, flattening it into five tables buys you joins you never wanted; that honest kernel is where Document Databases begins, two lessons from now.

So the staff-level summary, the one you'd defend under follow-up: relational is the default because its gifts — data independence, declarative queries, joins over single-copy facts, constraints, transactions — cover what almost every system needs almost all of the time. It stops being the default only when a measured workload makes its costs bite harder than its gifts help — and you priced the alternative's runbook. The full checklist for that call is SQL vs NoSQL: The Decision Rubric.

Five real marketplace incidents landed in the postmortem channel. For each, name the single schema feature from this lesson that would have made the incident impossible. Commit to answers before you scroll.

1. A restaurant closed and was deleted; 4,000 old orders now crash the courier app when it loads their restaurant details.
2. A user double-clicked Pay on a slow connection and was charged twice for the same order.
3. An intern's backfill script set delivery_fee to -250 for a whole city.
4. The app crashed between creating an order and decrementing stock; the dashboard now shows an order for an item that was never reserved.
5. Marketing asked "which customers ordered in May but never again?" — and engineering scheduled a two-week project to build it.

Answers. (1) FOREIGN KEY — with the reference in place, the delete is rejected (or explicitly cascaded as a decision, not an accident) — no orphan orders. (2) UNIQUE(idempotency_key) — the retry's insert bounces; one charge, ever. (3) CHECK (delivery_fee >= 0) — the script fails loudly at the first bad row instead of silently poisoning a city; note that validation in the app would not have helped: the script never went through the app. (4) a transaction around order+stock — the crash rolls both back; no phantom orders. (5) trick question: that's not an incident, it's a query — GROUP BY customer_id HAVING max(ordered_at) < '2026-06-01'. If a new question costs a two-week project, you've lost data independence somewhere — usually by scattering facts across stores or services.

• "Joins are slow." Joins are slow when columns aren't indexed or the model is wrong. Modern planners reorder joins and push filters down automatically — they don't care how you wrote the query. And the alternative to a database join is a join you hand-write in application code, without an optimizer, over the network. That one is slow.
• "SQL is legacy technology." A 1992 query runs faster on a 2026 engine, unchanged — that's not legacy, that's the longest-running compatibility win in software. 58.2% of professional developers use Postgres alone, and the trend line points up; every declared SQL-killer of the last lesson ended up speaking SQL.
• "Constraints are redundant — my app validates." Your app validates one door. The import script, the second service, and the 3 a.m. console session use other doors. You drove this one: three of the six attacks got through a validated API path's store anyway, because no request-check can catch a crash, a race, or a replay.
• "Schemas slow teams down." The schema is the shared contract that lets five teams and your analytics stack read one truth safely. The real cost is change management — locks during migrations — and the cure is a technique (expand-contract), not abandoning the contract. Schemaless doesn't delete the schema; it moves it into every reader, unenforced.
• "Always normalize everything." One-fact-one-place is the write-side default, not a religion. Deliberately duplicating data to speed reads is a real tool with real costs — it gets its own lesson (Denormalization) in Scaling Reads.
• "Relational = SQL = Postgres." Three layers: a mathematical model (Codd, 1970), a query language over it (SQL — not the only one ever proposed, just the winner), and engines implementing both (Postgres, MySQL, SQLite, Aurora…). Keep them separate and vendor arguments stop being confusing: you can dislike an engine without indicting the model.

• Codd's bargain (1970): describe data as tables — its natural shape — and the engine owes you data independence forever: storage can change, indexes can appear, and your queries survive untouched. Proof: a 1992 query runs faster on a 2026 engine.
• Declarative + planner: you say WHAT, the cost-based planner (System R's descendants) finds HOW — a new business question is one query, not a deploy, and the plan improves as data grows.
• One fact, one place: joins are the read-side of write-side honesty, not a tax; many-to-many is where this model shines.
• Constraints are physics, not advice: PK, FK, UNIQUE, CHECK, NOT NULL make bad states impossible at every door — app validation only guards one.
• Transactions: all-or-nothing across multiple writes, safe under concurrent writers — the family's native question, deepened in ACID, Precisely.
• The bill: constraint write-tax (handed back deliberately at sharded hyper-scale — Vitess), migration locks (Schema Migrations: Expand-Contract), and the impedance mismatch — which is exactly where the next two lessons' families begin.
• Next: the family that answers one question faster than this one ever will — Key-Value Stores.