MIME-Version: 1.0
References: <CAB+=1TWW4DJwD0XPgud2bTJOuW0HkJ+rU11CL=mU93d4HXwfeA@mail.gmail.com>
 <CANzqJaALHY3HiyPz64g1iOaNe9aVmzUe_33KpyqZVsnHMS=P_Q@mail.gmail.com>
In-Reply-To: <CANzqJaALHY3HiyPz64g1iOaNe9aVmzUe_33KpyqZVsnHMS=P_Q@mail.gmail.com>
From: veem v <veema0000@gmail.com>
Date: Sat, 27 Sep 2025 01:45:29 +0530
Message-ID: <CAB+=1TXm-4bAQBiomkoob6RBO3R7QJo_ZzC+KiFv7rQwgtD_EQ@mail.gmail.com>
Subject: Re: Correct query for monitor
To: Ron Johnson <ronljohnsonjr@gmail.com>, 
	pgsql-general <pgsql-general@lists.postgresql.org>
Content-Type: multipart/alternative; boundary="000000000000189fe4063fb9f31c"
Archived-At: <https://www.postgresql.org/message-id/CAB%2B%3D1TXm-4bAQBiomkoob6RBO3R7QJo_ZzC%2BKiFv7rQwgtD_EQ%40mail.gmail.com>
Precedence: bulk

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

Thank you so much for the quick response. I have a follow up question on
this as below,

If we want to identify, what exact query inside a procedure is taking a
longer time:- Using any pg_* views, Is there an easy way to tie the
query_id of the procedure with the query_ids of the internal sqls(those are
executed within the procedure) to quickly get the culprit sql? And say , we
got the sql and saw a bad plan and we want to change the plan or attach a
good plan to that query , is there a possible way to do that in postgres?

On Fri, 26 Sept 2025 at 18:53, Ron Johnson <ronljohnsonjr@gmail.com> wrote:

> They seem reasonable to me.  My stance is "run it until you want more
> features, or find a flaw."
>
> On Fri, Sep 26, 2025 at 6:23=E2=80=AFAM veem v <veema0000@gmail.com> wrot=
e:
>
>> Hello,
>> We want to have monitoring on three things 1) If the database restarted
>> or went down in the last few hours? 2)If the connections are high 3) Hig=
h
>> tablespace growth . Want to understand , if we can utilize below queries
>> for the same or any flaws in this strategy?
>>
>> 1)SELECT
>>   CASE
>>     WHEN now() - pg_postmaster_start_time() < interval '12 hours'
>>     THEN 'ALERT: DB was restarted in the last 12 hours'
>>     ELSE 'OK'
>>   END AS status;
>>
>> 2)SELECT
>>     CASE
>>         WHEN conn_count > max_conn * 0.8 THEN
>>             'ALERT: Connection usage is above 80%'
>>         ELSE
>>             'OK: Connection usage is under control'
>>     END AS status,
>>     conn_count AS current_connections,
>>     max_conn AS max_connections,
>>     ROUND(conn_count * 100.0 / max_conn, 2) AS percent_used
>> FROM (
>>     SELECT
>>         COUNT(*) AS conn_count,
>>         (SELECT setting::int FROM pg_settings WHERE name =3D
>> 'max_connections') AS max_conn
>>     FROM pg_stat_activity
>> ) sub;
>>
>> 3)SELECT spcname, pg_size_pretty(pg_tablespace_size(oid)) AS size
>> FROM pg_tablespace;
>>
>> Regards
>> Veem
>>
>
>
> --
> Death to <Redacted>, and butter sauce.
> Don't boil me, I'm still alive.
> <Redacted> lobster!
>

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

<div dir=3D"ltr">Thank you so much for the quick response. I have a follow =
up question on this as below,<div><br></div><div>If we want to identify, wh=
at exact query=C2=A0inside=C2=A0a procedure is taking a longer time:- Using=
 any pg_* views, Is there an easy way to tie the query_id of the procedure =
