Content-Type: multipart/alternative;
 boundary=Apple-Mail-A8E062A3-188C-47F8-BEF3-5A37B8299E2D
Content-Transfer-Encoding: 7bit
From: Jeremy Schneider <schneider@ardentperf.com>
Mime-Version: 1.0 (1.0)
Subject: Re: Any risk or overhead considerations for frequently executing
 queries against catalog tables?
Date: Sun, 26 Jan 2025 09:56:47 -0800
Message-Id: <3FC4230E-AFF1-4BEB-99B1-66D1AA9BBBE4@ardentperf.com>
References: <0C899A03-5B27-4380-9EA9-59DE2047FDFD@gmail.com>
Cc: peter plachta <pplachta@gmail.com>,
 Pavel Stehule <pavel.stehule@gmail.com>,
 pgsql-performance@lists.postgresql.org
In-Reply-To: <0C899A03-5B27-4380-9EA9-59DE2047FDFD@gmail.com>
To: frits.hoogland@gmail.com
Archived-At: 
 <https://www.postgresql.org/message-id/3FC4230E-AFF1-4BEB-99B1-66D1AA9BBBE4%40ardentperf.com>
Precedence: bulk


--Apple-Mail-A8E062A3-188C-47F8-BEF3-5A37B8299E2D
Content-Type: text/plain;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable


> On Jan 25, 2025, at 2:42=E2=80=AFPM, frits.hoogland@gmail.com wrote:
>=20
> One thing I found is that because certain statistics are provided after a q=
uery has run, measuring them in a fine grained way shows a peak for somethin=
g that in reality is taking place over a period of time. =20

+1

I have also encountered this. I think most stats (pg_stat_statements, block h=
it/read counts, object access counts, etc) are all updated at query completi=
on time. I think it=E2=80=99s related to the architecture of the statistics c=
ollector. The main thing I=E2=80=99ve seen change in real time is fields in p=
g_stat_activity (like wait events).

As a result, I=E2=80=99m not sure how useful per-second stats on the other v=
iews are. Mainly I think it will just tell you which second the query comple=
ted, and that=E2=80=99s easy to get from logging (db or app tier). stats fro=
m the other views tend to be most useful when normalized over minutes or hou=
rs.

I wrote out a Postgres observability wishlist two weeks ago and much of it w=
ould probably best be added to pg_stat_activity [1].

I wonder if we might eventually want the underlying pg_stat_activity functio=
n to have an argument to exclude pulling data that=E2=80=99s less frequently=
 accessed, similar to how the pg_stat_statements function can exclude query t=
ext to be more efficient.

-Jeremy


1: https://ardentperf.com/2025/01/08/postgres-per-connection-statistics/


--Apple-Mail-A8E062A3-188C-47F8-BEF3-5A37B8299E2D
Content-Type: text/html;
	charset=utf-8
Content-Transfer-Encoding: quoted-printable

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; charset=3D=
utf-8"></head><body dir=3D"auto"><br><div dir=3D"ltr"><blockquote type=3D"ci=
te">On Jan 25, 2025, at 2:42=E2=80=AFPM, frits.hoogland@gmail.com wrote:<br>=
</blockquote></div><blockquote type=3D"cite"><div dir=3D"ltr"><div><br></div=
><div>One thing I found is that because certain statistics are provided afte=
r a query has run, measuring them in a fine grained way shows a peak for som=
ething that in reality is taking place over a period of time. &nbsp;</div></=
div></blockquote><br><div>+1</div><div><br></div><div>I have also encountere=
d this. I think most stats (pg_stat_statements, block hit/read counts, objec=
t access counts, etc) are all updated at query completion time. I think it=E2=
=80=99s related to the architecture of the statistics collector. The main th=
ing I=E2=80=99ve seen change in real time is fields in pg_stat_activity (lik=
e wait events).</div><div><br></div><div>As a result, I=E2=80=99m not sure h=
ow useful per-second stats on the other views are. Mainly I think it will ju=
st tell you which second the query completed, and that=E2=80=99s easy to get=
 from logging (db or app tier). stats from the other views tend to be most u=
seful when normalized over minutes or hours.</div><div><br></div><div>I wrot=
e out a Postgres observability wishlist two weeks ago and much of it would p=
robably best be added to pg_stat_activity [1].</div><div><br></div><div>I wo=
nder if we might eventually want the underlying pg_stat_activity function to=
 have an argument to exclude pulling data that=E2=80=99s less frequently acc=
essed, similar to how the pg_stat_statements function can exclude query text=
 to be more efficient.</div><div><br></div><div>-Jeremy</div><div><br></div>=
<div><br></div><div>1:&nbsp;<a href=3D"https://ardentperf.com/2025/01/08/pos=
tgres-per-connection-statistics/">https://ardentperf.com/2025/01/08/postgres=
-per-connection-statistics/</a></div><div><br></div><div><br></div></body></=
html>=

--Apple-Mail-A8E062A3-188C-47F8-BEF3-5A37B8299E2D--