Postgres too many connections error: causes and fixes

PostgreSQL refuses new logins with "FATAL: sorry, too many clients already" when every slot allowed by max_connections (100 by default) is taken, and with "too many connections for role" or "for database" when a connection limit on a role or database is reached. For quick relief, find idle, leaked and idle-in-transaction sessions in pg_stat_activity and close them. For a lasting fix, pool connections, add timeouts and give each service a connection budget, rather than simply raising max_connections.

What the error means

Each variant tells you which limit you hit. All of them carry the SQLSTATE code 53300, too_many_connections, which is useful for alerts and client retry logic.

Two lookalikes come from elsewhere. "QueuePool limit ... reached" (SQLAlchemy) or a PoolTimeout (psycopg) means your application's own pool is full, not the server. "no more connections allowed (max_client_conn)" comes from PgBouncer, whose client limit is 100 unless you raise it. On Amazon RDS, the default max_connections for PostgreSQL scales with instance memory: LEAST({DBInstanceClassMemory/9531392}, 5000).

Diagnose with pg_stat_activity

To diagnose, you first need a free slot. The superuser reserve exists for exactly this, though on managed services the admin user isn't always a true superuser, so check your provider's documentation. Then find out who holds the connections:

SHOW max_connections;

SELECT count(*) AS client_connections
FROM pg_stat_activity
WHERE backend_type = 'client backend';

SELECT usename, application_name, client_addr, state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY 1, 2, 3, 4
ORDER BY 5 DESC;

The state column is the key. active means running a query; idle means connected and waiting for the next command; idle in transaction means inside an open transaction but doing nothing. To see which transactions have been open longest:

SELECT pid, usename, application_name, client_addr,
       now() - xact_start   AS transaction_age,
       now() - state_change AS in_state_for,
       left(query, 60)      AS last_query
FROM pg_stat_activity
WHERE state IN ('idle in transaction', 'idle in transaction (aborted)')
ORDER BY xact_start;

How to read the results:

• Hundreds of idle connections from one service mean oversized pools, a persistent connection per thread, or a leak.
  
  
• idle in transaction sessions minutes old mean code opened a transaction and then waited on something else. Besides taking a slot, they hold locks and stop vacuum from cleaning up recently deleted rows.
  
  
• Many active sessions mean slow queries or genuine overload. Each request holds its connection longer, so more are needed at once.
  
  

Set application_name in every service's connection settings, such as web, celery or reports, and this table tells you instantly who is responsible.

Common causes

1. Pools multiplied by processes. Thirty app servers × eight Gunicorn workers × a pool of four is 960 connections before anyone notices, and autoscaling makes it worse.
   
   
2. A persistent connection per thread. Django with CONN_MAX_AGE above 0 keeps one connection per worker thread, used or not.
   
   
3. Leaks. Scripts, cron jobs, notebooks or background threads open connections and never close them.
   
   
4. Idle in transaction. A transaction stays open while the code calls an SMS gateway, waits on a user, or hits an error path that forgets to roll back.
   
   
5. Slow queries. A missing index makes every request hold its connection longer, so concurrency rises with no change in traffic.
   
   
6. Reconnect storms. After a restart or failover, every client reconnects at the same moment.
   
   
7. Side tools. Dashboards, admin tools and migration jobs connecting directly, each with its own pool.
   
   

Quick relief

When the database is refusing connections during an exam, start by ending transactions that have clearly been abandoned. Terminating a session rolls back its open transaction, so be specific about which sessions you target:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
  AND state = 'idle in transaction'
  AND now() - state_change > interval '10 minutes';

Then buy more time:

• Restart or scale down the leaking service. Fewer workers or pods release their connections at once.
  
  
• Pause non-essential consumers, such as report exports and analytics jobs, until the peak passes.
  
  
• Stop it recurring. Set a timeout for the application role, which applies to its new sessions: ALTER ROLE app SET idle_in_transaction_session_timeout = '60s';
  
  

Why not just raise max_connections?

