Обсуждение: Add wal_fpi_bytes_[un]compressed to pg_stat_wal

Hi hackers,

I am proposing a patch that adds wal_fpi_bytes_[un]compressed columns
to pg_stat_wal. These columns help us calculate WAL FPI (full page
image) compression rates, confirm the usefulness of wal_compression
and determine which compression algorithms are most effective.

Currently, we must use cumbersome methods to compute the WAL compression rate:
1.  Run the same benchmark twice (once with wal_compression enabled
and one disabled) and compare the wal_bytes values in pg_stat_wal.
However, this value reflects the total WAL reduction, not just the
reduction from full page images. (pg_waldump --stats can provide
similar data but it also requires direct access to WAL files and must
be run on the server.)

2. Run pg_waldump --fullpage and compare the reported compressed size
against the calculated uncompressed size (e.g. 8192 bytes -
hole_length). This computation is inconvenient and also requires WAL
access on the server.

With these patches applied, we can easily compute the FPI compression
rate with the following SQL:
=# SELECT wal_fpi_bytes_compressed / wal_fpi_bytes_uncompressed * 100
AS wal_compression_rate FROM pg_stat_wal;
  wal_compression_rate
-------------------------
 34.07161865906799706100
(1 row)

The 0001 patch adds these columns to pg_stat_wal. The 0002 and 0003
patches add this information to EXPLAIN (WAL) and pg_stat_statements,
respectively. I don't think these additions (0002 and 0003) are
mandatory, so I suggest we focus the discussion on the 0001 patch
first.

Thoughts?

--
Best regards,
Shinya Kato
NTT OSS Center

On Wed, Oct 22, 2025 at 05:04:58PM +0900, Shinya Kato wrote:
> The 0001 patch adds these columns to pg_stat_wal. The 0002 and 0003
> patches add this information to EXPLAIN (WAL) and pg_stat_statements,
> respectively. I don't think these additions (0002 and 0003) are
> mandatory, so I suggest we focus the discussion on the 0001 patch
> first.

We already know the number of FPIs generated.  Hence my take would be
to use only one counter, not two: an aggregated FPI length like in
xlogstats.h as exposed in pg_walinspect.  That should be enough to
offer trends regarding the effects of compression, even if some pages
have holes that are discarded.

> Thoughts?

I would suggest to leave PGSS out of it for now.  We really need to do
something about the number of fields computed, with more GUCs to
disable groups of them, at least, like JIT or the planning parts.  No
objections for the EXPLAIN and pg_stat_wal parts.

The patch can be simpler.  There is no need to pass the calculated
number(s) across multiple functions in the stack, you can just
aggregate the numbers in pgWalUsage directly in XLogRecordAssemble().
The only extra thing to do is that one needs to set
pgstat_report_fixed to true to force the report to pgstats.
--
Michael

On Wed, Oct 22, 2025 at 5:45 PM Michael Paquier <michael@paquier.xyz> wrote:
> We already know the number of FPIs generated.  Hence my take would be
> to use only one counter, not two: an aggregated FPI length like in
> xlogstats.h as exposed in pg_walinspect.  That should be enough to
> offer trends regarding the effects of compression, even if some pages
> have holes that are discarded.

Yeah, I would like to know the trends of FPI compression rates, not
the exact FPI compression rates. So, I agree with Michael, and have
updated the patches.

> I would suggest to leave PGSS out of it for now.  We really need to do
> something about the number of fields computed, with more GUCs to
> disable groups of them, at least, like JIT or the planning parts.  No
> objections for the EXPLAIN and pg_stat_wal parts.

Okay, since I'm not strongly attached to this idea,  I've removed the
0003 patch for now.

> The patch can be simpler.  There is no need to pass the calculated
> number(s) across multiple functions in the stack, you can just
> aggregate the numbers in pgWalUsage directly in XLogRecordAssemble().
> The only extra thing to do is that one needs to set
> pgstat_report_fixed to true to force the report to pgstats.

Thank you for your review. I've implemented this suggestion in the v2 patches.


--
Best regards,
Shinya Kato
NTT OSS Center