Book Four · Fire
The Database Depths
The fire is already burning: your database caches data on its own. In this realm, you learn to understand and tune those caches before adding another.
The Database Depths
The realm
Air, Water and Earth all hold copies. That is what made them fast, and what made them dangerous — any of them can quietly disagree with the truth.
This realm is the truth. The database is your source of truth: the original every copy was made from. When a cached value and the database disagree, the database is right by definition. The other three realms exist to keep work away from this one; this one exists to be correct.
It also caches internally, and that is the part people underestimate. A database has several layers of caching built in already, running whether you think about them or not.
The buffer pool is memory the database sets aside to hold recently used data. Databases do not read individual rows from disk — they read pages, fixed-size blocks containing many rows, and those pages stay in memory afterwards. On a well-configured database the buffer pool holds the entire working set — the part of your data actually being used — and its hit ratio is above 99%. Underneath it, the operating system keeps its own cache of recently read disk blocks.
The query plan cache stores the strategy for running a query — which index to use, what order to join tables — so the same query shape is not re-planned every time. Note carefully: it caches the plan, not the results. The query still runs.
Between them, most database reads never reach a disk at all. The data is already in memory by the time a query asks for it.
You tune here, you do not build
This realm is unlike the other three. In the browser you set headers. At the edge you configure keys. In your application you write the caching code yourself. Here, the caching already exists and is largely automatic.
What you actually control:
| Lever | What it does |
|---|---|
| Indexing | The big one. A precomputed structure for finding rows without scanning the table |
| Partitioning | Split one large table into smaller pieces so a query touches less of it |
| Sharding | Split the data across machines when one is no longer enough |
| Buffer pool sizing | Give the database enough memory to hold the working set |
Most of the answer, most of the time, is the first three. A query that is slow because it scans a million rows does not need a cache in front of it. It needs an index, or a partitioning scheme that means it only ever looks at this month's data.
Why an index beats a cache
An index is a data structure the database maintains so it can find rows without examining every row in the table. Without one, finding all reviews for product 42 means checking every review in the table — a full table scan. With one, the database goes straight to them.
That makes an index a form of precomputation, which sounds cache-adjacent. But it has two properties no cache has:
- It is never stale. The database updates it as part of the write. It cannot disagree with the data.
- There is nothing to invalidate. No TTL, no purge, no event to publish, no race to lose.
So there is a question worth asking before every cache you add:
If the answer is a missing index, add the index. A cache makes the query look fast while the work behind it stays exactly as expensive; an index makes the work itself cheaper, and the database keeps it current for you as part of every write.
An index is not free. It takes disk space, and it makes writes slightly slower, because every insert and update has to maintain it too. But those costs are paid in one place and you can measure them. Caching a query that needed an index means accepting everything in Lesson 11 — lifetimes, invalidation, staleness — in order to hide a problem you could have removed.
The N+1 problemoptional read
This is the version of that mistake you are most likely to meet in real code.
You load fifty products, then loop over them to show each one's reviews:
products = load_all_products() 1 query
for p in products:
show(p.reviews) 1 query each — 50 more
Fifty-one queries when one joined query would do. It is called N+1 because that is the shape: one query for the list, then N more for the things hanging off it.
It is easy to miss because nothing looks wrong. Each individual query is fast. The page is slow because there are fifty of them, and each one costs a round trip to the database.
The tempting fix is to cache the fifty lookups. That works — it turns fifty-one database queries into one query and fifty cache hits, and the page does get faster.
But you have not removed the fifty round trips. You have moved them to a faster machine, and taken on a staleness problem for the privilege.
The real fix is to ask for the reviews alongside the products, in one query:
products = load_all_products_with_reviews() 1 query, joined
Fifty-one queries become one joined query. There is no extra cache to invalidate or keep fresh. Your ORM may call this eager loading or include; some implementations use two queries instead of a join.
Before you cache in front of a database
Two things to do first.
Measure. You may find the product query takes 0.8ms, served entirely from the buffer pool, while your Redis lookup takes 0.5ms. That cache bought you 0.3ms and a staleness problem. This is The Sight from Lesson 1, applied where it is easiest to skip.
Then read the following, twice.
When keeping a cache up to date costs too much
MySQL used to have a query cache: same query text, same result handed back without executing anything. It was deprecated, and then removed entirely.
The reason is the most useful thing in this lesson.
Any write to a table had to invalidate every cached result that touched that table. On a busy system, tracking which results those were cost more than simply running the queries again. Under concurrent load the cache itself became the bottleneck, with every request queuing on the lock that protected it.
Now read that again with Lesson 11 in mind.
They had every advantage you will not. In Lesson 11 you invalidate from outside the database, guessing when things changed from the code paths you remembered to cover. That is not an argument against caching — it is an argument for knowing what you are taking on, and for reaching first for the index, which has no invalidation problem at all.
At the Storefront
Before caching the product query anywhere, check two things: whether it is already being served from the buffer pool, and whether it uses an index.
You may discover the cache you were about to add is a cache in front of a cache.
“The database is slow, so I need a cache in front of it.”
The database is already caching aggressively, at several levels, and it is very good at it. Measure before adding a layer — otherwise you are paying in staleness for a problem you did not have.