skip to content

After a planned PostgreSQL restart, queries stay slow until shared_buffers warms up: how do pg_prewarm and its autoprewarm worker help?

level: seniorimportance: nice to knowfreq 24%

answer

  1. an empty pool after restart
  2. three modes, one fills shared_buffers
  3. autoprewarm.blocks in the data directory
  4. preload the library, not just the extension
  5. stops when free buffers run out

basics

~10 s

pg_prewarm loads a relation's blocks into shared_buffers or the OS cache on demand. Its autoprewarm worker records which blocks were cached in autoprewarm.blocks and reloads them after a restart, shortening the cold-cache period.

solid answer

~40 s

After a restart shared_buffers is empty, so early traffic pays for disk reads. The contrib `pg_prewarm` module offers two tools. The function `pg_prewarm(regclass, mode, fork, first_block, last_block)` loads a relation on demand: mode `buffer` (the default) reads into shared_buffers, while `read` and `prefetch` only warm the OS cache. The **autoprewarm** worker runs when `pg_prewarm` is in `shared_preload_libraries` and `pg_prewarm.autoprewarm` is on (its default): it writes the list of cached blocks to `autoprewarm.blocks` every `pg_prewarm.autoprewarm_interval` (300 s) and at shutdown, and after the next start reloads those blocks with background workers, stopping as soon as no free buffers remain. Prewarmed pages get no protection from later eviction, so this shortens warm-up rather than pinning a working set.

code

ini · 4 lines
ini
# postgresql.conf
shared_preload_libraries = 'pg_prewarm'
pg_prewarm.autoprewarm = on
pg_prewarm.autoprewarm_interval = 300s

go deeper

for a junior

Recall that shared_buffers starts empty after a restart and that the pg_prewarm extension can load chosen tables or indexes back in.

for a middle

Explain the three modes, which one targets shared_buffers, and how the autoprewarm worker uses autoprewarm.blocks across restarts.

for a senior

Plan restarts and failovers with warm-up in mind: preload the library, keep a periodic dump, warm hot indexes first, and know prewarmed pages get no protection.

for a principal

Decide whether warm-up time belongs in your availability targets, and whether prewarming, a larger pool or standby read traffic is the cheaper way to meet them.

## Why a restart hurts `shared_buffers` lives in shared memory, which is created fresh at every server start. After a planned restart for a minor upgrade or a restart-only setting, the pool is empty. On a hotel-booking database, the first availability searches each read `rooms`, the availability index and recent `bookings` pages from disk or, at best, from the kernel cache. Latency stays high until the clock sweep has rebuilt the working set, which on a large pool can take a long time. The contrib module **pg_prewarm** shortens that window in two ways. ## The pg_prewarm() function Installed with `CREATE EXTENSION pg_prewarm`, the function `pg_prewarm(regclass, mode text default 'buffer', fork text default 'main', first_block int8 default null, last_block int8 default null)` returns the number of blocks it processed. The mode decides where the pages go: | Mode | Where the data lands | Notes | |---|---|---| | `buffer` (default) | PostgreSQL's shared_buffers | Reads through the normal replacement path, so it can evict other pages | | `read` | OS page cache only | Synchronous reads; works on every platform | | `prefetch` | OS page cache only | Asynchronous prefetch requests; raises an error where unsupported | The `fork` argument picks the relation fork, usually `main`, and the block range lets you warm only part of a large table. ## The autoprewarm worker Autoprewarm automates the same idea across restarts: 1. Add `pg_prewarm` to `shared_preload_libraries` (a restart-only setting). With `pg_prewarm.autoprewarm` on, its default, a leader background worker starts with the server. 2. Every `pg_prewarm.autoprewarm_interval` (default 300 seconds; 0 means dump only at shutdown) and at a clean shutdown, it records the identity of every block in shared buffers into the file `autoprewarm.blocks` in the data directory. 3. After the next start, it reads that file and loads the listed blocks back into shared buffers, using two background workers in all, while the server already accepts connections. `autoprewarm_dump_now()` forces an immediate dump, and `autoprewarm_start_worker()` launches the leader later if it was not started with the server. ## Limits and gotchas - **No protection afterwards.** Prewarmed pages are ordinary buffers; the next large index scan can evict them like any other page. - **Manual buffer mode on a huge relation evicts itself.** Prewarming more blocks than fit makes later blocks push out earlier ones, and it can push out pages that were already hot. - **Autoprewarm is polite.** It stops loading once no free buffers remain, so it never evicts pages already cached by live traffic. - **Worker slots.** Its workers come from `max_worker_processes`, the same pool parallel query uses. - **After a crash** there is no shutdown dump, so the file reflects the last periodic dump, which is one reason not to set the interval to 0. - **Standbys keep their own file.** A standby's dump reflects its own buffers, shaped by WAL replay and its read traffic, not the primary's working set; after a promotion the cache matches what the standby was doing. ## Checking that it worked - When the reload finishes, the server log reports `autoprewarm successfully prewarmed N of M previously-loaded blocks`; N below M can mean the free buffers ran out (for example, a smaller pool than before the restart) or that listed relations no longer exist. - The leader works through the file database by database, launching one per-database worker at a time, so a cluster with many databases warms in sequence. - `pg_buffercache_summary()` shows `buffers_used` climbing and `buffers_unused` falling during the reload; once no unused buffers remain, autoprewarm stops whatever is left in the file. - The number that justifies the setting is the latency of the availability search in the first minutes after a planned restart, measured with and without it. ## Choosing what to warm Autoprewarm needs no choice; it restores what was cached. For manual warming, decide from evidence: snapshot `pg_buffercache` grouped by relation during normal business hours, and warm those relations, indexes first, since index pages are reused most. For a large table, warm only the recent block range or use `prefetch` to fill the kernel cache without disturbing shared buffers. Sizing the pool itself is a memory-tuning decision.

  • Why is CREATE EXTENSION pg_prewarm not enough to get PostgreSQL's autoprewarm after every restart?
    CREATE EXTENSION only installs the SQL functions in one database. The autoprewarm leader is a background worker registered while the server starts, and that happens only when the library is listed in shared_preload_libraries, which itself needs a restart to change. autoprewarm_start_worker() can launch the worker by hand, but without the preload it will not come back on its own after the next restart.
  • After a PostgreSQL standby is promoted, will autoprewarm give it the old primary's warm cache?
    No. Autoprewarm on the standby recorded the standby's own shared buffers, which hold pages touched by WAL replay and by any read-only queries it served, not the primary's working set. If the standby took little read traffic, the new primary starts with a replay-shaped cache, so warming the known hot relations explicitly after promotion closes the gap.

  • pg_prewarm pins the relation in shared_buffers so it can never be evicted.
  • CREATE EXTENSION pg_prewarm alone makes the cache reload after every restart.
  • The prefetch mode loads blocks straight into PostgreSQL's shared_buffers.
  • Setting pg_prewarm.autoprewarm_interval to 0 makes the dump continuous.
  • Autoprewarm evicts live pages to restore everything listed in its file.