Add wal_fpi_bytes_[un]compressed to pg_stat_wal

From: Shinya Kato <shinya11(dot)kato(at)gmail(dot)com>
To: pgsql-hackers <pgsql-hackers(at)postgresql(dot)org>
Subject: Add wal_fpi_bytes_[un]compressed to pg_stat_wal
Date: 2025-10-22 08:04:58
Message-ID: CAOzEurQtZEAfg6P0kU3Wa-f9BWQOi0RzJEMPN56wNTOmJLmfaQ@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

Attachment Content-Type Size
v1-0001-Add-wal_fpi_bytes_-un-compressed-to-pg_stat_wal.patch application/octet-stream 11.9 KB
v1-0002-Add-wal_fpi_bytes_-un-compressed-to-EXPLAIN-WAL.patch application/octet-stream 4.6 KB
v1-0003-pg_stat_statements-Add-wal_fpi_bytes_-un-compress.patch application/octet-stream 17.4 KB

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message jian he 2025-10-22 08:08:49 Re: [PATCH] Add pg_get_trigger_ddl() to retrieve the CREATE TRIGGER statement
Previous Message Nazir Bilal Yavuz 2025-10-22 08:04:18 Re: Improve docs syntax checking and enable it in the meson build