CRITICAL

How to Fix PostgreSQL FATAL: sorry, too many clients already

Quick Fix Summary

TL;DR

Immediately kill idle connections and increase `max_connections` in `postgresql.conf`, then restart the service.

PostgreSQL has reached its configured maximum number of concurrent client connections (`max_connections`). No new connections can be established until existing ones are closed or the limit is raised. This is a hard stop for application traffic.

Diagnosis & Causes

Application connection leaks or missing connection.close().

`max_connections` set too low for production load.

Missing or misconfigured connection pooling (e.g., PgBouncer).

Sudden traffic spike overwhelming the database.

Long-running queries or idle transactions holding connections.

Recovery Steps

Step 1: Emergency Relief - Terminate Idle Connections

Immediately free up slots by killing non-critical, idle connections. This is a temporary fix to restore service.

sql

SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE state = 'idle' AND pid <> pg_backend_pid();
-- More aggressive: kill all connections from a specific app user
SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE usename = 'your_app_user';

Step 2: Diagnose the Connection Load

Identify what is consuming all connections to understand the root cause before making permanent changes.

sql

SELECT usename, application_name, client_addr, state, count(*)
FROM pg_stat_activity
GROUP BY 1,2,3,4
ORDER BY count DESC;

-- Check current vs. max connections
SELECT count(*) as current_connections,
       (SELECT setting FROM pg_settings WHERE name='max_connections') as max_connections
FROM pg_stat_activity;

Step 3: Increase `max_connections` and `shared_buffers`

Permanently raise the connection limit. **Crucial:** You must also increase `shared_buffers` proportionally to avoid performance collapse.

bash

# Edit postgresql.conf (location varies)
sudo nano /etc/postgresql/16/main/postgresql.conf

# Find and update these values. Example for increasing from 100 to 300:
max_connections = 300
shared_buffers = 768MB  # ~25% of system RAM for this connection count

# Restart PostgreSQL to apply changes
sudo systemctl restart postgresql

Step 4: Implement PgBouncer for Connection Pooling (Production Mandate)

A connection pooler like PgBouncer is non-negotiable for production. It maintains a small pool of database connections shared by many application clients.

bash

# 1. Install PgBouncer
sudo apt-get install pgbouncer

# 2. Configure /etc/pgbouncer/pgbouncer.ini
[databases]
yourdb = host=localhost port=5432 dbname=yourdb

[pgbouncer]
pool_mode = transaction  # Best for most apps
max_client_conn = 1000
default_pool_size = 20  # Only 20 connections to PostgreSQL!

# 3. Point your application to PgBouncer port (6432) instead of 5432.

Step 5: Configure Application-Level Timeouts

Prevent connection leaks by enforcing strict timeouts in your application's database configuration.

python

# Example for a Python/Psycopg2 application
import psycopg2
from psycopg2 import pool

# Use a connection pool with timeout
connection_pool = psycopg2.pool.SimpleConnectionPool(
    1,  # minconn
    20, # maxconn - FAR less than PostgreSQL's max_connections
    host='localhost',
    database='mydb',
    user='myuser',
    password='mypass',
    connect_timeout=5  # Fail fast
)

Step 6: Monitor and Set Alerts

Proactively monitor connection usage to prevent future outages.

sql

-- Create a monitoring query (run via cron or monitoring tool)
SELECT
  (SELECT setting FROM pg_settings WHERE name='max_connections')::int as max_conn,
  count(*) as used_conn,
  (count(*) * 100.0 / (SELECT setting FROM pg_settings WHERE name='max_connections')::int) as percent_used
FROM pg_stat_activity;

-- Alert if percent_used > 80% for 5 minutes.

Architect's Pro Tip

"Never set `max_connections` above 500 without expert tuning. Each connection consumes ~10MB of RAM. Use PgBouncer in 'transaction' pooling mode to support thousands of app clients with just 20-50 real PostgreSQL connections."

Frequently Asked Questions

I increased max_connections but still get the error. Why?

You must restart the PostgreSQL service (`sudo systemctl restart postgresql`) for the new `max_connections` value in `postgresql.conf` to take effect. A reload (`pg_ctl reload`) is insufficient for this parameter.

What's the difference between PgBouncer and application connection pooling?

Application pooling (e.g., HikariCP, PgBouncer in 'session' mode) manages connections per app instance. PgBouncer in 'transaction' mode is a system-level pooler that shares connections across *all* app instances, dramatically reducing the load on PostgreSQL itself.

How high can I safely set max_connections?

The practical limit is dictated by your server's RAM (`work_mem` * max_connections + shared_buffers + OS). Exceeding 300-500 connections without a pooler often leads to memory contention and poor performance. Always use a pooler for high client counts.