PostgreSQL Connection Exhaustion: How to Fix 'Too Many Connections' with PgBouncer

Why PostgreSQL's Connection Architecture Creates This Problem

PostgreSQL uses a process-per-connection model: each client connection maps to a dedicated backend process on the server. This design gives PostgreSQL strong isolation and simplifies the kernel scheduling model, but it imposes a real cost at connection scale. Each backend process consumes approximately 5–10 MB of memory at idle, plus the process spawn overhead when the connection is first established.

At 200 connections, that baseline is 1–2 GB. 5–5 GB — purely from connection overhead, not from any query work. The max_connections default of 100 in PostgreSQL exists because the process-per-connection model genuinely degrades server performance beyond a few hundred connections.

The typical production recommendation is to keep max_connections under 200–400 depending on instance memory and RAM, and to use connection pooling for everything beyond that. The problem becomes acute with modern application deployment patterns: containerised microservices each maintaining their own connection pools, serverless functions establishing new connections on every invocation, and ORM frameworks with connection pools configured independently per application instance. A Kubernetes deployment with 20 application pods, each running a connection pool of 10, is attempting to maintain 200 permanent connections against PostgreSQL before any user traffic arrives.

-- Check current connection count and limits
SELECT
  max_conn,
  used,
  res_for_super,
  max_conn - used - res_for_super AS available
FROM (
  SELECT
    (SELECT setting::int FROM pg_settings WHERE name = 'max_connections') AS max_conn,
    count(*) AS used,
    (SELECT setting::int FROM pg_settings WHERE name = 'superuser_reserved_connections') AS res_for_super
  FROM pg_stat_activity
) t;

-- See active connections by application and state
SELECT
  application_name,
  state,
  count(*)
FROM pg_stat_activity
WHERE state IS NOT NULL
GROUP BY application_name, state
ORDER BY count DESC;

How to Confirm Connection Exhaustion Is Your Problem

Before deploying PgBouncer, confirm that connection exhaustion is actually the issue. The clearest signal is the FATAL error in your application logs combined with a connection count approaching max_connections in pg_stat_activity. Check the CloudWatch DatabaseConnections metric if you are on RDS — a flat line at or near max_connections during incident periods is diagnostic.

Check your application connection pool metrics: if the pool is at maximum size and connections are queuing, the pool is exhausted. Examine the wait_event and wait_event_type columns in pg_stat_activity: connections waiting on ClientRead are idle connections held open by the application; a large number of these means your application is holding connections longer than necessary. Also check for connections in the 'idle in transaction' state — these are connections inside an open transaction that is not currently executing a query.

A large count of idle-in-transaction connections indicates the application is not committing or rolling back transactions promptly, holding connections and locks simultaneously.

-- Connections by state — look for large 'idle' and 'idle in transaction' counts
SELECT
  state,
  wait_event_type,
  wait_event,
  count(*) AS conn_count
FROM pg_stat_activity
WHERE pid != pg_backend_pid()
GROUP BY state, wait_event_type, wait_event
ORDER BY conn_count DESC;

