Connection Pooling Cheat Sheet
Explains why database connection pooling matters, how pool sizing works, and configuration patterns for tools like PgBouncer and application-level pools.
2 PagesIntermediateMar 15, 2026
Why Connection Pooling
The problem pooling solves.
- Connection overhead- Opening a new TCP + auth handshake per request can take 5-50ms and consumes real server memory (a few MB per connection)
- Max connections limit- Databases cap concurrent connections (e.g., Postgres default max_connections=100); each idle connection still reserves memory
- Pool- A cache of already-open, reusable connections that the application borrows and returns instead of opening a fresh one per query
- Thundering herd- Without pooling, a traffic spike can open thousands of simultaneous connections and crash the database
- External vs application pooling- Application pools (e.g., HikariCP, node-postgres Pool) live inside your app process; external poolers (PgBouncer, ProxySQL) sit between many app instances and the DB
PgBouncer Configuration
A minimal transaction-pooling setup.
ini
[databases]mydb = host=127.0.0.1 port=5432 dbname=mydb[pgbouncer]listen_port = 6432listen_addr = *auth_type = md5auth_file = /etc/pgbouncer/userlist.txt# transaction pooling: connection is returned to the pool after each transactionpool_mode = transactionmax_client_conn = 1000default_pool_size = 20reserve_pool_size = 5
Node.js Application Pool
Configuring and using node-postgres's Pool.
javascript
const { Pool } = require('pg');const pool = new Pool({ host: 'localhost', database: 'mydb', max: 20, // max connections in the pool idleTimeoutMillis: 30000, connectionTimeoutMillis: 2000,});// Borrow a connection, run a query, return it automaticallyconst result = await pool.query('SELECT * FROM orders WHERE id = $1', [42]);// Always release manually-checked-out clientsconst client = await pool.connect();try { await client.query('BEGIN'); await client.query('UPDATE accounts SET balance = balance - 100 WHERE id = 1'); await client.query('COMMIT');} finally { client.release();}
Pool Sizing & Modes
How pooling modes trade off compatibility for reuse.
- Session pooling- Client holds the same server connection for the whole session; safest but least efficient reuse
- Transaction pooling- Connection is returned to the pool after each transaction commits; higher reuse but breaks session-level features (e.g., prepared statements, advisory locks)
- Statement pooling- Connection returned after every single statement; most aggressive, incompatible with multi-statement transactions
- Pool size formula- A common starting point is connections = ((core_count * 2) + effective_spindle_count); oversized pools cause context-switch thrashing on the DB, not more throughput
- Connection leaks- Forgetting to release/close a checked-out connection exhausts the pool over time; always release in a finally block
HikariCP Production Tuning
Sizing and leak-detection settings for a JVM connection pool under load.
java
HikariConfig config = new HikariConfig();config.setJdbcUrl("jdbc:postgresql://localhost:5432/mydb");config.setUsername("app_user");config.setPassword(System.getenv("DB_PASSWORD"));// Keep this close to (core_count * 2) + effective_spindle_count, not "as high as possible"config.setMaximumPoolSize(20);config.setMinimumIdle(5);// Fail fast instead of hanging a request thread waiting on a free connectionconfig.setConnectionTimeout(3000);// Recycle connections before cloud load balancers / DB idle timeouts kill them silentlyconfig.setMaxLifetime(1800000); // 30 minconfig.setIdleTimeout(600000); // 10 min// Log a warning if a connection is checked out longer than this without being returnedconfig.setLeakDetectionThreshold(60000);HikariDataSource ds = new HikariDataSource(config);
Avoiding Prepared Statement Errors Under Transaction Pooling
Transaction-mode pooling breaks session state; here's how to work around it safely.
sql
-- In pgbouncer.ini with pool_mode = transaction:-- * SET session-level GUCs (search_path, timezone) do NOT persist across pooled connections-- * Named prepared statements can silently bind to the wrong underlying connection-- Bad under transaction pooling: relies on session state surviving between callsSET search_path = tenant_42;SELECT * FROM orders; -- may run on a different physical connection, wrong schema!-- Safer: qualify explicitly per statement, avoid relying on session stateSELECT * FROM tenant_42.orders;-- For prepared statements, disable client-side statement caching or use-- pgbouncer's `max_prepared_statements` (PgBouncer 1.21+) which tracks and-- rebinds prepared statements per-connection automatically.
Detecting Pool Exhaustion in Postgres
Query active vs idle-in-transaction connections to catch a leak before it takes down the app.
sql
SELECT state, count(*) AS connections, max(now() - state_change) AS longest_in_stateFROM pg_stat_activityWHERE datname = current_database()GROUP BY stateORDER BY connections DESC;-- 'idle in transaction' connections held open for minutes usually indicate-- an application bug (forgot to COMMIT/ROLLBACK or release the client)-- Kill a specific runaway idle-in-transaction sessionSELECT pg_terminate_backend(pid)FROM pg_stat_activityWHERE state = 'idle in transaction' AND now() - state_change > interval '10 minutes';
Pooling Topology at Scale
How pooling changes once you run more than one app instance or one database.
- Pooler-of-poolers- Each app instance keeps a small local pool, all pointed at a single shared PgBouncer/ProxySQL instance, which holds the real, tightly-bounded pool against the database
- Multiplexing ratio- The ratio of client connections a pooler accepts to backend connections it maintains (e.g. 1000 client : 20 server) — the real lever for scaling connection count without scaling the database
- Read replica pool splitting- Route read-only queries to a separate pool pointed at replicas, keeping the primary's pool small and reserved for writes
- Serverless/Lambda connection storms- Each cold-started function instance opening its own connection can exhaust max_connections in seconds; use an HTTP-based pooler (e.g. PgBouncer in a sidecar, or a proxy like RDS Proxy/Neon's pooler) designed for high connection churn
- Sharded pooling- With horizontally sharded databases, the pooler (e.g. ProxySQL query rules) must route each connection to the correct shard rather than pooling against a single backend
- Health-checked pool members- Poolers fronting multiple read replicas should actively probe replica lag/liveness and evict unhealthy backends from rotation, not just round-robin blindly
Failure Modes and Symptoms
Recognizing pool-related incidents from their symptoms.
- Pool starvation- Symptom: requests hang until connectionTimeoutMillis, then fail in bursts; cause: pool size too small or connections leaked and never released
- Stale connection errors- Symptom: 'connection reset by peer' on first query after idle period; cause: a firewall, load balancer, or the DB itself closed the TCP connection while it sat idle in the pool without the pool detecting it
- Cascading pool exhaustion- Symptom: one slow downstream query holds connections longer, shrinking effective pool size, causing more requests to queue and hold connections even longer — a feedback loop
- Connection storm on deploy- Symptom: brief spike of connection errors during rolling deploys; cause: new pods opening full-size pools before old pods have drained theirs
- Thundering reconnect after DB failover- Symptom: massive reconnect spike right after a primary failover; cause: every pooled client detects the dropped connection simultaneously and retries at once — mitigate with jittered reconnect backoff
- Silent max_connections ceiling hit- Symptom: intermittent 'too many connections' errors only under peak load; cause: pooler's total backend connections across all app instances exceeds the database's max_connections, often invisible until traffic peaks
Pro Tip
More pool connections rarely means more throughput — past a certain point (often surprisingly low, like 2x CPU cores plus disk count) additional connections just queue on the database's internal lock manager; benchmark before scaling the pool size up.
Was this cheat sheet helpful?
Explore Topics
#ConnectionPooling#ConnectionPoolingCheatSheet#Database#Intermediate#WhyConnectionPooling#PgBouncerConfiguration#NodeJsApplicationPool#PoolSizingModes#Databases#CheatSheet#SkillVeris
