Re: Query regarding pg_prewarm extension

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

-Jeremy

--
http://about.me/jeremy_schneider