I want to know what will be impact enabling pg_prewarm extension on postgresql where DB have MV and while refreshing MV concurrently would this extention create extra overhead or any performance issue on DB ?
PG Version – 11
Thanks in advance
New learning where want to know if this will be useful or not.
2
Answers
Depends on the type of data you store in your database really, if you store something that is frequently accessed, then it is a good idea to enable pg_prewarm as it creates a cache of the data and loads it faster, saving you some time. However, if you have some data which is rarely accessed like log files and such, it will slow you down for other tasks as it stores unnecessary data onto the cache everytime it loads up. If you’re refreshing MVs concurrently, it might affect the buffer cache, as both MV refresh and pg_prewarm are performing I/O operation.
pg_prewarm consumes IO. That might be beneficial when other processes find the data needed in cache, because of the work pg_prewarm did for you. If not, pg_prewarm turns into a burden and your processes become slower.
Without any information about your system, your processes, and most important, the problem pg_prewarm should solve, we can’t help you in the decision if you should install this extension or not.
What we do know, is that PostgreSQL version 11 will be EOL in the next few months. Migrating to a newer version should have some priority.