Database Connection Pooling: Why Your App Slows Down Under Load

#node #backend #performance #database

Database Connection Pooling: Why Your App Slows Down Under Load

Most apps work fine with a handful of users. Under load, they slow to a crawl because of connection pool misconfiguration.

What Is a Connection Pool?

Opening a DB connection is expensive: TCP handshake, auth, session setup — 20-100ms per connection. A pool maintains open connections that are reused across requests.

The Classic Mistake

// Bad: new connection per request = 50ms overhead every time
app.get('/users', async (req, res) => {
  const client = new pg.Client(connectionString);
  await client.connect();
  const result = await client.query('SELECT * FROM users');
  await client.end();
  res.json(result.rows);
});

// Good: pool created once, reused for every request
const pool = new pg.Pool({ connectionString, max: 20 });

app.get('/users', async (req, res) => {
  const result = await pool.query('SELECT * FROM users');
  res.json(result.rows);
});

Pool Sizing

const pool = new pg.Pool({
  max: 20,                       // Maximum connections
  min: 2,                        // Keep 2 warm always
  idleTimeoutMillis: 30000,      // Remove idle after 30s
  connectionTimeoutMillis: 5000, // Fail fast if exhausted
});

Sweet spot: 10-25 connections per app instance. Too small = queuing. Too large = DB overwhelmed.

Serverless: Use a Pooler

Serverless functions can't use traditional pools — hundreds of cold starts can exhaust DB connections. Use PgBouncer, Supabase pooler, or Prisma Accelerate as a proxy.

Monitor Pool Health

setInterval(() => {
  metrics.gauge('db.pool.total', pool.totalCount);
  metrics.gauge('db.pool.idle', pool.idleCount);
  metrics.gauge('db.pool.waiting', pool.waitingCount);
}, 5000);