A query that reads one row takes half a millisecond. The same query, when the row is already in memory, takes a microsecond. Nothing about the query changed — only where the bytes were.
That gap is not a detail. It is the reason caches exist, the reason databases are built the way they are, and the reason "just add an index" sometimes works and sometimes does nothing. Almost every storage decision in the rest of this course is an attempt to keep data in a faster place, or to make the trip to a slower place happen less often.
Four places, each about a hundred times slower than the last
Data can live in several places, and the differences between them are enormous.
| Where | Rough read time | Survives a restart? | Rough capacity |
|---|---|---|---|
| CPU cache | ~1 ns | No | Megabytes |
| Main memory (RAM) | ~100 ns | No | Gigabytes to low terabytes |
| Solid-state disk (SSD) | ~100 µs | Yes | Terabytes |
| Spinning disk (HDD) | ~10 ms | Yes | Terabytes, cheaply |
| Object storage over a network | ~50–200 ms | Yes | Effectively unlimited |
Read that column of numbers again, because the ratios matter more than the values. Memory is roughly a thousand times faster than an SSD, which is roughly a hundred times faster than a spinning disk.
Those factors are hard to feel as words. Plotted, they are unmistakable — note the axis, which climbs by a factor of ten per gridline. On an ordinary axis the first four points would sit flat on the floor.
Roughly how long one read takes, by where it lives
The data as a table
| Time for one read | Time for one read |
|---|---|
| CPU cache | 1 ns |
| Main memory | 100 ns |
| SSD | 1,00,000 ns |
| Spinning disk | 1,00,00,000 ns |
| Cross-region | 15,00,00,000 ns |
The practical consequence is that the jumps are not gentle. Missing cache and going to memory is survivable; missing memory and going to disk changes what your system can promise. Most performance work is really an argument about which of these five rows a piece of data lives in.
Volatile and durable are the real division
The table has two groups, split by one question: does the data survive losing power?
- Volatile — CPU cache and RAM. Fast, and gone the instant the process dies.
- Durable — SSD, HDD, object storage. Slower, and still there afterwards.
This is why a database cannot simply keep everything in memory and be done. A system that acknowledges a write must be able to honour it after a crash, and honouring it means the bytes reached something durable.
Diagram, as text
- Write request leads to Memory buffer
- Memory buffer leads to Append to log on disk
- Append to log on disk leads to Acknowledge to client
- Memory buffer leads to Flush to data files later
That shape — write to memory, append to a durable log, acknowledge, tidy up afterwards — appears in nearly every storage engine, because it is the cheapest way to be both fast and honest.
Sequential and random access are not the same operation
Two reads of the same number of bytes can differ by orders of magnitude depending on whether they are next to each other.
Sequential access reads a contiguous run of bytes. On a spinning disk the head is already in place; on an SSD and over a network, a large contiguous request amortises the fixed cost of each operation.
Random access jumps to unrelated positions. Each jump pays the fixed cost again — a physical seek on a spinning disk, a separate operation elsewhere.
This is why appending is so much cheaper than updating in place, and why so many systems are built around append-only logs. It is also why reading a million rows in primary-key order can be dramatically faster than reading the same million rows scattered.
Why "just add an index" is not always the answer
An index is a sorted structure that turns a scan into a lookup. It converts reading a million rows into reading a handful.
The costs are equally real:
- Every write must update every index. A table with six indexes turns one insert into seven writes.
- Indexes occupy space — in memory if you want them fast, on disk regardless.
- An index that does not match the query is dead weight: it is maintained on every write and used by nothing.
An index helps when a query is selective. If a query returns most of the table, following an index means a random read per row, and a plain sequential scan of the whole table is often faster. This is why databases sometimes ignore an index you built — and are usually right to.
Object storage changed the shape of the problem
Object storage — large blobs addressed by key, over a network — has different properties again: effectively unlimited capacity, very low cost per byte, high durability, and latency measured in tens of milliseconds.
You cannot run a database on it. You can put every photo, video, backup and log file on it, and keep only small rows of metadata in your database. That split — bytes in object storage, metadata in the database — is one of the most reliable structural decisions in system design, and it is what makes the storage arithmetic in the estimation lesson come out sensibly.
Check your understanding
Common mistakes
Treating all storage as one thing. "The disk" spans a thousandfold range in latency. Which device, and whether access is sequential, changes the answer entirely.
Storing large binary data in a relational database. It inflates backups, slows replication and evicts useful pages from the cache. Store a key and put the bytes elsewhere.
Adding indexes until queries are fast. Each one taxes every write. Indexes should be justified by a query that exists, and removed when that query goes.
Assuming an acknowledged write is a durable write. It depends on configuration, and the default is not always the safe one.
Benchmarking with a warm cache. The second run of a query reads from memory and tells you nothing about the first. Measure cold as well as warm.
Practice
A read-heavy service does 20,000 queries a second against a 400 GB database on a machine with 64 GB of RAM. Latency is fine most of the time but occasionally spikes badly. Explain the most likely cause and two ways to address it.
Write your own answer first, on paper or in a note. Then compare.
Show a hint
Compare the size of the working set with the size of memory, and ask what happens on a miss.
Show a strong answer
The likely cause is cache misses against a working set larger than memory.
64 GB of RAM cannot hold a 400 GB database, so the database keeps the hot pages in memory and reads the rest from disk. Most queries hit hot pages and are fast. A query that touches a cold page pays an SSD read — a thousandfold jump — and if it touches several, the costs add up. That is exactly the profile of "fine most of the time, occasionally terrible": you are not seeing a slow system, you are seeing a bimodal one, and the average hides it completely.
The giveaway is that the spikes correlate with cache hit ratio, not with CPU or query complexity.
Two ways to address it:
- Shrink the working set. Most of a 400 GB database is usually cold history. Move rows older than some cutoff to separate storage, or push large columns out to object storage and leave narrow rows behind. Halving the hot data can turn a miss rate of several percent into a fraction of one.
- Increase memory, or add a cache in front. More RAM is the blunt version and works until the data grows again. A dedicated cache for the hottest keys is more targeted and lets you keep the database smaller.
What will not help: optimising the query, or adding CPU. The query is already fast when its data is in memory.
Design the storage for a document-editing product: documents up to 10 MB, 5 million users, every keystroke saved, and full version history. Say where each kind of data lives and why.
Write your own answer first, on paper or in a note. Then compare.
Show a hint
Separate what must be read on every keystroke from what is read once a month.
Show a strong answer
Four kinds of data, and they belong in different places.
- The live document being edited — memory. Editing is a stream of tiny updates that must apply in milliseconds. This is served from memory, on a server that owns that document's session, with changes appended to a durable log so a crash loses at most the last moment rather than the document.
- The keystroke stream — an append-only log on durable storage. This is the classic sequential-write workload: high volume, never updated, read back in order. Appending is cheap; updating a document in place on every keystroke would be catastrophic.
- Document metadata — the database. Title, owner, permissions, collaborators, the pointer to the current version. Small rows, queried constantly ("list my documents"), needs indexes and transactions. A few hundred bytes per document.
- Version history and document snapshots — object storage. 5 million users × several documents × a snapshot per session is enormous, cheap per byte, and read rarely. Latency of 100 ms is irrelevant for "restore the version from last Tuesday".
The arithmetic that forces this: even at one 10 MB document each, 5 million users is 50 TB before any history. History multiplies it. That number rules out keeping documents in the database, and the access patterns — constant tiny writes, rare large reads — rule out treating them all the same way.
The general principle: split data by how it is accessed, not by what it is about. Everything about one document ends up in three different systems, and that is correct.
Key takeaways
- Each level of the hierarchy is roughly a hundred to a thousand times slower than the one above it. Those ratios drive most storage architecture.
- Volatile versus durable is the real division. An acknowledged write is only as safe as the device it reached before the acknowledgement was sent.
- Sequential access is dramatically cheaper than random access, which is why append-only logs are everywhere.
- An index trades write cost and space for read speed, and only pays off when a query is selective.
- Put large binary data in object storage and keep metadata in the database. It is the split that makes the numbers work at scale.
Further reading
- Jeff Dean, "Numbers Everyone Should Know" (Stanford lecture notes, 2007) — the latency figures this lesson builds on.
- Martin Kleppmann, Designing Data-Intensive Applications (O'Reilly, 2017), chapter 3 — storage engines, log-structured merge trees and B-trees.
- Patrick O'Neil, Edward Cheng, Dieter Gawlick and Elizabeth O'Neil, "The Log-Structured Merge-Tree (LSM-Tree)" (Acta Informatica, 1996) — why appending beats updating in place.
- Ulrich Drepper, "What Every Programmer Should Know About Memory" (Red Hat technical report, 2007) — the CPU-cache end of the hierarchy in detail.
- Adam Jacobs, "The Pathologies of Big Data" (ACM Queue, 2009) — sequential versus random access, with measurements.