The failover itself was perfect. Patched host, planned promotion, the standby took over in forty seconds, the application's health checks went green, and the maintenance window was declared a success. Then the latency graph came in: p99 jumped from 8 ms to just over 300 ms and stayed there for twenty-five minutes while every dashboard that measures errors stayed green. Nothing was wrong in any sense a health check understands. The new primary simply had an empty buffer cache — forty gigabytes of working set that the old primary had been serving from RAM was now being read from storage, one 8 KB page at a time, by every query that arrived. The failover didn't fail. The cache did, silently, in the one metric nobody had graphed.
We have since done this enough times — restarts, failovers, instance class changes, kernel patches — to treat cache warm-up as a first-class phase of every restart, with its own measurement and its own automation. Here is the whole routine.
Why is a freshly restarted PostgreSQL slow when nothing is broken?
Because PostgreSQL serves reads from two caches — its own shared_buffers and the operating system's page cache — and a restart empties the first while a reboot empties both, so every page the workload touches must come from storage until the working set is re-read. The database is fully correct the instant it opens; it is just doing physical reads where it used to do memory hits, and on a workload sized so the hot set fits in RAM, that is the difference between single-digit and triple-digit millisecond latencies. The deceptive part is the shape of the recovery: there is no cliff edge where things get fixed, just a long exponential tail as the hottest pages land first and the merely-warm pages trickle in. Our twenty-five minutes was a 40 GB hot set re-read at whatever rate the storage and the traffic pattern allowed — not an outage, but absolutely an incident, and one that a second restart an hour later would have repeated from zero.
How do you measure cache warm-up instead of guessing?
You watch the physical read rate and the buffer occupancy, because those two curves define the warm-up phase completely. The read side comes from pg_stat_database — blks_read versus blks_hit as rates over an interval, not lifetime totals — and on PostgreSQL 16 and later, pg_stat_io breaks reads down by backend type, which tells you whether it is user queries or maintenance doing the loading:
SELECT datname,
blks_read,
blks_hit,
round(100.0 * blks_hit / NULLIF(blks_hit + blks_read, 0), 1) AS hit_pct
FROM pg_stat_database
WHERE datname = current_database();Sample it a minute apart during the first half hour: a warming cache shows blks_read elevated far above baseline and falling on a curve, and the minute the read rate flattens back to baseline is the minute the restart is actually over — we write that number, time-to-warm, into the incident log next to the failover time. The occupancy side is the pg_buffercache extension, which shows what shared_buffers actually holds right now; aggregating it per relation tells you whether the pages being cached are the ones your workload needs or just the ones a nightly vacuum walked past:
SELECT c.relname,
count(*) AS buffers,
pg_size_pretty(count(*) * 8192) AS cached
FROM pg_buffercache b
JOIN pg_class c ON c.relfilenode = b.relfilenode
GROUP BY c.relname
ORDER BY count(*) DESC
LIMIT 15;One honest caveat on that query: relfilenode is not unique across databases in the cluster, so join pg_database or filter by b.reldatabase on multi-database clusters, or you will credit the wrong table. If read rates during warm-up look pathological rather than merely elevated, the differential diagnosis against genuine storage trouble is the high-IO debugging notes, and the wait-event side of the same picture is in the wait events field guide.
What does pg_prewarm automate, and how do you set it up?
pg_prewarm solves the restart half of the problem: loaded via shared_preload_libraries, its autoprewarm background worker periodically records which blocks shared_buffers holds into a file called autoprewarm.blocks and, after a restart, reloads those same blocks automatically — the cache you had is approximately the cache you get back. The feature has shipped since PostgreSQL 11, costs one line of configuration plus an optional interval, and on our clusters it cut time-to-warm from the twenty-five-minute organic curve to under six minutes:
-- postgresql.conf:
-- shared_preload_libraries = 'pg_prewarm'
-- pg_prewarm.autoprewarm = on -- default when loaded
-- pg_prewarm.autoprewarm_interval = 300s -- default
-- manual warm-up of one relation, into shared_buffers:
SELECT pg_prewarm('public.orders', 'buffer', 'main');The manual form takes three modes that are worth understanding before production use. buffer reads blocks into shared_buffers — the real thing, and the one autoprewarm uses. read just pulls them into the OS page cache, which is the right choice when you want the file warm without evicting what shared_buffers already holds. prefetch asks the kernel for asynchronous readahead where supported. Two caveats from the manual that experience confirms: prewarming more blocks than the cache holds evicts the earlier blocks as the later ones arrive, so a whole-table prewarm of a table larger than shared_buffers is self-defeating; and prewarmed pages get no protection from eviction afterward, so prewarm is a startup ritual, not a pinning mechanism.
Is a promoted standby already warm?
Partially, and the wrong half is warm. A streaming standby has been reading pages into shared_buffers constantly — to apply WAL — so its cache is full of recently modified pages, which for write-heavy hot spots genuinely overlaps with the read working set. What it lacks is the read-only side: index root and interior pages walked by lookups that changed nothing, cold-table pages that dashboards read hourly, the long tail that only read traffic caches. After promotion, that read-shaped half of the working set loads from disk exactly as if the restart had happened. This is why autoprewarm belongs on the standbys too — it runs on replicas and dumps the standby's own buffer contents, so the promotion inherits a read-warm cache instead of a write-warm one — and why our failover runbook ends with the same time-to-warm measurement as a restart, not with the promotion log line.
What goes into a restart runbook that includes warm-up?
Ours is five lines, and every line exists because skipping it once produced an incident. First, before the restart, record the current read rate so you know what "back to normal" means numerically. Second, restart or promote and let autoprewarm run. Third, for the dozen relations that matter most — we keep the list from pg_statio_user_tables, ranked by shared blocks hit — run manual pg_prewarm for anything autoprewarm missed. Fourth, watch the blks_read rate until it returns to baseline and write down time-to-warm. Fifth, ramp traffic rather than flipping it: if the load balancer allows weighted draining, give the fresh primary a minute at ten percent before full weight, because a cold cache under full load is how twenty-five-minute warm-ups turn into queue pile-ups. For verifying that a specific query is back to serving from memory, EXPLAIN (ANALYZE, BUFFERS) shows shared hit versus read per execution — the EXPLAIN BUFFERS deep dive is the companion read for that number.
Watching cache warm-up with MonPG
Everything in this post is a time series: the physical read rate bending back to baseline, buffer hit ratio per database, block reads per backend type, and p99 latency plotted across the restart line. MonPG graphs exactly these PostgreSQL series as part of its PostgreSQL monitoring, which turns the question "is it warm yet?" from a guess into a graph with a visible end point — and makes the next change window's go/no-go a number instead of a feeling. A failover that takes forty seconds and a cache that takes six minutes is a five-minute story; the same failover with no prewarm and no measurement is the twenty-five-minute one your users tell you about.