MIME-Version: 1.0
References: 
 <CAHg+QDcE2zrcQqA2aE2SNxVnPNHG5WkOeDve8G2+gLT20_G3JA@mail.gmail.com>
 <acXylU7iQ4P_YbGD@paquier.xyz>
In-Reply-To: <acXylU7iQ4P_YbGD@paquier.xyz>
From: SATYANARAYANA NARLAPURAM <satyanarlapuram@gmail.com>
Date: Fri, 27 Mar 2026 11:26:43 -0700
Message-ID: 
 <CAHg+QDdsBwHHDJ3sm2rsK+vzQCqRQZR8sbZF2Y2NyduVE2JM1Q@mail.gmail.com>
Subject: 
 =?UTF-8?Q?Re=3A_=5BProposal=5D_pg=5Fstat=5Fwal=5Frecords_=E2=80=93_per=2Drecord=2Dty?=
	=?UTF-8?Q?pe_WAL_generation_statistics?=
To: Michael Paquier <michael@paquier.xyz>
Cc: PostgreSQL Hackers <pgsql-hackers@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000484a99064e05a591"
Archived-At: 
 <https://www.postgresql.org/message-id/CAHg%2BQDdsBwHHDJ3sm2rsK%2BvzQCqRQZR8sbZF2Y2NyduVE2JM1Q%40mail.gmail.com>
Precedence: bulk

--000000000000484a99064e05a591
Content-Type: text/plain; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

Hi  Michael,

Thank you for your feedback!

On Thu, Mar 26, 2026 at 7:59=E2=80=AFPM Michael Paquier <michael@paquier.xy=
z> wrote:

> On Thu, Mar 26, 2026 at 05:29:25PM -0700, SATYANARAYANA NARLAPURAM wrote:
> > Postgre already has pg_stat_wal for aggregate WAL volume (bytes,
> full-page
> > images, buffers), and pg_walinspect (superuser access required) for
> > post-hoc forensic analysis of individual WAL segments. But I don't see =
a
> > lightweight, observability tool that answers in real time which record
> > types are responsible for the WAL. Additionally, pg_walinspect runs
> against
> > on-disk WAL files, which is expensive. This view will be useful for
> > monitoring systems to poll cheaply.
> >
> > *Use cases:*
> > WAL volume investigation: see which record types dominate WAL generatio=
n
> in
> > real time without touching disk.
> > Monitoring integration: Prometheus/Grafana can poll the view to track W=
AL
> > composition over time and alert on anomalies.
> > Replication tuning: identify whether WAL volume is dominated by data
> > changes, index maintenance, FPIs, or vacuum activity to guide tuning.
> > Extension debugging: custom WAL resource managers get visibility
> > automatically.
>
> Why is it useful to have access to this information in live for
> monitoring purposes, divided by RMGR?


Per-RMGR breakdown is essentially a real-time X-ray of what the database
is actually doing. Existing view help us understand what queries were
submitted,
but what physical operations resulted from them can be seen with this view.


> What do you define as an
> anomaly in this context and what can be changed on the server side to
> get out of an anomaly, based on the definition you would give to it?
>

A few examples, HOT ratio dropped, BTree page splits, some app adding
logical decoding messages, GIN generates more WAL than the corresponding
heap modifications, high lock counts etc.


>
> The current WAL and IO stats are directly useful because they provide
> numbers about flush, read and write quantity and timings.  These are
> useful metrics for benchmarking.
>
> This proposal only informs about the number of records, and we have a
> various number of record types that have a variable length, that can
> influence the distribution of the data written on disk.
>

yeah, that was a downside, didn't add sizes to keep the overhead less.

As a whole, I am doubtful that this information is worth counting in
> live, eating a stats kind ID.  One could also implement a background
> worker that provides more advanced aggregate stats outside the WAL
> insert path, with a custom stats kind.  No need to have that into
> core, especially if the code that increments the stats eats more and
> more cycles.


Your argument makes sense to me, I was a bit hesitant on
writing a background worker because of the potential side effects of tailin=
g
 the WAL. Let me try a different approach by implementing an ondemand
sniffing of the WAL, which can be implemented as an extension without
changes to core. Do you have thoughts on making it a contrib module or
modify existing pg_walinspect to accommodate these requirements?

Thanks,
Satya

