RDS PostgreSQL Connection Pool Exhausted — Diagnosis and Fix

Connection exhaustion is one of the fastest ways to take down a PostgreSQL application. The error FATAL: remaining connection slots are reserved for non-replication superuser connections (or too many connections) means your RDS instance has run out of server processes. New connections are refused. Existing connections may queue or fail immediately.

On AWS RDS PostgreSQL 16, 17, and 18, the problem is almost always architectural — not a matter of raising max_connections.

Step 1 — Understand max_connections on RDS

RDS sets max_connections based on instance RAM using this formula:

max_connections = LEAST(DBInstanceClassMemory / 9531392, 5000)

Each PostgreSQL backend process consumes approximately 5–10 MB of RAM (more with large work_mem settings). The RDS default targets roughly 12–14% of RAM for connection overhead. Common values:

Step 2 — Diagnose the current connection state

Run these queries to understand who is using connections and what they're doing:

-- Count connections by state and user:
SELECT
  usename,
  state,
  count(*)                               AS conn_count,
  max(now() - state_change)              AS oldest_state
FROM   pg_stat_activity
WHERE  pid <> pg_backend_pid()
GROUP BY usename, state
ORDER BY conn_count DESC;

-- Show long-idle connections (prime candidates for termination):
SELECT
  pid,
  usename,
  application_name,
  state,
  now() - state_change                  AS idle_duration,
  left(query, 60)                        AS last_query
FROM   pg_stat_activity
WHERE  state = 'idle'
  AND  state_change < now() - INTERVAL '10 minutes'
ORDER BY idle_duration DESC;

If you see large numbers of idle connections — especially with long idle durations — your application is not returning connections to a pool correctly, or you have no pool at all. Each idle connection holds a backend process and consumes RAM.

Step 3 — Emergency: terminate idle connections

If the instance is at or near max_connections, terminate idle connections to restore headroom:

-- Terminate connections idle for more than 10 minutes:
SELECT pg_terminate_backend(pid)
FROM   pg_stat_activity
WHERE  state = 'idle'
  AND  state_change < now() - INTERVAL '10 minutes'
  AND  pid <> pg_backend_pid();

This is a temporary fix only. Terminated connections will reconnect. The permanent fix is connection pooling between your application and RDS.

The permanent fix — pgBouncer in transaction mode

pgBouncer is a lightweight connection pooler that sits between your application and RDS. Your application opens connections to pgBouncer; pgBouncer maintains a small pool of real RDS connections and multiplexes hundreds of application connections across them.

For most OLTP web workloads, transaction mode is the right choice: pgBouncer borrows an RDS connection for the duration of each transaction, then returns it to the pool. This allows 500+ application connections to share 20–50 RDS connections.

# pgbouncer.ini — key settings for RDS
[databases]
mydb = host=your-rds-endpoint.rds.amazonaws.com port=5432 dbname=mydb

[pgbouncer]
pool_mode = transaction
max_client_conn = 500           # max connections from your app
default_pool_size = 25          # RDS connections per database/user pair
min_pool_size = 5
reserve_pool_size = 5
reserve_pool_timeout = 3
server_idle_timeout = 600       # close idle RDS connections after 10 min
server_connect_timeout = 15
server_login_retry = 15
auth_type = scram-sha-256       # required for RDS PostgreSQL 16+
listen_port = 5432
listen_addr = 0.0.0.0

RDS Proxy as an alternative

AWS RDS Proxy is a managed connection pooler that integrates with IAM and Secrets Manager. It's suitable for serverless workloads (Lambda, ECS tasks with bursty connection patterns) where managing your own pgBouncer deployment adds operational overhead. It uses transaction mode internally.

RDS Proxy has two relevant limitations: it adds ~1–2ms latency per query (the proxy hop), and it does not expose pgBouncer's pool statistics for fine-grained diagnosis. For dedicated application servers with stable connection counts, self-hosted pgBouncer gives more control and lower latency.

Calculating the right pool size

The classic formula from the HikariCP documentation applies to PostgreSQL:

pool_size = (core_count * 2) + effective_spindle_count

For an RDS db.r5.xlarge (4 vCPUs, NVMe-backed gp3 storage — treat spindles as 1):

pool_size = (4 × 2) + 1 = 9   # per application server node

This is a starting point. A pool of 9 per app server with 10 app servers = 90 RDS connections — well within max_connections even on a db.r5.large. The remaining headroom is used by migrations, monitoring agents, and superuser connections.