PostgreSQL remaining connection slots reserved: find the pool oversubscription

The symptoms

• •New application sessions fail with a FATAL message naming remaining connection slots, while previously established sessions continue to function normally.
• •Intermittent connection failures during traffic spikes or after a rolling deploy that increases pool size, with the application retrying until it times out.
• •pg_stat_activity shows a count near max_connections, including idle-in-transaction and idle sessions from application pools, background workers, and replication slots.
• •Connection pool or driver logs report authentication or connection acquisition timeouts that align with the rejection window in server logs.

Likely causes

• •Application-side connection pool (PgBouncer, HikariCP, psycopg, JDBC, RDS Proxy) sized so that peak pool_total exceeds server max_connections across all instances and roles.
• •Server-side max_connections is too low for the workload; superuser_reserved_connections and reserved_connections leave fewer slots than expected for the application role.
• •Connection leaks: code paths that fail to close sessions or return connections to the pool, leaving idle-in-transaction rows that accumulate over time.
• •Background workers, replication slots, or administrative tooling hold long-lived connections that reduce slots available to the application role.
• •Misconfigured per-user connection limits (ALTER ROLE ... CONNECTION LIMIT) causing premature rejection before max_connections is reached.

First ten minutes

1. 01Capture the exact FATAL message text and timestamp from server logs to confirm the failure mode matches the connection-slots condition rather than authentication or DNS.
2. 02Record current max_connections, superuser_reserved_connections, and reserved_connections values so the available slot budget is known before changing anything.
3. 03Query pg_stat_activity to count sessions grouped by state and application_name, distinguishing idle, idle in transaction, active, and fastpath function calls.
4. 04Check pg_stat_database for the target database and any pool front-end database to compare numbackends against the configured connection limit.
5. 05Identify which clients, hosts, and applications are connected by examining client_addr, application_name, and backend_start ordering, then correlate with deploy or traffic events.

Evidence to collect

• •Server log entries containing the FATAL remaining-connection-slots message, including the role and database that were rejected.
• •PostgreSQL configuration values for max_connections, superuser_reserved_connections, reserved_connections, and any per-role CONNECTION LIMIT.
• •A snapshot of pg_stat_activity rows at the moment of failure, including state, query, wait_event, backend_start, application_name, and client_addr.
• •Counts from pg_stat_database for numbackends versus the per-database connection limit, plus pool server statistics if PgBouncer or RDS Proxy fronts the database.
• •Pool-side metrics: active vs idle connections per pool, pool total, acquire timeouts, and connection creation rate from the application or pool telemetry.

Where to look

• •The boundary between application connection pools and the PostgreSQL listener, where each pool instance multiplies its configured size by replica count.
• •The boundary between the application role and per-role CONNECTION LIMIT, which can reject sessions even when server-wide slots exist.
• •The boundary between regular connections and reserved connections: superuser_reserved_connections and reserved_connections, which are subtracted from the available slot budget.
• •The boundary between the primary and replicas: read replicas and logical replication subscribers also consume max_connections.
• •Operational telemetry from the pool layer (PgBouncer show pools, RDS Proxy metrics) that determines whether the bottleneck is server capacity or pool acquisition behavior.

Diagnostic steps

1. 01Compare sum(pool_total) across all application replicas against max_connections minus superuser_reserved_connections minus reserved_connections to determine whether the pool fan-out exceeds the available budget.
2. 02Run pg_stat_activity grouped by application_name and state to identify whether most sessions are idle, idle in transaction, or actively executing; idle in transaction indicates a leak path rather than legitimate load.
3. 03Inspect backend_start and state_change to detect long-lived sessions older than the pool's expected idle lifetime, which usually represent leaked or stuck connections.
4. 04Verify that the rejection occurs for the application role, not for a superuser or replication role, by reading the role name in the FATAL message and cross-referencing pg_roles.
5. 05Inspect pool server metrics (PgBouncer show pools, show clients; HikariCP pool usage metrics) for wait counts and acquire timeouts that align with the rejection timestamps.
6. 06Check for replication and background workers in pg_stat_activity that share the max_connections budget and may not appear in application pool metrics.
7. 07Test whether a single superuser psql session can still connect during the failure window; success confirms the regular-slot exhaustion pattern rather than a listener or authentication failure.

