Managing connections

Gen 2

Connections to your Databases for PostgreSQL deployment use resources, so it is important to consider how many connections you need to tune your deployment's performance. PostgreSQL uses a max_connections setting to limit the number of connections (and resources that are consumed by connections) to prevent run-away connection behavior from overwhelming your deployment's resources.

You can check the value of max_connections with your Manager user and psql.

ibmclouddb=> SHOW max_connections;
 max_connections
-----------------
 115
(1 row)

Connection limits

At provision, Databases for PostgreSQL sets the maximum number of connections to your PostgreSQL database to 115. 15 connections are reserved for the superuser to maintain the state and integrity of your database, and 100 connections are available for you and your applications. If the number of connections to the database exceeds the 100-connection limit, new connections fail and return an error.

FATAL: remaining connection slots are reserved for
non-replication superuser connections

Exceeding the connection limit for your deployment can cause your database to be unreachable by your applications.

You can check the number of connections to your deployment with the user that has the Manager role, psql, and pg_stat_database.

SELECT count(distinct(numbackends)) FROM pg_stat_database;

If you need to figure out where the connections are going, you can break down the connections by database.

SELECT datname, numbackends FROM pg_stat_database;

To further investigate connections to a specific database, query pg_stat_activity.

SELECT * FROM pg_stat_activity WHERE datname='<DBname>';

Terminating connections

Your Manager user has the pg_signal_backend role. If you find connections that need to reset or be closed, the Manager user can use both pg_cancel_backend and pg_terminate_backend. The pid of a process is found from the pg_stat_activity table.

pg_cancel_backend cancels a connection's current query without terminating the connection, and without stopping any other queries that it might be running.
```
SELECT pg_cancel_backend(pid);
```
pg_terminate_backend stops the entire process and closes the connection.
```
SELECT pg_terminate_backend(pid);
```

Users with the Manager role, or those with admin privileges, can reset or close connections for any user in the deployment, except superusers. Ensure that replication connections from the ibm_replication user are not terminated, as doing so disrupts the high-availability of your deployment.

End connections

Currently, ending (killing) connections is not supported via UI, CLI, or API on Gen 2. However, you can execute a kill_all_connections() function from psql as a user with the Manager role.

Connection pooling

One way to prevent exceeding the connection limit and ensure that connections from your applications are being handled efficiently is through connection pooling. If you find yourself setting the IBM Cloud® Databases for PostgreSQL connection limit to more than 500 connections, you should seriously consider using connection pooling or reevaluating how to more efficiently use and maintain connections. Performance benchmarking in the PostgreSQL community suggests 500 connections or fewer to be optimal for database performance.

Many PostgreSQL driver libraries have connection pooling classes and functions. You need to consult your driver's documentation to implement connection pooling that is optimal for your use case. For example, the Python driver Psycopg2 has classes to handle connection pooling in your application. The Java PostgreSQL JDBC driver has methods for connection pooling at both the application and application server level.

Alternatively, you can use a third-party tool such as PgBouncer to manage your application's connections.