Postgres Too Many Clients — PgBouncer Pool Fix

FATAL: sorry, too many clients already. Postgres says it flatly and hangs up — no queue, no waiting room, just a refused connection. It lands during deploys, autoscale events, and cron stampedes: the database is calm, queries are fast, and nothing can connect. With the default max_connections of 100, it takes remarkably little to fill the house.

Each Postgres connection is a forked backend process with its own memory — there is no cheap thread to hand out. App pools that look modest per instance multiply across pods into demand the server can't honor, and idle-in-transaction sessions camp on slots for hours while doing nothing.

This guide covers the drill and the architecture: confirming saturation in pg_stat_activity, pruning idle holders safely, inserting PgBouncer in transaction mode, sizing app pools to the new budget, and adding timeouts so the house can't fill with ghosts again. The same drill rescues cron stampedes, runaway reporting jobs, and deploys that forgot the pooler — connection exhaustion always rhymes.

The Error Means the Doorman Is Full

Sorry, too many clients already is Postgres refusing at the door: backend count hit max_connections and your connection wasn't a superuser with a reserved slot. There's no queue and no graceful degradation — the server hangs up mid-handshake. With the 100 default, a single service with 10 pods × 10 pool slots fills the house alone, before cron, replicas, humans, or the second microservice open a thing.

Confirm with three numbers from a superuser session: max_connections (the ceiling), superuser_reserved_connections (the fire escape, default 3), and count(*) from pg_stat_activity (current occupancy). Full house with calm CPU is the signature — it separates connection exhaustion from every query-performance problem instantly, and it tells the team to stop tuning SQL and start counting holders.

Memory is the reason the ceiling stays low. Each backend carries its own work_mem allotments, buffers, and process overhead; 400 real backends need gigabytes that 100 never did. Raising the ceiling without RAM math converts refused connections into swapping and OOM kills — the incident that takes down existing backends too. Budget the ceiling for memory, then fit demand under it with pooling.

-- Ceiling, fire escape, current occupancy
SHOW max_connections;
SHOW superuser_reserved_connections;
SELECT count(*) AS backends FROM pg_stat_activity;

-- Occupancy by state (holders vs real work)
SELECT state, COUNT(*) AS n
FROM pg_stat_activity
GROUP BY state
ORDER BY n DESC;

pg_stat_activity: Who Holds the Slots

pg_stat_activity is the complete roster: pid, user, application_name, client address, state, and current query per backend. Group by state first — active is real work, idle is pool reservations, idle in transaction is the dangerous middle: a transaction holding locks and an xmin horizon (blocking VACUUM) while the app does something else entirely. Twenty-five such rows from one reporting job can stall a whole database while looking like nothing.

Group by application_name second to name the owner. One service with 200 idle rows is an oversized pool; fifty crons with 3 each is a leak pattern; a human psql idle in transaction for 40 minutes is a forgotten console holding production locks. The idle_for computation (now() - state_change) ranks victims for termination: oldest idle first, since nothing real can have waited that long.

Snapshot the grouped output into the ticket before terminating anything. It justifies the pool change ('service X held 312 of 400 slots in Sleep') and preserves evidence that evaporates the moment backends die. Terminate from data, and let the roster rank your targets. Re-run the grouping after each prune round — the falling top row proves you are draining the real culprit, not a symptom.

-- Holders by state, user, and application (biggest group = culprit)
SELECT state, usename, application_name, COUNT(*) AS n
FROM pg_stat_activity
GROUP BY 1, 2, 3
ORDER BY n DESC;

-- Oldest abandoned transactions first (termination candidates)
SELECT pid, usename, application_name, client_addr,
  now() - state_change AS idle_for, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY state_change
LIMIT 20;

Terminate Safely: pg_terminate_backend

pg_terminate_backend(pid) kills the whole backend: connection gone, open transaction rolled back, locks released. For idle rows that's pure recovery — nothing real dies. For idle in transaction it abandons the transaction, which is usually exactly what you want (the app forgot it), at the cost of rolling back whatever it had staged. Check the query column first so you know what you're discarding; a 40-minute 'SELECT * FROM huge' in a forgotten console is safe, a migration's DDL is not.

Prefer pg_cancel_backend(pid) for active runaways: it stops the query but keeps the connection, so the app's pool slot survives and no reconnect storm follows. Reserve terminate for idle states, and never script blanket termination of active backends — murdering 50 running checkouts to free slots converts a connection incident into a data and retry incident.

Terminate oldest-first in small batches, re-checking occupancy between rounds. Each batch frees slots for the app immediately, and stopping once recovery begins leaves innocent holders alive. Log every terminated pid with its idle age and query — the postmortem needs the list, and 'we killed 60 ghosts' reads better with receipts attached for the review.

-- Reclaim idle holders older than 10 minutes (pure recovery)
SELECT pg_terminate_backend(pid)
FROM pg_stat_activity
WHERE state = 'idle'
  AND now() - state_change > interval '10 minutes';

-- Abandoned transactions: inspect first, then terminate
SELECT pid, now() - state_change AS idle_for, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
ORDER BY state_change
LIMIT 20;
-- SELECT pg_terminate_backend(12345);  -- run per pid after review

-- Active runaway: cancel the query, keep the connection
-- SELECT pg_cancel_backend(12345);

