Table of Contents
Connection pooling is critical for application performance, yet it's one of the most misconfigured components in production systems. This guide explores advanced strategies for optimal pool configuration.
Why Connection Pooling Matters
Establishing a database connection is expensive. It involves TCP handshake, authentication, and session initialization. Connection pooling amortizes this cost by reusing connections.
Performance Impact
Without pooling, each request creates a new connection:
// Anti-pattern: new connection per request
app.get('/users', async (req, res) => {
const conn = await db.connect(); // 20-50ms overhead
const users = await conn.query('SELECT * FROM users');
await conn.close();
res.json(users);
});With pooling, connections are reused:
// Better: reuse pooled connections
const pool = new Pool({ max: 20 });
app.get('/users', async (req, res) => {
const users = await pool.query('SELECT * FROM users'); // <1ms
res.json(users);
});Pool Sizing Strategy
The formula for pool size is not arbitrary. Consider these factors:
The Formula
A good starting point:
pool_size = ((core_count * 2) + effective_spindle_count)For a 4-core server with SSD storage:
pool_size = (4 * 2) + 1 = 9Key considerations:
- More connections ≠ better performance
- Too many connections cause context switching overhead
- Database CPU and memory are limiting factors
- Connection pool size should match your workload pattern
Configuration Parameters
Beyond pool size, these parameters matter:
const pool = new Pool({
max: 20, // Maximum pool size
min: 5, // Minimum idle connections
idleTimeoutMillis: 30000, // Close idle connections after 30s
connectionTimeoutMillis: 2000, // Fail fast if no connection available
maxUses: 7500, // Retire connections after N uses
});Connection Lifecycle
Understanding the lifecycle helps debug issues:
- Connection requested from pool
- If available, connection returned immediately
- If pool full, request waits (up to timeout)
- After use, connection returned to pool
- Idle connections closed after timeout
Monitoring Pool Health
Track these metrics in production:
- Pool utilization: active / total connections
- Wait time: time requests spend waiting for connections
- Checkout failures: requests that timeout
- Connection errors: failed connection attempts
// Export pool metrics
pool.on('acquire', () => {
metrics.increment('pool.acquire');
});
pool.on('release', () => {
metrics.increment('pool.release');
});
setInterval(() => {
metrics.gauge('pool.size', pool.totalCount);
metrics.gauge('pool.idle', pool.idleCount);
metrics.gauge('pool.waiting', pool.waitingCount);
}, 10000);Common Pitfalls
1. Connection Leaks
Always return connections to the pool:
// Wrong: connection leak
const client = await pool.connect();
const result = await client.query('SELECT ...');
// client.release() missing!
// Correct: always release
const client = await pool.connect();
try {
const result = await client.query('SELECT ...');
return result;
} finally {
client.release(); // Always executes
}2. Pool Per Request
Create the pool once at startup, not per request.
3. Ignoring Database Limits
Your pool size must respect the database's max_connections setting. If you have 10 application instances with pool size 20, you need at least 200 database connections available.
Summary
Connection pooling is not set-and-forget. Right-size your pools based on workload, monitor key metrics, and avoid common pitfalls like connection leaks. Start conservative and adjust based on real production data.