System Design Handbook · llms.txt
This chapter answers five questions:
This chapter builds on two earlier ones. Relational databases covers ACID transactions, isolation levels, replication, federation, the basics of sharding and SQL tuning. NoSQL and choosing a database covers the NoSQL families, BASE, and how to choose between SQL and NoSQL. Those chapters describe what a database promises. This one looks at the machinery that keeps those promises.
We'll keep using Crumb, the recipe site from the Scalability chapter. Crumb keeps users, recipes, comments, ratings and paid cooking-class bookings in PostgreSQL. It logs every page view in a separate write-heavy store. Its main tables have started to outgrow one server, so it has begun sharding them by user ID. Each section below asks what happens underneath one of those choices.
A storage engine is the part of a database that writes rows to disk and finds them again. The layers above it parse SQL and plan queries. The engine does the actual reading and writing.
Every storage engine is shaped by one fact: sequential I/O is much cheaper than random I/O. Sequential I/O reads or writes blocks that sit next to each other, one after another. Random I/O jumps between blocks scattered across the disk.
On a spinning disk the gap is obvious, because each jump means moving the read head. SSDs have no moving parts, but the gap is still there. A flash drive can't overwrite a page in place. It writes the new data to a fresh page and erases old blocks in the background. So many small random writes make the drive copy data around internally, which slows it down and wears it out. Large sequential writes are the cheapest thing you can ask any drive to do.
Two families of engines respond to this fact in different ways.
Rudolf Bayer and Edward McCreight invented the B-tree while working at Boeing Research Labs. According to Wikipedia, they designed it "to efficiently manage index pages for large random-access files". Their paper first circulated in 1970 and was published in Acta Informatica in 1972. A variant called the B+ tree is what MySQL's InnoDB engine, PostgreSQL's indexes, SQLite and SQL Server use.
Say Crumb's ratings table has grown to a billion rows, and the database needs the row for one rating. Here is how a B+ tree finds it.
WHERE recipe_id BETWEEN 100 AND 300 walks down the tree once to the first matching leaf. Then it follows the links sideways until it passes the end of the range.Now a Crumb user edits a comment, a change of about 100 bytes. InnoDB can't write just those 100 bytes. It does three things:
If a page is full when a new row arrives, the engine splits it into two pages, and the parent page has to change too.
A crash can happen between any of these steps. To survive one, B-tree engines keep a write-ahead log (WAL), an append-only file that records each change before the data pages are touched. Relational databases explains how the WAL makes committed transactions durable. MySQL calls this file the redo log, and PostgreSQL calls it the WAL. After a crash, the engine replays the log to redo any changes that hadn't reached the data pages.
There is one more risk. A power cut can interrupt a 16 KB page write halfway, leaving a torn page that is part old data and part new. A log record of one row change can't repair that. So engines protect the pages themselves:
full_page_writes setting, which is on by default, writes "the entire content of each disk page to WAL during the first modification of that page after a checkpoint" (PostgreSQL documentation).So every change is written at least twice, once to the log and once to the page, and a small change costs a whole page write. Writing more bytes to disk than the application asked to change is called write amplification. When many writes are spread across a large table, the page writes land all over the disk, so they are random I/O. That is where B-tree engines run out of disk throughput first.
In 1996, Patrick O'Neil, Edward Cheng, Dieter Gawlick and Elizabeth O'Neil published The Log-Structured Merge-Tree (LSM-Tree). Its idea is to never update data in place on disk. Writes are collected in memory, written out as new sorted files, and merged with older files in the background.
LSM-trees are used by LevelDB, RocksDB, Cassandra, ScyllaDB, HBase, Google Bigtable, and CockroachDB (through Pebble, its RocksDB-like engine).
Crumb's page-view log shows why. Every page view is a small write, thousands arrive each second, and they are spread across many recipes. In a B-tree, each write would dirty a different page, and the engine would spend its disk time rewriting 16 KB pages to record a few bytes each. An LSM-tree turns the same stream into a few large sequential writes.
PUT key value or DELETE key) is appended to a log on disk, so it survives a crash. Appends are sequential.A key's newest value may be in the MemTable or in any SSTable. So a read checks the newest data first:
Checking many files to find one key is called read amplification. Two things keep it down.
Bloom filters. Each SSTable has a Bloom filter, usually kept in memory. A Bloom filter is a small bit array that answers the question "is this key in this file?" with either "definitely not" or "probably yes". When it says "definitely not", the engine skips the file without reading it. RocksDB's Bloom filter guide gives "9.9 bits per key (1% false positive rate)". So a point lookup usually reads only the one file that holds the key. Bloom filters don't help range scans, which still have to read every file whose key range overlaps the range asked for.
Compaction. Background threads read several SSTables and merge them in key order, like the merge step of merge sort. They keep only the newest version of each key, drop tombstones once no older value could be hiding behind them, and write new SSTables. Fewer files means fewer places to look and less disk spent on old versions.
There are two common compaction strategies:
RocksDB's compaction overview sums up the trade-off. Leveled compaction "minimizes space amplification at the cost of read and write amplification". Tiered compaction "minimizes write amplification at the cost of read and space amplification".
| B-Tree (InnoDB, PostgreSQL) | LSM-tree (RocksDB, Cassandra) | |
|---|---|---|
| Good at | Predictable reads and range scans | High write rates |
| How a write lands | Log append now, page overwrite later | Log append and MemTable insert now; files written sequentially later |
| Write amplification | A whole page per small change, plus the log | Data rewritten by each compaction, but always sequentially |
| Read cost | One path down the tree, often one or two disk reads | MemTable plus several files; Bloom filters skip most of them |
| Space overhead | Partly empty pages after splits and deletes | Old versions until compacted; low with leveled, higher with size-tiered |
| Latency spikes | Checkpoints can cause bursts of page flushes | Heavy compaction can compete with normal reads and writes |
Neither design wins everywhere. Plenty of write-heavy systems run well on PostgreSQL, and RocksDB serves read-heavy workloads too. Benchmark with your own workload before you switch.
Relational databases explains what an index is, which columns to index, and what indexes cost. This section looks at how indexes are laid out inside the engine. The layout explains why two queries that look almost the same can run at very different speeds.
When someone signs in to Crumb, the app runs SELECT * FROM users WHERE email = ?. Here is what that query does in InnoDB.
Clustered index. A clustered index stores the table's rows inside the index itself, in key order.
NOT NULL. If there is none, it adds a hidden row ID column and clusters on that (MySQL documentation).CLUSTER command sorts the heap by an index once, but "Clustering is a one-time operation": rows inserted or updated later are not kept in order (PostgreSQL documentation).Secondary index. A secondary index is an index on other columns, such as CREATE INDEX idx_email ON users(email).
user_id = 42. The second goes down the clustered index to fetch the row for user 42.Covering index. If every column a query needs is already in the index, the database answers from the index alone and skips the second lookup. This is called an index-only scan, and the index is said to cover the query.
SELECT user_id, email FROM users WHERE email = ? is covered by idx_email in InnoDB. The leaf already holds email and the primary key user_id.(email, display_name). In PostgreSQL and SQL Server you can also write INCLUDE (display_name), which stores the column in the leaves without making it part of the search key (PostgreSQL documentation).The trade-off: every index slows down writes and takes space, and wide covering indexes do so more.
A composite index is an index on several columns. Crumb's bookings table records paid cooking-class bookings, and it has this index:
CREATE INDEX idx_bookings ON bookings (user_id, status, created_at);The entries are sorted by user_id first. Entries with the same user_id are sorted by status, and entries with the same status are sorted by created_at. It works like a phone book sorted by last name and then by first name. The index helps when a query constrains a leftmost prefix of its columns, meaning the first column, or the first two, or all three:
-- Uses all three columns
SELECT * FROM bookings WHERE user_id = 10 AND status = 'PAID' AND created_at > '2026-01-01';
-- Uses user_id and status
SELECT * FROM bookings WHERE user_id = 10 AND status = 'PAID';
-- Uses user_id only
SELECT * FROM bookings WHERE user_id = 10;
-- Can't seek with this index, because the first column isn't constrained
SELECT * FROM bookings WHERE status = 'PAID';
SELECT * FROM bookings WHERE created_at > '2026-01-01';The last two are like looking up everyone named "Maria" in a phone book sorted by last name. The sort order doesn't help you. Some engines have workarounds. MySQL's skip scan runs one small range scan for each distinct value of the first column, so it only pays off when that column has few distinct values. Don't design your indexes around it.
Put equality columns first and the range column last. A range condition is one that matches a span of values: <, >, BETWEEN, or LIKE 'abc%'. Once the index reaches a range condition, the columns after it can't narrow the search any further. Take this query:
WHERE user_id = 10 AND status = 'PAID' AND created_at > '2026-01-01'(user_id, created_at, status), the database seeks to user_id = 10 and the start of the date range. Then it scans every booking after that date, paid or not, and checks status on each one.(user_id, status, created_at), it seeks straight to user_id = 10, status = 'PAID' and scans only the matching date range.So put the columns compared with = first, and the range column last.
Sharding splits a table's rows across several databases, and a shard key decides which database, or shard, each row lives on. Relational databases covers why and when to shard, how to choose a shard key, and the main disadvantages, with Notion's move to sharded PostgreSQL as an example. This section goes deeper: the three ways to map keys to shards, and the mistakes that cause the most pain once you have shards.
A router sits between the app and the shards. It can be a library in the app, or a proxy such as Vitess (for MySQL) or Citus (for PostgreSQL). It reads the shard key from each query and sends the query where it needs to go.
A query that includes the shard key goes to one shard. A query without it has to ask every shard.
There are three main ways to decide which shard owns a key.
| Strategy | How a key finds its shard | Strength | Weakness |
|---|---|---|---|
| Range | Each shard owns a continuous span of keys | Range queries on the key touch few shards | Increasing keys all land on the last shard |
| Hash | Hash the key, then map the hash to a shard | Spreads keys evenly | Range queries on the key touch every shard |
| Directory | Look the key up in a mapping table | Full control over placement | The directory must stay available and consistent |
Each shard holds a continuous range of the key. For example, user IDs 1 to 1,000,000 go on shard 1 and 1,000,001 to 2,000,000 on shard 2. Or each shard holds one month of data.
WHERE id BETWEEN 500 AND 600, touches one shard or a few neighboring ones.Hash the shard key, and use the hash to pick a shard. A hash function turns a key into a number that looks random, and the same key always gives the same number.
WHERE user_id BETWEEN 10 AND 100, has to go to every shard.The simplest version is shard = hash(user_id) mod N, where N is the number of shards. Its problem is changing N. Going from 4 shards to 5 changes the shard for about 80% of keys. A key stays put only when hash mod 4 equals hash mod 5, which is true for 4 out of every 20 hash values. So most of the data has to move.
Consistent hashing fixes this. It places shards and keys on a ring. A new shard takes over only the keys next to it on the ring, about 1/N of the total, and virtual nodes keep the load even. Many systems get the same effect in a simpler way. They split the hash space into many more fixed partitions than there are servers, say 1,024, and move whole partitions between servers. Notion's 480 logical shards, described in Relational databases, work this way.
A lookup service maps each key, or each tenant, to a shard: tenant A → shard 3, tenant B → shard 1. The mapping lives in a small, strongly consistent store such as etcd, ZooKeeper or a replicated SQL table, and the routers cache it.
The range case above is the classic hot spot. Hash partitioning has its own version: a key that is hot by nature. Relational databases gives the Crumb example of a celebrity chef with two million followers. If Crumb shards by author_id, all the traffic for her recipes goes to one shard. A better hash function doesn't help, because her ID still hashes to exactly one place. The same happens when you shard by tenant_id and one tenant produces half the traffic.
So before choosing a shard key, look at how traffic is distributed across keys, as well as how many distinct keys there are. If a few keys will be very hot, plan for them. Consistent hashing lists the usual fixes, such as caching hot reads close to the app and splitting one hot key into several.
Crumb shards bookings by user_id. Its support team wants the ten newest pending bookings:
SELECT * FROM bookings WHERE status = 'PENDING' ORDER BY created_at DESC LIMIT 10;The query has no user_id, so the router can't tell which shard holds the answer. It has to scatter the query and gather the results:
The query is only as fast as the slowest shard. Suppose each shard has a 1% chance of a slow response. A query that touches 50 shards hits at least one slow shard 1 − 0.99⁵⁰ ≈ 40% of the time. Every scatter query also uses capacity on every shard, so the total query load grows with the number of shards.
Occasional scatter queries are fine. If a frequent query needs a different key, keep a second copy of the data that is sharded or indexed by that key. Two common forms are a global secondary index, which the database maintains across all shards, and a separate table kept up to date from a stream of changes.
On one server, joining users to bookings is a local operation. If the two tables are sharded on unrelated keys, matching rows sit on different servers. The join then has to ship rows over the network to one place to be matched. A transaction that updates rows on two shards needs a distributed transaction protocol such as two-phase commit, which is slower and harder to operate.
The fix is to co-locate data that is used together, by sharding it on the same key. Crumb shards bookings, payments and class_reviews all by user_id. One customer's bookings and payments then live on one shard, and joins and transactions on them stay local.
Spanner supports co-location directly with interleaved tables. Its documentation says it "physically colocates child rows with parent rows in storage", and that it "can perform joins by primary key locally, minimizing storage access and network traffic."
Resharding means changing how data is split, for example splitting one busy shard into two. You have to copy data while it is still being written, then switch traffic to the new shards without losing any writes. Doing this by hand, under pressure, is risky.
Plan for it from the start. Choose a scheme that moves whole partitions instead of individual keys, or use a system that automates the move. Vitess, for example, copies and verifies data on the new shards while the old shards keep serving traffic. Its documentation says the final switch happens "with only a few seconds of read-only downtime." The distributed SQL databases in the next section split and move data on their own.
NoSQL and choosing a database describes the old choice. A single-node relational database gives you SQL, joins and ACID transactions, but scaling writes past one machine means sharding by hand, with all the problems above. A NoSQL store scales out on its own, but traditionally gave up multi-row transactions and joins.
Distributed SQL databases, such as Google Spanner, CockroachDB, YugabyteDB and TiDB, aim for both. They offer SQL and serializable transactions across many machines, and the database itself handles sharding and replication.
They don't escape the CAP theorem. They choose consistency. If a network partition cuts a piece of data off from a majority of its replicas, that data becomes unavailable. And every write pays for a round of agreement between replicas, which is slow when the replicas are in different regions.
These systems store every table and every index as entries in one large sorted key space. Each key is built from the table ID, the index ID and the row's primary key. So one table's rows sit next to each other, in primary key order.
range_max_bytes, which is 512 MiB by default (CockroachDB documentation).A transaction that touches several ranges uses a commit protocol layered on top of consensus. It is a form of two-phase commit in which every participant is itself a replicated group. So a single machine failure doesn't leave the transaction stuck.
A database spread across many datacenters still needs to give each transaction a timestamp. Ideally those timestamps match real-world order: if transaction B starts after transaction A has committed, B should get a later timestamp.
Ordinary clocks make this hard. Servers keep their clocks in step with NTP, the Network Time Protocol. According to Wikipedia, NTP "can usually maintain time to within tens of milliseconds over the public Internet". It adds: "Asymmetric routes and network congestion can cause errors of 100 ms or more." Worse, a machine can't tell how wrong its own clock is. Suppose server A's clock runs 10 ms ahead of server B's. A transaction on B that starts after one on A has committed can still get an earlier timestamp. A reader could then see the second change without the first.
Google's Spanner handles this with an API called TrueTime, described in its 2012 paper.
TT.now() doesn't return a single time. It returns an interval, [earliest, latest], that is guaranteed to contain the true time. Half the width of that interval is the clock uncertainty, written ε. The paper reports that in production, ε "is typically a sawtooth function of time, varying from about 1 to 7 ms".Spanner uses that interval for commit wait:
s no earlier than TT.now().latest. So s is not before the true time.TT.now().earliest > s. At that point, s is certainly in the past on every clock.s = TT.now().latest
wait until TT.now().earliest > s (at least 2ε)
then release locks and acknowledge the commitBecause of the wait, any transaction that starts after this one commits, on any server anywhere, sees a time later than s and gets a larger timestamp. So timestamps match real-time order. This property is called external consistency, also known as strict serializability.
The paper says the expected wait is at least 2ε, which is a few milliseconds. It also says the wait "is typically overlapped with Paxos communication". Spanner has to wait for replication anyway, so commit wait often adds little extra latency. A smaller ε means a shorter wait, which is why Google invests in the clock hardware.
Spanner uses locks (two-phase locking) for read-write transactions. The timestamps also let read-only transactions read a consistent snapshot at any timestamp without taking locks. CAP and consistency covers what Spanner's design means for availability.
CockroachDB runs on ordinary hardware without TrueTime. It uses hybrid logical clocks, which combine the machine's physical time with a logical counter that breaks ties. It also assumes every node's clock is within a configured maximum offset of the others. The default is 500 ms, and the documentation says that "if observed clock offsets exceed this limit, servers will crash to minimize the likelihood of reading inconsistent data."
That offset creates an uncertainty window around each read. Sometimes a read finds a value whose timestamp is slightly later than its own, but within the window. It can't tell whether that write really happened before it or after it. So, as the transaction layer documentation describes, CockroachDB pushes the reading transaction's timestamp past the uncertain value. If the transaction's earlier reads are still valid at the new timestamp, it carries on. If not, it retries at the new timestamp.
The result is serializable transactions without commit wait. The cost is occasional retries, and a weaker real-time guarantee than Spanner's.
A B-tree updates pages in place. Each small write means a WAL append and, later, writing back the whole 8 or 16 KB page that holds the row, at that page's position on disk. With writes spread over a large table, those page writes are random, and each one carries mostly unchanged bytes.
An LSM-tree turns every write into sequential I/O. The write is appended to the WAL and inserted into the in-memory MemTable, and then it is acknowledged. When the MemTable fills, it is written out as an SSTable in one sequential pass. Merging old versions is left to background compaction, which also writes sequentially. The cost moves to reads, which may have to check several files. Bloom filters let most of those checks happen in memory.
Updates and deletes are written as new entries, so old versions and tombstones accumulate in older SSTables until compaction merges them away. More files means more places to check for each key (read amplification) and more disk spent on dead data (space amplification). If compaction falls behind the write rate, both grow.
Size-tiered compaction merges files of similar size. It rewrites data fewer times, so it suits heavy writes, but one key's versions can be spread across many files, and a large merge needs free disk for the old and new files at once. Leveled compaction keeps each level's files non-overlapping, so a lookup reads at most one file per level and space use stays low. The price is more rewriting, because data is merged again each time it moves down a level.
InnoDB stores rows inside the clustered primary key index. A secondary index on email stores only email and the primary key user_id. For SELECT *, the engine searches the email index to find user_id, then searches the clustered index to fetch the row. That is two tree searches, and the second may need a random disk read. For SELECT user_id, email, both columns are already in the email index's leaf. The query is answered from the index alone, as an index-only scan, and the second search never happens. Adding more columns to the index, or using INCLUDE in PostgreSQL, can make other queries covered too, at the cost of slower writes and more space.
The range condition on created_at comes before status in the index. The engine can seek to the user_id and the start of the date range. But then it has to scan every entry in that range and check status on each one, including all the bookings that aren't paid. An index on (user_id, status, created_at) puts both equality columns first. The engine then seeks directly to user_id = ?, status = 'PAID' and scans only the date range it needs.
Each new row has a larger key than every earlier row, so it always falls in the last range. All inserts go to one shard, which runs out of CPU, I/O or connections, while the shards holding older ranges get no writes. Adding shards doesn't help, because new writes still go only to the newest range. Hash partitioning on the key, or partitioning on a different key such as user_id, spreads inserts over all shards. The cost is that range queries on the hashed key must go to every shard.
The query has no user_id, so the router must send it to all 50 shards, collect each shard's top results, and merge them. It finishes only when the slowest shard answers. If each shard is slow 1% of the time, at least one of 50 is slow about 40% of the time (1 − 0.99⁵⁰). It also adds load to every shard. Since this query runs constantly, keep a second copy of the data organized by what it needs: a global secondary index on (status, created_at), or a small table of pending bookings fed from a stream of changes. Then the dashboard reads one place instead of fifty.
It guarantees external consistency. If transaction T2 starts after T1 has committed in real time, T2 gets a larger timestamp, even if the two run on servers on different continents. The coordinator picks T1's timestamp s at or above TT.now().latest. Then it waits until TT.now().earliest > s before releasing locks and acknowledging. After that, every clock in the system is past s, so any later transaction's timestamp is larger. The wait only works because TrueTime's interval is guaranteed to contain the true time. Ordinary NTP clocks have no such bound, so a system without one can't know how long to wait. The wait is at least twice the uncertainty, a few milliseconds, which is why keeping ε small matters. CockroachDB, which lacks TrueTime, instead assumes a maximum clock offset and retries reads that fall inside the uncertainty window.
Previous: NoSQL and choosing a database · Next: Caching