On 20th of December, Robert Haas committed patch:

pg_prewarm, a contrib module for prewarming relationd data.
Patch by me.  Review by Álvaro Herrera, Amit Kapila, Jeff Janes,
Gurjeet Singh, and others.

This is contrib extension, so if you'd want to use it – you will have to “create extension pg_prewarm" before.

When we're restarting/rebooting or doing any other kind of low-level operations caches are cleared. This means that after restart/reboot queries are usually slower.

There were counter measures, of course, but running “find $PGDATA/base -type f -exec cat {} +" is not really nice/intuitive.

Now, we have a better tool.

For starters – it can be used from the SQL connection. Second – if we want to load to caches some specific table, we no longer have to know/understand the number directories/files in $PGDATA.

So, how to use it?

It's very, very simple:

$ select pg_prewarm('plans');
(1 row)

But there is more to it. First argument, the only one I used, is table name (well, regclass, but that's not that important).

Second, called “mode" can be one of:

  • prefetch : makes kernel, asynchronously, read the file. I.e. in background. This will put content of the file in kernel cache for disk, and it works in background. But – it doesn't work on all platforms, so it can error out.
  • read : end result is as with prefetch, but it's done synchronously, and will work everywhere.
  • buffer : this will actually make Pg load the data to shared_buffers – that is to the closest (to Pg) level of cache. That's also the default.

Third argument is called “fork". Long story short – you generally don't need to worry about it. For completeness sake, possible values (for now, it might change in future) are: “main", “fsm", “vm". Default is “main", and I generally can't imagine that you'd need to prewarm the other ones, but just in case – it's there.

Fourth and fifth arguments, are numbers, which specify range of pages to prewarm. By default the whole relation is prewarmed, but you can decide, for example, to prewarm just the last 1000 pages:

select pg_prewarm(
    first_block := (
        select pg_relation_size('plans') / current_setting('block_size')::int4 - 1000

That will definitely be very useful. Thanks Robert.

  1. One comment

  2. # vikrant patil
    Aug 31, 2017

    Facing error as
    “starting block number must be between 0 and -1”

    Call is as below:
    SELECT pg_prewarm(‘schema_name.table_name’,’buffer’,’main’,1,440 );

    This table is partitioned table based on year-month and I want only current and previous months data loaded in memory and there are 440 records in these partitions.

Leave a comment