From: Frits Hoogland <frits.hoogland@gmail.com>
Message-Id: <0E68C6B4-391F-481E-9764-B0D2FE4CF15D@gmail.com>
Content-Type: multipart/alternative;
	boundary="Apple-Mail=_6321713D-D65E-4459-82B4-D30232398673"
Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.300.87.4.3\))
Subject: Re: Any risk or overhead considerations for frequently executing
 queries against catalog tables?
Date: Sat, 25 Jan 2025 17:59:55 +0100
In-Reply-To: 
 <CAFj8pRANyuda5uf9KR4jY2dGYJ0SDDNhyjRWvnaCRkjffX-ydw@mail.gmail.com>
Cc: pgsql-performance@lists.postgresql.org
To: Pavel Stehule <pavel.stehule@gmail.com>
References: <F3953D11-A866-49CE-80C3-F00469A2521A@gmail.com>
 <CAFj8pRANyuda5uf9KR4jY2dGYJ0SDDNhyjRWvnaCRkjffX-ydw@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/0E68C6B4-391F-481E-9764-B0D2FE4CF15D%40gmail.com>
Precedence: bulk


--Apple-Mail=_6321713D-D65E-4459-82B4-D30232398673
Content-Transfer-Encoding: quoted-printable
Content-Type: text/plain;
	charset=utf-8

Thank you Pavel, that is really useful. I can imagine other people =
thinking about getting fine grained data from postgres might wonder the =
same as I do about this.
And really from a computer's perspective I would say that once a second =
isn't really a high frequency?
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 objects =
including pg_database are not actively queried.

I git 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 datfrozenxid and datminmxid?

There is a lock for updating the frozenxid and mxid for a database in =
(vacuum.c:LockDatabaseFrozenIds, ExclusiveLock), but it seems a select =
should play nice with that?

btw, it's interesting to see that both datfrozenxid and datminmxid are =
in place updated, with no read consistency provided.

Frits Hoogland


> On 25 Jan 2025, at 14:32, Pavel Stehule <pavel.stehule@gmail.com> =
wrote:
>=20
> Hi
>=20
> so 25. 1. 2025 v 12:23 odes=C3=ADlatel Frits Hoogland =
<frits.hoogland@gmail.com <mailto:frits.hoogland@gmail.com>> napsal:
>> For monitoring database behaviour and trying to build an history of =
activity, if I would create an application that creates a single =
connection and execute something like:
>> select * from pg_stat_activity;
>> select * from pg_stat_database;
>> select * from pg_stat_bgwriter;
>> select * from pg_stat_wal;
>> select * from pg_settings;
>> select * from pg_database;
>> For which the query is prepared, and execute that every 1 second, =
would there be any realistic danger or overhead that should be =
considered?
>> My thinking is that the data for these catalogs are all in shared =
memory and when executed serially and do not cause any significant =
resources to be taken?
>=20
> The queries to all tables excluding pg_database every 1 sec will have =
probably zero impact to performance.
>=20
> I am not sure about pg_database - it is a very important table, and =
your query can block operations that need exclusive lock to this table. =
So theoretically, there can be some impact to performance.
>=20
> Regards
>=20
> Pavel
> =20
>>=20
>> Thanks,
>>=20
>> Frits Hoogland
>>=20
>>=20
>>=20
>>=20


--Apple-Mail=_6321713D-D65E-4459-82B4-D30232398673
Content-Transfer-Encoding: quoted-printable
Content-Type: text/html;
	charset=utf-8

<html><head><meta http-equiv=3D"content-type" content=3D"text/html; =
charset=3Dutf-8"></head><body style=3D"overflow-wrap: break-word; =
-webkit-nbsp-mode: space; line-break: after-white-space;">Thank you =
Pavel, that is really useful. I can imagine other people thinking about =
getting 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>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 objects =
including pg_database are not actively queried.<br><div><br></div><div>I =
git 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 datfrozenxid and datminmxid?</div><div><br></div><div>There =
is a lock for updating 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 datminmxid are in place =
updated, with no read consistency provided.</div><div><br =
id=3D"lineBreakAtBeginningOfMessage"><div>
<div dir=3D"auto" style=3D"caret-color: rgb(0, 0, 0); color: rgb(0, 0, =
0); letter-spacing: normal; text-align: start; text-indent: 0px; =
text-transform: none; white-space: normal; word-spacing: 0px; =
-webkit-text-stroke-width: 0px; text-decoration: none; word-wrap: =
break-word; -webkit-nbsp-mode: space; line-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 14:32, Pavel =
Stehule &lt;pavel.stehule@gmail.com&gt; wrote:</div><br =
class=3D"Apple-interchange-newline"><div><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;12:23 odes=C3=ADlatel Frits Hoogland &lt;<a =
href=3D"mailto:frits.hoogland@gmail.com">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, if 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>select * 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 that every 1 second, would =
there be any realistic danger or overhead that should be =
considered?</div><div>My thinking is that the data for these catalogs =
are all in shared memory and when executed serially and do not cause any =
significant resources to be =
taken?</div></div></blockquote><div><br></div><div>The queries to all =
tables excluding pg_database every 1 sec will have probably zero impact =
to performance.</div><div><br></div><div>I am not sure about pg_database =
- it is a very important table, and your query can block operations that =
need exclusive lock to this table. So theoretically, there can be some =
impact to =
performance.</div><div><br></div><div>Regards</div><div><br></div><div>Pav=
el<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);padding-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-indent: 0px; text-transform: none; white-space: normal; =
word-spacing: 0px; text-decoration: 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></body></html>=

--Apple-Mail=_6321713D-D65E-4459-82B4-D30232398673--