PostgreSQL allows 100 concurrent connections by default. exceeding the stated number will result in an error stating "too many clients already". However, while working with Postgres, users may encounter a situation where they need to maximize the "max_connections". To deal with such situations, the ALTER SYSTEM command can be used alongside the SET clause.
This write-up will explain how to use the ALTER SYSTEM command in Postgres to change the value of the max_connections parameter.
How to Alter max_connections Parameter in PostgreSQL?
To alter the “max_connections” parameter in Postgres, the ALTER SYSTEM command is used along with the SET clause followed by the number of maximum connections:
ALTER SYSTEM SET max_connections = num_of_connections;
Follow the below-exhibited steps to change the max_connections parameter in Postgres:
Step 1: Show Max Connections
Open the SQL Shell and type the following command to check the value of the max_connections parameter:
SHOW MAX_CONNECTIONS;
The following snippet shows that the current value of the max_connections parameter is “100”:
Step 2: Alter Max Connections
Type in the following ALTER SYSTEM command to modify the value of the “max_connections” parameter:
ALTER SYSTEM SET max_connections = 125;
The below snippet illustrates that the value of the “max_connections” parameter is updated:
Step 3: Restart Postgres Server
Restart the Postgres server to apply the desired changes. For this purpose, open the “services” app, locate “postgresql” service, and hit the restart button:
Step 4: Verify Altered Connections
Verify the altered value of the max_connections parameter by executing the below-stated command:
SHOW MAX_CONNECTIONS;
The following snippet shows that the value of the max_connections parameter has been successfully altered/incremented:
Note: To optimize the performance of your application, it is preferred to adjust the value of the "shared_buffers" parameter as well. To do that, use the "ALTER SYSTEM SET shared_buffers = buffer_size;". Altering the buffer size will enhance the overall performance/efficiency of the system.
Conclusion
To alter the “max_connections” parameter in Postgres, the “ALTER SYSTEM SET max_connections = num_of_connections;” command is used. After that, restart the Postgres server to apply the desired changes. For this purpose, launch the “services” app, select the “postgresql” service, and click the restart button. Users can verify the altered value of the “max_connections” parameter by executing the “SHOW MAX_CONNECTIONS;” command. This post has provided a complete guide on altering the value of the max_connections parameter.
