Knowledge Base

Issue

Production PostgreSQL cluster becomes unreachable for write operations due to connection pool exhaustion. All 500 available connections are consumed, causing new connection attempts to fail with FATAL: connection limit exceeded.

Root Cause

Connection pool exhaustion is typically caused by one or more of the following:

Long-running idle transactions blocking connection release

PgBouncer misconfiguration following infrastructure changes

Application connection leak introduced by a recent deployment

Sudden traffic spike exceeding pool capacity without connection timeout settings

Resolution Steps

Immediate Mitigation (< 5 minutes)

-- Identify idle connections holding locks
SELECT pid, usename, application_name, state, query_start, now() - query_start AS duration
FROM pg_stat_activity
WHERE state = 'idle'
AND query_start < now() - interval '5 minutes'
ORDER BY duration DESC;

-- Terminate idle connections older than 5 minutes
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
AND query_start < now() - interval '5 minutes';

Root Cause Investigation

Check PgBouncer pool settings: SHOW POOLS; in pgbouncer console

Review recent deployments for connection handling changes

Check max_connections setting in postgresql.conf

Review application logs for connection leak patterns

Long-term Fix

Implement connection timeout: statement_timeout = '30s'

Configure PgBouncer transaction pooling mode

Set idle_in_transaction_session_timeout = '5min'

Add connection pool monitoring alert at 80% utilization