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
Scroll to view full table

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}'
Scroll to view full table

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}'
Scroll to view full table

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;
Scroll to view full table

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
Scroll to view full table

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}'
Scroll to view full table

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}'
Scroll to view full table

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.

More from Cloud

Strengthening cybersecurity in life sciences with IBM and AWS

7 min read - Cloud is transforming the way life sciences organizations are doing business. Cloud computing offers the potential to redefine and personalize customer relationships, transform and optimize operations, improve governance and transparency, and expand business agility and capability. Leading life science companies are leveraging cloud for innovation around operational, revenue and business models. According to a report on mapping the cloud maturity curve from the EIU, 48% of industry executives said cloud has improved data access, analysis and utilization, 45% say cloud…

7 min read

Kubernetes version 1.27 now available in IBM Cloud Kubernetes Service

< 1 min read - We are excited to announce the availability of Kubernetes version 1.27 for your clusters that are running in IBM Cloud Kubernetes Service. This is our 22nd release of Kubernetes. With our Kubernetes service, you can easily upgrade your clusters without the need for deep Kubernetes knowledge. When you deploy new clusters, the default Kubernetes version remains 1.25 (soon to be 1.26); you can also choose to immediately deploy version 1.27. Learn more about deploying clusters here. Kubernetes version 1.27 In…

< 1 min read

Redefining the consumer experience: Diageo partners with SAP and IBM on global digital transformation

3 min read - In an era of evolving consumer preferences and economic uncertainties, the beverage industry stands as a vibrant reflection of changing trends and shifting priorities. Despite the challenges posed by inflation and the cost-of-living crisis, a dichotomy has emerged in consumer behavior, where individuals untouched by the crisis continue to indulge in their favorite beverages, while those directly affected pivot towards more affordable luxuries, such as a bottle of something special. This intriguing juxtaposition highlights the resilient nature of consumers and…

3 min read

IBM Cloud releases 2023 IBM Cloud for Financial Services Agreed-Upon Procedures (AUP) Report

2 min read - IBM Cloud completed its 2023 independent review of IBM Cloud services and processes. The review report demonstrates to its clients, partners and other interested parties that IBM Cloud services have implemented and adhere to the technical, administrative and physical control requirements of IBM Cloud Framework for Financial Services. What is the IBM Cloud Framework for Financial Services? IBM Cloud for Financial Services® is designed to build trust and enable a transparent public cloud ecosystem with features for security, compliance and…

2 min read