Correct query for monitor
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
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:
Show quoted text
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 <veema0000@gmail.com> 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!
Import Notes
Reply to msg id not found: CANzqJaALHY3HiyPz64g1iOaNe9aVmzUe_33KpyqZVsnHMS=P_Q@mail.gmail.com
On Fri, Sep 26, 2025 at 4:15 PM veem v <veema0000@gmail.com> wrote:
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?
Are there queries inside a cursor loop?
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?
PG does not support hints or "attaching plans to queries".
--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!
On Sat, 2025-09-27 at 01:45 +0530, veem v wrote:
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?
No, you have to read the function body. Then you can look for the statements therein
in pg_stat_statements.
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?
No, there isn't. You can use the pg_hint_plan extension and its query hints to force
a certain execution plan.
Yours,
Laurenz Albe