PgBouncer Transaction Pooling

PgBouncer in transaction mode is the architectural fix: thousands of lightweight client connections in, a small pool of real server connections out, with a backend assigned only for each transaction's duration. Statement boundaries return the backend to the pool, so idle app threads cost zero server slots. A fleet that needed 380 direct backends runs on default_pool_size 25 without noticing — autoscaling stops owning your connection count.

The modes matter. Session pooling pins a backend per client (safe, barely multiplexes). Transaction pooling multiplexes per transaction (the standard choice) but forbids server-side prepared statements, LISTEN/NOTIFY, and advisory locks across statements on the same backend. Statement pooling goes further and breaks transactions outright — never use it for app traffic. Know which guarantees your drivers need, then pick the strongest multiplexing that preserves them.

Configure conservatively and observe. default_pool_size 20–25 per database, reserve_pool_size 5 for bursts, max_client_conn in the thousands, and server_idle_timeout to release unused backends. Point app DSNs at port 6432, watch SHOW POOLS (cl_waiting vs sv_idle), and grow the server pool only when clients genuinely wait while backends sit idle — which, after app pools shrink, is rare.

# /etc/pgbouncer/pgbouncer.ini (transaction multiplexing)
# [databases]
# shop = host=db-primary port=5432 dbname=shop
# [pgbouncer]
# listen_port = 6432
# pool_mode = transaction
# default_pool_size = 25
# reserve_pool_size = 5
# max_client_conn = 1000
# server_idle_timeout = 60

# Observe the multiplex: clients waiting vs backends idle
psql -h pooler -p 6432 pgbouncer -c 'SHOW POOLS;'
psql -h pooler -p 6432 pgbouncer -c 'SHOW CLIENTS;' | head -20

# App DSN points at the pooler, not Postgres directly
# postgresql://app:secret@pooler:6432/shop

App Pool Sizing That Fits the New Budget

Behind a pooler, app pools shrink dramatically: maximumPoolSize 5 per pod is plenty when PgBouncer multiplexes, because the app pool now manages checkout latency, not server slots. The fleet equation becomes pods × 5 plus headroom under the pooler's max_client_conn — numbers that stay boring through 3× autoscale events. Keep minimumIdle at 1–2 so quiet pods release even pooler-side slots.

Disable server-side prepared statements in drivers behind transaction-mode poolers: JDBC prepareThreshold=0, Npgsql with matching settings, SQLAlchemy/psycopg2 already client-side by default. A backend reassigned mid-session can't honor a prepare from its previous tenant — the errors look bizarre (prepared statement does not exist) until you know the mode changed underneath the driver.

Roll the change service by service, watching SHOW POOLS after each: sv_used should stay flat while cl_active absorbs the load. If clients wait (cl_waiting climbs) while backends idle, grow default_pool_size slightly; if backends saturate, the app pools are still too generous. The pooler dashboard, not vibes, sizes the final numbers. Document the per-service pool values next to the PgBouncer config so the next autoscale review starts from facts.

#!/bin/bash
# Fleet budget behind PgBouncer: pods x 5 + headroom < max_client_conn
set -euo pipefail
PODS=$(kubectl get deploy api -o jsonpath='{.status.replicas}')
echo "pods=$PODS x 5 = $((PODS * 5)) app slots vs max_client_conn=1000"
cat > application.properties << 'EOF'
spring.datasource.hikari.maximum-pool-size=5
spring.datasource.hikari.minimum-idle=1
spring.datasource.hikari.connection-timeout=30000
# JDBC behind transaction-mode PgBouncer: no server prepares
# spring.datasource.hikari.data-source-properties.prepareThreshold=0
EOF
echo "wrote application.properties (max 5, idle 1)"

Prevention: Timeouts, Alerts, and Driver Hygiene

Server-side timeouts bound every leak class at once. idle_in_transaction_session_timeout ('60s') kills abandoned transactions that hold locks and vacuum horizons; statement_timeout ('30s') stops runaway queries before they camp; idle_session_timeout (PG 14+) reaps forgotten consoles. Set all three via ALTER SYSTEM plus reload so they survive restarts — session-level SETs evaporate with the connection that set them.

Alert on the two predictive graphs: total backends over 75% of max_connections, and any idle-in-transaction older than 5 minutes. The first warns of the cliff; the second names tomorrow's lock incident today. Export pg_stat_activity states to your metrics (one gauge per state) so dashboards show the house filling by category instead of a single scary number.

Audit driver hygiene per deploy: application_name set per service (the roster is useless without it), prepared-statement settings matching the pooler mode, and no per-request connections bypassing pools. Connection behavior deserves the same review rigor as SQL — the prettiest query still 500s when there is no backend to run it. Review connection settings with the same checklist rigor as schema migrations — both fail loudly at 2 AM.

-- Kill abandoned transactions + runaways server-side (persistent)
ALTER SYSTEM SET idle_in_transaction_session_timeout = '60s';
ALTER SYSTEM SET statement_timeout = '30s';
SELECT pg_reload_conf();

-- Predictive alert queries (wire to monitoring)
SELECT count(*) AS backends FROM pg_stat_activity;
SELECT pid, now() - state_change AS idle_for, query
FROM pg_stat_activity
WHERE state = 'idle in transaction'
  AND now() - state_change > interval '5 minutes';