Our pg_stat_wal view currently doesn't expose the number of WAL segments recycled, although this information is already logged by the checkpointer in the database log. For example,
LOG: checkpoint complete: wrote 317 buffers (1.9%); 0 WAL file(s) added, 0 removed, 3 recycled; write=0.003 s, sync=0.001 s, total=0.004 s; sync files=8, longest=0.001 s, average=0.001 s; distance=56015 kB, estimate=56015 kB; lsn=0/6FB8478, redo lsn=0/6FB8420
It might be beneficial to surface the number of WAL files added, removed, and recycled in the pg_stat_wal view. As the wal_init_zero setting is typically static, I don't think it's necessary to report the number of WAL files zero-filled separately. It's important to note that the checkpoint log's count of added WAL files doesn't include files added via XLogFileInit(), which is a significant statistic to consider.
What do others think?
On Wed, Jan 22, 2025 at 10:18 PM Robert Pang <robertpang@google.com> wrote:
>
> On Wed, Jan 15, 2025 at 12:05 PM Andres Freund <andres@anarazel.de> wrote:
> >
> > If you have wal_recycle=true, this overhead will only be paid the first time a
> > WAL segment is used, of course, not after recycling.
>
> Today, our pg_stat_wal view [1] does not report the no. of WAL
> segments recycled. How about if we add a column to report it so users
> can track how many WAL segments are recycled vs. created over a period
> of time?
Maybe even add the full set of (recycled, created, deleted and
zero-filled) so we have a full picture.
And possible duplicate the set for "in the background" and "inline /
stalling the production work" for the operations where it makes sense
(created, zero-filled)
> [1] https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-PG-STAT-WAL-VIEW
>
>
