Database Connection Pooling Explained

Quick Answer

A connection pool keeps a fixed set of already-open database connections ready to reuse, so your application skips the cost of a fresh TCP handshake and authentication step on every query. Requests borrow a connection, use it, and hand it back instead of opening and closing one each time. Pool size should match what your database can actually handle concurrently, not how many requests you expect — an oversized pool just moves the bottleneck onto the database server.

Why opening a connection is expensive

Every time your application talks to a database over a brand-new connection, several things have to happen before a single query runs: a TCP handshake with the server, TLS negotiation if the connection is encrypted, and an authentication exchange to prove who you are. PostgreSQL then forks an entire new backend process for that connection; MySQL spins up a thread per connection. None of this touches your data — it is pure overhead that has to complete before the first SELECT even starts.

On a fast local network this might take 5-20ms. Over the network path a real cloud app actually runs on, or with TLS added, it is often 50-100ms. Compare that to the query itself, which for an indexed lookup might take 1-2ms. Open a new connection for every request and you can spend 95% of your total request time on connection setup and 5% on the work you actually wanted.

A pool removes this cost by paying it once, up front, for a batch of connections, then reusing each one thousands of times. This is not an optimization you bolt on once things get slow — for anything beyond a one-off script, pooling is simply the correct default way to talk to a database.

How a connection pool actually works

A pool is a fixed-size set of ready connections plus a waiting line. When your code asks for a connection, the pool hands over one that is free. If none are free, the request waits until someone releases one. Here is that mechanism stripped down to plain JavaScript, with a string standing in for a real database socket so you can see the logic on its own:

class ConnectionPool {
  constructor(size) {
    this.available = Array.from({ length: size }, (_, i) => `conn-${i + 1}`);
    this.waiting = [];
  }

  acquire() {
    if (this.available.length > 0) {
      return Promise.resolve(this.available.pop());
    }
    return new Promise((resolve) => this.waiting.push(resolve));
  }

  release(conn) {
    if (this.waiting.length > 0) {
      const resolve = this.waiting.shift();
      resolve(conn);
    } else {
      this.available.push(conn);
    }
  }
}

async function runQuery(pool, label, holdMs) {
  const start = Date.now();
  const conn = await pool.acquire();
  console.log(`[${label}] got ${conn} after ${Date.now() - start}ms wait`);
  await new Promise((r) => setTimeout(r, holdMs));
  pool.release(conn);
  console.log(`[${label}] released ${conn}`);
}

const pool = new ConnectionPool(2);

// 5 requests arrive "at once" but only 2 connections exist
for (let i = 1; i <= 5; i++) {
  runQuery(pool, `request-${i}`, 100);
}

Running this with a pool of 2 connections against 5 concurrent requests produces:

[request-1] got conn-2 after 0ms wait
[request-2] got conn-1 after 5ms wait
[request-1] released conn-2
[request-3] got conn-2 after 116ms wait
[request-2] released conn-1
[request-4] got conn-1 after 123ms wait
[request-3] released conn-2
[request-5] got conn-2 after 238ms wait
[request-4] released conn-1
[request-5] released conn-2

request-1 and request-2 get a connection immediately. request-3 through request-5 queue behind whichever connection frees up next — notice their wait time climbing. This is exactly what a real pool in pg, mysql2, or an ORM's built-in pool does, just with actual sockets instead of string labels. The queue is the part beginners miss: a full pool does not reject the 6th request, it makes it wait. A pool that is too small does not throw errors, it just makes everything slower in a way that is easy to blame on the database itself.

Sizing the pool: bigger is not better

The instinct is to set the pool size high — if 10 connections is good, surely 100 is safer. It usually backfires. Your database has a fixed number of CPU cores, and each active connection competes for them. Push concurrent connections past what those cores can actually execute in parallel, and the database spends more time context-switching between connections than running queries. Throughput goes down as pool size goes up past a certain point.

