Content-Type: multipart/alternative;
 boundary=Apple-Mail-30F8AE05-2BB8-44DC-BD5B-E8C17F40CF05
Content-Transfer-Encoding: 7bit
From: peter plachta <pplachta@gmail.com>
Mime-Version: 1.0 (1.0)
Subject: Re: Any risk or overhead considerations for frequently executing
 queries against catalog tables?
Date: Sat, 25 Jan 2025 14:08:29 -0800
Message-Id: <62B9D71B-DD8C-45DE-8A15-1C1D716CA645@gmail.com>
References: <1C9EF412-CED3-40F6-9081-A9464CC87C4B@gmail.com>
Cc: Pavel Stehule <pavel.stehule@gmail.com>,
 pgsql-performance@lists.postgresql.org
In-Reply-To: <1C9EF412-CED3-40F6-9081-A9464CC87C4B@gmail.com>
To: Frits Hoogland <frits.hoogland@gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/62B9D71B-DD8C-45DE-8A15-1C1D716CA645%40gmail.com>
Precedence: bulk


--Apple-Mail-30F8AE05-2BB8-44DC-BD5B-E8C17F40CF05
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">DataDog =E2=80=94 which implements such met=
rics for Postgres - has ran into multiple issues doing this type of thing. Y=
ou may be able to search their bugs / repo to see what they were. I just can=
=E2=80=99t remember them off hand, it=E2=80=99s been a while.<div><br id=3D"=
lineBreakAtBeginningOfSignature"><div dir=3D"ltr">Sent from my iPhone</div><=
div dir=3D"ltr"><br><blockquote type=3D"cite">On Jan 25, 2025, at 12:01=E2=80=
=AFPM, Frits Hoogland &lt;frits.hoogland@gmail.com&gt; wrote:<br><br></block=
quote></div><blockquote type=3D"cite"><div dir=3D"ltr">=EF=BB=BF<meta http-e=
quiv=3D"content-type" content=3D"text/html; charset=3Dutf-8">I am looking at=
 whether sampling key database catalog information per second would have any=
 drawback whatsoever.<div>I think you're saying that you think isn't the cas=
e, except maybe for pg_database, and I figure that is because of the frozen a=
nd multi xact fields per database.</div><div><br></div><div>If the database c=
lient application is too unpredictable to know what SQL it will produce, the=
n having runtime data available at that granularity, so it can be reasonably=
 constructed what is going on is very convenient and allows tremendous insig=
ht. It would also allow usage of the waitevents to spot any weird behavior, s=
uch as short-lived peaks. (pg_stat_statements can do that on a busy database=
, for example).</div><div>And if there is no known drawback, if such a low i=
nterval can be organized: why not? I am not saying you are doing it wrong, t=
his is about trying to figure out what are the borders of what would be tech=
nically possible without unreasonably affecting the database, a thought expe=
riment.</div><div><br></div><div>If course the gathered data needs to be org=
anized so that you don't swamp in it, and it shouldn't lead to the monitorin=
g data swamping the system, either in memory or on disk, but that is a given=
.</div><div><br></div><div>Why would per second be too much for locks? Is th=
ere overhead to select from pg_locks, or pg_blocking_pids()?</div><div><br><=
/div><div>Again, please realise I am happy and appreciative of the time you t=
ake, I am toying with the above described idea.</div><div><br id=3D"lineBrea=
kAtBeginningOfMessage"><div>
<div dir=3D"auto" style=3D"caret-color: rgb(0, 0, 0); color: rgb(0, 0, 0); l=
etter-spacing: normal; text-align: start; text-indent: 0px; text-transform: n=
one; white-space: normal; word-spacing: 0px; -webkit-text-stroke-width: 0px;=
 text-decoration: none; word-wrap: break-word; -webkit-nbsp-mode: space; lin=
e-break: after-white-space;"><div><b>Frits Hoogland</b></div><div><br></div>=
</div><br class=3D"Apple-interchange-newline"><br class=3D"Apple-interchange=
-newline">

</div>
<div><br><blockquote type=3D"cite"><div>On 25 Jan 2025, at 19:18, Pavel Steh=
ule &lt;pavel.stehule@gmail.com&gt; wrote:</div><br class=3D"Apple-interchan=
ge-newline"><div><div dir=3D"ltr"><div dir=3D"ltr"><div>Hi<br></div><br><div=
 class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"gmail=
_attr">so 25. 1. 2025 v&nbsp;18:00 odes=C3=ADlatel Frits Hoogland &lt;<a hre=
f=3D"mailto:frits.hoogland@gmail.com">frits.hoogland@gmail.com</a>&gt; napsa=
l:<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"><div>Thank you P=
avel, that is really useful. I can imagine other people thinking about getti=
ng fine grained data from postgres might wonder the same as I do about this.=
<div>And really from a computer's perspective I would say that once a second=
 isn't really a high frequency?</div></div></blockquote><div><br></div><div>=
