Using pg-prewarm to cache database tables

pg_prewarm

pg_prewarm is a postgres extension that reads an entire table – hopefully caching it for you, ready for a benchmark run. When I used this on a large-ish pgbench SF-1000 DB I still saw some disk reads, maybe they were reads of an index? I am not sure.

Anyhow the install/usage is simple. The extensions are shippped with postgres (maybe in contrib) and they live here o my Ubunto Linux: (/usr/share/postgresql/14/extension)

This extension can be used for any postgres table – not just pgbench.

Installation

Check to see if the extension is installed already

pgbench-sf1000=# \dx
                   List of installed extensions
    Name    | Version |   Schema   |         Description
------------+---------+------------+------------------------------
 pg_prewarm | 1.2     | public     | prewarm relation data
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language

Create and use the extension

Installation is per database example for the database names pgbench-sf1000

postgres=# \c pgbench-sf1000;

pgbench-sf1000=# create extension pg_prewarm;

pgbench-sf1000=# select pg_prewarm('pgbench_accounts');

pgbench-sf1000=# \d
              List of relations
 Schema |       Name       | Type  |  Owner
--------+------------------+-------+----------
 public | pgbench_accounts | table | postgres
 public | pgbench_branches | table | postgres
 public | pgbench_history  | table | postgres
 public | pgbench_tellers  | table | postgres