-- Connections idle for longer than 5 minutes (candidates for pgbouncer's server_idle_timeout)
SELECT
  pid,
  application_name,
  state,
  now() - state_change AS idle_duration,
  usename
FROM pg_stat_activity
WHERE state IN ('idle', 'idle in transaction')
  AND now() - state_change > INTERVAL '5 minutes'
ORDER BY idle_duration DESC;

Related: why your API is slow even when the database looks fine, the N+1 query problem

What PgBouncer Does and the Three Pooling Modes

PgBouncer is a lightweight connection pooler that sits between your application and PostgreSQL. Your application connects to PgBouncer as if it were the database. PgBouncer maintains a small pool of actual PostgreSQL backend connections and multiplexes application connections across them.

When an application connection sends a query, PgBouncer assigns it a free backend connection from the pool, routes the query, and returns the backend connection to the pool when done. From PostgreSQL's perspective, it sees only the pooler's N backend connections — never the potentially hundreds of application connections behind it. PgBouncer supports three pooling modes with different trade-offs.

Session pooling assigns a backend connection to each application connection for its entire lifetime — effectively the same as direct PostgreSQL connections, with no multiplexing benefit during query execution. Transaction pooling assigns a backend connection for the duration of each transaction. This is the highest-multiplexing mode and what most teams need: a backend connection is only held while work is in progress, not during the time between transactions when the application connection is idle.

Statement pooling assigns a connection per individual statement — incompatible with multi-statement transactions and rarely used. For most production workloads, transaction pooling with a pool_size of 10–20 backend connections per database-user pair handles hundreds of application connections with no connection errors.

Step-by-Step: Installing and Configuring PgBouncer

PgBouncer is a single binary with a plain-text configuration file. On Debian/Ubuntu, install with apt. On Amazon Linux, use yum or compile from source.

txt (authentication credentials). ini [databases] section defines connection targets — each entry maps a logical database name (what the application connects to) to the actual PostgreSQL connection parameters. The [pgbouncer] section sets the listen address and port, the pooling mode, pool sizing, authentication method, and operational limits.

Place PgBouncer on the same server as your application (sidecar pattern) or on a dedicated proxy host in the same subnet as PostgreSQL. For Kubernetes deployments, running PgBouncer as a sidecar container in the application pod eliminates network latency and keeps the proxy close to the consumer. For RDS, PgBouncer on an EC2 instance or ECS task in the same VPC and subnet achieves sub-millisecond proxy overhead.

# pgbouncer.ini — transaction pooling configuration
[databases]
# Maps logical name → actual PostgreSQL connection
myapp = host=prod-rds.cluster.us-east-1.rds.amazonaws.com port=5432 dbname=myapp

[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 5432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

# Pooling mode
pool_mode = transaction

# Core sizing
max_client_conn = 2000        # max application connections to PgBouncer
default_pool_size = 20        # backend connections per db-user pair
min_pool_size = 5             # keep N connections warm at all times
reserve_pool_size = 5         # extra connections for spikes
reserve_pool_timeout = 3      # seconds to wait before using reserve pool

# Timeouts
server_idle_timeout = 600     # close idle backend connections after 10 min
client_idle_timeout = 0       # never close idle client connections
query_wait_timeout = 30       # queue application queries for up to 30 sec
server_connect_timeout = 15   # fail fast if PostgreSQL is unreachable

# Logging
log_connections = 0           # disable in production (verbose)
log_disconnections = 0
stats_period = 60

# userlist.txt (scram-sha-256 or md5 passwords)
# "username" "password"

Calculating the Right pool_size

The correct pool_size is not a fixed number — it depends on your PostgreSQL instance's CPU count, your workload's query duration, and your target concurrency. The formula commonly used is: pool_size = (CPU cores × 2) + effective_io_concurrency. 2xlarge with 8 vCPUs, that gives a baseline of 17–24 backend connections per database-user pair.

In practice, start at 15–20 for OLTP workloads where queries complete in milliseconds, and benchmark upward. The pool_size determines how many queries can execute simultaneously against PostgreSQL. If you set it too low, application connections queue behind PgBouncer waiting for a free backend slot — you trade connection errors for queue latency.

If you set it too high, you push PostgreSQL's backend process count past its efficient operating range. Monitor the PgBouncer stats table (SHOW STATS, SHOW POOLS) to see average query wait time and pool utilisation. If cl_waiting (clients waiting for a pool connection) is consistently above zero, increase pool_size.

If sv_idle (idle backend connections) is consistently high relative to sv_used, decrease pool_size.

-- Connect to PgBouncer admin interface and check pool status
-- psql -h 127.0.0.1 -p 6432 -U pgbouncer pgbouncer

-- See pool utilisation per database-user pair
SHOW POOLS;
-- Key columns:
-- cl_active: clients connected and executing
-- cl_waiting: clients queued waiting for a server connection (want this at 0)
-- sv_active: server connections in use
-- sv_idle: server connections idle (available)
-- sv_used: server connections recently used

-- Overall stats
SHOW STATS;
-- avg_query_time: average query duration through the pooler (microseconds)
-- avg_wait_time: average time clients spent waiting for a server connection
-- total_query_count: total queries routed since last reset

Related: our AWS RDS performance tuning guide

Serverless Functions and Connection Exhaustion — A Special Case

Serverless functions (AWS Lambda, Vercel Edge Functions, Cloudflare Workers) are the worst-case scenario for direct PostgreSQL connections. Each function invocation establishes a new connection because there is no persistent process between invocations to maintain a connection pool. A Lambda function that executes 500 times per minute creates up to 500 connection establishment attempts per minute.

Even with connection reuse across warm invocations — which is unreliable and depends on invocation frequency — the connection count scales linearly with concurrent executions. For Lambda and similar runtimes, two solutions exist: AWS RDS Proxy (a managed PgBouncer equivalent integrated with IAM authentication, useful when you are already on RDS and want zero infrastructure to manage) and a PgBouncer instance on a persistent compute layer (EC2, ECS, or a small container) that the serverless functions connect to. 015 per vCPU-hour on the underlying instance.

small EC2 instance costs approximately $15/month and handles tens of thousands of application connections with sub-millisecond proxy overhead.

# AWS RDS Proxy — managed connection pooling for Lambda
# Create via AWS CLI (requires IAM role and Secrets Manager secret)
aws rds create-db-proxy \
  --db-proxy-name myapp-proxy \
  --engine-family POSTGRESQL \
  --auth '[{"AuthScheme":"SECRETS","SecretArn":"arn:aws:secretsmanager:...","IAMAuth":"REQUIRED"}]' \
  --role-arn arn:aws:iam::123456789:role/rds-proxy-role \
  --vpc-subnet-ids subnet-abc123 subnet-def456 \
  --vpc-security-group-ids sg-abc123

# Lambda environment: connect to Proxy endpoint (not RDS directly)
# DB_HOST = myapp-proxy.proxy-abc123.us-east-1.rds.amazonaws.com

# For self-managed PgBouncer on EC2:
# Application → EC2 PgBouncer (port 5432) → RDS PostgreSQL
# PgBouncer maintains 20 backend connections
# Lambda can have 500+ concurrent connections to PgBouncer safely

Monitoring PgBouncer in Production

PgBouncer exposes operational metrics through its admin interface — a virtual database named pgbouncer accessible on the pooler's listen port. SHOW POOLS gives per-pool utilisation with client and server connection counts split by state. SHOW STATS gives aggregate throughput metrics including average query time, average wait time, and total requests.

SHOW CLIENTS and SHOW SERVERS give per-connection detail useful during incident investigation. For continuous monitoring, pgbouncer_exporter is a Prometheus exporter that scrapes the admin interface and exposes all metrics for Grafana dashboards. The three metrics that matter most in production: cl_waiting (should be zero or near-zero at steady state), avg_wait_time (should stay under 5ms for OLTP workloads), and sv_idle (should stay above zero — if it reaches zero, increase pool_size before clients start queuing).

Set alerting on cl_waiting greater than 10 for more than 30 seconds — that is the leading indicator of connection starvation before it manifests as application errors.

-- Prometheus alerting rules for PgBouncer
# Alert if clients are waiting (connection starvation starting)
- alert: PgBouncerClientsWaiting
  expr: pgbouncer_pools_cl_waiting > 5
  for: 30s
  labels:
    severity: warning
  annotations:
    summary: "PgBouncer clients waiting for connections"
    description: "{{ $value }} clients waiting in pool {{ $labels.database }}/{{ $labels.user }}"

# Alert if average wait time exceeds 10ms
- alert: PgBouncerHighWaitTime
  expr: pgbouncer_stats_avg_wait_time_seconds > 0.01
  for: 1m
  labels:
    severity: warning

# Alert if no idle server connections (pool fully saturated)
- alert: PgBouncerPoolSaturated
  expr: pgbouncer_pools_sv_idle == 0 and pgbouncer_pools_sv_active > 0
  for: 30s
  labels:
    severity: critical

Related: our database troubleshooting service