--000000000000484a99064e05a591
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><br></div>Hi=C2=A0 Michael,<div><br></div=
><div>Thank you for your feedback!<div><br><div class=3D"gmail_quote gmail_=
quote_container"><div dir=3D"ltr" class=3D"gmail_attr">On Thu, Mar 26, 2026=
 at 7:59=E2=80=AFPM Michael Paquier &lt;<a href=3D"mailto:michael@paquier.x=
yz">michael@paquier.xyz</a>&gt; wrote:<br></div><blockquote class=3D"gmail_=
quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,=
204);padding-left:1ex">On Thu, Mar 26, 2026 at 05:29:25PM -0700, SATYANARAY=
ANA NARLAPURAM wrote:<br>
&gt; Postgre already has pg_stat_wal for aggregate WAL volume (bytes, full-=
page<br>
&gt; images, buffers), and pg_walinspect (superuser access required) for<br=
>
&gt; post-hoc forensic analysis of individual WAL segments. But I don&#39;t=
 see a<br>
&gt; lightweight, observability tool that answers in real time which record=
<br>
&gt; types are responsible for the WAL. Additionally, pg_walinspect runs ag=
ainst<br>
&gt; on-disk WAL files, which is expensive. This view will be useful for<br=
>
&gt; monitoring systems to poll cheaply.<br>
&gt; <br>
&gt; *Use cases:*<br>
&gt; WAL volume investigation: see which record types dominate WAL generati=
on in<br>
&gt; real time without touching disk.<br>
&gt; Monitoring integration: Prometheus/Grafana can poll the view to track =
WAL<br>
&gt; composition over time and alert on anomalies.<br>
&gt; Replication tuning: identify whether WAL volume is dominated by data<b=
r>
&gt; changes, index maintenance, FPIs, or vacuum activity to guide tuning.<=
br>
&gt; Extension debugging: custom WAL resource managers get visibility<br>
&gt; automatically.<br>
<br>
Why is it useful to have access to this information in live for<br>
monitoring purposes, divided by RMGR?=C2=A0</blockquote><div>=C2=A0</div><d=
iv><div>Per-RMGR breakdown is essentially a real-time X-ray of what the dat=
abase</div><div>is actually doing. Existing view help us understand what qu=
eries were submitted,=C2=A0</div><div>but what physical operations resulted=
 from them can be seen with this view.</div></div><div><br></div><div>=C2=
=A0</div><blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8e=
x;border-left:1px solid rgb(204,204,204);padding-left:1ex">What do you defi=
ne as an<br>
anomaly in this context and what can be changed on the server side to<br>
get out of an anomaly, based on the definition you would give to it?<br></b=
lockquote><div><br></div><div>A few examples,=C2=A0HOT ratio dropped, BTree=
 page splits, some app adding=C2=A0</div><div>logical decoding messages, GI=
N generates more WAL than the corresponding=C2=A0</div><div>heap modificati=
ons, high lock counts etc.</div><div>=C2=A0</div><blockquote class=3D"gmail=
_quote" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204=
,204);padding-left:1ex">
<br>
The current WAL and IO stats are directly useful because they provide<br>
numbers about flush, read and write quantity and timings.=C2=A0 These are<b=
r>
useful metrics for benchmarking.<br>
<br>
This proposal only informs about the number of records, and we have a<br>
various number of record types that have a variable length, that can<br>
influence the distribution of the data written on disk.<br></blockquote><di=
v><br></div><div>yeah, that was a downside, didn&#39;t add sizes to keep th=
e overhead less.</div><div><br></div><blockquote class=3D"gmail_quote" styl=
e=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);paddin=
g-left:1ex">
As a whole, I am doubtful that this information is worth counting in<br>
live, eating a stats kind ID.=C2=A0 One could also implement a background<b=
r>
worker that provides more advanced aggregate stats outside the WAL<br>
insert path, with a custom stats kind.=C2=A0 No need to have that into<br>
core, especially if the code that increments the stats eats more and<br>
more cycles.</blockquote><div><br></div><div>Your argument makes sense to m=
e, I was a bit hesitant on</div><div>writing a background worker because of=
 the potential side effects of tailing</div><div>=C2=A0the WAL. Let me try =
a different approach by implementing an ondemand</div><div>sniffing of the =
WAL, which can be implemented as an extension without</div><div>changes to =
core. Do you have thoughts on making it a contrib module or</div><div>modif=
y existing pg_walinspect to accommodate=C2=A0these requirements?</div><div>=
<br></div><div>Thanks,</div><div>Satya</div></div></div></div></div>

--000000000000484a99064e05a591--