ConnectionThresholdReached

Runbook: ConnectionThresholdReached Alert

Alert Details

• Alert Name: ConnectionThresholdReached
• Expression: sum without (datname, datid) (pg_stat_database_numbackends{datname!~"template.*"}) / (sum without (server) (pg_settings_max_connections)) * 100 >= 80

Description

This alert triggers when the currently established connections to a database host is equal or higher than 80% of the possible maximum connections of the server. If connections reach 100% (MaxConnectionsReached), new connection attempts will be rejected entirely, causing application errors.

Possible Causes

• Connection pool misconfiguration — pool size set too high relative to max_connections
• Connection leak in application code — connections opened but never closed
• Sudden traffic spike creating more connections than usual
• Long-running idle transactions holding connections open
• Missing or misconfigured connection pooler (PgBouncer)

Troubleshooting Steps

1. Check current connection count and the configured limit

1
2
3
4
5
6
7

SELECT
    count(*) AS current_connections,
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_connections,
    round(count(*) * 100.0 /
        (SELECT setting::int FROM pg_settings WHERE name = 'max_connections'), 1) AS usage_pct
FROM pg_stat_activity
WHERE datname NOT LIKE 'template%';

2. Break down connections by database, user, and state

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

SELECT
    datname AS database,
    usename AS user,
    application_name,
    state,
    count(*) AS connections
FROM pg_stat_activity
WHERE datname NOT LIKE 'template%'
GROUP BY datname, usename, application_name, state
ORDER BY connections DESC;

Look for:

• A single application_name or user holding an unexpectedly large share of connections
• Many connections in idle state — these are open but doing nothing and can often be closed
• Connections in idle in transaction — these hold locks and should be investigated first

3. Identify long-running idle connections

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT
    pid,
    datname AS database,
    usename AS user,
    application_name,
    client_addr,
    state,
    EXTRACT(EPOCH FROM now() - state_change) AS idle_for_seconds,
    query
FROM pg_stat_activity
WHERE state IN ('idle', 'idle in transaction')
    AND state_change < now() - interval '5 minutes'
ORDER BY idle_for_seconds DESC;

4. Terminate idle connections to free up capacity

Terminate connections that have been idle for more than a threshold (adjust interval as needed):

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT
    pg_terminate_backend(pid),
    datname,
    usename,
    application_name,
    client_addr,
    state,
    EXTRACT(EPOCH FROM now() - state_change) AS idle_for_seconds
FROM pg_stat_activity
WHERE state = 'idle'
    AND state_change < now() - interval '10 minutes'
    AND datname NOT LIKE 'template%';

Note: Only terminate idle connections, not active ones. For idle in transaction connections, refer to the HighTransactionFailRate runbook.

5. Increase max_connections if the workload genuinely requires it

For RDS/Aurora, max_connections is a parameter group setting — it cannot be changed with ALTER SYSTEM:

• AWS Console → RDS → Parameter Groups → select the group for the affected instance → search max_connections → modify the value
• Changes require a reboot of the instance to take effect
• Check available memory first: each PostgreSQL connection consumes roughly 5–10 MB of RAM. Increasing max_connections without sufficient memory causes swapping and degrades performance significantly. The default formula for RDS is LEAST({DBInstanceClassMemory/9531392}, 5000).

6. Long-term: add a connection pooler

If this alert recurs, the recommended fix is to add PgBouncer in front of PostgreSQL. PgBouncer multiplexes many application connections onto a smaller number of real server connections, keeping PostgreSQL’s connection count low regardless of application concurrency.

Additional resources

• Related alert: MaxConnectionsReached
• Related alert: HighTransactionFailRate