Connection Saturation

Overview

Postgres allocates a backend process per connection. Each backend uses ~5–10 MB of memory and a file descriptor. At high connection counts, Postgres spends increasing time managing locks, shared memory, and backend overhead rather than executing queries. Above ~200 connections, performance typically degrades even without lock contention.

More critically, once connections reach max_connections, new connection attempts receive an error immediately—breaking the application. insightral fires this rule when connection usage exceeds 80% of max_connections, giving time to act before saturation.

The correct long-term fix is a connection pooler (PgBouncer in transaction mode), not increasing max_connections, which just defers the problem.

Detection SQL

Run this query with a read-only role. No writes, no locks.

-- Connection usage as percentage of max_connections
SELECT
  max_conn.setting::int                           AS max_connections,
  count(a.pid)                                    AS active_connections,
  round(
    count(a.pid)::numeric / max_conn.setting::numeric * 100,
    1
  )                                               AS used_pct,
  count(a.pid) FILTER (WHERE a.state = 'active')  AS executing,
  count(a.pid) FILTER (WHERE a.state = 'idle')    AS idle,
  count(a.pid) FILTER (WHERE a.wait_event_type = 'Lock') AS waiting_on_lock
FROM pg_stat_activity a
CROSS JOIN (SELECT setting FROM pg_settings WHERE name = 'max_connections') max_conn
WHERE a.datname = current_database()
GROUP BY max_conn.setting;

Fix SQL

Replace placeholders in <angle brackets> with values from the detection output before running.

-- Short term: terminate idle connections to free slots
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND query_start < now() - interval '10 minutes';

-- Long term: introduce a connection pooler (PgBouncer)
-- In pgbouncer.ini:
-- pool_mode = transaction
-- max_client_conn = 1000
-- default_pool_size = 25

Worked example

Common false positives

• A superuser connection from pg_dump or a monitoring agent counts toward max_connections — reserve connections via superuser_reserved_connections (default 3).
• Short-lived spikes during deployment rollouts where old and new pods briefly coexist — this is transient and not actionable.