Idle connections are not free either — PostgreSQL reserves several megabytes of memory per connection whether it is doing anything or not. And PostgreSQL's own default max_connections setting is 100 for the entire server, not per application.

That last number matters more than people expect once you have more than one app instance. If you run 10 replicas of your API and each opens a pool of 20, that is 200 simultaneous connections aimed at a database that only accepts 100 by default — and the 101st connection attempt is refused outright, not queued. A widely cited starting formula (popularized by the HikariCP connection pool project) is roughly ((core_count × 2) + effective_disk_count) connections per pool. In practice: start small (10-20), watch actual database CPU and wait times under real load, and size up only with evidence.

The classic mistake: forgetting to release

A pool is only as safe as your promise to give connections back. The most common bug is an early return or a thrown error on the path before the release call runs:

// BUGGY: forgets to release on the error path
async function buggyQuery(pool, label, shouldThrow) {
  const conn = await pool.acquire();
  console.log(`[${label}] acquired ${conn}`);
  if (shouldThrow) {
    console.log(`[${label}] threw before release() ran -- connection leaked`);
    return; // release(conn) never runs
  }
  pool.release(conn);
}

const pool = new ConnectionPool(2);
await buggyQuery(pool, 'query-1', true);
await buggyQuery(pool, 'query-2', true);
// Both connections are now leaked -- 0 available, nobody waiting yet

console.log('available connections left:', pool.available.length);

const timeout = new Promise((resolve) => setTimeout(() => resolve('TIMED_OUT'), 800));
const result = await Promise.race([
  pool.acquire().then(() => 'GOT_CONNECTION'),
  timeout,
]);
console.log('query-3 result:', result);

Running this against a pool of 2:

[query-1] acquired conn-2
[query-1] threw before release() ran -- connection leaked
[query-2] acquired conn-1
[query-2] threw before release() ran -- connection leaked
available connections left: 0
query-3 result: TIMED_OUT

Both connections leak on the very first two calls. There is nothing left for query-3, so it waits — forever, in a real app, unless the driver enforces its own acquisition timeout and throws. Note that the pool itself did not crash or log anything; it just quietly ran out. That is what makes this bug painful in production: it looks like the database got slow, when the actual cause is application code that never let go of what it borrowed.

The fix is unglamorous: wrap the borrowed connection in try/finally so release always runs, success or failure:

// FIXED: release always runs, success or failure
async function safeQuery(pool, label, shouldThrow) {
  const conn = await pool.acquire();
  try {
    console.log(`[${label}] acquired ${conn}`);
    if (shouldThrow) throw new Error('something failed');
  } finally {
    pool.release(conn); // guaranteed to run
  }
}

Every mature database driver's pool implementation follows this same shape internally — the discipline is making sure your own code around it does too.

Pooling in Node.js, and the serverless trap

In practice you rarely write pool logic yourself — drivers ship it. In Node, mysql2 and pg both expose a pool through configuration:

const mysql = require('mysql2');

const pool = mysql.createPool({
  host: 'localhost',
  user: 'app',
  password: process.env.DB_PASSWORD,
  database: 'priodemy',
  waitForConnections: true, // queue instead of throwing when the pool is full
  connectionLimit: 10,      // max simultaneous connections
  queueLimit: 0,            // 0 = unlimited queued requests
});

waitForConnections is the queueing behaviour from earlier; connectionLimit is the pool size; queueLimit caps how many requests are allowed to wait before the driver gives up and rejects outright, rather than letting the queue grow without bound under sustained overload.

There is one environment where this whole model gets shakier: serverless functions. A Lambda or Cloud Function invocation can spin up a fresh process, meaning a fresh pool, on every cold start. Ten short-lived functions each opening a pool of 10 can hit a database with 100 real connections that were never actually shared or reused the way pooling promises — the opposite of the point. That is why serverless architectures typically put an external pooler like PgBouncer or AWS RDS Proxy in front of the database itself: it holds the real connections and lets each function instance make a cheap, short-lived connection to the pooler instead.

Frequently Asked Questions