Connection Poolers

Failover’s last step was redirecting applications to the new primary; that means the application connects not directly to the database, but through a layer in between. The same layer solves a second problem encountered every day, even without a failure.

The problem is this: as the number of application servers grows, so does the number of connections. When every application instance opens its own connections, ten instances at twenty connections each add up to two hundred connections. A connection is not a cheap object for a database engine — each one consumes memory, requires a separate process or thread on most engines, and its cost continues even while it sits idle. Once the connection count crosses a certain threshold, the engine slows down, then stops accepting new connections.

The Cost of a Connection

Opening a connection looks like a single operation from a distance. Inside it there are several steps: establishing the transport-layer connection, negotiating an encrypted session, authentication, starting a process or thread on the server side, preparing session state. None of these read any data.

The block below measures the lowest form of this cost: a file-based engine has no network, no encryption, no authentication, and no server-side process is started. What gets measured is only opening the file and reading schema information. On a server-based engine, steps on top of this cost get added; the measurement gives a floor, not a ceiling. The timings are machine-dependent.

rm -f pool.db

node - <<'EOF'
const { DatabaseSync } = require("node:sqlite");
const QUERY = "SELECT COUNT(*) c FROM loan WHERE member_id=?";
const N = 3000;

const setup = new DatabaseSync("pool.db");
setup.exec("CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, pickup TEXT)");
setup.exec("CREATE INDEX loan_member ON loan(member_id)");
const insert = setup.prepare("INSERT INTO loan(book_id,member_id,pickup) VALUES(?,?,?)");
setup.exec("BEGIN");
for (let i = 1; i <= 20000; i++) insert.run(i % 400, i % 250, "2025-06-01");
setup.exec("COMMIT");
setup.close();

const measure = (label, task) => {
  const t = process.hrtime.bigint();
  task();
  const ms = Number(process.hrtime.bigint() - t) / 1e6;
  console.log(label.padEnd(43) + " | " + ms.toFixed(1).padStart(9) + " | " +
              (ms * 1000 / N).toFixed(1).padStart(11));
};

console.log("path".padEnd(43) + " | total ms  | us per request");
console.log("-".repeat(43) + "-|-----------|--------------");
measure("new connection + statement each request", () => {
  for (let i = 0; i < N; i++) {
    const db = new DatabaseSync("pool.db");
    db.prepare(QUERY).get(i % 250);
    db.close();
  }
});
const shared = new DatabaseSync("pool.db");
measure("shared connection, statement each request", () => {
  for (let i = 0; i < N; i++) shared.prepare(QUERY).get(i % 250);
});
const prepared = shared.prepare(QUERY);
measure("shared connection, prepared statement", () => {
  for (let i = 0; i < N; i++) prepared.get(i % 250);
});
EOF

path                                        | total ms  | us per request
--------------------------------------------|-----------|--------------
new connection + statement each request     |     163.0 |        54.3
shared connection, statement each request   |      18.1 |         6.0
shared connection, prepared statement       |      11.7 |         3.9

The same query ran three ways, and the per-request time changed by a factor close to fourteen. The query itself does the same work in all three rows; what differs is the setup surrounding the query.

The gap between the second and third rows shows a separate point: when a prepared statement is reused, parsing and planning work is not repeated. This is the operating side of the same gain covered under plan caching in the Advanced SQL course.

A connection pool carries this table from the first row to the third. The pool holds a set of already-open connections; when the application requests one, it borrows it from the pool and returns it when the work is done. The connection does not close — the next request finds it already there.

Pooling Levels

When the pool takes the connection back determines what the application has to give up. There are three levels.

Session-level pooling hands the connection to the same client until the client connection closes. Nothing changes from the application’s point of view; every session-bound feature works. Its cost is that a database connection is needed for as many connections as there are concurrent clients — the connection count problem is not fully solved.

Transaction-level pooling hands out the connection only for the duration of a transaction; once the transaction ends the connection returns to the pool and can go to another client. This level is what lets a small number of connections serve a large number of clients. Its cost is that everything tied to the session becomes unreliable: temporary tables, session variables, session-level locks, and prepared statements attached to the connection.

Statement-level pooling takes the connection back after every statement. In high-client-count setups it gives the highest sharing, but because a multi-statement transaction cannot be written, the application cannot use transactions.

Transaction-level pooling’s constraint is not merely a performance detail; it can produce a security consequence. The block below shows this on the mechanism from the Row-Level Security lesson: the policy looks at a session variable, while the connection changes hands between two requests.

rm -f desk.db

node - <<'EOF'
const { DatabaseSync } = require("node:sqlite");

