public inbox for [email protected]  

help / color / mirror / Atom feed

From: veem v <[email protected]>
To: Ron Johnson <[email protected]>
To: pgsql-general <[email protected]>
Subject: Re: Correct query for monitor
Date: Sat, 27 Sep 2025 01:45:29 +0530
Message-ID: <CAB+=1TXm-4bAQBiomkoob6RBO3R7QJo_ZzC+KiFv7rQwgtD_EQ@mail.gmail.com> (raw)
In-Reply-To: <CANzqJaALHY3HiyPz64g1iOaNe9aVmzUe_33KpyqZVsnHMS=P_Q@mail.gmail.com>
References: <CAB+=1TWW4DJwD0XPgud2bTJOuW0HkJ+rU11CL=mU93d4HXwfeA@mail.gmail.com>
	<CANzqJaALHY3HiyPz64g1iOaNe9aVmzUe_33KpyqZVsnHMS=P_Q@mail.gmail.com>

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 <[email protected]> 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 AM veem v <[email protected]> wrote:
>
>> 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) High
>> 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 =
>> '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!
>

view thread (3+ messages)  latest in thread

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: [email protected]
  Cc: [email protected], [email protected], [email protected]
  Subject: Re: Correct query for monitor
  In-Reply-To: <CAB+=1TXm-4bAQBiomkoob6RBO3R7QJo_ZzC+KiFv7rQwgtD_EQ@mail.gmail.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox