Connection Pooling skill for Claude Code

A pool setup is right when the sum of every client’s maximum connections stays under the database limit with headroom, each pool is sized to what the database can execute in parallel (not to request concurrency), connections are returned promptly, and timeouts fail fast with a clear error instead of hanging.

Workflow

- [ ] 1. Draw the connection topology: every process that connects, its pool max, replicas/instances
- [ ] 2. Compute the connection budget against max_connections
- [ ] 3. Size each pool from database parallelism, not traffic
- [ ] 4. Decide whether an external pooler is needed (and which mode)
- [ ] 5. Set timeouts on acquire, idle, lifetime, statement, and idle-in-transaction
- [ ] 6. Reproduce exhaustion under load; check for leaks
- [ ] 7. Monitor pool wait time and server connection counts

1-2. Connection budget

total = sum over services (instances x workers_per_instance x pool_max)
      + migration/cron/admin tools + replication + superuser_reserved_connections
total must be <= max_connections x 0.8

Example: 6 pods x 4 Gunicorn workers x pool 10 = 240 connections before cron jobs. On a database with max_connections = 200, this fails during the next scale-out. Autoscaling multiplies the first term; size for the maximum replica count, not the current one.

Postgres connections are processes (several MB each, more with work_mem use). Hundreds of mostly-idle connections still cost memory and snapshot overhead; beyond a few hundred, add a pooler rather than raising max_connections.

3. Pool size

Start small. Throughput peaks when active connections roughly equal what the database can run at once - about 2 x CPU cores (plus disk parallelism) on the database server, shared across all clients. Per-process pool max is that budget divided among processes.

Client	Setting	Default	Starting point
HikariCP (JVM)	maximumPoolSize, minimumIdle	10, same as max	10; set minimumIdle = max (fixed pool)
node-postgres	new Pool({ max })	10	10 per process; idleTimeoutMillis: 10000, connectionTimeoutMillis: 5000
SQLAlchemy	pool_size, max_overflow	5, 10	5 + 5; pool_pre_ping=True, pool_recycle=1800
Prisma	connection_limit URL param	cores x 2 + 1	explicit value; pool_timeout
Go database/sql	SetMaxOpenConns, SetMaxIdleConns, SetConnMaxLifetime	unlimited, 2, none	10-25, same idle, 30 min
Django	CONN_MAX_AGE (persistent per thread), OPTIONS: {"pool": True} on Django 5.1+ with psycopg 3	0 (new conn per request)	60 with health checks, or the psycopg pool

Rules:

• Async apps (asyncio, Node) with one process still need a bounded pool; unbounded concurrency simply moves the queue to the database.
• Bigger pools do not fix slow queries; they let more slow queries compete for CPU and locks.
• A thread or task that holds a connection while calling an external API (HTTP, LLM, S3) is the most common exhaustion cause. Release the connection before slow I/O.

4. External pooler: PgBouncer modes

Mode	Server connection held for	Breaks
session	the whole client session	nothing; only helps with connection churn
transaction	one transaction	session state: SET (use SET LOCAL), session advisory locks, LISTEN/NOTIFY, temp tables across transactions, WITH HOLD cursors
statement	one statement	multi-statement transactions; rarely appropriate

Default for web apps: transaction mode. Use a direct (non-pooled) connection for migrations, LISTEN, long sessions, and admin work.

; pgbouncer.ini (1.21+)
[databases]
app = host=10.0.0.5 port=5432 dbname=app

[pgbouncer]
listen_port = 6432
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 5000
default_pool_size = 20          ; server connections per (db, user) pair
reserve_pool_size = 5
reserve_pool_timeout = 3
max_prepared_statements = 200   ; protocol-level prepared statements in transaction mode
server_idle_timeout = 600
query_wait_timeout = 30         ; clients waiting longer than this get an error
server_reset_query = DISCARD ALL ; only applied in session mode

