pg_prewarm

The pg_prewarm module provides a convenient way to load relation data into the operating system buffer cache or the Halo buffer cache. Prewarming can be performed manually using the pg_prewarm function, or automatically by including pg_prewarm in shared_preload_libraries. In the latter case, the system will run a background worker that periodically records the contents of shared memory in a file called autoprewarm.blocks, and reloads those blocks after a restart using two background workers.

1. Functions

pg_prewarm(regclass, mode text default 'buffer', fork text default 'main',

first_block int8 default null,

last_block int8 default null) RETURNS int8

The first argument is the relation to prewarm.

The second argument is the prewarming method to use, discussed further below.

The third argument is the relation fork to be prewarmed, typically main.

The fourth argument is the first block number to prewarm (NULL is also accepted, which is equivalent to zero).

The fifth argument is the last block number to prewarm (NULL means prewarm up to the last block of the relation). The return value is the number of blocks prewarmed.

There are three available prewarming methods. prefetch issues an asynchronous prefetch request to the operating system (if asynchronous prefetch is supported), or throws an error if asynchronous prefetch is not supported. read reads the requested range of blocks. Unlike prefetch, it is synchronous and is supported on all platforms, but may be slower. buffer reads the requested range of blocks into the database buffer cache.

Note: Attempting to prewarm more blocks than can be cached using any method — using prefetch or read (by the OS) or using buffer (by Halo) — will likely result in higher-numbered blocks evicting lower-numbered blocks from the cache. The prewarmed data also does not receive special protection from buffer replacement, so other system activity may evict recently prewarmed blocks shortly after they are read. Conversely, prewarming may evict other data from the cache. For these reasons, prewarming is typically most useful at startup time, when the buffer cache is mostly empty.

autoprewarm_start_worker() RETURNS void

Starts the main autoprewarm worker. This will normally happen automatically, but this function can be useful if automatic prewarming was not configured at server startup and the user wishes to start the worker at a later time.

autoprewarm_dump_now() RETURNS int8

Immediately updates autoprewarm.blocks. This function is useful if the autoprewarm worker is not running but the user wants it to run after the next restart. The return value is the number of records written to autoprewarm.blocks.

2. Configuration Parameters

pg_prewarm.autoprewarm (boolean)

Controls whether the server should run the autoprewarm worker.

The default is on. This parameter can only be set at server startup.

pg_prewarm.autoprewarm_interval (int)

This is the interval at which autoprewarm.blocks is updated.

The default is 300 seconds. If set to 0, the file will not be dumped at regular intervals but only when the server shuts down.