Managing Connections

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 admin user, 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='ibmclouddb';

Terminating Connections

Your Admin user has the pg_signal_backend role. If you find connections that need to reset or be closed, the Admin 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);
  

The admin user does have the power to reset or close the connections for any user on the deployment except superusers. Be careful not to terminate replication connections from the ibm-replication user, as it interferes with the high-availability of your deployment.

ibmcloud cdb deployment-kill-connections <deployment name or CRN>

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.

Raising the Connection Limit

PostgreSQL allocates some amount of memory on a per-connection basis, typically around 5 - 10 MB per connection. It is important to consider the total amount of memory that is available to your deployment before increasing the connection limit. To raise the connection limit, first you might want to scale your deployment to ensure that you have enough memory to accommodate more connections.

Next, change the value of max_connections on your deployment. To make permanent changes to the PostgreSQL configuration, you want to use the Cloud Databases cli-plugin or API to write the changes to the configuration file for your deployment.

For example, to raise max_connections to 215, it might be a good idea to scale your deployment to at least 2 GB of RAM per data member, for a total of 4 GB of RAM for your deployment. Once the scaling operation has finishes, then set the connection limit. Before you adjust max_connections, make sure to target your preferred region with a command like:

ibmcloud target -r <region>

Next, increase the amount of memory available to a deployment group with a command like:

ibmcloud cdb deployment-groups-set example-deployment member --memory 4096

Lastly, adjust max_connections with a command like:

ibmcloud cdb deployment-configuration example-deployment '{"configuration":{"max_connections":215}}'

To make the changes through the API,

curl -X PATCH `https://api.{region}.databases.cloud.ibm.com/v5/ibm/deployments/{id}/groups/member' \
-H "Authorization: Bearer $APIKEY" \
-H "Content-Type: application/json" \
-d '{"memory": {
        "allocation_mb": 4096
      }
    }'

curl -X PATCH 'https://api.{region}.databases.cloud.ibm.com/v5/ibm/deployments/{id}/configuration' \
-H "Authorization: Bearer $APIKEY" \
-H "Content-Type: application/json" \
-d '{"configuration":{
        "max_connections":215
      }
    }'