MIME-Version: 1.0
References: <F3953D11-A866-49CE-80C3-F00469A2521A@gmail.com>
In-Reply-To: <F3953D11-A866-49CE-80C3-F00469A2521A@gmail.com>
From: Pavel Stehule <pavel.stehule@gmail.com>
Date: Sat, 25 Jan 2025 14:32:36 +0100
Message-ID: 
 <CAFj8pRANyuda5uf9KR4jY2dGYJ0SDDNhyjRWvnaCRkjffX-ydw@mail.gmail.com>
Subject: Re: Any risk or overhead considerations for frequently executing
 queries against catalog tables?
To: Frits Hoogland <frits.hoogland@gmail.com>
Cc: pgsql-performance@lists.postgresql.org
Content-Type: multipart/alternative; boundary="0000000000008498b2062c87e21e"
Archived-At: 
 <https://www.postgresql.org/message-id/CAFj8pRANyuda5uf9KR4jY2dGYJ0SDDNhyjRWvnaCRkjffX-ydw%40mail.gmail.com>
Precedence: bulk

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

Hi

so 25. 1. 2025 v 12:23 odes=C3=ADlatel Frits Hoogland <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 connecti=
on
> 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?
>

The queries to all tables excluding pg_database every 1 sec will have
probably zero impact to performance.

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.

Regards

Pavel


>
> Thanks,
>
> *Frits Hoogland*
>
>
>
>
>

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

<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=C2=A012:=
23 odes=C3=ADlatel Frits Hoogland &lt;<a href=3D"mailto:frits.hoogland@gmai=
l.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 rg=
b(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 t=
hat creates a single connection and execute something like:<div>select * fr=
om pg_stat_activity;</div><div>select * from pg_stat_database;</div><div>se=
lect * from pg_stat_bgwriter;</div><div>select * from pg_stat_wal;</div><di=
v>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 th=
ere be any realistic danger or overhead that should be considered?</div><di=
v>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 tabl=
es excluding pg_database every 1 sec will have probably zero impact to perf=
ormance.</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 exclus=
ive lock to this table. So theoretically, there can be some impact to perfo=
rmance.</div><div><br></div><div>Regards</div><div><br></div><div>Pavel<br>=
</div><div>=C2=A0<br></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><br></div><div>Thanks,</div><div><br></div><div><div>
<div dir=3D"auto" style=3D"color:rgb(0,0,0);letter-spacing:normal;text-alig=
n: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>

--0000000000008498b2062c87e21e--