Relational databases

Why it matters

Fifty years after the relational model was proposed, Postgres and MySQL still sit under most of the world's applications, including ones at a scale that was supposed to be impossible for them. Knowing why they remain the default, and exactly where their limits are, keeps you from either abandoning them too early or clinging to them too long.

The idea

A relational database stores data in tables of typed rows, relates tables through keys, enforces rules declaratively, and answers questions written in SQL that the planner turns into an efficient execution strategy. The model's power is that you describe the data once, correctly, and ask any question later.

Normalisation: each fact in one place

A normalised schema stores every fact exactly once. The customer's name lives in customers, orders reference it by customer_id, and a name change is one update that every join sees. This eliminates update anomalies and makes integrity enforceable: a foreign key guarantees an order cannot reference a customer that does not exist; a unique constraint guarantees one active subscription per user; a check constraint guarantees stock never goes negative. The database, not the application, upholds the rules, which means every code path and every analyst's script gets them for free.

Joins and the planner

A join combines rows from two tables on a condition. The query planner picks how: a nested loop with an index for a small driving set, a hash join for two large sets, a merge join when both are already sorted. It uses statistics about the data to choose, which is why an EXPLAIN plan is the first tool for a slow query and why missing indexes or stale statistics are the usual cause. Joins across properly indexed tables are fast; the reputation that "joins do not scale" comes from joining unindexed columns or from joining across machines, which relational databases were not designed to do.

How far one database goes

Further than most people assume. A single well-provisioned Postgres primary handles tens of thousands of transactions per second and terabytes of data. The scaling sequence is: tune queries and indexes; add read replicas so reads scale horizontally and the primary takes only writes; move heavy analytics to a warehouse or replica; cache hot reads; partition large tables within the database; and only then shard across primaries, which is the step that costs cross-shard joins and transactions. Many companies never reach that last step, and those that do often use a distributed SQL system to keep the model.

What it is not good at

Write throughput beyond one primary without sharding. Schema-less or wildly heterogeneous records. Deep graph traversals (recursive joins exist but get slow). Full-text relevance ranking and similarity search beyond basics. Each of those is a lesson later in this module, and in every case the usual answer is a specialist store fed from the relational source of truth.

Trade-offs

The relational model buys integrity, flexibility of query and a mature ecosystem, and pays with schema ceremony, vertical scaling of writes and the eventual pain of sharding. For most systems the ceremony is a feature: it is the cheapest bug prevention available. The costs arrive only at scales and shapes that most products never meet.

In an interview

Make it the default and defend the scaling path: "Postgres is the source of truth for users, orders and payments: constraints enforce invariants and transactions keep them consistent. Reads scale with replicas and a cache; when a table outgrows one primary we partition it, and if the whole write load does, we shard by tenant." Saying the path in order shows you know relational scaling is incremental, not a cliff.