Common mistakes

• •Increasing max_connections without reducing pool size, which masks the oversubscription and degrades memory and replication performance across the cluster.
• •Treating the error as transient and adding retry logic without bounds, which can deepen the saturation when backoff intervals overlap.
• •Looking only at server logs and ignoring pg_stat_activity, missing idle-in-transaction sessions that signal a leak rather than a capacity issue.
• •Assuming pooling at PgBouncer or RDS Proxy removes the need to size max_connections, when transaction or session pooling still requires backend capacity per application replica.
• •Forgetting per-role CONNECTION LIMIT, which can reject sessions even though the server-wide budget appears healthy in monitoring.

Safe fixes

• •If sum(pool_total) exceeds the available budget, reduce pool_max_size on the application or pool configuration so that pool fan-out plus reserved connections stays below the limit, then redeploy with the smaller pool.
• •If leaked idle-in-transaction sessions are present, identify the offending code path from application_name and query text, then add connection validation or query timeouts that close stale sessions.
• •If the budget is genuinely insufficient for the workload, raise max_connections together with shared_buffers, work_mem, and max_wal_size planning, and verify the new limit with a controlled load test before broad rollout.
• •If replication or background workers consume a large share, evaluate whether they can be consolidated or moved to dedicated nodes so the application budget is not eroded.
• •If per-role CONNECTION LIMIT is the binding constraint, raise it only after confirming that server-wide capacity exists, and document the new ceiling in capacity planning records.

Prove the fix

1. 01Re-run pg_stat_activity under representative peak load and confirm that the number of sessions for the application role stays below max_connections minus superuser_reserved_connections minus reserved_connections for at least one full peak window.
2. 02Capture server logs over the same window and confirm there are zero FATAL remaining-connection-slots messages for the application role during steady-state and burst traffic.
3. 03Replay the original failure scenario (same traffic pattern, same deploy) and verify that pool acquire timeouts and pool wait counts return to their pre-incident baseline rather than degrading silently.
4. 04Schedule a periodic check that compares sum(pool_max_size across replicas) against the available slot budget, so future drift toward oversubscription is detected before it produces user-visible failures.

Prevention and next steps

• •Capacity-plan max_connections as server_budget >= replicas * pool_max_size + reserved_connections + superuser_reserved_connections + headroom for replication and tooling.
• •Track idle-in-transaction age and pool wait counters as first-class alerts, with thresholds well before the remaining-slots error fires.
• •Treat pool sizing as a deployment property reviewed whenever instance counts, replica counts, or work patterns change.
• •Document per-role CONNECTION LIMIT alongside max_connections so application teams know the effective ceiling for their role.

Safe commands and checks

psql -h <host> -p <port> -U <role> -d <database> -c "SHOW max_connections; SHOW superuser_reserved_connections; SHOW reserved_connections;"
psql -h <host> -p <port> -U <role> -d <database> -c "SELECT application_name, state, count(*) FROM pg_stat_activity GROUP BY 1,2 ORDER BY 3 DESC;"
psql -h <host> -p <port> -U <role> -d <database> -c "SELECT pid, usename, application_name, client_addr, state, now() - backend_start AS age, query FROM pg_stat_activity WHERE state IN ('idle','idle in transaction') ORDER BY age DESC;"
psql -h <host> -p <port> -U <role> -d <database> -c "SELECT datname, numbackends FROM pg_stat_database WHERE datname IN ('<database>','<pool_database>') ORDER BY 2 DESC;"
psql -h <host> -p <port> -U <role> -d <database> -c "SELECT rolname, rolconnlimit FROM pg_roles WHERE rolname = '<application_role>';"
psql -h <host> -p <port> -U <role> -d <database> -c "SELECT application_name, count(*) FILTER (WHERE backend_type = 'client backend') AS clients, count(*) FILTER (WHERE backend_type <> 'client backend') AS others FROM pg_stat_activity GROUP BY 1 ORDER BY clients DESC;"