pg_stat_prefetch_recovery

A statistics view showing information about WAL blocks prefetched by a standby
This entry relates to a feature which was incorporated into PostgreSQL 14 but subsequently withdrawn.

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

pg_stat_prefetch_recovery was added in PostgreSQL 14, but withdrawn with commit c2dc1934.

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_prefetch_recovery (PostgreSQL 14)

                 View "pg_catalog.pg_stat_prefetch_recovery"
     Column      |           Type           | Collation | Nullable | Default 
-----------------+--------------------------+-----------+----------+---------
 stats_reset     | timestamp with time zone |           |          | 
 prefetch        | bigint                   |           |          | 
 skip_hit        | bigint                   |           |          | 
 skip_new        | bigint                   |           |          | 
 skip_fpw        | bigint                   |           |          | 
 skip_seq        | bigint                   |           |          | 
 distance        | integer                  |           |          | 
 queue_depth     | integer                  |           |          | 
 avg_distance    | real                     |           |          | 
 avg_queue_depth | real                     |           |          |
    

Documentation: pg_stat_prefetch_recovery

Change history

Examples

Contents of pg_stat_prefetch_recovery when recovery_prefetch is set to on:

postgres=# SELECT * FROM pg_stat_prefetch_recovery\gx
-[ RECORD 1 ]---+------------------------------
stats_reset     | 2021-04-11 21:42:25.517493+01
prefetch        | 203252
skip_hit        | 640
skip_new        | 99928872
skip_fpw        | 196724
skip_seq        | 69833
distance        | 74672
queue_depth     | 1
avg_distance    | 161000.27
avg_queue_depth | 1.297113

Contents of pg_stat_prefetch_recovery when recovery_prefetch is set to off (disabled):

postgres=# SELECT * FROM pg_stat_prefetch_recovery\gx
-[ RECORD 1 ]---+------------------------------
stats_reset     | 2021-04-11 21:47:31.604959+01
prefetch        | 0
skip_hit        | 0
skip_new        | 0
skip_fpw        | 0
skip_seq        | 0
distance        | 0
queue_depth     | 0
avg_distance    | 0
avg_queue_depth | 0

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

Categories

Statistics / monitoring view, WAL, Withdrawn

See also

recovery_prefetch, recovery_prefetch_fpw, wal_decode_buffer_size, maintenance_io_concurrency