Stale connections

Symptom: pg_stat_activity shows dozens or hundreds of sessions that have been idle for hours, the connection count creeps up until too many clients, or an application restarted and its old sessions are still there.

Is it stale, or just pooled?

An application pool keeps idle connections open on purpose, so idle alone is fine. Stale means the client is gone but the server hasn't noticed. Compare with what you expect:

select usename, application_name, client_addr, count(*) as sessions,
       max(now() - state_change) as longest_idle, min(backend_start) as oldest_connection
from pg_stat_activity
where backend_type = 'client backend' and state = 'idle'
group by 1, 2, 3 order by sessions desc;

If a service's pool size is 20 and it shows 200 sessions, or the address belongs to a machine that has been replaced, they're stale.

If state_change is old and the state is idle, Postgres never saw anything go wrong. Nothing on the server will end that session until the operating system's TCP keepalive gives up.

Why it happens

• The client process was killed or crashed, or the machine was terminated (common with autoscaling and deploys).
• A firewall, NAT gateway or load balancer silently dropped the connection after its own idle timeout, without telling either side.
• Postgres' keepalive settings default to 0, which means the operating system default: usually two hours before the first probe.

Fix

1. Make the server notice dead peers quickly (best first step; it doesn't touch healthy connections):

   tcp_keepalives_idle = 60
   tcp_keepalives_interval = 10
   tcp_keepalives_count = 6
   

   A dead peer is now dropped in about two minutes. On PostgreSQL 14 and later also set client_connection_check_interval = '10s', so a long-running query stops when its client has gone.
2. Set the pool's idle timeout lower than your firewall's (for example 5 minutes against a firewall's 10), so the pool closes connections before the network silently kills them.
3. Use a pooler such as PgBouncer with server_idle_timeout, so idle server connections are recycled.
4. idle_session_timeout (PostgreSQL 14+) ends sessions idle longer than the limit. It's blunt: it also closes healthy pooled connections, so clients that don't reconnect cleanly will error. Use it only when your clients handle that.

To clear stale sessions right now (check the list first):

select pg_terminate_backend(pid) from pg_stat_activity
where backend_type = 'client backend' and state = 'idle' and state_change < now() - interval '2 hours'
  and usename = 'app_user' and pid <> pg_backend_pid();

Idle in transaction is different

A session in idle in transaction holds locks and blocks vacuum. It is much more harmful than a plain idle one. Set idle_in_transaction_session_timeout = '60s' for application roles. See Locks and blocked queries.