I usually work with minute sampling and usually it is good enough (statistic=
s are cumulative, so you can lose the timestamp, but you never lose data.</d=
iv><div><br></div><div>Only when we try to investigate some special case, th=
en I use second sampling. When you investigate lock issues, then seconds are=
 too much<br></div><div><br></div><div>Regards</div><div><br></div><div>Pave=
l<br></div><div>&nbsp;</div><blockquote class=3D"gmail_quote" style=3D"margi=
n:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);padding-left:1ex"=
><div><div>If I time the amount of time that these queries take, it's around=
 20ms (local connection), so there is a relative long time of all the object=
s including pg_database are not actively queried.<br><div><br></div><div>I g=
it grepped the sourcecode, it seems that there is a rowexclusive lock for pg=
_database manipulation in case of addition, removal and change of a database=
 in dbcommands.c, but I do think your reasoning is based on the columns datf=
rozenxid and datminmxid?</div><div><br></div><div>There is a lock for updati=
ng the frozenxid and mxid for a database in (vacuum.c:LockDatabaseFrozenIds,=
 ExclusiveLock), but it seems a select should play nice with that?</div><div=
><br></div><div>btw, it's interesting to see that both datfrozenxid and datm=
inmxid are in place updated, with no read consistency provided.</div><div><b=
r id=3D"m_540779283335872413lineBreakAtBeginningOfMessage"><div>
<div dir=3D"auto" style=3D"letter-spacing: normal; text-align: start; text-i=
ndent: 0px; text-transform: none; white-space: normal; word-spacing: 0px; te=
xt-decoration: none;"><div><b>Frits Hoogland</b></div><div><br></div></div><=
br><br>

</div>
<div><br><blockquote type=3D"cite"><div>On 25 Jan 2025, at 14:32, Pavel Steh=
ule &lt;<a href=3D"mailto:pavel.stehule@gmail.com" target=3D"_blank">pavel.s=
tehule@gmail.com</a>&gt; wrote:</div><br><div><div dir=3D"ltr"><div>Hi<br></=
div><br><div class=3D"gmail_quote"><div dir=3D"ltr" class=3D"gmail_attr">so 2=
5. 1. 2025 v&nbsp;12:23 odes=C3=ADlatel Frits Hoogland &lt;<a href=3D"mailto=
:frits.hoogland@gmail.com" target=3D"_blank">frits.hoogland@gmail.com</a>&gt=
; napsal:<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"><div>For=
 monitoring database behaviour and trying to build an history of activity, i=
f I would create an application that creates a single connection and execute=
 something like:<div>select * from pg_stat_activity;</div><div>select * from=
 pg_stat_database;</div><div>select * from pg_stat_bgwriter;</div><div>selec=
t * from pg_stat_wal;</div><div>select * from pg_settings;</div><div>select *=
 from pg_database;</div><div>For which the query is prepared, and execute th=
at every 1 second, would there be any realistic danger or overhead that shou=
ld be considered?</div><div>My thinking is that the data for these catalogs a=
re all in shared memory and when executed serially and do not cause any sign=
ificant resources to be taken?</div></div></blockquote><div><br></div><div>T=
he queries to all tables excluding pg_database every 1 sec will have probabl=
y zero impact to performance.</div><div><br></div><div>I am not sure about p=
g_database - it is a very important table, and your query can block operatio=
ns that need exclusive lock to this table. So theoretically, there can be so=
me impact to performance.</div><div><br></div><div>Regards</div><div><br></d=
iv><div>Pavel<br></div><div>&nbsp;<br></div><blockquote class=3D"gmail_quote=
" style=3D"margin:0px 0px 0px 0.8ex;border-left:1px solid rgb(204,204,204);p=
adding-left:1ex"><div><div><br></div><div>Thanks,</div><div><br></div><div><=
div>
<div dir=3D"auto" style=3D"letter-spacing:normal;text-align:start;text-inden=
t:0px;text-transform:none;white-space:normal;word-spacing:0px;text-decoratio=
n:none"><div><b>Frits Hoogland</b></div><div><br></div></div><br><br>

</div>

<br></div></div></blockquote></div></div>
</div></blockquote></div><br></div></div></div></blockquote></div></div></di=
v>
</div></blockquote></div><br></div></div></blockquote></div></body></html>=

--Apple-Mail-30F8AE05-2BB8-44DC-BD5B-E8C17F40CF05--