Custom Alert - Total connections in idle-in-transaction state, as a percentage of max_connections


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 ('idle in transaction') 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.

Alert Definition

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

SQL Statement:

select ROUND(CAST((FLOAT4(tc.total_connections)/FLOAT4(mc.max_connections))*100 AS NUMERIC),2)
FROM (select count(*) as total_connections from pg_stat_activity where state = 'idle in transaction') tc,
(SELECT setting as max_connections from pg_settings where name = 'max_connections') mc
WHERE tc.total_connections > 0

Execute Against: Monitored Instance

Units: %

High Threshold: Min 95, Max <blank> 

Medium Threshold: Min 85, Max 95

Low Threshold: Min 75, Max 85