PostgreSQL too many connections: locate pool exhaustion

The symptoms

• •Application logs show Psycopg2/asyncpg/pgx errors containing "FATAL: too many connections for role" or "remaining connection slots are reserved for non-replication superuser connections"
• •New queries fail with SQLSTATE 53300 while existing in-flight queries continue to run, indicating the limit cap rather than query-level failure
• •Connection pool warm-up fails with EAI_AGAIN or ECONNREFUSED at peak traffic, but succeeds during low-traffic windows, suggesting slot starvation rather than network failure
• •PostgreSQL log includes lines matching "connection limit exceeded for non-superusers" or "remaining connection slots are reserved" at the timestamp of the failure
• •Monitoring shows pg_stat_activity counts approaching or pinned at max_connections, with many entries in "idle" or "idle in transaction" state held by the application pool

Likely causes

• •Application-side pool size multiplied by replica count exceeds PostgreSQL max_connections, so cumulative pool capacity oversubscribes the server limit
• •Connections leak from the application pool: aborted transactions, missing pool.release()/conn.close(), or exception paths that bypass pool return calls, leaving entries in "idle in transaction"
• •PostgreSQL reserved_connections (default 3) is consumed by replication slots, WAL receivers, or admin sessions, reducing effective slots visible to the application role
• •Per-role or per-database CONNECTION LIMIT set lower than the application's pool size, causing the application role itself to be capped independently of max_connections
• •Long-running queries or idle-in-transaction sessions hold slots without releasing them, blocking new checkouts during traffic spikes

First ten minutes

