Connection Pool Exhaustion | PG Pilot PostgreSQL Monitoring

What is this issue?

PostgreSQL has a hard limit on concurrent connections set by max_connections.
When this limit is reached, new connection attempts fail with:

FATAL: too many connections for role "username"
FATAL: sorry, too many clients already

Each connection consumes memory (typically 5-10 MB), so the limit exists
to prevent memory exhaustion.

Why it matters

Application Failures

New requests can't connect and fail immediately

Admin Lockout

Even administrators can't connect to diagnose issues

Cascade Failures

Connection pool exhaustion spreads across application instances

No Self-Healing

Often requires manual intervention to recover

How PG Pilot detects it

```sql
SELECT
  count(*) AS current_connections,
  current_setting('max_connections')::int AS max_connections,
  current_setting('superuser_reserved_connections')::int AS reserved,
  round(100.0 * count(*) /
    (current_setting('max_connections')::int -
     current_setting('superuser_reserved_connections')::int), 2) AS usage_pct
FROM pg_stat_activity;
```

How to fix it

1

Identify connection consumers

Find what's using connections:

SELECT
  usename,
  application_name,
  client_addr,
  count(*) AS connections
FROM pg_stat_activity
GROUP BY usename, application_name, client_addr
ORDER BY connections DESC;

2

Terminate idle connections

Close connections that have been idle for a long time:

SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND state_change < now() - interval '1 hour'
  AND pid != pg_backend_pid();

3

Review application connection pooling

Check if applications are:
- Using connection pools (PgBouncer, built-in pools)
- Properly releasing connections after use
- Setting appropriate pool sizes

4

Increase max_connections if needed

If connections are legitimately needed:

ALTER SYSTEM SET max_connections = 200;

Requires restart. Also ensure sufficient memory.

Prevention

Use connection poolers (PgBouncer) for high-concurrency apps
Set idle connection timeouts in application pools
Monitor connection usage with PG Pilot
Reserve connections for superusers (superuser_reserved_connections)

Related Features

Connection Management

PostgreSQL Documentation

PostgreSQL Connection Settings