Autoprewarm: A New Functionality in pg_prewarm

September 25, 2018

Contributed by Mithun Chicklore Yogendra


In PostgreSQL 11, a new functionality of autoprewarm has been added into the contrib module pg_prewarm.  This automatically warms the shared buffers with the same pages held before the last server restart. To accomplish this, Postgres now has a background worker to periodically record the contents of the shared buffers in the file -- “autoprewarm.blocks”. Next,  it will reload those pages after the server restart.

Now, that we know the basic functionality of autoprewarm, let's dive into the details of how to use this feature.

How to enable Autoprewarm?

To enable autoprewarm, set "shared_preload_libraries" with pg_prewarm. This parameter requires the server restart to take effect.

postgres=# alter system set shared_preload_libraries = 'pg_prewarm';

After the restart, there will be a new background worker process -- "autoprewarm master" for automatic prewarming of shared buffers.

$ ps -aef | grep autoprewarm
mithuncy   5453 5446  8 02:56 ?    00:01:15 postgres: autoprewarm master

To be precise, "autoprewarm master" will periodically record the information about pages in shared buffers in the file "$PGDATA/autoprewarm.blocks". The frequency of updating "autoprewarm.blocks" is decided by a configuration parameter pg_prewarm.autoprewarm_interval. Once the server restarts, the master will read "autoprewarm.blocks" and sort the list of pages to be prewarmed. Next, it will launch a worker for each database, one-at-a-time. Then the per-database worker aka autoprewarm worker will load the pages that belong to their database. Once the prewarm is completed, the master will keep updating the "autoprewarm.blocks" periodically.

$ ps -aef | grep autoprewarm
mithuncy   6377 6370  5 03:50 ? 00:00:00 postgres: autoprewarm master  
mithuncy   6393 6370 15 03:50 ?    00:00:00 postgres: autoprewarm worker

To see the effects of prewarming, I re-ran the following query after a server restart.

postgres=# explain (analyze, buffers) select count(*) from apw_tests;
                          QUERY PLAN
Aggregate  (cost=188.44..188.45 rows=1 width=8) (actual time=1.804..1.804 rows=1 loops=1)
   Buffers: shared hit=45
    -> Seq Scan on apw_tests  (cost=0.00..159.75 rows=11475 width=0) (actual time=0.013..1.071 rows=10000 loops=1)
       Buffers: shared hit=45

Here you see all the buffers are hit. That means we found them in the shared buffer cache and none of them was loaded from disk. Without autoprewarm those pages will be loaded from disk after a restart.

postgres=# explain (analyze, buffers) select count(*) from apw_tests;
                             QUERY PLAN
Aggregate  (cost=170.00..170.01 rows=1 width=8) (actual time=1.798..1.798 rows=1 loops=1)
   Buffers: shared read=45
   -> Seq Scan on apw_tests  (cost=0.00..145.00 rows=10000 width=0) (actual time=0.021..1.118 rows=10000 loops=1)
      Buffers: shared read=45


Test setup: pg_bench prepared read only tests for 1 client.

Machine: x86_64 8 core Intel machine with 16GB ram.

Server setup : shared_buffers = 8GB, pgbench scale_factor=300 (entire data fits into shared buffers)

TPS was measured at every 5 seconds of the run. From the tests, it was observed with autoprewarm system produced peak performance right immediately after the restart. When autoprewarm was disabled it took almost 300 secs to reach same peak TPS.


Inside "autoprewarm.blocks"

The contents of the file are in a readable format.


The first line says about the total number of pages and each line after that gives information about a page. Each page is uniquely represented by database oid, tablespace oid, relfilenode of the relation, fork file number, and the block number.


Utility Functions

  • autoprewarm_start_worker() RETURNS void
    Use this to launch the autoprewarm worker if autoprewarm was not configured during the server startup.
  • autoprewarm_dump_now() RETURNS int8
    This updates autoprewarm.blocks immediately. This may be useful if the autoprewarm worker is not running currently but it is expected to be used at the next server restart.
Share this

Relevant Blogs

Finding memory leaks in Postgres C code

I spent the last week looking for a memory leak in Postgres’s WAL Sender process. I spent a few days getting more acquainted with Valgrind and gcc/clang sanitizers, but ultimately...
March 27, 2024

More Blogs

Let's Workshop an Unplanned Postgres Outage

Nobody likes it when their database goes down for any reason, especially if it was unintentional. Sometimes companies are open about their experiences and provide post-mortems for the world to...
July 07, 2023