with the query_ids of the internal sqls(those are executed within the proce=
dure) to quickly get the culprit sql? And say , we got the=C2=A0sql and saw=
 a bad plan and we want to change the plan or attach a good plan to that qu=
ery , is there a possible way to do=C2=A0that in postgres?</div></div><br><=
div class=3D"gmail_quote gmail_quote_container"><div dir=3D"ltr" class=3D"g=
mail_attr">On Fri, 26 Sept 2025 at 18:53, Ron Johnson &lt;<a href=3D"mailto=
:ronljohnsonjr@gmail.com">ronljohnsonjr@gmail.com</a>&gt; wrote:<br></div><=
blockquote class=3D"gmail_quote" style=3D"margin:0px 0px 0px 0.8ex;border-l=
eft:1px solid rgb(204,204,204);padding-left:1ex"><div dir=3D"ltr">They seem=
 reasonable to me.=C2=A0 My stance is &quot;run it=C2=A0until you want more=
 features, or find a flaw.&quot;</div><br><div class=3D"gmail_quote"><div d=
ir=3D"ltr" class=3D"gmail_attr">On Fri, Sep 26, 2025 at 6:23=E2=80=AFAM vee=
m v &lt;<a href=3D"mailto:veema0000@gmail.com" target=3D"_blank">veema0000@=
gmail.com</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"><div dir=3D"ltr">Hello,<br>We want to have monitoring on three t=
hings 1) If the database restarted or went down in the last few hours? 2)If=
 the connections are high 3) High tablespace growth . Want to understand , =
if we can utilize below queries for the same or any flaws in this strategy?=
<br><br><font face=3D"monospace">1)SELECT <br>=C2=A0 CASE <br>=C2=A0 =C2=A0=
 WHEN now() - pg_postmaster_start_time() &lt; interval &#39;12 hours&#39; <=
br>=C2=A0 =C2=A0 THEN &#39;ALERT: DB was restarted in the last 12 hours&#39=
;<br>=C2=A0 =C2=A0 ELSE &#39;OK&#39;<br>=C2=A0 END AS status;<br>=C2=A0<br>=
2)SELECT <br>=C2=A0 =C2=A0 CASE <br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 WHEN conn_c=
ount &gt; max_conn * 0.8 THEN <br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0=
 &#39;ALERT: Connection usage is above 80%&#39;<br>=C2=A0 =C2=A0 =C2=A0 =C2=
=A0 ELSE <br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 &#39;OK: Connection =
usage is under control&#39;<br>=C2=A0 =C2=A0 END AS status,<br>=C2=A0 =C2=
=A0 conn_count AS current_connections,<br>=C2=A0 =C2=A0 max_conn AS max_con=
nections,<br>=C2=A0 =C2=A0 ROUND(conn_count * 100.0 / max_conn, 2) AS perce=
nt_used<br>FROM (<br>=C2=A0 =C2=A0 SELECT <br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 C=
OUNT(*) AS conn_count,<br>=C2=A0 =C2=A0 =C2=A0 =C2=A0 (SELECT setting::int =
FROM pg_settings WHERE name =3D &#39;max_connections&#39;) AS max_conn<br>=
=C2=A0 =C2=A0 FROM pg_stat_activity<br>) sub;<br><br>3)SELECT spcname, pg_s=
ize_pretty(pg_tablespace_size(oid)) AS size<br>FROM pg_tablespace;</font><d=
iv><font face=3D"monospace"><br></font></div><div><font face=3D"monospace">=
Regards</font></div><div><font face=3D"monospace">Veem</font></div></div>
</blockquote></div><div><br clear=3D"all"></div><div><br></div><span class=
=3D"gmail_signature_prefix">-- </span><br><div dir=3D"ltr" class=3D"gmail_s=
ignature"><div dir=3D"ltr">Death to &lt;Redacted&gt;, and butter sauce.<div=
>Don&#39;t boil me, I&#39;m still alive.<br><div><div>&lt;Redacted&gt; lobs=
ter!</div></div></div></div></div>
</blockquote></div>

--000000000000189fe4063fb9f31c--