PostgreSQLConnectionsCritical¶

What it means¶

More than 90% of the connections that PostgreSQL allows (max_connections) are in use, and this has lasted for 2 hours. The value of max_connections depends on the compute plan of the instance.

What happens if you do nothing¶

When all connections are in use, PostgreSQL refuses new connections with the error sorry, too many clients already. Your applications cannot connect to the database anymore.

What to do¶

1. Check the limit:

   SHOW max_connections;
   

2. Check which users and applications hold the connections, and in which state:

   SELECT usename, application_name, client_addr, state, count(*)
   FROM pg_stat_activity
   GROUP BY usename, application_name, client_addr, state
   ORDER BY count(*) DESC;
   

3. Look at the state column:

   • Many connections in state idle: the connection pool of an application is larger than needed. Make the pool smaller.
   • Many connections in state idle in transaction: an application started a transaction and did not end it. This is an error in the application. See PostgreSQLLongRunningTransaction.
   • Many connections in state active: the database is busy. Check for slow queries.
4. Use a connection pool in your applications, with a fixed maximum size. If you run several copies of an application, each copy has its own pool. Add up the pool sizes of all copies and all applications: the sum must stay below max_connections.
5. If you need more connections, move the instance to a larger compute plan. See Scaling.

Close connections¶

To close the connections of an application that is stuck, use pg_terminate_backend. This ends the connection and rolls back its open transaction. For example, to close all connections that are idle in a transaction for more than 10 minutes:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND state_change < now() - interval '10 minutes';