Database Connection Pool Tuning for High-Load Services

Introduction

Connection pooling is often the hidden bottleneck in high-load systems. Too few connections lead to queueing and timeouts, while too many overwhelm the database. This guide explains how to size and tune pools in Node.js with PostgreSQL.

Key Pool Parameters

• max: total concurrent connections allowed
• idleTimeoutMillis: how long idle connections remain open
• connectionTimeoutMillis: how long to wait for a connection before failing

Estimate a Starting Pool Size

A practical starting point:

pool_size = (cpu_cores * 2) + effective_io_threads

Then validate with load testing and database metrics.

Node.js Example Using pg

import pg from "pg";

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  max: 30,
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000
});

export async function fetchOrders(customerId) {
  const client = await pool.connect();
  try {
    const result = await client.query(
      "SELECT id, total_amount FROM orders WHERE customer_id = $1 ORDER BY created_at DESC LIMIT 50",
      [customerId]
    );
    return result.rows;
  } finally {
    client.release();
  }
}

Watch for Pool Saturation

Symptoms include:

• Growing request latency
• Connection timeouts
• Database CPU spikes without query-level bottlenecks

Monitor waiting counts and average wait time in your pool metrics.

Database Limits Matter

PostgreSQL has max_connections. A pool size that exceeds this will cause connection storms. Leave headroom for admin and background processes.

Advanced Techniques

• Use separate pools for read replicas and write masters.
• Enable prepared statement caching when supported.
• Apply circuit breakers when the pool is saturated.

Conclusion

Connection pools should be tuned based on workload and database capacity. Start with conservative defaults, measure contention, and iterate with production metrics.