const db = new DatabaseSync("desk.db");   // the pool's only connection
db.exec(`
CREATE TABLE loan(id INTEGER PRIMARY KEY, book_id INT, member_id INT, branch_id INT, pickup TEXT);
CREATE TABLE session(key TEXT PRIMARY KEY, value TEXT NOT NULL);
`);
const insert = db.prepare("INSERT INTO loan(book_id,member_id,branch_id,pickup) VALUES(?,?,?,?)");
db.exec("BEGIN");
for (let i = 1; i <= 900; i++)
  insert.run(i % 400, i % 250, i <= 500 ? 1 : (i <= 800 ? 2 : 3), "2025-06-01");
db.exec("COMMIT");

// The policy looks at a session variable (the mechanism from the Row-Level Security lesson).
const visible = () => db.prepare(
  "SELECT COUNT(*) c FROM loan WHERE branch_id=" +
  "(SELECT CAST(value AS INTEGER) FROM session WHERE key='branch_id')").get().c;
const setBranch = (branch) => db.prepare(
  "INSERT INTO session VALUES('branch_id',?)" +
  " ON CONFLICT(key) DO UPDATE SET value=excluded.value").run(String(branch));
const actual = (b) => db.prepare("SELECT COUNT(*) c FROM loan WHERE branch_id=?").get(b).c;

console.log("actual row count per branch: 1 -> " + actual(1) + ", 2 -> " + actual(2));
console.log();

// Request 1: branch 1's desk takes the connection.
setBranch(1);
console.log("request 1 (branch 1) visible rows".padEnd(38) + ": " + visible());

// The connection is returned to the pool. The pool does not clear session state.
console.log("request 2 (branch 2), session not set".padEnd(38) + ": " + visible() +
            "  <- branch 1's rows");

// Same order, but this time the pool clears session state on handoff.
db.exec("DELETE FROM session");
console.log("request 2 (branch 2), pool cleared".padEnd(38) + ": " + visible() +
            "  <- no rows visible");
setBranch(2);
console.log("request 2 (branch 2), session set".padEnd(38) + ": " + visible());
EOF

actual row count per branch: 1 -> 500, 2 -> 300

request 1 (branch 1) visible rows     : 500
request 2 (branch 2), session not set : 500  <- branch 1's rows
request 2 (branch 2), pool cleared    : 0  <- no rows visible
request 2 (branch 2), session set     : 300

The second line is the problem itself. Branch two’s desk should see three hundred rows, but it sees five hundred; what it sees are another branch’s loan records. The policy is written correctly, the query is correct, the data is correct — the only thing wrong is that the connection is carrying the previous user’s session value.

The third line shows the fix: the pool clears session state as it hands off the connection. In the cleared state, the policy returns no rows at all. This behavior is the intended one — failing closed: when information is missing, no data is shown. The application resetting the session value at the start of every request produces the correct result on the fourth line.

The operating rule that follows applies to every setup using transaction-level pooling: no piece of session-bound information is written assuming it will survive across requests, and the pool’s handoff cleanup is verified against its configuration.

Pool Size and the Queue

Pool size determines how many requests can enter the database at the same time. If it is too small, requests wait in a queue; if it is too large, the number of concurrently running transactions on the engine grows and the transactions slow each other down — because of lock contention, cache sharing, and scheduler overhead.

The block below studies this by simulation. Arrivals, base transaction time, and the contention coefficient are model values; no real database is being run. The shape of the contention is also an assumption: as the number of concurrent transactions exceeds the core count, each transaction’s time grows linearly. On a real system this curve is found by measurement. What the model shows is that the curve has a best point.

node - <<'EOF'
// Model: requests wait for a connection from the pool, then run against the database.
// As the number of concurrent transactions exceeds the core count, every transaction
// slows down (contention).
const BASE = 10, CORES = 8, CONTENTION = 0.25;   // ms, count, coefficient
const RATE = 700, DURATION = 30000, STEP = 0.25; // requests/s, ms, ms
const TIMEOUT = 200;                             // ms

let seed = 20250616;
const random = () => (seed = (seed * 1103515245 + 12345) % 2147483648) / 2147483648;
const arrivals = [];
for (let t = 0; t < DURATION; ) {
  t += -Math.log(1 - random()) * 1000 / RATE;
  if (t < DURATION) arrivals.push(t);
}

console.log("load: " + RATE + " req/s, base transaction time " + BASE + " ms, " +
            CORES + " cores, " + arrivals.length + " requests");
