pg_prewarm: Preload Relation Data into Buffer Caches

<sect1 id="pgprewarm" xreflabel="pg_prewarm"> <title>pg_prewarm — preload relation data into buffer caches</title> <indexterm zone="pgprewarm"> <primary>pg_prewarm</primary> </indexterm> <para> The <filename>pg_prewarm</filename> module provides a convenient way to load relation data into either the operating system buffer cache or the <productname>PostgreSQL</productname> buffer cache. Prewarming can be performed manually using the <filename>pg_prewarm</filename> function, or can be performed automatically by including <literal>pg_prewarm</literal> in <xref linkend="guc-shared-preload-libraries"/>. In the latter case, the system will run a background worker which periodically records the contents of shared buffers in a file called <filename>autoprewarm.blocks</filename> and will, using 2 background workers, reload those same blocks after a restart. </para> <sect2 id="pgprewarm-funcs"> <title>Functions</title> <synopsis> pg_prewarm(regclass, mode text default 'buffer', fork text default 'main', first_block int8 default null, last_block int8 default null) RETURNS int8 </synopsis> <para> The first argument is the relation to be prewarmed. The second argument is the prewarming method to be used, as further discussed below; the third is the relation fork to be prewarmed, usually <literal>main</literal>. The fourth argument is the first block number to prewarm (<literal>NULL</literal> is accepted as a synonym for zero). The fifth argument is the last block number to prewarm (<literal>NULL</literal> means prewarm through the last block in the relation). The return value is the number of blocks prewarmed. </para> <para> There are three available prewarming methods. <literal>prefetch</literal> issues asynchronous prefetch requests to the operating system, if this is supported, or throws an error otherwise. <literal>read</literal> reads the requested range of blocks; unlike <literal>prefetch</literal>, this is synchronous and supported on all platforms and builds, but may be slower. <literal>buffer</literal> reads the requested range of blocks into the database buffer cache. </para> <para> Note that with any of these methods, attempting to prewarm more blocks than can be cached — by the OS when using <literal>prefetch</literal> or <literal>read</literal>, or by <productname>PostgreSQL</productname> when using <literal>buffer</literal> — will likely result in lower-numbered blocks being evicted as higher numbered blocks are read

The pg_prewarm module allows preloading relation data into either the operating system buffer cache or the PostgreSQL buffer cache. This can be done manually using the pg_prewarm function or automatically by including pg_prewarm in shared_preload_libraries, which will use background workers to record and reload shared buffer contents after a restart. The pg_prewarm function takes the relation to prewarm, a prewarming method ('prefetch', 'read', or 'buffer'), the relation fork ('main'), the first block number to prewarm, and the last block number to prewarm. The function returns the number of blocks prewarmed. 'prefetch' issues asynchronous prefetch requests, 'read' reads the blocks synchronously, and 'buffer' reads the blocks into the database buffer cache.