Databases: Why Your Data Survives a Power Cut

You move 30 euros from savings to your current account. Halfway through, the bank's server loses power. When it comes back, your savings show 30 euros less, and your current account shows nothing extra. Money has vanished. You would, rightly, never use that bank again, and yet the bank's systems are running on ordinary computers that crash all the time.

The reason you have never seen this happen is a field of computer science that most people never hear about. A database is the layer that keeps a company's facts: who owns what, who booked which seat, what you ordered. It has to answer questions about millions of records in a fraction of a second, let thousands of people change the same records at once, and survive crashes without losing a cent.

This piece gives you the handful of ideas a graduate of a databases course still sees everywhere: why data lives in tables, why a lookup in a billion rows takes about four steps, what "transaction" really promises, and what you give up when you leave the traditional model behind.

Tables, keys and joins

Why not just keep files? A spreadsheet or a text file works for one person and a few thousand rows. It stops working when two people edit at once, when the file is too big to scan, when a crash leaves it half-written, and when the same fact (a customer's address) is copied into twenty places and the copies drift apart. A database management system exists to fix those four problems.

In 1970, Edgar F. Codd, a researcher at IBM, proposed the relational model in the journal Communications of the ACM: keep facts in plain tables and let programs ask questions without knowing how the data is laid out on disk. A table is a set of rows. Each row has a primary key, a column whose value is unique and names the row (a customer number). A different table refers to a row by storing its key as a foreign key (each order stores the customer number it belongs to).

The join is the move that makes the model powerful, and it is simple. Imagine pairing every customer with every order (that's the "cross product"), then throwing away every pair where the keys do not match. What's left is the join.

SQL: say what you want, not how to get it

Before relational databases, programmers wrote loops that walked through records one by one, following pointers. If the storage changed, the program broke. Codd's model came with a different idea: the programmer describes the result, and the system works out the method. The language that grew from this, SQL (first called SEQUEL, developed at IBM in the 1970s by Donald Chamberlin and Raymond Boyce), became an ANSI standard in 1986 and an ISO standard in 1987, and it is still how the world asks databases questions.

SELECT SUM(total) FROM orders
WHERE city = 'Lisbon' AND total >= 300;

That sentence says nothing about loops, order, or files. The engine has a query planner that picks a plan: scan every row, or jump straight to the Lisbon rows using an index, whichever it estimates to be cheaper. The answer is the same either way; only the cost differs. This is called declarative programming, and it is the reason a query written in 1995 can run faster today without a single change.

Indexes: why a billion rows takes four steps

An index is the back-of-the-book index of a table: a sorted structure that says "Lisbon: rows here". Without one, the only plan is to read everything. With one, you navigate. The standard structure is the B-tree, invented in the early 1970s by Rudolf Bayer and Edward McCreight. Each node holds hundreds of sorted keys, so each step discards all but one of hundreds of branches, and the tree stays only a few levels deep.

This is the same idea as the binary search you know from Data Structures (34/23), widened. Binary search halves the range each step. A B-tree divides it by the fanout f, the number of branches per node, because disks and SSDs read data in pages, and one page may as well hold hundreds of keys.

Worked number: with N = 1,000,000,000 rows and a fanout of 200, 200⁴ = 1.6 × 10⁹ is enough, so a lookup reads about four pages. A full scan of the same table would read, on average, half a billion rows if you were searching for one record.

The price: every index must be updated on each write, and it takes space. Indexing everything makes reads fast and writes slow. Choosing what to index is half of a database engineer's craft.

Transactions: all or nothing, and not tripping over each other

A transaction is a group of steps the database treats as one. In 1983, Theo Härder and Andreas Reuter gave the group of guarantees a name that stuck: ACID, building on earlier work by Jim Gray.

• Atomicity: all the steps happen or none do.
• Consistency: rules you declared (a balance can't go below zero) are never broken by a committed transaction.
• Isolation: transactions running together behave as if they had run one after another.
• Durability: once the database says "committed", the change survives a crash, because it was first written to a log on disk.

Isolation is the subtle one. Suppose Alice deposits 10 and Bob deposits 20 into an account holding 100, at the same moment. Each reads the balance, adds, and writes it back. If their steps interleave, one deposit is lost: the classic lost update. Databases prevent it with locks (or with versioned copies of the data), which makes the second transaction wait or retry. Stronger isolation means safer answers and less speed. The SQL standard defines several levels, and databases such as PostgreSQL document which anomalies each level allows, so that you can choose a cheaper level when you know the risk is acceptable.

Normalisation: store each fact once

The last relational habit a graduate keeps is normalisation: design the tables so that each fact is stored in exactly one place. If Ana's city is copied onto each of her orders, then when she moves, you must find and change every copy. Miss one and the database now holds two contradictory "truths". Splitting the data into customers and orders, joined by key, means her city is changed in one row.

Normalisation is a trade, not a law. Joins cost time; some systems deliberately copy data (denormalise) to make reads fast, and then must work harder to keep the copies in step.

NoSQL, and what CAP really says

In the 2000s, companies such as Google and Amazon needed to spread data over thousands of machines. Splitting a relational database with strong guarantees across so many machines was hard, and a family of systems nicknamed NoSQL arose: key-value stores, document stores, wide-column stores, graph databases. They usually relax some of the relational model (joins, rigid schemas, sometimes strict isolation) in exchange for scale, flexible data shapes or speed. They are tools with trade-offs, not a replacement; many mature systems now offer both styles.

The most quoted trade-off is the CAP theorem. Eric Brewer conjectured it in a 2000 talk and Seth Gilbert and Nancy Lynch proved a formal version in 2002. Stated in plain words: when the network splits a system into parts that cannot talk to each other (a partition), each part must choose between answering requests and giving answers that agree with the other part. You can't have both during the split. The real choice is therefore not "pick two of three": networks do fail, so partition tolerance isn't optional for a distributed system; the choice is what to give up while one is happening.

Two cautions from the literature. Daniel Abadi pointed out in 2012 that CAP only describes behaviour during a partition, while the everyday trade-off is between consistency and latency (waiting for other machines to confirm); he called the fuller picture PACELC. And Martin Kleppmann argued in 2015 that CAP's definitions are narrow and often misapplied, so labelling a whole product "CP" or "AP" is usually too crude.

What a graduate just sees

• A table is a set of facts, and a join is "pair rows whose keys match", nothing more.
• A query says what, and the planner decides how. Same answer, very different cost.
• An index turns "read everything" into "walk a few levels"; each extra level multiplies capacity by the fanout.
• Any multi-step change that must not be half-done belongs inside a transaction.
• Two people reading, thinking and writing the same value will lose an update unless something prevents it.
• A fact stored twice will one day disagree with itself.
• When a network splits, you choose between refusing and possibly being wrong; there is no free third option.
• Never build SQL by gluing user text into a string; that is how injection attacks happen.

Where it shows up next

• Operating Systems (34/26): the log that makes commits durable and the locks that give isolation are the same ideas an operating system uses for files and processes.
• Computer Networks (34/27): CAP is a statement about unreliable networks; replication is a networking problem.
• Computer Security (34/33): SQL injection is a database bug in the application, and one of the most famous web attacks.
• Machine Learning (34/32): training data lives in tables, and getting it out correctly is most of the real work.
• In the world: every payment, booking, inventory and login system, and most "apps", are a user interface on top of a database.

If you want to go deeper

• 📘 Database System Concepts, 7th edition (2019), Avi Silberschatz, Henry Korth and S. Sudarshan: the standard university text, best for the full tour from relational algebra to recovery. Its companion site db-book.com has slides and an in-browser SQL lab.
• 📘 Designing Data-Intensive Applications, Martin Kleppmann; a second edition (with Chris Riccomini) appeared in 2026. Best for how real systems handle replication, partitions and consistency.
• ▶️ Gentler: the lectures of CMU 15-445, Intro to Database Systems (Andy Pavlo), are free on YouTube and clear about how engines really work.
• 🌐 Free course: CMU 15-445/645 (slides, notes, projects); or MIT OpenCourseWare 6.830 Database Systems.