PostgreSQL Max Connections: Managing Connection Limits
PostgreSQL allows administrators to control the maximum number of concurrent database connections using the max_connections parameter. This setting ensures that server resources are efficiently utilized without overwhelming the system. Adjusting this parameter requires changes to the PostgreSQL configuration file and a server restart. In this guide, we will explore how to configure and manage max_connections with examples.
Syntax:
To set the maximum number of connections:
# Inside the postgresql.conf file max_connections = <number>
To check the current max_connections value:
SHOW max_connections;
Examples and Code:
1. Viewing Current Max Connections
Code:
-- Check the current maximum allowed connections
SHOW max_connections;
Output:
plaintext Copy code max_connections ----------------- 100 (1 row)
2. Modifying Max Connections in postgresql.conf
1. Locate the postgresql.conf file:
Typically located in /etc/postgresql/ /main/ or /var/lib/pgsql/data/.
2. Edit the file to set the desired value:
max_connections = 200
3. Restart the PostgreSQL server to apply changes:
sudo systemctl restart postgresql
4. Verify the updated value:
SHOW max_connections;
Output:
plaintext Copy code max_connections ----------------- 200 (1 row)
3. Setting Connections Dynamically Using SQL (Temporary)
Code:
-- Set max connections dynamically for the session
SET max_connections TO 150;
Note: This change only lasts for the current session and does not persist after a restart.
4. Monitoring Active Connections
To monitor the number of active and idle connections:
Code:
-- View active connections
SELECT
datname AS database_name,
usename AS user_name,
count(*) AS active_connections
FROM pg_stat_activity
GROUP BY datname, usename;
Output:
database_name user_name active_connections postgres admin 10 mydb user1 5
Explanation
1. Default Value:
PostgreSQL’s default max_connections is typically set to 100, sufficient for small-scale applications but may need adjustment for larger setups.
2. Configuration File:
The max_connections parameter resides in the postgresql.conf file. Changes here are system-wide and require a restart to take effect.
3. Server Resources:
Each connection consumes memory and system resources. Increasing max_connections without proper system resources (CPU, RAM) may degrade performance.
4. Connection Pooling:
Instead of increasing max_connections, consider using a connection pooler like PgBouncer or Pgpool-II to optimize resource usage.
5. Dynamic Changes:
While temporary changes can be made using SET, permanent changes should be configured in the postgresql.conf file or through environment-specific tools like Kubernetes ConfigMaps.
Use Cases:
All PostgreSQL Questions, Answers, and Code Snippets Collection.
RetroSearch is an open source project built by @garambo | Open a GitHub Issue
Search and Browse the WWW like it's 1997 | Search results from DuckDuckGo
HTML:
3.2
| Encoding:
UTF-8
| Version:
0.7.4