Raising the limit makes the error go away, and can make the system slower. The reasons:

• It needs a restart. max_connections can only be set at server start, and a standby must be set at least as high as its primary.
  
  
• It costs memory before anyone connects. PostgreSQL sizes shared memory, including the lock table, from this setting.
  
  
• Active connections compete. Past the point where CPU is saturated, more concurrent queries lower total throughput. Memory can also run away, because work_mem applies to each sort or hash step of each query: in an illustrative case, 500 active connections running three 16 MB sorts each could want about 24 GB. AWS warns that setting connection limits too high on RDS can cause a low-memory condition.
  
  
• Idle connections aren't free either. In a 2020 analysis on PostgreSQL 12, Andres Freund measured 10,000 idle connections roughly halving the throughput of 48 active ones. PostgreSQL 14 reduced that cost a lot, but it didn't make connections free.
  
  
• It treats the symptom. A leak fills any limit you set; it just takes longer.
  
  

A modest increase is reasonable once pooling is in place, to leave room for monitoring, replication and administration. Going from 100 to 2,000 to fit an unpooled fleet is the move to avoid.

Permanent fixes

1. Pool connections in two tiers: small pools in each process and PgBouncer in transaction mode in front of the database, so thousands of client connections share a few dozen server connections.
   
   
2. Budget by service. Give each service its own database role with a CONNECTION LIMIT and its own PgBouncer pool size, and cap the autoscaler's maximum so the budget holds at full scale.
   
   
3. Set timeouts. idle_in_transaction_session_timeout (off by default) ends abandoned transactions, statement_timeout ends runaway queries, and PostgreSQL 17 added transaction_timeout. The documentation warns against idle_session_timeout on connections made through a pooler, which may not handle unexpected disconnects well.
   
   
4. Fix leaks. Close connections in scripts, and in long-running Django processes call django.db.close_old_connections().
   
   
5. Size pools from real numbers. Sizing a connection pool with Little's Law shows how many connections your traffic needs.
   
   
6. Monitor before it breaks. As part of your monitoring and alerting, alert when connections pass about 80% of max_connections, when idle-in-transaction sessions grow old, and when PgBouncer clients start waiting.
   
   
7. Retry politely. Clients that see error 53300 should back off with random jitter, not reconnect in a tight loop.
   
   

The background on why pools exist is in our guide to connection pooling. The PostgreSQL documentation on connection settings and the pg_stat_activity view is the reference for everything above.

Key takeaways

• "Too many clients" means max_connections is full; the "for role" and "for database" variants mean a per-role or per-database limit was hit.
  
  
• Use pg_stat_activity, grouped by user, application and state, to find who holds the connections.
  
  
• For quick relief, end abandoned idle-in-transaction sessions and scale down leaking services.
  
  
• Raising max_connections costs memory and can lower throughput; it hides the cause instead of fixing it.
  
  
• Pooling, per-service budgets, timeouts and monitoring are the permanent fix.
  
  

Frequently asked questions

How to increase max connections in PostgreSQL?

Set a new value in postgresql.conf, or run ALTER SYSTEM SET max_connections = 200; as a superuser, then restart PostgreSQL, because this setting only takes effect at server start. On managed services such as Amazon RDS, change it in the database's parameter group and reboot. Before increasing it, check memory headroom and whether a connection pooler would solve the problem better, and remember that standbys need a value at least as high as the primary.

How to get number of connections in Postgres?

Run SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend'; for current client connections, and SHOW max_connections; for the limit. Group the same view by usename, application_name and state to see who holds connections and whether they are active, idle or idle in transaction. The numbackends column of pg_stat_database gives the count for each database.

How to change max connections in PostgreSQL?

The value can come from postgresql.conf, from ALTER SYSTEM, which writes it to postgresql.auto.conf, or from the server's start-up command. Whichever you use, a configuration reload isn't enough; PostgreSQL must restart. Afterwards, confirm the new value with SHOW max_connections;. On Amazon RDS the default is calculated from instance memory, so you change it through a custom parameter group rather than the file.