The Database Is Already a Cache
"Cache your database queries" is advice given without qualification, and it skips something important: the database is already .
A relational database keeps recently read pages in a buffer pool, normally gigabytes of it. After the first execution a repeated query usually touches no disk at all, so an application cache in front of it is not saving a disk read.
That changes what to measure. Comparing a cache against a cold database makes every query look worth caching, because you are measuring a disk read the database would have eliminated on its own.
So What Is Actually Left?
The script is at scripts/labs/querycache/whats_left.py. It loads 300,000 rows into a real PostgreSQL, warms the buffer pool deliberately, and then measures four queries with EXPLAIN (ANALYZE, BUFFERS).
After three executions the database serves all of these without touching disk. What an application cache would save is the server column, and it ranges from 0.03 milliseconds to 12.91.
The spread is 430 times, across four queries on one table in one database. advice that treats them the same is advice about the wrong thing, because what a cache saves is the height of these bars and nothing else.
Run EXPLAIN (ANALYZE, BUFFERS) before deciding to cache a query. If the read count is already zero on a warm system, the disk argument for caching it does not exist.
Caching the cheapest one replaces 0.03 milliseconds of work with a network round trip, which the previous lesson measured at 17 microseconds on loopback and far more in production. Most teams cache the query they call most often, which is usually the key lookup.
Where the Remaining Time Goes
This is why an aggregate is so effective and caching a lookup is not. The aggregate's cost is proportional to how much data it touches, so it grows as the table grows, and the cache turns a growing cost into a constant one.
Every write had to invalidate every cached result touching the modified table, which required a global lock, and on a busy server that lock cost more than the cache saved. The buffer pool survives everywhere because caching pages has no such problem.
The Cheaper Answers First
A cache in front of a sequential scan hides a missing index, performs beautifully in normal conditions, and delivers the unindexed query to your database at full traffic the moment the cache restarts.
For an expensive aggregate allowed to be minutes old, this replaces the cache, the , the invalidation and the extra dependency with one scheduled statement, and it survives a restart.
Keys, Patterns and Staleness
A query cache key must contain every input that changes the answer. Row level security, default schemas and session variables are all invisible in the text and all change the result.
A search result storing whole rows is invalidated by any change to any of them. A list of ids is only invalidated when the membership changes, and each row is cached under its own key shared by every query that returns it.
The Order to Work In
Every step before it is cheaper, permanent and has no staleness. The queries that reach the cache are the aggregates and reports, which is exactly the category the measurement showed a cache helps by hundreds of times.
A query taking 2 milliseconds ten thousand times a minute costs more than one taking 900 milliseconds once, and only one of them gets complained about.
Quick Check
Knowledge Check
4 questions - Score 80% to pass
You run EXPLAIN (ANALYZE, BUFFERS) on a repeated query and see 3,093 shared hits and 0 shared reads. What does adding a cache in front of it save?
On the measured table, a primary key lookup took 0.03 ms warm and a group-and-sort took 12.91 ms. Which is the better caching candidate?
Why did MySQL remove its query result cache in 8.0?
A query is slow and EXPLAIN shows a sequential scan with a non-zero shared read count. What should you do first?
