Pooling Basics

Each PostgreSQL backend is a process with memory overhead. Raising max_connections without pooling increases context switching and cache churn; PgBouncer multiplexes clients to fewer server backends on PostgreSQL 18.4.

Recipe

Quick-reference recipe card - copy-paste ready.

SHOW max_connections;
SELECT count(*) AS active_backends FROM pg_stat_activity WHERE backend_type = 'client backend';
 
-- sizing heuristic (OLTP): pool_size ~= (CPU cores * 2) + effective_spindle_count
-- validate with p95 latency, not app instance count

When to reach for this: Connection count approaches max_connections, or CPU idle while queries queue in pool wait.

Working Example

SELECT
  setting::int AS max_connections,
  (SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend') AS current,
  setting::int - (SELECT count(*) FROM pg_stat_activity WHERE backend_type = 'client backend') AS headroom
FROM pg_settings
WHERE name = 'max_connections';
 
SELECT state, count(*)
FROM pg_stat_activity
WHERE backend_type = 'client backend'
GROUP BY state
ORDER BY count DESC;

Pair with PgBouncer SHOW POOLS (admin console) to see cl_active, cl_waiting, and sv_active.

What this demonstrates:

• Server connection headroom vs app connections
• Idle vs active backend breakdown
• Why pool wait time matters more than raw connection count

Deep Dive

How It Works

• App servers open many TCP connections; pooler reuses fewer PostgreSQL backends.
• Each backend allocates work_mem potential per query and private caches.
• Context switching rises when hundreds of backends compete for CPU.
• Pool sits between apps and Postgres: apps -> PgBouncer -> PostgreSQL.

Sizing Signals

SQL Notes

SELECT usename, application_name, state, wait_event_type, query_start
FROM pg_stat_activity
WHERE backend_type = 'client backend'
ORDER BY query_start NULLS LAST
LIMIT 20;

Gotchas

• max_connections = app instances * pool size - Exhausts RAM and CPU. Fix: Central pooler; size server pool from cores.
• No pooler for serverless burst - Each lambda opens connections. Fix: External pool (RDS Proxy, PgBouncer, Supavisor).
• Ignoring idle in transaction - Holds server connection from pool. Fix: idle_in_transaction_session_timeout.
• One giant pool for batch and OLTP - BI scans starve short queries. Fix: Separate pools and roles.
• Raising max_connections instead of pooling - Linear memory growth. Fix: Pool first, scale connections modestly.

Alternatives

FAQs

Related

• PgBouncer Modes - Pooling semantics
• Pooling Best Practices - Sizing checklist
• Connection Math - Capacity formulas
• Role & Timeout per Pool - Workload separation

Stack versions: This page was written for PostgreSQL 18.4 (stable 18, maintenance 17), pgvector 0.8+, PgBouncer 1.x, Patroni 3.x, and PostGIS 3.5+.