Plain files lose data in two ways, and a database exists to stop both
A database owns the files and makes two promises: committed work survives a crash, and concurrent work does not collide.
A program’s memory vanishes when it exits, so anything worth keeping goes to disk. Writing it there yourself fails in two ways. First, a crash halfway. A transfer takes fifty from account A and is about to add it to account B when the power fails. Fifty has left A and arrived nowhere. Second, two writers at once. Two deposits of ten each read the balance, one hundred. Each adds ten and writes one hundred and ten. One deposit has silently disappeared. A database is a separate program that owns the files and makes two promises. Durability: once it says committed, the change survives a crash. And isolation: transactions running at the same time never see each other’s half-finished work.
Why databases exist · Going deeper · 1 of 2
The database stack
Bytes on a page, indexes that find rows, transactions that hold invariants, replication that survives losing a machine, a planner that picks a plan, and pipelines that move it all to where analytics needs it. What differs between products is the access method and the language on top.
Why databases exist · Going deeper · 2 of 2
Doing it by hand versus a database
Without a database every application would write its own log, call fsync correctly on every platform, replay partial records after a crash and manage its own locks. Skipping the flush looks fast in benchmarks and corrupts data at the next power cut. Group commit batches many transactions into one flush.
SQL and the query planner
You state the question in SQL; a planner chooses how to answer it
The relational model separates what the data is from how it is stored, so a query planner can pick the cheapest route.
Before relational databases, a program fetched records by following pointers, so how you asked was welded to how the data was stored. The relational model split those apart. You write the question: order ids and customer names, for orders over a hundred. Nothing in it says how. The database, meanwhile, keeps statistics: ten million orders, two hundred thousand customers. One way to answer is to look up a customer for every single order. Estimated cost: ninety eight million. Another is to load the small customers table into memory and pass over the orders once. About one million, nearly a hundred times cheaper, so the planner picks it. A third sorts both tables first. All three give the same answer.
SQL and the query planner · Going deeper · 1 of 2
From SQL text to a physical plan
A table has a primary key identifying each row; a foreign key in another table points at it. The query is parsed, checked, turned into a logical plan and then a physical one. EXPLAIN shows the choice; EXPLAIN ANALYZE runs it and shows the real row counts beside the estimates.
SQL and the query planner · Going deeper · 2 of 2
Three plans, scored
The cost model counts rows and I/O per operator. Stale statistics are how a query silently regresses overnight. A separate trap: NULL is not equal to itself. WHERE x = NULL matches nothing; write IS NULL. And NOT IN with a NULL in the list rejects every row.
Write-ahead log
Write the intention to a log first, and a crash can always be repaired
The write-ahead log makes a transaction all-or-nothing and durable: logged before applied, replayed after a crash.
A transaction is a group of changes that must all happen, or none. One idea delivers that. Before the database touches its real data files, it appends a description of the change to a log, and forces that log onto the disk. Only then does it tell the client: committed. The data files themselves are updated later, in the background. Now the power fails. Look at what is in the log. Transaction one: three updates and a commit. Transaction two: two updates and a commit. Transaction three: one update, and no commit. On restart the database replays every record, so the data files match the log. Then it reverses anything without a commit. Transaction three vanishes, which breaks no promise, because its client was never told it had succeeded.
Write-ahead log · Going deeper
ACID and the log
Atomicity: all of a transaction or none. Consistency: declared rules hold before and after. Isolation: concurrent transactions appear to run alone. Durability: committed work survives. The log gives the first and last. PostgreSQL calls it the WAL, MySQL the redo log; the recovery algorithm, ARIES, is the same.
Check yourself
The power fails with this log on disk. After restart, which changes are in the database?
T1 has a COMMIT record, so it is replayed and kept. T2 has none, so its updates are undone.
Isolation anomalies
Two transactions can each be correct and still break a rule together
Isolation levels decide what concurrent transactions may see of each other. Only the strongest prevents every anomaly.
Isolation sounds like a yes or no property. It is actually a dial, and most databases do not ship with it turned all the way up. Here is what slips through. A hospital rule says at least one doctor must be on call. Two are. Alice wants to leave. Her transaction checks: two on call, so it is fine. It takes her off. At the same moment Bob’s transaction checks. It also sees two on call, and takes him off. Both commit. Nobody is on call. Neither transaction did anything wrong by itself, and since they changed different rows the database saw no conflict. The levels build on each other. Only the last, serializable, guarantees a result that could have come from running them one at a time.
Isolation anomalies · Going deeper · 1 of 2
Levels and the anomalies each allows
Dirty read: seeing uncommitted data. Non-repeatable read: a row changes between your two reads. Phantom: a range query returns different rows the second time. Write skew: the doctors. PostgreSQL’s “repeatable read” is really snapshot isolation. Code that assumes serializable without setting it is where these bugs hide.
Isolation anomalies · Going deeper · 2 of 2
Write skew in detail
Each transaction reads a consistent snapshot and writes a different row, so no write conflict is detected. Fixes: lock the rows you read with SELECT … FOR UPDATE, add a constraint that checks the final state, or run at SERIALIZABLE and retry when the database aborts one of the pair.
MVCC
Keep old versions of a row, and readers never have to wait for writers
MVCC: an update adds a new version instead of overwriting, and every transaction reads a frozen snapshot.
How can one transaction read a row while another is changing it, without either waiting? By keeping more than one version. Transaction A begins, then B. A changes a balance from one hundred to one hundred and fifty. The database does not overwrite the row. It writes a second version beside the first. B reads the balance. A has not committed, so B is shown version one: one hundred. A commits. B reads again, and still sees one hundred, because B’s view was frozen at the moment it began. That is what a stable snapshot means. Transaction C begins after the commit. It is shown version two: one hundred and fifty. Nobody waited for anybody.
MVCC · Going deeper
Versions, visibility and vacuum
Each row version records which transaction created it and which replaced it; a reader checks those against its snapshot. The alternative, two-phase locking, makes readers and writers block each other and must detect deadlocks. Old versions are removed by a background process (VACUUM in PostgreSQL), which a long-open transaction holds back.
Check yourself
A rule says at least one doctor must be on call. Two are, and both sign off at the same instant, each after checking the rule. Under the default isolation level, what happens?
Each saw two doctors on call and changed a different row, so no conflict was detected. Only serializable isolation, a lock or a constraint prevents it.
B-tree indexes
An index finds one row in a billion by reading five pages
A B-tree index is a shallow sorted tree of disk pages: lookups take a handful of steps instead of a full scan.
Without an index, finding one row means reading every row. For a billion rows that is hopeless. An index is a sorted tree built for disks. Follow a search for id five hundred and twelve. Start at the root, a single page holding a few hundred keys. It says which branch covers this id. That branch is another page, and it narrows the range again. These upper levels are small enough to stay in memory. The last step is a leaf page, usually one read from disk, holding the address of the row itself. Because every page holds hundreds of keys, each level multiplies the reach by hundreds. A billion rows are only four or five levels deep.
B-tree indexes · Going deeper
Inside a B-tree
Pages are 8 KB in PostgreSQL and 16 KB in MySQL. Every leaf sits at the same depth. When a leaf overflows it splits in two and its middle key moves up to the parent; only when the root splits does the tree grow taller. Partial indexes and covering indexes are often worth more than indexing a low-variety column.
LSM trees
For heavy writes, never edit a file: append, then merge later
An LSM tree trades read speed for write speed. Which index a database uses tells you the workload it was built for.
A B-tree edits pages in place, which means scattered writes. When a system must absorb enormous numbers of writes, there is another design. Every write goes into a small sorted table in memory. Nothing could be faster. When that table fills, it is written to disk in one pass as a sorted file. The file is never edited again. In the background, files are merged together into larger sorted levels, each about ten times the size of the last, dropping outdated values along the way. The cost shows up on reads. A lookup may need to check memory and then several levels. A small filter on each file, which can say “definitely not here”, lets most of them be skipped.
LSM trees · Going deeper · 1 of 2
Memtable, levels and compaction
RocksDB, Cassandra, ScyllaDB and HBase all run variants. Bloom filters cut read work by 10 – 100×. Some databases, such as CockroachDB and TiDB, put a SQL layer on top of an LSM key-value store.
LSM trees · Going deeper · 2 of 2
One SELECT, end to end
The query is parsed and planned; the executor walks the chosen index to get row addresses; the buffer pool, the database’s in-memory cache of pages, supplies those pages; and a visibility check drops row versions the transaction’s snapshot should not see.
Database families
Different shapes of question get different shapes of database
Document, graph, time-series and vector stores each specialise in one access pattern; relational stays the general tool.
The relational database is the general tool. For one particular kind of question, a specialised design can be faster or simpler. Relational: tables of rows. Any question, joins across tables, and full transactions. Document: each record is a whole nested object, and objects need not share a shape. Good for application data that varies. Graph: things and the connections between them, stored so that hopping from neighbour to neighbour is cheap. Good when the relationships are the data, as in fraud rings. Time series: measurements stamped with a time, always appended, queried by range and summarised. And vector: each item is a point in space, and the question is, what is nearest to this? That is how search by meaning works.
Database families · Going deeper
Five families
Time-series stores compress to under 2 bytes per sample. Vector stores index points of 768 or 1,536 dimensions with HNSW, a layered graph giving fast approximate nearest neighbours at over 95% recall. Many of these stores choose availability over strong consistency: fine for a shopping cart, hostile for a bank balance.
Row and column storage
Store a table by row for transactions, by column for analysis
OLTP touches one row at a time; OLAP scans one column of millions. The storage layout decides which is fast.
The same table can be laid out on disk in two ways, and the choice decides what it is good at. Take one question: what is the average age? A row store keeps each row together: id, name, age, city, then the next row. That is ideal for fetching or saving one whole record. But to average the ages, it must read past every name and city as well. A column store keeps each column together. All the ages sit side by side, so the question reads only those. In a table with fifty columns, that is two percent of the data, and values of one kind compress far better. These suit two opposite jobs: many tiny transactions, or a few enormous analytical questions.
Row and column storage · Going deeper · 1 of 2
Row-major versus column-major
Parquet, ORC and the formats inside Snowflake, BigQuery and Redshift are columnar, adding dictionary and run-length encoding and per-page minimum and maximum values, which let a scan skip pages that cannot match. A single-row insert or update is expensive in a column store.
Row and column storage · Going deeper · 2 of 2
Two workloads, opposite needs
Transactional: thousands of operations a second, each touching a few rows, in single-digit milliseconds, on gigabytes to low terabytes. Analytical: a few queries at once, each over millions to billions of rows, in seconds to minutes, on terabytes to petabytes.
Check yourself
A table has 50 columns and a billion rows. A query averages one column. Roughly how much of the stored data must a column store read?
Each column is stored separately, so one column of fifty is about a fiftieth of the data. A row store would read all of it.
Data pipelines
Pipelines carry each change from where data is born to where it is used
Data pipelines move changes from transactional databases into warehouses, in batches or as a continuous stream.
The database that runs the business is the wrong place to analyse the business. So data has to travel. It is born when an app writes to a transactional database. A connector reads that database’s own log and turns every insert, update and delete into a stream of change events. The database does no extra work. Those changes land in a warehouse, stored by column with full history, and from there feed reports and machine learning. There are two rhythms for moving and processing it. Batch works on a whole day, or hour, at once. It is exact and easy to reason about, but the answer is as old as the window. Streaming handles each record as it arrives, so answers are under a second old. But the input never ends, and late records have to be dealt with.
Data pipelines · Going deeper · 1 of 3
Batch and stream
Streaming adds windows, tumbling, sliding or per session, over which to aggregate, and rules for events that arrive after their window closed. Most platforms run both: a stream for freshness and a batch for the canonical, reconciled figures.
Data pipelines · Going deeper · 2 of 3
Change data capture
Debezium and similar tools tail the write-ahead log and emit each row change in commit order. The transactional database becomes the source of truth for an event-driven system without any change to application code.
Data pipelines · Going deeper · 3 of 3
Warehouse versus lakehouse
A lakehouse keeps Parquet files on object storage and adds a table format, Iceberg, Delta Lake or Hudi, that brings atomic commits, schema changes and time travel to plain files. ELT loads raw data first and transforms it inside the warehouse with SQL, which tools like dbt organise and test.
Summary
A database is a stack, and every layer rests on one promise
From pages and the log up to pipelines, each layer adds one capability. Next: operations, keeping all of this running.
Put the lesson in order, from the bottom up. At the base, pages on disk and the log: the part that survives a crash. On that, indexes, which find one row among a billion in a few steps. On that, transactions: changes that are all or nothing, and isolated from each other. Then the planner, which lets you say what you want and works out how. Then copies and shards, from the distributed systems lesson, to survive losing a machine and to outgrow one. And on top, pipelines, carrying data to where questions are asked. SQL itself is an international standard, which is why the same query language works across so many products.
Summary · Going deeper · 1 of 2
Replication topologies
An asynchronous primary with replicas is the default for read-heavy web apps. Consensus-replicated storage is the default where a committed write must never be lost. Multi-primary suits write-anywhere cases where the application can resolve conflicts.
Summary · Going deeper · 2 of 2
Partitioning one table
Range partitioning suits time series and history; hash spreads point lookups evenly; list suits tenants and data-residency rules. Real systems combine them, for example by day and then by a hash of the user id.
Lesson complete
You finished “Data”
Answer the questions along the way to see your score here.