pg_stat_recovery_prefetch
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 16)
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
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
- PostgreSQL 15
- added (commit 5dc0418f)
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.
References
- PostgreSQL 15 documentation: pg_stat_recovery_prefetch