1. 01Capture the exact PostgreSQL error string and SQLSTATE from the application log; record the timestamp and the application role name reported in the error verbatim before any change
2. 02Read the PostgreSQL server log file (typically under the data directory's pg_log or log directory) for the matching timestamp and grep for "too many connections" or "remaining connection slots" to confirm server-side rejection
3. 03Query the running configuration to record max_connections, reserved_connections, superuser_reserved_connections, and the runtime value of each so you have a baseline before changing anything
4. 04Snapshot pg_stat_activity to count connections by state (active, idle, idle in transaction, fastpath function call) and group by usename, datname, application_name, and client_addr to identify which pool is consuming slots
5. 05Cross-check the application's configured pool max-size against the running PostgreSQL max_connections and the number of application instances/pods to compute whether oversubscription is mathematically possible

Evidence to collect

• •Output of SHOW max_connections; SHOW reserved_connections; SHOW superuser_reserved_connections from the affected server
• •Count and per-state breakdown of rows from pg_stat_activity, grouped by usename, datname, application_name, and state, captured at the failure timestamp
• •Server log lines containing "too many connections" or "remaining connection slots are reserved" with their timestamps, matched to the application's reported failure time
• •Application pool configuration: max pool size, min idle, idle timeout, connection timeout, and the number of service replicas that each open a pool to the same database
• •Per-role and per-database CONNECTION LIMIT values from pg_roles and pg_database for the affected role and database

Where to look

• •PostgreSQL server log directory (commonly pg_log inside the data directory, or wherever log_directory and log_filename point) for "too many connections" / "remaining connection slots" entries at the failure timestamp
• •pg_stat_activity and pg_stat_database system catalogs, which expose live connection state, per-database connection counts, and per-role usage needed to attribute slot consumption
• •pg_roles and pg_database system catalogs, which expose per-role and per-database CONNECTION LIMIT values that act as a secondary cap independent of max_connections
• •Application-side pool configuration files or environment variables (e.g., HikariCP maximumPoolSize, PgBouncer pool_size, psycopg_pool min_size/max_size, pgxpool config) where the pool capacity is defined per replica
• •Replication and admin boundary: pg_stat_replication rows and superuser sessions, which can consume reserved_connections and reduce slots available to the application role

Diagnostic steps

1. 01Confirm the cap: run SHOW max_connections and compare to the current count from SELECT count(*) FROM pg_stat_activity; if the count equals or exceeds max_connections, the server-side limit is the active constraint
2. 02Attribute the consumers: run SELECT usename, datname, state, count(*) FROM pg_stat_activity GROUP BY 1,2,3 ORDER BY 4 DESC to find which role, database, and state dominates the slots
3. 03Check per-role cap: run SELECT rolname, rolconnlimit FROM pg_roles WHERE rolname = current_user; if rolconnlimit is non-negative and lower than the pool size, the role-level cap is the real cause regardless of max_connections
4. 04Check per-database cap: run SELECT datname, datconnlimit FROM pg_database; if datconnlimit is below -1 and lower than the pool size, the database-level cap is the constraint
5. 05Distinguish leak from capacity: examine the state column; a high count of "idle" or "idle in transaction" rows held by the application pool's application_name indicates a leak or undersized pool, while "active" rows saturating the cap indicates true workload oversubscription
6. 06Verify replication impact: run SELECT count(*) FROM pg_stat_replication; if this plus the application role's connections approaches max_connections, reserved_connections for replication is shrinking the effective pool beyond what max_connections alone suggests

Common mistakes

• •Increasing max_connections without recalculating pool capacity per replica, which simply raises the ceiling while the application continues to oversubscribe it under load
• •Assuming the error is network-related because new connections appear to fail, instead of first verifying the SQLSTATE 53300 text and the server log line that names the limit
• •Counting only "active" rows in pg_stat_activity and missing "idle in transaction" sessions, which are the most common indicator of a leaked pool holding slots without doing work
• •Ignoring per-role CONNECTION LIMIT because max_connections is large, when the role itself is capped at a value smaller than the application's pool size
• •Restarting the application to "free" connections without first identifying the pool configuration that caused the oversubscription, so the same exhaustion recurs on the next traffic cycle

Safe fixes

• •If math shows replicas * pool_max > max_connections, reduce the application pool max-size per replica so that replicas * pool_max leaves a 10-20% headroom under max_connections, then redeploy with a single canary replica and verify before rolling out
• •If pg_stat_activity shows many "idle in transaction" rows from the pool, fix the application's transaction boundary first (commit/rollback in finally blocks, ensure pool.release() on error paths) and reduce pool idle timeout so leaked sockets are reaped before they consume slots
• •If per-role CONNECTION LIMIT is the constraint, raise it only to the value supported by the recalculated application capacity, and confirm with SHOW rolconnlimit that the change took effect before re-testing
• •Introduce a connection pooler (e.g., PgBouncer) in transaction-pooling mode between the application and PostgreSQL, configure pool_size per database, and keep application pool sizes small (typically a handful per replica) so the pooler multiplexes many clients onto few server connections
• •Set statement_timeout and idle_in_transaction_session_timeout on the server or the application role so that stuck sessions cannot indefinitely hold slots reserved for other clients

Prove the fix

1. 01During a load test that previously reproduced the failure, the PostgreSQL server log contains zero "too many connections" or "remaining connection slots are reserved" lines at the failure timestamp window
2. 02SELECT count(*) FROM pg_stat_activity peaks well below max_connections - reserved_connections, and the per-role count for the application role remains below rolconnlimit throughout the test window
3. 03Application error rate for SQLSTATE 53300 / "FATAL: too many connections" is zero for the soak duration, and p99 connection-acquisition latency stays below the configured pool connection timeout
4. 04After 24 hours of production traffic, the ratio of "idle in transaction" connections to total connections for the application role remains low (inspectable via pg_stat_activity grouped by state), indicating no leak regression

Prevention and next steps

• •Document the connection-budget arithmetic: replicas * pool_max_size must remain below max_connections - reserved_connections - replication_slots, and enforce this with a CI check that fails if the configured pool size exceeds the budget
• •Add alerts on pg_stat_activity count approaching a threshold (e.g., 80% of max_connections) and on the count of connections in "idle in transaction" state for the application role, so exhaustion is detected before it becomes user-visible
• •Set idle_in_transaction_session_timeout and statement_timeout at the role level so that any future code path that holds a transaction open cannot pin slots indefinitely
• •Use a centralized pooler (PgBouncer or equivalent) in front of PostgreSQL so that application pool sizes can be tuned independently of server-side max_connections, and so that failures present as pooler queue time rather than hard rejections

Safe commands and checks

SHOW max_connections; SHOW reserved_connections; SHOW superuser_reserved_connections;
SELECT state, count(*) FROM pg_stat_activity GROUP BY 1 ORDER BY 2 DESC;
SELECT usename, datname, state, application_name, count(*) FROM pg_stat_activity GROUP BY 1,2,3,4 ORDER BY 5 DESC;
SELECT rolname, rolconnlimit FROM pg_roles WHERE rolname = current_user;
SELECT datname, datconnlimit FROM pg_database;
SELECT count(*) FROM pg_stat_replication;
SELECT pid, usename, application_name, client_addr, state, query_start, state_change FROM pg_stat_activity WHERE state IN ('idle in transaction','idle in transaction (aborted)') ORDER BY query_start;