How to set connection limits in PostgreSQL

Connection limits keep PostgreSQL responsive by preventing bursts of client sessions from consuming all available resources and cascading into slow queries, timeouts, or outages.

Each database session maps to a server-side backend, consuming memory and CPU even when idle, so allowing too many concurrent connections can reduce cache efficiency and increase context switching. PostgreSQL enforces a global ceiling with max_connections, while CONNECTION LIMIT on roles and databases provides safer, targeted controls that avoid raising the overall cap.

Limits apply only to new sessions and will reject excess connections with a FATAL error, so overly aggressive caps can break applications that do not pool connections. Commands below assume a systemd-managed PostgreSQL instance using the postgresql unit name on Ubuntu or Debian, and increasing max_connections requires a planned restart with enough memory headroom.

1. Open a terminal on the PostgreSQL host with sudo privileges.
2. Display the server-wide connection cap from max_connections.

   $ sudo -u postgres psql --no-psqlrc --tuples-only --no-align --command "SHOW max_connections;"
   100

3. Display the reserved superuser slots from superuser_reserved_connections.

   $ sudo -u postgres psql --no-psqlrc --tuples-only --no-align --command "SHOW superuser_reserved_connections;"
   3

   Non-superuser sessions are limited to max_connections minus superuser_reserved_connections.

4. Count current client sessions from pg_stat_activity.

   $ sudo -u postgres psql --no-psqlrc --tuples-only --no-align --command "SELECT count(*) FROM pg_stat_activity WHERE usename IS NOT NULL;"
   2

5. List connections per role and database to size limits.

   $ sudo -u postgres psql --no-psqlrc --command "SELECT datname, usename, count(*) AS connections FROM pg_stat_activity WHERE usename IS NOT NULL GROUP BY datname, usename ORDER BY connections DESC;"
    datname  | usename  | connections 
   ----------+----------+-------------
    postgres | postgres |           1
             | postgres |           1
   (2 rows)

6. Set a per-role connection limit for the application role.

   $ sudo -u postgres psql --no-psqlrc --command "ALTER ROLE appuser CONNECTION LIMIT 2;"
   ALTER ROLE

   -1 means unlimited, and 0 prevents new logins for that role.

7. Set a per-database connection limit for the application database.

   $ sudo -u postgres psql --no-psqlrc --command "ALTER DATABASE appdb CONNECTION LIMIT 20;"
   ALTER DATABASE

   Database limits are useful when multiple apps share one server-wide max_connections pool.

8. Verify the role connection limit from pg_roles.

   $ sudo -u postgres psql --no-psqlrc --tuples-only --no-align --command "SELECT rolname, rolconnlimit FROM pg_roles WHERE rolname = 'appuser';"
   appuser|2

9. Verify the database connection limit from pg_database.

   $ sudo -u postgres psql --no-psqlrc --tuples-only --no-align --command "SELECT datname, datconnlimit FROM pg_database WHERE datname = 'appdb';"
   appdb|20

10. Change max_connections only when a higher global cap is required.

    $ sudo -u postgres psql --no-psqlrc --command "ALTER SYSTEM SET max_connections = 120;"
    ALTER SYSTEM

    Changing max_connections requires a PostgreSQL restart and increases memory requirements for connection-related structures.

11. Restart PostgreSQL to apply the new max_connections value.

    $ sudo systemctl restart postgresql

    A restart disconnects active sessions and should be scheduled for a maintenance window.

    Related: How to manage the PostgreSQL service with systemctl in Linux

12. Confirm the updated max_connections value after the restart.

    $ sudo -u postgres psql --no-psqlrc --tuples-only --no-align --command "SHOW max_connections;"
    120

13. Confirm the rejection message when the role limit is reached.

    $ psql "host=127.0.0.1 dbname=appdb user=appuser"
    psql: error: connection to server at "127.0.0.1", port 5432 failed: FATAL:  too many connections for role "appuser"

    Idle sessions count toward connection limits, so pooling typically reduces both connection count and rejection risk.