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?
They seem reasonable to me. My stance is "run it until you want more features, or find a flaw."
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.
