Cold cache

What a cold cache is

PostgreSQL stores table and index data as 8KB pages on disk. When a query needs a page, PostgreSQL checks shared_buffers first. If the page is there — a cache hit — the read takes roughly 0.1ms. If not — a cache miss — PostgreSQL reads the page from the filesystem, which takes 1–10ms depending on whether the OS page cache has it or the read goes all the way to the storage device.

A cold cache means most or all of those lookups will be misses. There are two layers of "cold" to consider:

• Cold shared_buffers — after a PostgreSQL restart, the buffer cache is allocated but empty. Every query's first access to any page is a miss. This is the most common meaning of "cold cache."
• Cold OS page cache — the operating system also caches file reads in kernel memory. After a full server reboot, or after explicitly flushing the page cache, this second layer is empty too. Reads go directly to the storage device. This is a fully cold cache — the worst case for latency.

In practice, PostgreSQL restarts without an OS reboot leave the OS page cache intact. This softens the blow considerably: pages not in shared_buffers may still be served from kernel memory at microsecond latency rather than millisecond disk latency. A PostgreSQL-only restart is a partially cold cache. A full machine reboot is a fully cold cache — the equivalent of arriving at the manor to find the fires unlit and the larder bare.

Why cold caches matter

Allow me to be direct: the performance difference between a warm cache and a cold cache can be dramatic — often 10x to 100x for individual queries, depending on how many pages they touch.

• Post-restart performance dip — after restarting PostgreSQL for a version upgrade, configuration change, or crash recovery, the first few minutes of traffic experience elevated latency. Queries that normally complete in 2ms may take 50ms or more while the working set loads into memory. For high-traffic applications, this can trigger cascading timeouts, connection pool exhaustion, or alert storms.
• Failover latency — when a replica is promoted to primary, its buffer cache may not contain the same pages the old primary had. The new primary effectively starts with a cold cache for the workload patterns it inherits. The staff have changed shifts, and the new arrival does not yet know where anything is kept.
• Misleading benchmarks — benchmarking a query only on a warm cache measures best-case performance. In production, some fraction of queries will always hit cold data — new records, infrequently accessed tables, the first query of the day against a report table. Understanding cold-cache performance gives you the full picture.

Warming a cold cache

A proper household is warm before the guests arrive, not after. There are two approaches: let traffic do it naturally, or use pg_prewarm to load pages explicitly.

Natural warming

Normal application traffic warms the cache over time. Each query that misses the cache loads the pages it needs, and those pages stay resident for future queries. For most workloads, the cache reaches a steady state — where the working set fits comfortably in shared_buffers — within minutes.

Natural warming is adequate when the cold-start penalty is tolerable. If your application can absorb a few minutes of slower queries without user-facing impact, no special action is needed. I should note, however, that "tolerable" is a word that means different things at 3pm and 3am.

pg_prewarm

The pg_prewarm extension provides explicit control. You can load specific tables and indexes into the buffer cache before traffic arrives.

-- Manually warm a specific table into the buffer cache
SELECT pg_prewarm('orders');

-- Warm a specific index
SELECT pg_prewarm('orders_pkey');

-- Warm with a specific method: 'buffer' loads into shared_buffers,
-- 'prefetch' asks the OS to read ahead, 'read' does a kernel-level read
SELECT pg_prewarm('orders', 'buffer');

For fully automated warming, pg_prewarm includes an autoprewarm feature. When loaded as a shared library, it periodically records which blocks are in the buffer cache to a file on disk. At the next startup, it reads that file and reloads those same blocks — effectively restoring the cache to its pre-shutdown state without any manual intervention.

-- Enable autoprewarm in postgresql.conf (requires restart)
shared_preload_libraries = 'pg_prewarm'

-- pg_prewarm will periodically dump the list of cached blocks
-- to a file (autoprewarm.blocks) and reload them at startup.
-- No manual intervention needed after the first restart.

Autoprewarm is the most practical solution for production databases where restart latency matters. It requires no maintenance and adapts automatically as your workload changes. The house warms itself before you arrive. As it should.

Cold cache in benchmarking

Meaningful benchmarks measure both cold-cache and warm-cache performance. Each tells you something different.

• Cold-cache benchmarks measure worst-case, I/O-bound latency. They reveal how much a query depends on disk speed and how many pages it needs to read. This is the performance your users see after a restart, a failover, or when accessing infrequently used data.
• Warm-cache benchmarks measure best-case, CPU-bound latency. They show the performance ceiling when all data is in memory. This is what your most frequent queries experience under normal operation.

To benchmark with a truly cold cache, you need to clear both caching layers.

-- 1. Stop PostgreSQL (clears shared_buffers)
sudo systemctl stop postgresql

-- 2. Drop the OS page cache (Linux)
sudo sync
sudo sh -c 'echo 3 > /proc/sys/vm/drop_caches'

-- 3. Start PostgreSQL
sudo systemctl start postgresql

-- Both caches are now cold. The next query will read entirely from disk.

Only the first query execution after this procedure is a true cold-cache measurement. The second execution will already benefit from pages loaded by the first.

You can verify the current cache state with pg_buffercache.

-- Check buffer cache usage (requires pg_buffercache extension)
SELECT
  count(*) AS total_buffers,
  sum(case when reldatabase is not null then 1 else 0 end) AS used_buffers,
  round(
    sum(case when reldatabase is not null then 1 else 0 end)::numeric /
    count(*) * 100, 2
  ) AS usage_pct
FROM pg_buffercache;

-- Monitor cache hit ratio recovering after a restart
SELECT
  datname,
  blks_hit,
  blks_read,
  round(
    blks_hit::numeric / nullif(blks_hit + blks_read, 0) * 100, 2
  ) AS hit_ratio
FROM pg_stat_database
WHERE datname = current_database();

-- Run this periodically after a restart to watch the ratio climb
-- from near 0% back toward 99%+

How Gold Lapel relates

Gold Lapel sits between your application and PostgreSQL as a query proxy. When it starts up — or when the PostgreSQL server behind it restarts — it faces its own cold-start challenge: it needs to understand your workload before it can optimize anything. I find this rather familiar territory.

When pg_stat_statements is installed, Gold Lapel reads it at startup to seed its query model with historical data. Rather than waiting to observe patterns from live traffic, it inherits the workload profile that pg_stat_statements has already gathered. This shortens the cold-start window from minutes to seconds — the proxy equivalent of pg_prewarm for the buffer cache. One does not ask guests to wait in a cold foyer while the staff familiarize themselves with the day's schedule.