pg_prewarm extention to Pre-warming the buffer cache in PostgreSQL

The pg_prewarm module provides to load relation data into either the operating system buffer cache or the PostgreSQL buffer cache.
It is available for PostgreSQL 9.4 or higher releases.

You can load the most important tables to the cache when the postgresql was restarted.

First install postgresql contrib package

example:
yum install postgresql96-contrib
apt-get install postgresql-contrib-9.6

Login to database and create the extension.

psql -U user databasename
create extension pg_prewarm;

enable prewarn on desired tables
SELECT pg_prewarm(‘tablename’);

example
SELECT pg_prewarm(‘languages’);
SELECT pg_prewarm(‘messages’);

 

You have to run SELECT pg_prewarm(‘tablename’); whenever you restart postgresql database. You can add SELECT statements into a bash script and call it when the database become online.

References:

https://www.postgresql.org/docs/9.4/static/pgprewarm.html

https://blog.dbi-services.com/pre-warming-the-buffer-cache-in-postgresql/

https://www.depesz.com/2014/01/10/waiting-for-9-4-pg_prewarm-a-contrib-module-for-prewarming-relationd-data/

 

ismail yenigul

 

Advertisements
pg_prewarm extention to Pre-warming the buffer cache in PostgreSQL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s