On Fri, Dec 13, 2024 at 05:20:05PM -0800, Jeremy Schneider wrote:
> On Fri, 13 Dec 2024 16:16:16 +0530
> Ayush Vatsa <[email protected]> wrote:
>
> > How can I decide which range of pages to prewarm?
> > I assume that it is related to hot pages in the relation,
> > but how can I identify which pages are likely to be hot
> > before they are even in the buffer cache?
> > Additionally, since tuples within a page can move to
> > different pages over time (due to operations like VACUUM FULL or
> > REINDEX), how should I handle this when selecting the pages to
> > prewarm?
>
> For my part, I've only used the block offsets when I wanted to fire off
> several jobs in parallel, attempting to prewarm a relation faster. I've
> never tried to track the location of specific rows for purposes of
> prewarming.
>
> You might try the "autoprewarm" feature. After adding pg_prewarm to
> your shared_preload_libraries, it will automatically keep track of the
> contents of the buffer cache and after a restart it will automatically
> prewarm the buffer cache with the blocks that were there before.
>
> https://www.enterprisedb.com/blog/autoprewarm-new-functionality-pgprewarm
>
> Alternatively you could just prewarm a few of your most important hot
> tables and indexes with a script after restarts.
>
> For most smaller databases, slightly slower performance for a short
> period after startup isn't a problem - while reading blocks from disk
> for the first time. After the first read, blocks that are frequently
> accessed will remain in the cache. The Postgres cache management
> algorithm works well in general.
>
> This is my two cents, anyway
It feels like we should document what the block range is used for, so
attached is a doc patch to do that.
--
Bruce Momjian <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Do not let urgent matters crowd out time for investment in the future.
diff --git a/doc/src/sgml/pgprewarm.sgml b/doc/src/sgml/pgprewarm.sgml
index 75f45b91b67..6907f214eb8 100644
--- a/doc/src/sgml/pgprewarm.sgml
+++ b/doc/src/sgml/pgprewarm.sgml
@@ -35,8 +35,10 @@ pg_prewarm(regclass, mode text default 'buffer', fork text default 'main',
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.
+ means prewarm through the last block in the relation). The block
+ range allows a single relation to be loaded in parallel using multiple
+ concurent function calls. The return value is the number of blocks
+ prewarmed.
</para>
<para>
