Understanding Database Connection Pooling

Opening a database connection is expensive: a TCP handshake, authentication, sometimes TLS negotiation — commonly tens of milliseconds before a single query runs. Open one connection per request and you pay that cost every time, and eventually the database refuses new connections because it has a hard limit on how many it can hold open at once. Connection pooling solves both problems by keeping a set of already-open connections ready to reuse.

What a pool actually does

A connection pool is a fixed set of database connections that live for the lifetime of your application, not the lifetime of a single request. When your code needs to run a query, it borrows a connection from the pool, uses it, and returns it — it doesn't open or close a socket at all.

const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  database: 'app',
  max: 10, // maximum number of connections in the pool
  idleTimeoutMillis: 30000,
});

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(); // return the connection to the pool, don't close it
  }
}

pool.connect() hands you an existing idle connection if one's available, or opens a new one (up to max) if not. client.release() is the part people forget — skip it and that connection is never returned to the pool, which is how you end up exhausting it under load.

Why an unbounded pool is worse than no pool

It's tempting to set max high "to be safe." The problem is your database also has a hard connection ceiling (max_connections in Postgres, often 100 by default), shared across every application instance talking to it. If you run 10 app instances with max: 50 each, you can request 500 connections against a database that only accepts 100 — and once that ceiling is hit, every other client, including your own admin tools, gets rejected.

A reasonable pool size is closer to (number of CPU cores × 2) + effective spindle count as a starting heuristic for the database itself, then divided across however many application instances connect to it — not "however many concurrent requests I expect." More connections than your database's CPU cores can genuinely serve in parallel just adds queueing, not throughput.

The most common pooling bug: not releasing on error

// Bad: if query() throws, the connection is never released
async function getUserBroken(id) {
  const client = await pool.connect();
  const result = await client.query('SELECT * FROM users WHERE id = $1', [id]);
  client.release();
  return result.rows[0];
}

If client.query() throws, client.release() never runs, and that connection is permanently checked out. Do this enough times and the pool silently shrinks to zero usable connections while looking completely healthy from the outside. The try/finally version above is the fix — finally runs whether the query succeeds or throws.

Using pool.query() for one-off statements

Most drivers give you a shortcut that borrows a connection, runs one query, and releases it automatically:

const result = await pool.query('SELECT * FROM users WHERE id = $1', [id]);

Use this for single, independent queries. It's not appropriate for transactions, because each call to pool.query() might run on a different connection — and a transaction only makes sense on one specific connection for its whole duration.

Transactions need an explicit connection

async function transferFunds(fromId, toId, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, fromId]);
    await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, toId]);
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Here the same client runs BEGIN, both updates, and COMMIT — that's what keeps them in one transaction. Mixing pool.query() calls into the middle of this would silently run some statements on other connections, outside the transaction.

Idle timeouts and stale connections

Connections that sit idle for a long time can be silently dropped by the database, a load balancer, or a firewall in between, without your application knowing. idleTimeoutMillis tells the pool to proactively close and replace connections that have been idle too long, rather than waiting for you to try to use a dead one and get a confusing error mid-request.

const pool = new Pool({
  max: 10,
  idleTimeoutMillis: 30000, // close idle connections after 30s
  connectionTimeoutMillis: 5000, // fail fast if no connection is available within 5s
});

connectionTimeoutMillis matters just as much: without it, a request that can't get a connection because the pool is exhausted will hang indefinitely instead of failing fast with a clear error.

Serverless changes the math entirely

In a traditional long-running server, one pool lives for the process's whole lifetime, which is exactly what pooling is designed for. In serverless (Lambda, Cloud Functions), each invocation can spin up a fresh process, and creating a full pool per invocation defeats the purpose — you're back to paying connection overhead constantly, and a burst of concurrent invocations can each open their own pool and collectively blow past the database's connection limit. The usual fix is an external pooler (PgBouncer, RDS Proxy) that sits between your functions and the database and multiplexes many short-lived clients onto a small number of real database connections.

Takeaway

A connection pool trades the cost of opening a connection per request for a fixed, reused set of already-open connections — but it's only as good as its sizing and its discipline around releasing connections. Size it against what the database can actually serve, always release in a finally, and keep transactions pinned to a single explicit connection.