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 |
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 |