Extensions
pg_prewarm

pg_prewarm

pg_prewarm : prewarm relation data

Overview

ID Extension Package Version Category License Language
5890
pg_prewarm
pg_prewarm
 1.2
ADMIN
PostgreSQL
C
Attribute Has Binary Has Library Need Load Has DDL Relocatable Trusted
--sLd--
No
Yes
Yes
Yes
no
no
Relationships
See Also
pgfincore
pg_cooldown
pgcozy
pg_buffercache
pg_repack
pg_rewrite
pg_squeeze
old_snapshot

Packages

PG18 PG17 PG16 PG15 PG14
1.2
1.2
1.2
1.2
1.2

This is a built-in contrib extension ship with the PostgreSQL kernel

Install

Config this extension to shared_preload_libraries:

shared_preload_libraries = 'pg_prewarm';

Create this extension with:

CREATE EXTENSION pg_prewarm;

Usage

pg_prewarm: prewarm relation data

The pg_prewarm extension provides functions to load relation data into the operating system buffer cache or PostgreSQL buffer cache, reducing I/O latency for subsequent queries.

Prewarm a Relation 

-- Load entire table into PostgreSQL buffer cache (default mode)
SELECT pg_prewarm('my_table');

-- Load with specific mode
SELECT pg_prewarm('my_table', 'prefetch');  -- async OS prefetch
SELECT pg_prewarm('my_table', 'read');      -- sync read into OS cache
SELECT pg_prewarm('my_table', 'buffer');    -- load into PG buffer cache

-- Load specific block range
SELECT pg_prewarm('my_table', 'buffer', 'main', 0, 999);

-- Prewarm an index
SELECT pg_prewarm('my_table_pkey');

Function Signature 

pg_prewarm(regclass,
           mode text DEFAULT 'buffer',
           fork text DEFAULT 'main',
           first_block int8 DEFAULT NULL,
           last_block int8 DEFAULT NULL
) RETURNS int8

Returns the number of blocks prewarmed.

Parameter Description
mode prefetch (async OS), read (sync OS), or buffer (PG cache)
fork Relation fork to prewarm (e.g., main, fsm, vm)
first_block First block number (default: 0)
last_block Last block number (default: last block of relation)

Autoprewarm

When loaded via shared_preload_libraries, autoprewarm periodically saves the list of buffers in the shared buffer cache to disk and restores them on restart.

-- Manually launch autoprewarm worker
SELECT autoprewarm_start_worker();

-- Force immediate dump of buffer state
SELECT autoprewarm_dump_now();  -- returns number of records written

GUC Parameters

Parameter Default Description
pg_prewarm.autoprewarm true Enable autoprewarm background worker
pg_prewarm.autoprewarm_interval 300s Interval between autoprewarm.blocks file updates (0 = dump only at shutdown)

Buffer state is saved to autoprewarm.blocks in the data directory. After restart, two background workers reload the saved buffers.

Last updated on
pgagentpgpool_adm