Re: Query regarding pg_prewarm extension

On Fri, Dec 13, 2024 at 05:20:05PM -0800, Jeremy Schneider wrote:
> On Fri, 13 Dec 2024 16:16:16 +0530
> Ayush Vatsa <ayushvatsa1810(at)gmail(dot)com> 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 <bruce(at)momjian(dot)us> https://momjian.us
EDB https://enterprisedb.com

Do not let urgent matters crowd out time for investment in the future.