Обсуждение: 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;
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:
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;RegardsVeem--Death to <Redacted>, and butter sauce.Don't boil me, I'm still alive.<Redacted> lobster!
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