Mastering PostgreSQL 12 - Third Edition

Prewarming caches with pg_prewarm

When PostgreSQL operates normally, it tries to cache important data. The shared_buffers variable is important as it defines the size of the cache that's managed by PostgreSQL. The problem now is this: if you restart the database server, the cache managed by PostgreSQL will be lost. Maybe the operating system still has some data to reduce the impact on disk wait, but in many cases, this won't be enough. The solution to this problem is called the pg_prewarm extension:

test=# CREATE EXTENSION pg_prewarm;CREATE EXTENSION

This extension deploys a function that allows us to explicitly prewarm the cache whenever this is needed:

test=# \xExpanded display is on.test=# \df *prewa*List of functions-[ RECORD 1 ]Schema ...