Victor's room

Quick Summary: The error FATAL: sorry, too many clients already occurs in PostgreSQL when incoming client connections exceed the configured max_connections threshold. This typically stems from application connection leaks, unpooled database clients opening new TCP sockets per request, lingering idle transactions (idle in transaction), or an undersized server connection limit.

Understanding the Root Causes

Each client connection in PostgreSQL spawns a dedicated backend OS process, consuming memory for connection state, work memory, and shared buffer management. When active clients reach the max_connections ceiling (reserved connections excluded for superusers), PostgreSQL immediately rejects all new non-superuser connections. Common triggers include:

• Application Connection Leaking: Web application threads or microservices failing to close database connections in finally blocks or after HTTP requests terminate.
• Lack of Connection Pooling: High-concurrency web servers (e.g., Gunicorn, Puma, Node.js clusters) establishing hundreds of direct connections instead of reusing a managed pool.
• Lingering “Idle in Transaction” Sessions: Long-running transactions holding locks and connection slots open indefinitely due to uncommitted queries or hanging external API calls.
• Default max_connections Limits: Running on default settings (typically 100 connections) under growing workload demands without an intermediate pooler.

Step 1: Quick Fix – Identify and Terminate Idle Connections

If you are locked out of normal access, connect as a PostgreSQL superuser (PostgreSQL reserves superuser_reserved_connections, default 3, for admin triage) and inspect connection states:

-- Connect as superuser (e.g., postgres)
psql -U postgres -d postgres

-- Check total connections grouped by database and state
SELECT datname, state, count(*) 
FROM pg_stat_activity 
GROUP BY datname, state 
ORDER BY count(*) DESC;

-- Identify idle in transaction queries running longer than 5 minutes
SELECT pid, usename, client_addr, state, now() - state_change AS idle_duration, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND (now() - state_change) > interval '5 minutes';

-- Terminate lingering idle connections safely (replace <pid>)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND (now() - state_change) > interval '10 minutes'
  AND pid <> pg_backend_pid();

Step 2: Increase max_connections and Optimize Server Settings

If your workload genuinely requires more concurrent connections and your server has sufficient RAM, adjust max_connections in postgresql.conf:

# Locate postgresql.conf
SHOW config_file;

# Edit postgresql.conf using a text editor
sudo nano /etc/postgresql/15/main/postgresql.conf

# Adjust the connection and timeout parameters:
max_connections = 300                  # Increase from default 100 based on RAM
superuser_reserved_connections = 5      # Ensure admin access during saturation
idle_in_transaction_session_timeout = 60000  # Automatically terminate idle transactions after 60s

# Restart PostgreSQL to apply changes (max_connections requires a restart)
sudo systemctl restart postgresql

Step 3: Deploy Connection Pooling with PgBouncer

Increasing max_connections beyond a few hundred degrades CPU cache efficiency and query performance. The recommended architectural fix is deploying an external connection pooler like PgBouncer:

# Install PgBouncer on Debian/Ubuntu
sudo apt-get update && sudo apt-get install pgbouncer -y

# Configure /etc/pgbouncer/pgbouncer.ini
[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 25

# Enable and start PgBouncer
sudo systemctl enable --now pgbouncer

# Update application connection strings to use port 6432 instead of 5432

Verification & Testing Steps

Verify that connection utilization is within safe thresholds and test the new pooling or connection limits:

-- Check current connection usage percentage
SELECT 
  count(*) AS current_connections,
  current_setting('max_connections')::int AS max_connections,
  round((count(*)::numeric / current_setting('max_connections')::numeric) * 100, 2) AS usage_percent
FROM pg_stat_activity;

-- Test that new client connections connect without errors
psql -h 127.0.0.1 -p 6432 -U myuser -d mydb -c "SELECT 1 AS connection_test;"

Troubleshooting Summary Comparison

Issue Scenario	Primary Root Cause	Recommended Fix
Immediate Lockout	Stale ‘idle in transaction’ sessions	Kill idle PIDs with pg_terminate_backend()
Traffic Spikes	Undersized default max_connections	Increase limit in postgresql.conf and restart
App Connection Leaks	Missing connection close in code	Set idle_in_transaction_session_timeout
High Concurrency (>300 clients)	PostgreSQL process-per-client overhead	Deploy PgBouncer in transaction pooling mode