Keeping your connection limits in check should lead to a much healthier performing database and application. PostgreSQL has a lot of metadata about both historical and current activity against a system. This custom alert utilized pg_stat_activity to help provide insight into how many total connections (in all states) are being used and then returns a percentage of max_connections found in pg_settings. This is just an example of how pg_stat_activity can be utilized to monitor the health of your PostgreSQL connections. Below are descriptions of each state:
- active – Currently running queries, or the number of connections you currently have
- idle – An application has a connection to the database, but nothing is happening. It's idle. A connection pooler like pgBouncer or pgPool-II is useful for managing these.
- idle in transaction – Sometimes you may have some application/query logic that has a BEGIN but is now waiting somewhere in transaction La La Land and not doing work.
To create the alert, click on Alerts > Manage Alerts tab and create a Custom Alert of type Custom SQL Alert - Single Numeric Return. Configure the alert with values similar to these:
Alert Name: Total connections as percentage of max_connections
Execution Interval: 10 minutes (default) -- change this according to your database workload. You may need to run this alert more frequently.
Notification Text: Empty
select ROUND(CAST((FLOAT4(tc.total_connections)/FLOAT4(mc.max_connections))*100 AS NUMERIC),2) FROM (SELECT sum(numbackends) as total_connections FROM pg_stat_database) tc, (SELECT setting as max_connections from pg_settings where name = 'max_connections') mc WHERE tc.total_connections > 0
Execute Against: Monitored Instance
High Threshold: Min 90, Max <blank>
Medium Threshold: Min 85, Max 90
Low Threshold: Min 80, Max 85