Too many connections

Symptom: FATAL: sorry, too many clients already, or new connections hang while existing ones work.

Confirm

show max_connections;

select state, count(*) from pg_stat_activity
where backend_type = 'client backend' group by 1 order by 2 desc;

select usename, application_name, count(*) from pg_stat_activity
group by 1, 2 order by 3 desc limit 10;

Mostly idle means too many pooled or leaked connections. Mostly idle in transaction means clients that opened a transaction and never finished it.

Causes

• No connection pooler, so every app process holds its own connections.
• Pool size times instance count is larger than max_connections, often after autoscaling.
• Code paths that leak connections, or transactions held open across slow calls.

Fix

1. Put a pooler in front (PgBouncer in transaction mode, or your provider's pooler). Size the pool near 2 to 4 times your CPU cores, not by traffic.
2. Cap forgotten sessions:

   alter role app_user set idle_in_transaction_session_timeout = '60s';
   alter role app_user set idle_session_timeout = '30min';  -- PostgreSQL 14+
   

3. Free slots right now by ending old idle sessions:

   select pg_terminate_backend(pid) from pg_stat_activity
   where state = 'idle' and state_change < now() - interval '1 hour' and usename = 'app_user';
   

Raising max_connections needs a restart and costs memory per backend. It is the last resort, not the first.

Prevent

Alert at 80% of max_connections. Keep superuser_reserved_connections so you can always get in to fix things.