pg_stat_recovery_prefetch

A statistics view showing information about WAL blocks prefetched by a standby

pg_stat_recovery_prefetch is a statistics view showing information about WAL blocks prefetched by a standby during recovery.

pg_stat_recovery_prefetch was added in PostgreSQL 15.

Usage

WAL prefetching is disabled by default and must be enabled by setting recovery_prefetch to on. It is only available on systems providing an effective posix_fadvise OS-level function.

Definition by PostgreSQL version

pg_stat_recovery_prefetch (PostgreSQL 15)

                View "pg_catalog.pg_stat_recovery_prefetch"
     Column     |           Type           | Collation | Nullable | Default 
----------------+--------------------------+-----------+----------+---------
 stats_reset    | timestamp with time zone |           |          | 
 prefetch       | bigint                   |           |          | 
 hit            | bigint                   |           |          | 
 skip_init      | bigint                   |           |          | 
 skip_new       | bigint                   |           |          | 
 skip_fpw       | bigint                   |           |          | 
 skip_rep       | bigint                   |           |          | 
 wal_distance   | integer                  |           |          | 
 block_distance | integer                  |           |          | 
 io_depth       | integer                  |           |          |
    

Documentation: pg_stat_recovery_prefetch

Change history

Examples

Contents of pg_stat_recovery_prefetch when recovery_prefetch is set to on or try on a standby, and other prerequisites are met:

postgres=# SELECT * FROM pg_stat_recovery_prefetch\gx
-[ RECORD 1 ]--+------------------------------
stats_reset    | 2022-04-30 20:45:30.247883+01
prefetch       | 25
hit            | 676
skip_init      | 733
skip_new       | 14349
skip_fpw       | 59
skip_rep       | 186202
wal_distance   | 0
block_distance | 0
io_depth       | 0

Contents of pg_stat_recovery_prefetch when recovery_prefetch is set to off (disabled), or the node is not a standby:

postgres=# SELECT * FROM pg_stat_recovery_prefetch\gx
-[ RECORD 1 ]--+------------------------------
stats_reset    | 2022-04-30 20:45:26.440076+01
prefetch       | 0
hit            | 0
skip_init      | 0
skip_new       | 0
skip_fpw       | 0
skip_rep       | 0
wal_distance   | 0
block_distance | 0
io_depth       | 0

Note that if recovery_prefetch is enabled then later disabled, pg_stat_recovery_prefetch will continue to display the last available statistics, until the backend statistics are reset.

Categories

Statistics / monitoring view, WAL

See also

recovery_prefetch, wal_decode_buffer_size, maintenance_io_concurrency