console.log();
console.log("pool  | completed | queued   | active  | total   | time");
console.log("size  | requests  | (avg ms) | (avg ms)| (avg ms)| outs");
console.log("------|-----------|----------|---------|---------|-------");
for (const poolSize of [2, 4, 8, 16, 32, 64]) {
  const queue = [], active = [];
  let nextIdx = 0, completed = 0, queueTotal = 0, transactionTotal = 0, timeouts = 0;
  for (let t = 0; t < DURATION * 1.5; t += STEP) {
    while (nextIdx < arrivals.length && arrivals[nextIdx] <= t) queue.push(arrivals[nextIdx++]);
    while (queue.length && active.length < poolSize) {
      const arrival = queue.shift();
      if (t - arrival > TIMEOUT) { timeouts++; continue; }
      active.push({ wait: t - arrival, remaining: BASE, started: t });
    }
    const slowdown = 1 + CONTENTION * Math.max(0, active.length - CORES);
    for (const tx of active) tx.remaining -= STEP / slowdown;
    for (let i = active.length - 1; i >= 0; i--) {
      if (active[i].remaining > 0) continue;
      queueTotal += active[i].wait;
      transactionTotal += t - active[i].started;
      completed++; active.splice(i, 1);
    }
    if (nextIdx >= arrivals.length && !queue.length && !active.length) break;
  }
  console.log(String(poolSize).padStart(5) + " | " + String(completed).padStart(9) + " | " +
    (queueTotal / completed).toFixed(1).padStart(8) + " | " +
    (transactionTotal / completed).toFixed(1).padStart(7) + " | " +
    ((queueTotal + transactionTotal) / completed).toFixed(1).padStart(7) + " | " +
    String(timeouts).padStart(6));
}
EOF

load: 700 req/s, base transaction time 10 ms, 8 cores, 21200 requests

pool  | completed | queued   | active  | total   | time
size  | requests  | (avg ms) | (avg ms)| (avg ms)| outs
------|-----------|----------|---------|---------|-------
    2 |      6040 |    197.4 |     9.8 |   207.2 |  15160
    4 |     12080 |    196.4 |     9.8 |   206.1 |   9120
    8 |     21200 |      3.9 |     9.8 |    13.6 |      0
   16 |     16116 |    192.9 |    29.7 |   222.6 |   5084
   32 |     13823 |    193.5 |    69.6 |   263.1 |   7377
   64 |     12919 |    190.9 |   149.2 |   340.1 |   8281

The table shows two distinct failure modes in the same column. In the pools sized two and four, time spent active is at its lowest value; the problem is in the queue. What the pool lets through is less than what comes in, and most requests time out. This is pool saturation.

At sixteen and above, the queue is just as bad, but for a different reason: time spent active has grown by a factor of three to fifteen. The pool let more requests in, they slowed each other down inside, and total completed work dropped. Growing the pool made things worse here.

The pool sized eight is far from both extremes: the wait is four milliseconds, total response is thirteen milliseconds, and there are no timeouts. This number is the same as the core count, and that is not a coincidence — the best pool size sits close to the amount of work the engine can genuinely run in parallel. In disk-wait-dominated workloads this number runs above the core count; in CPU-dominated workloads it stays at the core count. The only way to find the right value is to measure it.

Pool size is thought about not for a single application instance but for the system as a whole. Total connection count is the product of pool size and the number of application instances; in a ten-instance setup, twenty connections per instance arrives at the engine as two hundred connections. That application-side pools do not see this product is the main reason for keeping a single pooler in the middle.

One last setting is idle connection lifetime. When load drops, shrinking the pool frees resources; reopening every time brings back the cost from the first table. The balance between the two extremes is set against how load varies over the course of a day.

Summary

• Opening a connection carries a setup cost independent of the query itself; in the measurement, the same query varied by a factor close to fourteen between the new connection and prepared statement paths.
• The pool lends out already-open connections and removes this cost, capping the number of connections reaching the engine.
• Session-level pooling does not constrain the application but does not reduce connection count; transaction-level pooling reduces it and makes everything session-bound unreliable.
• A session variable inherited from the pool can let a row-level security policy show another account’s rows; the pool’s handoff cleanup must be verified.
• If pool size is too small the queue grows; if too large, concurrent transactions slow each other down. The best value sits close to the amount of work the engine can run in parallel, and it is found by measurement.
• Total connection count is the product of pool size and the number of application instances.

Next Step

Every decision in this lesson rested on a measurement: connection cost, queue time, concurrent transaction count, the share of requests that time out. None of these are visible on their own; pool saturation is felt on the application side only as “the database is slow” and is usually looked for in the wrong place. The next lesson covers monitoring: which magnitudes to collect, why the average is misleading, what percentiles show, catching slow queries with a threshold, how wait events name the bottleneck, and when an alert is meaningful.