📋

Description

This error indicates that the PostgreSQL server has reached its configured limit for concurrent client connections. It typically occurs when a large number of applications or users attempt to connect simultaneously, exhausting available server resources and preventing new connections.

💬

Error Message

too many connections

🔍

Known Causes

4 known causes

⚠️

Default Connection Limit Reached

The PostgreSQL server's `max_connections` parameter is set to a default or low value, which is easily exceeded by application demands.

⚠️

Inefficient Application Connections

Applications are opening new connections without properly closing old ones, or not utilizing connection pooling effectively, leading to a rapid accumulation of open sessions.

⚠️

High User Traffic Spike

An unexpected or unusually high volume of concurrent users or client applications attempts to connect to the database within a short period.

⚠️

Stale or Idle Connections

Previous connections remain open and idle, consuming connection slots even when not actively being used, preventing new connections from being established.

🛠️

Solutions

4 solutions available

1. Increase max_connections easy

Allow more simultaneous connections

1

Check current setting

SHOW max_connections;

2

Increase in postgresql.conf

# In postgresql.conf:
max_connections = 200  # Default is 100

3

Restart PostgreSQL

sudo systemctl restart postgresql

2. Use Connection Pooling medium

Pool connections with PgBouncer

1

Install PgBouncer

sudo apt install pgbouncer

2

Configure pgbouncer.ini

[databases]
mydb = host=127.0.0.1 port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = *
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20

3

Start PgBouncer

sudo systemctl start pgbouncer

4

Connect via PgBouncer

psql -h localhost -p 6432 -d mydb

3. Close Idle Connections easy

Free up unused connections

1

Find idle connections

SELECT pid, usename, application_name, state, query_start
FROM pg_stat_activity
WHERE state = 'idle'
ORDER BY query_start;

2

Terminate old idle connections

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
AND query_start < NOW() - INTERVAL '1 hour';

3

Set idle timeout

-- In postgresql.conf (PostgreSQL 14+):
idle_session_timeout = '10min'

4. Fix Connection Leaks medium

Ensure application closes connections

1

Check connections by application

SELECT application_name, count(*)
FROM pg_stat_activity
GROUP BY application_name
ORDER BY count DESC;

2

Fix in application code

# Python - Always close or use context manager
with psycopg2.connect(dsn) as conn:
    with conn.cursor() as cur:
        cur.execute(query)
# Connection returned to pool on exit

🔗

Related Errors

5 related errors

PostgreSQL Error 53300: Too Many Connections