Why Your Benchmark Measured the Cache

A query that takes 340 ms once and 12 ms on the next four runs has not been measured. The first number includes moving pages into memory; the rest report the cost of finding them already there. Which of the two an application experiences depends on facts about production that the benchmark has not reproduced.

Three caches sit under every query

The database keeps its own buffer pool — shared_buffers in PostgreSQL, innodb_buffer_pool_size in MySQL. Underneath it, the operating system keeps a page cache of file contents. Underneath that, the storage device has a cache of its own.

Each layer is invisible from the one above. A page absent from the buffer pool is requested from the operating system, which may return it from the page cache without the device being involved at all. That is why “buffer pool miss” and “disk read” are not synonyms, and why a benchmark can report I/O that never happened.

What the BUFFERS counters can and cannot tell you

In PostgreSQL, shared hit means the page was found in the buffer pool and read means it was not. BUFFERS is what separates a cold cache from a genuinely expensive plan, and that holds — but it bounds the answer from one side only. read counts pages PostgreSQL fetched, not pages the device served.

Separating the second layer from the third needs timing rather than counters. track_io_timing is off by default and turning it on costs something — it queries the operating system clock repeatedly — but with it enabled the plan reports the wall time spent in those reads. An average far below the read latency the storage can deliver is the page cache; compare it against the device’s published figures rather than a fixed threshold.

The dataset decides the regime more than the query does

A 2 GB table under 8 GB of buffers is fully resident after one pass, whatever the query does. Every comparison run against it is a comparison of CPU and access-path efficiency with the I/O term fixed at zero.

That is a real measurement. It is not the one that predicts behavior on a 400 GB table with the same 8 GB of buffers, where physical correlation, read-ahead, and the number of random reads an access path implies start to dominate — the regime in which a row estimate, rather than the index itself, decides the plan.

The constraint that follows is about ratios: the working set relative to memory in the benchmark has to resemble the ratio in production. Renting a smaller machine and keeping the full dataset preserves it. Keeping the machine and shrinking the dataset does not.

Warm is a legitimate thing to measure; unstated is not

A steady-state OLTP service runs warm nearly all the time. Benchmarking it cold measures a state it occupies for a few minutes a week, usually right after a restart. Warm is the correct choice there.

Two questions decide whether the result means anything: was the cache warmed before timing, and was the same warming applied to every variant. The second produces more false results than the first, because variant B looks faster for having run after variant A left the pages resident.

Forcing the regime you intend to test

# Cold: the buffer pool goes with the process, the page cache needs its own step.
sudo systemctl stop postgresql
sync && echo 3 | sudo tee /proc/sys/vm/drop_caches
sudo systemctl start postgresql

-- Warm and repeatable: put the same pages in the pool before every variant.
CREATE EXTENSION IF NOT EXISTS pg_prewarm;
SELECT pg_prewarm('orders');
SELECT pg_prewarm('orders_customer_id_idx');

Deliberate prewarming beats “run it five times and take the last one” because it gives every variant an identical starting state instead of whatever state its predecessor left behind.

Limits

Cache regime is one way a benchmark can be confidently wrong, not the only one. A single query repeated in a loop still has no concurrency, no lock contention, and no competing writes, and none of the above detects that. Dropping the page cache also requires root, so on a managed service the only lever left is dataset size — which is the more honest lever anyway.