DB Connection Pool Exhaustion — Why Your App Hangs at Peak Load

Introduction

Everything works in staging. Production traffic picks up, and requests stop responding. No errors in the logs — just timeouts. The database is healthy, CPU is low, memory is fine. The culprit is connection pool exhaustion: every available database connection is in use, and new requests queue indefinitely waiting for one to free up. This guide covers diagnosis, right-sizing, PgBouncer integration, and leak prevention.

How Connection Pool Exhaustion Happens

Opening a new database connection is expensive — TCP handshake, authentication, and memory allocation on both sides. Connection pools pre-open a fixed set of connections and reuse them across requests.

When all connections are in use:

Pool size: 10 connections
Concurrent requests: 200
 
200 requests arrive
10 get connections immediately
190 wait in queue
 
If each query takes 300ms:
→ Request 11 waits 300ms
→ Request 100 waits 2,700ms
→ Request 190 waits 5,400ms
→ connectionTimeoutMillis fires → TimeoutError

The tragedy: your database can be completely idle while your application is frozen. The connections are held but not actively querying — waiting on application code, network, or slow business logic.

Diagnosing Pool Exhaustion

Look for these signals:

Requests hang for exactly connectionTimeoutMillis then fail
Database CPU is near zero (queries never reach it)
Logs show "timeout acquiring connection from pool"
pg_stat_activity shows many connections in idle state

-- Check connection state distribution
SELECT state, count(*)
FROM pg_stat_activity
WHERE datname = 'your_db'
GROUP BY state;
 
-- Find connections held longest without querying
SELECT pid, usename, application_name, state, query_start,
       NOW() - query_start AS held_for, query
FROM pg_stat_activity
WHERE datname = 'your_db' AND state = 'idle'
ORDER BY query_start ASC
LIMIT 20;
 
-- See how close you are to max_connections
SELECT count(*) AS current, max_conn
FROM pg_stat_activity,
     (SELECT setting::int AS max_conn FROM pg_settings WHERE name = 'max_connections') mc
WHERE datname = 'your_db'
GROUP BY max_conn;

In Node.js with node-postgres, monitor pool state continuously:

const { Pool } = require('pg')
const pool = new Pool({ max: 20 })
 
setInterval(() => {
  console.log({
    total: pool.totalCount,
    idle: pool.idleCount,
    waiting: pool.waitingCount
  })
 
  if (pool.waitingCount > 5) {
    console.warn(`Pool pressure: ${pool.waitingCount} requests waiting`)
  }
}, 5000)

Right-Sizing the Pool

The correct pool size is not "as large as possible." PostgreSQL uses an OS thread per connection, and excessive connections add context-switching overhead that hurts throughput.

Rule of thumb formula:

pool_size = (num_cpu_cores * 2 + effective_spindle_count) / num_app_instances
 
For a 4-core DB server with 5 app instances:
  per_instance = ((4 * 2) + 1) / 5 = 1.8 → round up to 2-4
 
With PgBouncer in front: app can use 50-100 "connections" per instance
because PgBouncer multiplexes them to fewer real DB connections.

const { Pool } = require('pg')
 
const pool = new Pool({
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  max: 10,                       // per instance — tune with formula above
  min: 2,                        // keep 2 warm
  idleTimeoutMillis: 30000,      // release idle connections after 30s
  connectionTimeoutMillis: 5000, // fail fast after 5s wait
  statement_timeout: 10000,      // kill queries running over 10s
})

PgBouncer: Connection Pooling Proxy

For high-concurrency applications, put PgBouncer between your app and PostgreSQL. PgBouncer multiplexes thousands of application "connections" into a small pool of real database connections:

App instances (each: 50 connections) → PgBouncer → PostgreSQL (20 real connections)

# pgbouncer.ini
[databases]
myapp = host=db.internal port=5432 dbname=myapp
 
[pgbouncer]
pool_mode = transaction     ; connection returned to pool after each transaction
max_client_conn = 1000     ; app can request up to 1000 connections
default_pool_size = 20     ; but only 20 real DB connections
min_pool_size = 5
server_idle_timeout = 600
log_connections = 1
auth_type = md5

transaction pool mode is optimal for web APIs: a connection is only held for the duration of a single transaction, then returned. Between transactions, the application connection exists but holds no real DB connection.

Always Release Connections

Connection leaks happen when error paths skip the release() call. Every connection acquired must be released, even on error:

// WRONG: error path leaks the connection
async function getUser(id) {
  const client = await pool.connect()
  const result = await client.query('SELECT * FROM users WHERE id = $1', [id])
  // If query throws, client.release() never runs
  client.release()
  return result.rows[0]
}
 
// CORRECT: try/finally guarantees release
async function getUser(id) {
  const client = await pool.connect()
  try {
    const result = await client.query('SELECT * FROM users WHERE id = $1', [id])
    return result.rows[0]
  } finally {
    client.release()  // always runs
  }
}
 
// SIMPLEST: pool.query() handles acquire/release automatically
async function getUser(id) {
  const result = await pool.query('SELECT * FROM users WHERE id = $1', [id])
  return result.rows[0]
}

Use pool.query() for single-query operations. Use manual connect()/release() only when you need a transaction or multiple queries on the same connection.

Query and Statement Timeouts

Slow queries hold connections longer, accelerating pool exhaustion. Set timeouts at multiple levels:

const pool = new Pool({
  max: 20,
  connectionTimeoutMillis: 5000,   // fail if no connection available in 5s
  statement_timeout: 10000,         // kill any query running over 10s
  query_timeout: 15000,             // client-side timeout for query result
})
 
// Per-query timeout override
async function runReport() {
  const client = await pool.connect()
  try {
    await client.query('SET statement_timeout = 60000')  // 60s for reports
    return await client.query('SELECT ... FROM large_aggregate_query ...')
  } finally {
    client.release()
  }
}

Concurrency Limiting for Batch Jobs

Batch jobs that fire hundreds of concurrent DB queries will exhaust any pool. Limit concurrency to match pool size:

const pLimit = require('p-limit')
const limit = pLimit(10)  // max 10 concurrent queries — matches pool size
 
async function processBatch(userIds) {
  const results = await Promise.all(
    userIds.map(id =>
      limit(() => pool.query('SELECT * FROM users WHERE id = $1', [id]))
    )
  )
  return results.map(r => r.rows[0])
}

Key Takeaways

Connection pool exhaustion appears as hanging requests with no errors — diagnose with pg_stat_activity and pool waitingCount
The correct pool size formula is approximately (db_cpu_cores * 2) / app_instances — more connections does not mean more throughput
PgBouncer in transaction mode multiplexes thousands of app connections into 20–50 real PostgreSQL connections
Always use try/finally around client.release() — a missing release in an error path will exhaust the pool under load
pool.query() auto-releases and is safe for single-statement operations; prefer it over manual connect/release
Set statement_timeout at the pool level to kill slow queries before they hold connections indefinitely
Batch jobs should use a concurrency limiter (p-limit) sized to match the pool — concurrent queries equal to pool size is the ceiling
Monitor pool.waitingCount continuously; alert when it exceeds 5 and page when it exceeds 20

Introduction#

How Connection Pool Exhaustion Happens#

Diagnosing Pool Exhaustion#

Right-Sizing the Pool#

PgBouncer: Connection Pooling Proxy#

Always Release Connections#

Query and Statement Timeouts#

Concurrency Limiting for Batch Jobs#

Key Takeaways#

Related reading