--- name: databases-for-postgresql-howto-managing-connections title: Managing connections description: 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. last-updated: 2026-08-03 --- > ## Documentation Index > The table of contents for this documentation set is at https://cloud.ibm.com/docs/databases-for-postgresql?format=markdown > The index for all IBM Cloud docs is at: https://cloud.ibm.com/docs/llms.txt > Use these files to discover more information as needed. # Managing connections {: #managing-connections} 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 [admin user](https://cloud.ibm.com/docs/databases-for-postgresql?topic=databases-for-postgresql-user-management&format=markdown#user-admin) and [`psql`](https://cloud.ibm.com/docs/databases-for-postgresql?topic=databases-for-postgresql-connecting-psql&format=markdown). ```sh ibmclouddb=> SHOW max_connections; max_connections ----------------- 115 (1 row) ``` {: .codeblock} ## Connection limits {: #postgres-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. ```sh 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`. ```sql SELECT count(distinct(numbackends)) FROM pg_stat_database; ``` {: .codeblock} If you need to figure out where the connections are going, you can break down the connections by database. ```sql SELECT datname, numbackends FROM pg_stat_database; ``` {: .codeblock} To further investigate connections to a specific database, query `pg_stat_activity`. ```sql SELECT * FROM pg_stat_activity WHERE datname='ibmclouddb'; ``` {: .codeblock} ## Terminating connections {: #terminate-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`](https://www.postgresql.org/docs/current/functions-admin.html#FUNCTIONS-ADMIN-SIGNAL-TABLE){: .external}. 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. ```sql SELECT pg_cancel_backend(pid); ``` {: .codeblock} - `pg_terminate_backend` stops the entire process and closes the connection. ```sql SELECT pg_terminate_backend(pid); ``` {: .codeblock} 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. ### End connections {: #end-connections} If your deployment reaches the connection limit or you are having trouble connecting to your deployment and suspect that a high number of connections is a problem, disconnect all of the connections to your deployment. In the UI, on the _Settings_ tab, there is a button to `End connections` to your deployment. Use caution, because it disrupts anything that is connected to your deployment. The CLI command to end connections to the deployment is: ```sh ibmcloud cdb deployment-kill-connections ``` You can also use the [Cloud Databases API](https://cloud.ibm.com/apidocs/cloud-databases-api/cloud-databases-api-v5#killconnections) to perform the end all connections operation. ## Connection pooling {: #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](https://jdbc.postgresql.org/documentation/datasource/){: .external}. Alternatively, you can use a third-party tool such as [PgBouncer](https://www.pgbouncer.org/){: .external} to manage your application's connections. Databases for PostgreSQL deployments include built-in support for PgBouncer's `auth_query` authentication, so a PgBouncer instance that you run can validate users against the database without maintaining a local password list, and password changes take effect without a PgBouncer restart. For setup steps, see [Connection pooling with PgBouncer](https://cloud.ibm.com/docs/databases-for-postgresql?topic=databases-for-postgresql-pgbouncer&format=markdown). ## Raising the connection limit {: #raise-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](https://cloud.ibm.com/docs/databases-for-postgresql?topic=databases-for-postgresql-resources-scaling&format=markdown) 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](https://cloud.ibm.com/docs/databases-for-postgresql?topic=databases-for-postgresql-changing-configuration&format=markdown#changing-configuration), you want to use the Cloud Databases [cli-plugin](https://cloud.ibm.com/docs/databases-cli-plugin?topic=databases-cli-plugin-cdb-reference&format=markdown#deployment-configuration) or [API](https://cloud.ibm.com/apidocs/cloud-databases-api/cloud-databases-api-v5#updatedatabaseconfiguration) 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. 1. Before you adjust `max_connections`, make sure to target your preferred region with a command like: ```sh ibmcloud target -r ``` {: pre} 2. Next, increase the amount of memory available to a deployment group with a command like: ```sh ibmcloud cdb deployment-groups-set deployment-example member --memory 4096 ``` {: pre} 3. Lastly, adjust `max_connections` with a command like: ```sh ibmcloud cdb deployment-configuration '{"configuration":{"max_connections":215}}' ``` {: pre} To make the changes through the API, use the following command: ```sh 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 } }' ``` {: pre} ### Connection limits and TCP/IP keepalives settings {: #keepalives} In the event of a network connection or failover, it is possible that broken TCP/IP connections remain in a half-opened/closed state until the TCP keepalive timeouts are reached. To avoid this scenario, set the `socket_timeout` and `connection_timeout` settings in your specific application drivers, as well. The correct settings _vary based on the specific workload and it is important to run load tests before going to production_. A good starting point for the `connection_timeout` is 2 - 5 seconds. For the `socket_timeout`, a good starting point is 30 - 60 seconds. Furthermore, on the server side, the following [keepalive configurations](https://www.postgresql.org/docs/12/runtime-config-connection.html){: .external} are used as the default. - `tcp_keepalives_idle` is set to 5 minutes - `tcp_keepalives_interval` probe interval is set to 10 seconds - `tcp_keepalives_count` is set to 6 To prevent half-open/closed connections or bursts in connection attempts from overwhelming your deployment, set the [`max_connections` parameter](https://cloud.ibm.com/docs/databases-for-postgresql?topic=databases-for-postgresql-changing-configuration&format=markdown) for Postgres to at least double your expected connection count. If your connection limit is reached, you can [end all connections](https://cloud.ibm.com/docs/databases-for-postgresql?topic=databases-for-postgresql-managing-connections&format=markdown#end-connections) immediately.