When you have numerous concurrent connections for your server, exceeding the default number, you will run into “FATAL ERROR: too many clients already.” However, tuning the PostgreSQL max connections is possible to meet your needs. Read on!
Checking the Available Max Connections in PostgreSQL
If you are facing an error message which limits your application from receiving incoming connections, tuning the PostgreSQL max connections property for your database is the solution. You can adjust the max_connections property via the command line or the GUI. We will cover both ways, but let’s first see how to check the currently set max_connections and the shared_buffers.
Note that tuning the max_connections property for your PostgreSQL database affects the RAM usage. Thus, you should only adjust it to a reasonable size to avoid draining your RAM. Moreover, other properties like the shared_buffers will be affected when you tune the max_connections. It is also recommended to adjust the shared_buffers when tuning the max_connections to enhance the application’s performance.
There are two ways to check the max_connections and the shared_buffers:
1. Via the Config File
The location for the PostgreSQL config file depends on the PostgreSQL version that you are running and the system that you are using. In our case, we have the PostgreSQL database running on Ubuntu. Thus, we can access it via the terminal using the following command:
Once we access the server, run the following command to get the path to the config file:
Once you get the path, open the config file using an editor of your choice. Here, we open it using nano as follows:
Scroll down and find the max_connections in the config file. In our case, we can see that the available limit is the default limit of 100 connections.
Further down the file, the shared_buffers is set to 128MB.
2. Via the “Show Max_Connections” Command
Open your PostgreSQL database on your terminal and run the following commands to display the currently set max_connections and shared_buffers values:
# show shared_buffers;
We still get the same values as when we accessed the config file in the previous method.
Two Methods in Tuning the PostgreSQL Max Connections
You can adjust the max_connections value at any instance to meet your needs, provided you don’t overload the database.
There are two approaches to take. First, you can use the PostgreSQL “Alter System” command to assign a new value for your max_connections. Still, you can access the PostgreSQL config file with admin privileges and adjust the max_connections value. Take a look at the following examples:
1. Via the PostgreSQL Alter System Command
Open your PostgreSQL database on your terminal. Once you access the server, change the max_connections and the shared_buffers with the following syntax. Replace the values to match those for your preference:
# ALTER system SET shared_buffers=size-of-buffers;
For our example, we set the max_connections=250 and the shared_buffers=180MB.
After tuning the max_connections, you must restart the PostgreSQL database server for the changes to take effect. Here, we restart the PostgreSQL service with the systemctl command.
Quickly verify if the new max_connections and the shared_buffers are implemented.
2. Via the PostgreSQL Config File
You can directly adjust the max_connections by accessing PostgreSQL with admin privileges. The path to the config file is the same as the one that you accessed earlier when checking the currently set max_connections and shared_buffers.
When you open the file, locate the max_connections and change its value to the new limit that you wish to set.
Still, access the shared_buffers and change its limit to your preferred one, depending on the system resource that you have for your case.
Save the changes and exit the file editor. You then must restart the PostgreSQL database server for the changes to take effect.
With that, you successfully adjusted the PostgreSQL max_connections property on your server. Thus, you eliminate the error that you were facing earlier and enjoy a better performance for your application, with more concurrent connections being served by your PostgreSQL database server.
Conclusion
PostgreSQL allows you to adjust the max_connections to allow more concurrent connections to the database. Tuning the PostgreSQL max connections can be done in two ways. You can access its config file and manually edit the max_connections value. Alternatively, you can use the terminal with the “Alter system” command and set the new max_connections value. This guide presented both methods. Try them out!