• Prepared statements in transaction mode work since 1.21 via max_prepared_statements (default 200 since 1.24). Drivers using protocol-level prepares (pgx, node-postgres named statements, JDBC) then work; SQL-level PREPARE still does not.
• Older setups needed prepareThreshold=0 (JDBC), statement_cache_size=0 (asyncpg), or pgbouncer=true (Prisma); remove those only after confirming the PgBouncer version.
• Pool size is per database/user pair: 3 users x default_pool_size 20 = 60 server connections. Budget accordingly.
• Run PgBouncer close to the app or as a sidecar per node; one PgBouncer is single-threaded, so scale out with several instances (or so_reuseport) at very high throughput.

Serverless and edge

Each function instance opens its own connections, and instances scale to hundreds in seconds.

Platform	Approach
AWS Lambda + RDS/Aurora	RDS Proxy; pool max: 1 per instance; create the client outside the handler
Supabase	Supavisor transaction-mode port (6543) for serverless, direct or session port for migrations
Neon	pooled connection string (-pooler host, PgBouncer-based) for app traffic
Cloudflare Workers	Hyperdrive in front of Postgres/MySQL; open a client per request through it
Vercel/edge functions	HTTP or WebSocket drivers (Neon serverless driver, PlanetScale) or a pooled endpoint

Never cache a TCP connection in a global that outlives the platform’s freeze/thaw without a health check; the first query after thaw fails on a dead socket.

5. Timeouts

ALTER ROLE app SET statement_timeout = '15s';
ALTER ROLE app SET idle_in_transaction_session_timeout = '30s';
ALTER ROLE app SET lock_timeout = '5s';

Plus the client acquire timeout (fail in 2-5 s with “pool exhausted” rather than hang), idle timeout, and max lifetime shorter than any network/LB idle cutoff (often 350 s on AWS NAT, or a firewall’s limit). These role settings persist through transaction pooling; plain SET does not.

Diagnosing exhaustion

1. Confirm where the queue is: app pool wait time metrics vs pg_stat_activity counts vs PgBouncer SHOW POOLS (cl_waiting, maxwait).
2. Run the queries in references/pool-diagnostics.md: connections by state and application, idle in transaction sessions with age, long queries, and lock waits.
3. Classify:

Evidence	Cause	Fix
many idle in transaction, old xact_start	code opens a transaction then does slow work or forgets commit	release before I/O, fix missing commit/rollback in error paths, set idle-in-transaction timeout
many active, waiting on locks	lock contention	fix the locking query (see transactions-locking)
many active, CPU saturated	slow queries	tune queries; do not enlarge pools
connections near max, mostly idle	too many pools / instances	smaller pools, pooler, fewer workers
app pool waits but database is quiet	leak: connections checked out and never returned	enable leak detection (Hikari leakDetectionThreshold), audit acquire/release paths

4. Reproduce with a load test at expected peak plus 50%, watching pool wait time and pg_stat_activity counts. Leaks show as checked-out count climbing without falling.

Gotchas

• Read replicas need their own budget; failover can move every client to one node at once.
• Health-check queries (SELECT 1 on every borrow) add round trips; prefer validation on idle connections only (Hikari default) or pool_pre_ping at modest rates.
• max_connections changes require a Postgres restart; plan it with the user.
• Kubernetes rolling deploys briefly run old and new pods together: budget for maxSurge extra pods.
• Transaction-mode poolers make search_path and tenant SET leak between clients; pass tenant context with SET LOCAL inside the transaction (see row-level-security).

Verification

• Written budget: every client’s max connections summed, below 80% of max_connections at maximum autoscale.
• Load test at peak + 50% shows p99 pool acquire time under the agreed bound (e.g. under 50 ms) and zero acquire timeouts.
• pg_stat_activity shows no idle in transaction sessions older than the timeout.
• PgBouncer SHOW POOLS has cl_waiting = 0 in steady state.