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
