Database Connection Pooling
Connection pooling maintains a set of reusable database connections rather than opening and closing one per query. Opening a TCP connection and authenticating is expensive; pools amortize this cost across many queries.
Connection pooling = a hotel with N rooms (connections). Guests (queries) check in and out instead of building a new room each time. If all 10 rooms are full, new guests wait in the lobby (queue). Forget to check out (release)? You exhaust the hotel and everyone sleeps in the lobby forever.
Pool configuration parameters: min (connections always open), max (maximum connections), idleTimeoutMillis (close idle connections after N ms), connectionTimeoutMillis (throw if no connection available after N ms). A pool size of max=10 means a maximum of 10 concurrent queries to the database; further requests queue. Pool size should be tuned against database max_connections and application concurrency requirements. pg (node-postgres) uses Pool; mysql2 uses createPool; Sequelize and TypeORM have internal pools.
Pool sizing is a nuanced science. A common mistake is setting max too high: a PostgreSQL database with max_connections=100 and 10 Node.js instances each with pool max=20 creates 200 potential connections, exceeding the limit. The formula: (pool max per instance) × (Node.js instances) ≤ (DB max_connections - reserved connections for admin). Too few connections starves throughput; too many causes connection overhead and contention on the DB. Connection pools in Node.js are process-local—cluster mode (10 workers, max=10 each) creates 100 DB connections. Monitor pool utilization metrics (pool.totalCount, pool.idleCount, pool.waitingCount in pg) to right-size. PgBouncer as a connection proxy at the DB layer is the scalable solution for many application instances.
Connection pools reuse TCP connections to the database across queries, avoiding expensive handshake overhead. Key parameters: min, max, idleTimeout, connectionTimeout. Pool max × process count must not exceed DB max_connections. In clustered Node.js, each worker has its own pool—a cluster of 4 workers with pool max=25 creates 100 DB connections. Use PgBouncer for large-scale multi-instance deployments.
Async functions that open a pool connection in a try block but throw before releasing it will exhaust the pool. Always use try/finally to release: `const client = await pool.connect(); try { await client.query(...); } finally { client.release(); }`. Without the finally, the connection leaks and the pool eventually blocks all new queries with connection timeout errors.