Connection Pools and Timeouts

Your backend talks to a database over a network connection, and that connection is not free to create. How you open, share, and give back connections decides whether your API handles a hundred users smoothly or locks up under ten. At the Connection Pool, a fixed set of lanes carries every query, and a lane that is never returned is a lane nobody else can use.

Why a new connection per request is slow

Opening a database connection involves a network round trip, often a TLS handshake, logging in, and the database setting up a process or thread just for you. That can take tens of milliseconds, sometimes more than the query itself. Do it on every request and you pay that cost every time.

Databases also limit how many connections they accept. Postgres, for example, allows about 100 by default, and each one uses memory on the database server. If each request opens its own, a traffic spike can hit that limit and new requests start failing with "too many connections".

What a pool does

A connection pool opens a handful of connections once and keeps them open. When your code needs to run a query, it borrows a connection from the pool, uses it, and gives it back. The next request reuses the same connection with no setup cost.

const { Pool } = require('pg');
const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
  max: 10,                        // at most 10 open connections
  connectionTimeoutMillis: 2000,  // wait at most 2s to borrow one
});

// For a single query, pool.query borrows and returns for you
const { rows } = await pool.query('SELECT * FROM users WHERE id = $1', [id]);

Create the pool once when the app starts and share it, not inside each request handler. A pool created per request is just the slow approach with extra steps.

Always give the connection back

When you need several queries on the same connection, for example a transaction, you check one out yourself. Then releasing it is your job, and it must happen even if a query throws. That is what try/finally is for.

const client = await pool.connect();
try {
  await client.query('BEGIN');
  await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, from]);
  await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, to]);
  await client.query('COMMIT');
} catch (err) {
  await client.query('ROLLBACK');
  throw err;
} finally {
  client.release(); // runs on success and on error
}

If release() were only on the success path, every failed transfer would keep a connection forever. That is called a connection leak.

Pool exhaustion: what it looks like

When all connections are borrowed and none come back, the pool is exhausted. New requests wait in line for a connection. The symptoms are easy to misread: the site gets slower and slower and then requests start timing out, while the database itself looks idle and CPU is low. Nothing is working hard; everything is waiting.

Common causes are leaks (a missing release), slow queries holding connections for seconds, and a pool that is too small for the traffic. Most pool libraries expose how many connections are in use and how many requests are waiting; logging those numbers makes this problem obvious.

Timeouts so one slow thing cannot freeze everything

Every call that leaves your process can hang: a database query, an HTTP request to a payment API, a DNS lookup. Without a timeout, a hung call holds whatever it borrowed (a pool connection, memory, the user's request) until the other side eventually answers, which might be never.

Put a limit on each layer:

• a connection timeout for borrowing from the pool, so a request fails fast instead of queueing forever;
• a query or statement timeout so one runaway query is cancelled (Postgres has statement_timeout);
• a timeout on outgoing HTTP calls. In Node, fetch(url, { signal: AbortSignal.timeout(3000) }) gives up after 3 seconds.

A timeout turns "everything slowly freezes" into "this one feature returns an error quickly", which you can log, retry, or show a friendly message for.