March 11, 2019 By Phil Alger 3 min read

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:

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 "{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 "{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.

Was this article helpful?

More from Cloud

IBM Tech Now: April 8, 2024

< 1 min read - ​Welcome IBM Tech Now, our video web series featuring the latest and greatest news and announcements in the world of technology. Make sure you subscribe to our YouTube channel to be notified every time a new IBM Tech Now video is published. IBM Tech Now: Episode 96 On this episode, we're covering the following topics: IBM Cloud Logs A collaboration with IBM and Anaconda IBM offerings in the G2 Spring Reports Stay plugged in You can check out the…

The advantages and disadvantages of private cloud 

6 min read - The popularity of private cloud is growing, primarily driven by the need for greater data security. Across industries like education, retail and government, organizations are choosing private cloud settings to conduct business use cases involving workloads with sensitive information and to comply with data privacy and compliance needs. In a report from Technavio (link resides outside, the private cloud services market size is estimated to grow at a CAGR of 26.71% between 2023 and 2028, and it is forecast to increase by…

Optimize observability with IBM Cloud Logs to help improve infrastructure and app performance

5 min read - There is a dilemma facing infrastructure and app performance—as workloads generate an expanding amount of observability data, it puts increased pressure on collection tool abilities to process it all. The resulting data stress becomes expensive to manage and makes it harder to obtain actionable insights from the data itself, making it harder to have fast, effective, and cost-efficient performance management. A recent IDC study found that 57% of large enterprises are either collecting too much or too little observability data.…

IBM Newsletters

Get our newsletters and topic updates that deliver the latest thought leadership and insights on emerging trends.
Subscribe now More newsletters