pg_prewarm

What pg_prewarm does

When PostgreSQL starts, its shared buffer cache is empty. Every query must read data from disk until the working set is fully cached — a process that can take minutes to hours depending on database size and workload patterns. During this period, query latency can be 10-100x higher than steady state. Your users will notice. They always do.

pg_prewarm provides a function that loads specific relations into cache on demand. You specify a table or index, and the extension reads its blocks into memory. This gives you direct control over what gets cached and when, rather than waiting for the natural access pattern to warm things up.

Since PostgreSQL 11, the extension also includes autoprewarm — a background worker that periodically snapshots the contents of shared buffers to a file on disk. After a restart, the worker reads that file and restores the same pages into the buffer cache, effectively preserving your warm cache across restarts without any manual intervention.

When to use pg_prewarm

The extension addresses a specific and common pain point: slow performance after a PostgreSQL restart.

• After planned maintenance restarts — prewarm critical tables and indexes before routing production traffic back to the server
• After failover events — the new primary starts with a cold cache; prewarming the most-accessed relations reduces the performance dip
• Read replicas joining a cluster — new replicas start cold; prewarming brings them to full speed faster
• Time-sensitive workloads — if your application cannot tolerate the gradual warm-up period, prewarm the specific tables that matter most
• Automated cache restoration — autoprewarm handles the common case automatically, saving cache state before shutdown and restoring it on startup

Installation and setup

pg_prewarm is a contrib module that ships with PostgreSQL — no additional packages to install. For basic manual prewarming, a single statement and it is ready to serve.

-- Basic usage: just create the extension (no restart needed)
CREATE EXTENSION pg_prewarm;

-- Verify it's available
SELECT pg_prewarm('my_table');

For the autoprewarm feature, which saves and restores cache contents across restarts, you need to add pg_prewarm to shared_preload_libraries. This requires a server restart.

-- To enable autoprewarm, add to postgresql.conf (requires restart)
shared_preload_libraries = 'pg_prewarm'

-- Configuration (optional, these are the defaults)
pg_prewarm.autoprewarm = true
pg_prewarm.autoprewarm_interval = 300   -- seconds between dumps

The pg_prewarm.autoprewarm_interval parameter controls how often the background worker writes the current buffer contents to $PGDATA/autoprewarm.blocks. The default of 300 seconds (5 minutes) is reasonable for most workloads. Setting it to 0 disables periodic dumps — the file will only be written at server shutdown.

Practical examples

Basic prewarming

The pg_prewarm function accepts a relation name and returns the number of blocks loaded. Tables, indexes, materialized views — anything with physical storage is fair game.

-- Prewarm a table into PostgreSQL shared buffers (default mode)
SELECT pg_prewarm('orders');

-- Prewarm an index into shared buffers
SELECT pg_prewarm('orders_pkey');

-- Prewarm a specific index used by a critical query
SELECT pg_prewarm('idx_orders_customer_id');

Prewarming modes

The function supports three modes, each loading data into a different layer of the cache hierarchy. Choose according to your circumstances.

-- 'buffer' mode: load into PostgreSQL shared buffers (default)
-- Best for queries that need data immediately after restart
SELECT pg_prewarm('orders', 'buffer');

-- 'read' mode: load into the OS page cache only
-- Useful when shared_buffers is small and you want the OS to cache it
SELECT pg_prewarm('orders', 'read');

-- 'prefetch' mode: asynchronous OS prefetch requests
-- Non-blocking, lets the OS fetch pages in the background
SELECT pg_prewarm('orders', 'prefetch');

Partial prewarming and forks

For large tables, you can prewarm a specific range of blocks rather than the entire relation. You can also prewarm specific relation forks — the visibility map (vm) or free space map (fsm) — which can be important for index-only scans and space management.

-- Prewarm only the first 1000 blocks of a large table
SELECT pg_prewarm('orders', 'buffer', 'main', 0, 999);

-- Prewarm the visibility map fork (useful before index-only scans)
SELECT pg_prewarm('orders', 'buffer', 'vm');

-- Check how many blocks a relation has before prewarming
SELECT pg_relation_size('orders') / current_setting('block_size')::int AS blocks;

Autoprewarm management

When autoprewarm is enabled, these functions let you interact with the background worker directly.

-- Manually dump the current buffer cache contents to disk
-- (normally happens every pg_prewarm.autoprewarm_interval seconds)
SELECT autoprewarm_dump_now();

-- Manually start the autoprewarm worker if it wasn't configured at startup
SELECT autoprewarm_start_worker();

Startup prewarming script

If you are not using autoprewarm, a script that runs after every restart ensures the essential rooms are warm before the first guest walks through the door.

-- Startup prewarming script: run after a restart to warm critical tables
-- Returns the number of blocks loaded for each relation
SELECT 'orders' AS relation, pg_prewarm('orders') AS blocks_loaded
UNION ALL
SELECT 'customers', pg_prewarm('customers')
UNION ALL
SELECT 'idx_orders_customer_id', pg_prewarm('idx_orders_customer_id')
UNION ALL
SELECT 'idx_orders_created_at', pg_prewarm('idx_orders_created_at');

Function reference

pg_prewarm parameters

Configuration parameters

Cloud availability

How Gold Lapel relates

Allow me to draw a distinction. pg_prewarm warms the physical layer — loading raw pages from disk into the buffer cache so PostgreSQL's executor can find them in memory. It ensures the house has heat. Gold Lapel operates at the logical layer: maintaining materialized views and pre-computed result sets so that repeated query patterns are served from prepared answers rather than re-executing the full work each time. It ensures the house is organized.

Both address the same concern — queries are slow when the data they need is not ready — but from different angles. After a restart, pg_prewarm repopulates the buffer cache with the right pages. Gold Lapel ensures its materialized views are fresh and available so the expensive queries need not run at all.

I should be clear: pg_prewarm is valuable for any PostgreSQL deployment regardless of what sits in front of it. Gold Lapel adds a complementary layer that reduces the total work PostgreSQL must do in the first place — warmed cache or not. One heats the rooms. The other reduces the number of trips between them.