w3resource

Understanding PostgreSQL Max Connections Configuration


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:

  • High-Traffic Applications: Adjusting max_connections to accommodate more concurrent users.
  • Resource Optimization: Balancing between connection limits and server capacity.
  • Monitoring & Troubleshooting: Using pg_stat_activity to identify bottlenecks.

All PostgreSQL Questions, Answers, and Code Snippets Collection.



Follow us on Facebook and Twitter for latest update.