New configuration settings for IBM Cloud Databases for PostgreSQL

Having the ability to configure your databases to work well with your applications in a managed environment is one of the hallmarks of being in the cloud. IBM Cloud Databases for PostgreSQL has just released two configuration settings that now give you control over your connections and prepared transactions in PostgreSQL.

We’re always looking for ways to enhance your experience using IBM Cloud Databases for PostgreSQL. Now, we’ve added two configuration settings— max_connections and max_prepared_transactions—which you can now configure using the IBM Cloud CLI or the Cloud Databases API. This allows you to take control over the number of database connections you want your application to have access to and gives you the ability to use prepared transactions.

If you’re not familiar with any of these configurations, we recommend reading more about them before changing any of the default settings. You can read about them in the PostgreSQL documentation by clicking on any of the links below:

• max_connections

• max_prepared_transactions

Essentially, we’d like you to be familiar with how PostgreSQL handles connections and connection pooling before setting max_connections. Additionally, for prepared transactions and setting max_prepared_transactions, read up on using the PREPARE TRANSACTION command.

Setting max_connections

Databases for PostgreSQL starts off with a default of 115 connections, 15 of which are reserved for administration and not accessible to a non-superuser. Thus, you have 100 connections available to use by default. If you want to increase your connections beyond the 100 connections that are available to you, consider first using a connection pooling middleware like PgBouncer. Why? Because the more connections you add, the more resources you’ll use, and things can get dicey really quick with heavy workloads. But, if you want to scale up your connections, we’ve got you covered.

To change the max_connections setting, for example, to 150 from the CLI with the Cloud Databases plugin cdb, run the following in your terminal:

ibmcloud cdb deployment-configuration {deployment_name} '{"max_connections": 150}'

Or, using the API, you’d run:

    curl -sS -XPATCH "https://api.us-south.databases.cloud.ibm.com/v4/ibm/deployments/{deployment_id}/configuration/schema" \
    -H "Authorization: Bearer {IBM_CLOUD_API_TOKEN}" \
    -d '{"max_connections": 150}'

The databases will run the command to change the connections causing your database to restart. Once that’s completed, you can access your PostgreSQL database and run:

SHOW max_connections;

It’ll show you that you have 150 connections.

Note that 150 connections means that you’ll have 135 connections available to use (minus the 15 that are reserved). So, if you wanted to use exactly 150 connections for your application, you should add on 15 connections which would be 165 total.

Setting max_prepared_transactions

For max_prepared_transactions, you’d run the same commands, except you’d set max_prepared_transactions to a number that’s no more than the max_connections. This is recommended in the PostgreSQL documentation. The default value for max_prepared_transactions is 0, which means that it’s turned off by default. If you’re not going to use prepared transactions, keep it at 0 to prevent accidental creation of prepared transactions.

To change the max_prepared_transactions setting to 150 from the CLI with the Cloud Databases plugin cdb, run the following in your terminal:

ibmcloud cdb deployment-configuration {deployment_name} '{"max_prepared_transactions": 150}'

Or, using the API, you’d run:

curl -sS -XPATCH "https://api.us-south.databases.cloud.ibm.com/v4/ibm/deployments/{deployment_id}/configuration/schema" \
-H "Authorization: Bearer {IBM_CLOUD_API_TOKEN}" \
-d '{"max_prepared_transactions": 150}'

Like max_connections, running this command will trigger your PostgreSQL database to restart. Therefore, anticipate a very short connection interruption while it’s restarting – it will take only a couple seconds.

Stay tuned for more features coming to the Cloud Databases portfolio

Databases for PostgreSQL is continuously adding more features to allow you to configure the database for your own use cases. max_prepared_transactions and max_connections are just two of the configuration settings that have been released with Databases for PostgreSQL. So, keep watching for new features coming not only to Databases for PostgreSQL, but for all of the databases in the Cloud Databases portfolio.