Loading...
Loading...
Tables, schemas and ACID on a single primary — when B+ trees and strong guarantees beat distributed stores
The last module ended with clients asking politely: If-None-Match with an ETag, which is a version stamp the server compares to answer 304 without resending bytes, plus If-Match for conflict-free writes. That politeness saves bandwidth at the edge, but it assumes the origin itself knows the truth cheaply. Picture the food ordering app behind that polite API tracking users, orders, and menus, where orders point at users and menus point at restaurants. Scattered across loose files, every polite request becomes a scavenger hunt: which file holds one customer's orders, what happens when two writers edit the same file at once, and how do you answer all orders over $50 from last Tuesday without reading everything. Files have no shared language for links between things, so no validator can cover them.
Throwing more files or bigger servers at the mess fails because the problem is structure, not capacity. Without declared relationships the application reconstructs every link by hand, so each new question needs a new script, and concurrent writers overwrite each other because no central authority sequences their edits.
A relational database, which is a store that keeps data in tables of rows and columns linked by keys and questioned in a language called SQL, answers all three with one idea. Think of a restaurant where every order ticket names its table number so the kitchen always knows which table an order belongs to: one analogy for the whole idea, where tables are the dining room layout, keys are the ticket numbers linking orders to tables, and SQL is asking the kitchen a precise question across those links. Most business data turns out to be relationships of exactly this kind.
One sentence version: tables hold each kind of thing, keys record which rows relate, and SQL asks hard questions across those links without custom scripts.
A primary key, which is the column that uniquely identifies each row, gives every user a stable number. A foreign key, which is a column in one table holding a primary key from another, records that an order belongs to a user without copying the user's name. The database enforces the link, so an order cannot point at a nonexistent user once the constraint exists.
| id, the primary key | name | |
|---|---|---|
| 1 | Alice | alice@example.com |
| 2 | Bob | bob@example.com |
| id, the primary key | user_id, the foreign key | total |
|---|---|---|
| 101 | 1 | $50 |
| 102 | 1 | $75 |
Order 101 carries user_id 1 instead of a copied name, so Alice's total spending is a join, which is a query that matches the foreign key to the primary key, rather than a manual search through files. Change her email once and every future join sees the new value.
Move $500 between accounts and the power dies halfway. Did the money leave, arrive, both, or neither. A store that shrugs here cannot hold money, so relational databases promise four properties remembered as ACID, where each letter names one guarantee and the four together are the product being bought.
The debit and the credit are wrapped in a transaction, which is a group of writes that succeed or fail together. Half a transfer cannot be observed because the database shows either both sides or neither.
Constraints, which are declared rules such as balances never going negative or emails staying unique, are checked on every commit. Each transaction moves the database from one valid state to another valid state.
Two transfers in the same millisecond each run as if alone. The engine sequences their reads and writes, which is the subject of the isolation module, so interleavings that would corrupt balances are forbidden.
Once the database acknowledges a commit, the change sits in its write-ahead log, which is an append-only record on durable disk replayed after crashes, so a power cut cannot unsay it.
The engine choice matters less than the shared model, but each option earns its place. PostgreSQL, which is the open-source relational database most teams default to for its correctness and extensions, is the starting point unless a constraint says otherwise. MySQL, which is the widespread open-source engine behind a large share of web hosting, suits simple high-volume reads. Oracle and SQL Server, which are the commercial engines entrenched in large enterprises and in shops standardized on a major vendor's platform, are chosen where the organization already lives there. SQLite, which is a complete database stored in a single file with no server, fits phones, tests, and prototypes.
The default answer for new systems needing correctness and flexible queries.
Already everywhere in web hosting, strong at simple high-volume reads.
Chosen where enterprise contracts or platform standards already dictate them.
A whole database in one file for phones, tests, and prototypes.
The mechanics of the trade are physical. One machine holds the whole truth, which makes joins and constraints cheap because everything is local, but horizontal growth, which is spreading work across machines, hurts because joins and atomic commits stop crossing machine boundaries. Schema changes, which are alterations to table structure, need locks on huge tables, and giant joins across billions of rows degrade into long scans. Shapeless data such as raw logs fights the grid because every row must fit declared columns.
Walk the sizes with concrete arithmetic. At 100 million order rows of roughly 200 bytes each, the heap, which is the storage holding the actual row data, needs 100,000,000 times 200 bytes, which is 20,000,000,000 bytes or about 20 GB, plus another 10 to 15 GB of indexes, which are sorted shortcuts the engine maintains. That total sits comfortably on a server with 64 GB of memory, because the frequently read working set stays in the buffer pool, which is the memory cache for hot pages, and indexed point lookups answer in single-digit milliseconds. Push to a billion rows and the heap alone passes 200 GB, the working set no longer fits, uncached reads become disk operations, and maintenance such as vacuuming, which is reclaiming space from dead row versions, stretches to hours while adding a column with a default rewrites every page.
| Scale point | Back-of-envelope math | What you feel in production |
|---|---|---|
| 10M rows | Roughly 2 to 5 GB with an index about 3 levels deep | Everything fits in RAM, with single-digit millisecond indexed queries |
| 100M rows | Roughly 20 to 40 GB with indexes | Needs a 32 to 64 GB box, while full scans already take minutes |
| 1B+ rows | Over 200 GB, with backups taking hours and restores longer | Vertical scaling turns expensive, so partition, archive, or shard |
The mental math to carry: database pages hold 8 KB each and each tree node fans out to hundreds of entries, so index depth stays at 3 to 4 levels even into the billions of rows. Point lookups therefore scale logarithmically and stay fast, while scans, joins, and maintenance scale linearly with size and hurt first.
The first outage is the migration that locks the world. A Friday deploy adds a required column to a 200-million-row orders table, the schema change takes an exclusive lock, every checkout blocks behind it, and a deploy expected to take seconds holds the table for twenty minutes. The fix is expand then contract: add the column as nullable first, backfill in small batches, then add the constraint under a short lock. The rejected shortcut is skipping the database and serializing each order as a JSON file on disk, which looks fast for 10,000 rows but collapses before a million: a lookup by email scans every file at roughly 900 milliseconds per 8 million rows instead of a 1-millisecond index descent, and two concurrent writers overwrite each other with no transaction to sequence them. The second outage is the connection stampede. Each application server opens 100 connections and the fleet scales to 40 servers, so the database juggles 4,000 backends that mostly idle until one slow query makes them queue, memory balloons, and new connections are refused. The fix is a pooler, which is a proxy that multiplexes many logical connections onto a few hundred real backends, run in transaction mode, plus statement timeouts so one bad query cannot hold the pool hostage.
Tables hold truth beautifully on one machine, with correctness the engine guarantees rather than the application hopes for. Yet the 100-million-row math above hides an unanswered physical question: the heap fit in 20 GB and the working set stayed in the buffer pool, but how are those bytes actually laid out so a point lookup costs three page hops instead of a full scan, and why does that layout make writes stall while background merging catches up. The answer sits one layer down, in the sorted pages and buffered merges that decide latency before server size does.