PostgreSQL Extensions: pg_prewarm

​pg_prewarm is a PostgreSQL extension that allows developers to load relation data into either the operating system's buffer cache or PostgreSQL's shared buffers. This is particularly useful for reducing the time to warm up the cache after a database restart, hence the name 'pg_prewarm.'​

Installing the pg_prewarm Extension

​To install the pg_prewarm extension, you need to have PostgreSQL installed on your system. Once you have PostgreSQL, follow these steps:

1. Open your PostgreSQL command line interface.

2. Connect to the database where you want to install the extension.

3. Run the following command: CREATE EXTENSION pg_prewarm; ​Copy

This command will install the pg_prewarm extension in your current database.

​Using the pg_prewarm Extension

​Once you have installed the pg_prewarm extension, you can use it to prewarm tables in your database, which you know you will be read soon. Here's how:

1. Connect to your database.

2. Run the following command:

SELECT pg_prewarm('your_table_name'); Copy

Replace 'your_table_name' with the name of the table you want to prewarm. This command will load the data from the specified table into the cache.

Time-series use cases for the pg_prewarm extension

The pg_prewarm extension is handy in time-series databases where data is continuously added chronologically. By preloading the most recent chunks of a hypertable into the cache after a restart, you can significantly reduce the time to execute queries on these tables.

Using pg_prewarm with Timescale and time-series data

​If you're using Timescale, a time-series cloud database built on PostgreSQL, you can use the pg_prewarm extension to improve the performance of your database. Here's how:

1. Install the pg_prewarm extension in your Timescale database. On Timescale, you can find available extensions by going to Operations > Extensions from your service overview, which will also give you installation instructions.

2. Use the pg_prewarm function to prewarm your hypertables.

SELECT pg_prewarm(format('%s.%s', chunk_schema, chunk_name)) FROM   (SELECT * FROM timescaledb_information.chunks     WHERE hypertable_name = 'your_hypertable_name' AND                   range_end > now() - interval '1 day   ) a;

​Replace 'your_hypertable_name' with the name of your hypertable and ‘1 day’ with the lookback period to match chunks. In this example, you will prewarm any chunks that cover the last day.

This command will load the data from the specified hypertable chunks into the cache, improving the performance of your queries on the most recent day of data in the hypertable.

Learn More

Looking to learn more about extending PostgreSQL for scale and times-series scenarios? Check out the tutorials in the Timescale documentation to get started.