How to Diagnose and Fix PostgreSQL 'Too Many Connections' Error

If your application suddenly throws this error under load, your PostgreSQL server has hit its connection limit:

FATAL: remaining connection slots are reserved for non-replication superuser connections

Or its simpler variant:

FATAL: sorry, too many clients already

This is one of the most disruptive database errors in production environments, as it prevents all new connections — including from your application, monitoring agents, and even your own psql shell. On a dedicated VPS running PostgreSQL, understanding and resolving this properly is critical.

Why PostgreSQL Has a Connection Limit

Unlike MySQL, which uses a thread-per-connection model, PostgreSQL spawns a separate OS process for every connection. Each connection consumes:

• ~5-10MB of RAM for the backend process
• File descriptors from the OS limit
• Shared memory segments for buffer access

The default max_connections = 100 on most PostgreSQL installations means a maximum of 100 simultaneous connections. With 3 reserved for superusers, your applications effectively have 97 slots. For high-traffic web applications where each web worker opens its own DB connection, this fills up instantly.

Step 1: Check Current Connection Usage

Connect as a superuser and run:

-- Total connections and their states
SELECT state, count(*) 
FROM pg_stat_activity 
GROUP BY state 
ORDER BY count DESC;

-- Connections by application and user
SELECT usename, application_name, client_addr, state, count(*)
FROM pg_stat_activity
GROUP BY usename, application_name, client_addr, state
ORDER BY count DESC;

The output often reveals the culprit:

• idle connections in large numbers = application is not releasing connections back to the pool
• idle in transaction connections = application opened a transaction but never committed or rolled back
• active connections = legitimate queries running

Step 2: Immediate Mitigation — Terminate Idle Connections

To immediately free slots without restarting PostgreSQL:

-- Terminate all idle connections older than 10 minutes (excluding your own)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < NOW() - INTERVAL '10 minutes'
  AND pid <> pg_backend_pid();

-- Terminate ALL idle-in-transaction connections (potential lock holders)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND pid <> pg_backend_pid();

Step 3: Increase max_connections (Temporary Fix)

Edit /etc/postgresql/15/main/postgresql.conf:

# Increase from default 100 to 300
max_connections = 300

# Also increase shared_buffers proportionally
# Rule of thumb: 25% of available RAM
shared_buffers = 2GB  # For an 8GB RAM server

Restart PostgreSQL: sudo systemctl restart postgresql

Warning: Simply increasing max_connections without proper RAM is dangerous. Each connection requires memory. Calculate: max_connections × 10MB = minimum RAM reserved for connections. For 300 connections, that’s 3GB of RAM just for connection overhead.

Step 4: The Real Solution — PgBouncer Connection Pooling

The permanent, scalable fix is PgBouncer, a lightweight PostgreSQL connection pooler. PgBouncer sits between your application and PostgreSQL, maintaining a small pool of actual PostgreSQL connections and multiplexing thousands of application connections across them.

Install PgBouncer

sudo apt install -y pgbouncer

Configure PgBouncer

Edit /etc/pgbouncer/pgbouncer.ini:

[databases]
; Map your database name to the PostgreSQL server
myapp_db = host=127.0.0.1 port=5432 dbname=myapp_db

[pgbouncer]
listen_addr = 127.0.0.1
listen_port = 6432          ; Applications connect to port 6432 (PgBouncer)
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

; TRANSACTION mode is best for most web apps
pool_mode = transaction

; Max connections PgBouncer will open TO PostgreSQL
server_pool_size = 25

; Max connections FROM applications TO PgBouncer (can be thousands)
max_client_conn = 1000

; Log settings
log_connections = 0
log_disconnections = 0

Create the user authentication file:

# Get the MD5 hash of your password
echo -n "md5$(echo -n "yourpassword<username>" | md5sum | cut -d' ' -f1)"

# Add to userlist.txt in format: "username" "md5hash"
echo '"myapp_user" "md5abc123..."' | sudo tee /etc/pgbouncer/userlist.txt

Start and enable PgBouncer:

sudo systemctl enable pgbouncer
sudo systemctl start pgbouncer

Now update your application’s database connection string to use port 6432 (PgBouncer) instead of 5432 (PostgreSQL directly). PgBouncer handles all pooling transparently.

Step 5: Set Idle Connection Timeouts in postgresql.conf

Prevent connection accumulation by automatically terminating idle connections at the server level:

# Terminate idle connections after 10 minutes
idle_in_transaction_session_timeout = 600000  # 10 min in ms
tcp_keepalives_idle = 60
tcp_keepalives_interval = 10
tcp_keepalives_count = 6

For related database performance tuning on MySQL-based stacks, also see our guide on optimizing MySQL InnoDB buffer pool size. The same principle of right-sizing memory allocation for your workload applies across both database engines.

Conclusion

The too many connections error in PostgreSQL is a symptom of a connection management problem, not a database failure. By auditing idle connections, setting appropriate timeouts, and deploying PgBouncer as a connection pooler, you can support thousands of simultaneous application users while keeping PostgreSQL’s actual connection count low, efficient, and stable.

Configure high-throughput PostgreSQL connection pooling on Nextgen’s Pakistan VPS.