Connection Pool

The previous two lessons made the query generated from a single point. One more thing is needed for the query to run: a connection. So far, every script has opened its own connection, finished its job, and the process ended.

A continuously running service cannot behave this way. Opening a connection is not free, and the number of concurrent connections a database will accept is limited. A connection pool is a structure that keeps already-opened connections and lends them out to requests. This lesson builds the pool, measures how long requests wait in the queue by varying the pool size, and shows the two traps the pool brings.

Opening Cost

The first question is whether the cost really exists.

// opening-cost.mjs — opening a connection on every request versus reusing a single connection
import { DatabaseSync } from "node:sqlite";

const N = 2000;
const QUERY = "SELECT count(*) AS n FROM loan WHERE return_date IS NULL";

let t = performance.now();
for (let i = 0; i < N; i++) {
  const db = new DatabaseSync("library.db");
  db.prepare(QUERY).get();
  db.close();
}
const openClose = performance.now() - t;

const db = new DatabaseSync("library.db");
t = performance.now();
for (let i = 0; i < N; i++) db.prepare(QUERY).get();
const reuse = performance.now() - t;
db.close();

console.log(`open-close per request : ${openClose.toFixed(0)} ms  (${(openClose / N).toFixed(3)} ms/request)`);
console.log(`reuse                  : ${reuse.toFixed(0)} ms  (${(reuse / N).toFixed(3)} ms/request)`);
console.log(`ratio                  : ${(openClose / reuse).toFixed(1)}x`);

The command below sets up the schema and runs the measurement.

rm -f library.db
sqlite3 library.db <<'SQL'
CREATE TABLE loan (loan_id INTEGER PRIMARY KEY, book_id INTEGER NOT NULL,
                    member_id INTEGER NOT NULL, pickup_date TEXT NOT NULL, return_date TEXT);
INSERT INTO loan VALUES (1,1,1,'2025-01-10','2025-01-24'),(3,1,2,'2025-02-11',NULL),
  (7,5,4,'2025-04-21',NULL),(10,3,5,'2025-06-03',NULL),(12,4,4,'2025-06-20','2025-07-04');
SQL
node opening-cost.mjs

open-close per request : 113 ms  (0.057 ms/request)
reuse                  : 10 ms  (0.005 ms/request)
ratio                  : 11.3x

The durations depend on the hardware and the file system; the numbers change on another machine. What matters is the ratio: opening the connection every time to do the same work cost eleven times more than reusing an open connection. This measurement is against a local file. In a database spoken to over the network, the opening cost also includes session setup, authentication, and an encryption handshake; the ratio grows.

Pool and Queue

The pool manages two states: the list of idle connections and the queue of those waiting for a free connection. If an idle connection exists, the request is served immediately; if not, it enters the queue.

// pool.mjs — a fixed-size resource pool and its queue
export class Pool {
  constructor(create, size, queueTimeoutMs) {
    this.idle = Array.from({ length: size }, (_, i) => create(i));
    this.queue = [];
    this.queueTimeoutMs = queueTimeoutMs;
    this.size = size;
  }
  acquire() {
    if (this.idle.length > 0) return Promise.resolve(this.idle.pop());
    return new Promise((resolve, reject) => {
      const waiter = { resolve, reject };
      waiter.timer = setTimeout(() => {
        this.queue.splice(this.queue.indexOf(waiter), 1);
        reject(new Error("queue timeout"));
      }, this.queueTimeoutMs);
      this.queue.push(waiter);
    });
  }
  release(connection) {
    const waiter = this.queue.shift();
    if (waiter === undefined) { this.idle.push(connection); return; }
    clearTimeout(waiter.timer);
    waiter.resolve(connection);
  }
}

The release method’s behavior deserves attention: the queue is checked before the released connection goes back to the idle list. If someone is waiting in the queue, the connection is handed to them directly. This is both faster than putting it on the idle list and taking it again, and it preserves the waiters’ order.

The pool must not make a connection wait forever. Once the queueTimeoutMs duration elapses, the waiting request is removed from the queue and gets an error. This is a queue timeout; in its absence, a database that slows down leads to every request waiting indefinitely and the application coming to a complete halt.

Pooled Service

On every request the service acquires a connection from the pool, runs the query, and releases the connection back in a finally block. The finally is mandatory: a connection not released in an error case is permanently lost from the pool.

// server.mjs — every request takes a connection from the pool, runs the query, releases it back
import { createServer } from "node:http";
import { DatabaseSync } from "node:sqlite";
import { Pool } from "./pool.mjs";

const SIZE = Number(process.env.POOL_SIZE ?? 4);
const ROUND_TRIP_MS = Number(process.env.ROUND_TRIP_MS ?? 40);
const TIMEOUT_MS = Number(process.env.TIMEOUT_MS ?? 2000);

const pool = new Pool(() => new DatabaseSync("library.db"), SIZE, TIMEOUT_MS);
const measurements = [];
const wait = (ms) => new Promise((c) => setTimeout(c, ms));

const server = createServer(async (request, response) => {
  const path = new URL(request.url, "http://local").pathname;

  if (path === "/shutdown") { response.end("shutting down\n"); server.close(); return; }

  if (path === "/report") {
    const waits = measurements.map((o) => o.wait).sort((x, y) => x - y);
    const percentile = (p) => (waits.length === 0 ? 0 : +waits[Math.min(waits.length - 1, Math.floor(waits.length * p))].toFixed(1));
    response.writeHead(200, { "content-type": "application/json" });
    response.end(JSON.stringify({
      pool_size: SIZE, requests: measurements.length,
      rejected: measurements.filter((o) => o.rejected).length,
      wait_median_ms: percentile(0.5), wait_p95_ms: percentile(0.95),
      wait_max_ms: waits.length === 0 ? 0 : +waits[waits.length - 1].toFixed(1),
    }));
    return;
  }

  const queuedAt = performance.now();
  let connection;
  try {
    connection = await pool.acquire();
  } catch {
    measurements.push({ wait: performance.now() - queuedAt, rejected: true });
    response.writeHead(503, { "content-type": "text/plain" });
    response.end("pool full\n");
    return;
  }
  const waitMs = performance.now() - queuedAt;
  try {
    const row = connection.prepare(
      "SELECT count(*) AS n FROM loan WHERE return_date IS NULL").get();
    await wait(ROUND_TRIP_MS);          // model of the database round trip
    measurements.push({ wait: waitMs, rejected: false });
    response.writeHead(200, { "content-type": "text/plain" });
    response.end(`open record: ${row.n}  wait_ms: ${waitMs.toFixed(1)}\n`);
  } finally {
    pool.release(connection);
  }
});
server.listen(8080, () => console.log(`pool size ${SIZE}, port 8080`));

The ROUND_TRIP_MS duration is a model, and it stays a model throughout this lesson. Because the database here runs inside the process, there is no real network round trip; the response time of a database sitting on a separate server is represented by this wait. What is measured is not the round trip itself, but the effect on the queue of the connection being busy for that duration.

Pool Size and Wait Time

The measurement setup is simple: send twenty-four requests at the same time, vary the pool size, and look at how long the requests wait in the queue.

# measure.sh — measures the wait time of 24 parallel requests as the pool size varies
for size in 1 2 4 8 24; do
  POOL_SIZE=$size ROUND_TRIP_MS=40 node server.mjs >/dev/null &
  server=$!
  sleep 0.6
  requests=""
  start=$(date +%s%N)
  for i in $(seq 24); do
    curl -s -o /dev/null http://localhost:8080/loan &
    requests="$requests $!"
  done
  wait $requests
  end=$(date +%s%N)
  printf 'total_ms=%s  ' $(( (end - start) / 1000000 ))
  curl -s http://localhost:8080/report
  echo
  curl -s -o /dev/null http://localhost:8080/shutdown
  wait $server
done

sh measure.sh

total_ms=1035  {"pool_size":1,"requests":24,"rejected":0,"wait_median_ms":493.8,"wait_p95_ms":904.2,"wait_max_ms":945.1}
total_ms=533  {"pool_size":2,"requests":24,"rejected":0,"wait_median_ms":241.7,"wait_p95_ms":444.5,"wait_max_ms":445.1}
total_ms=284  {"pool_size":4,"requests":24,"rejected":0,"wait_median_ms":115.3,"wait_p95_ms":196.3,"wait_max_ms":196.7}
total_ms=161  {"pool_size":8,"requests":24,"rejected":0,"wait_median_ms":37,"wait_p95_ms":76.5,"wait_max_ms":76.5}
total_ms=83  {"pool_size":24,"requests":24,"rejected":0,"wait_median_ms":0,"wait_p95_ms":0.1,"wait_max_ms":0.3}

The absolute durations depend on the machine; the relationship does not. Every time the pool size doubles, the median wait halves: 494, 242, 115, 37 milliseconds. The reason is directly visible. Twenty-four requests are processed in $\lceil 24/b \rceil$ rounds by a pool of size $b$; each round takes about one service duration, so the average wait in the queue is proportional to the round count.

When the pool is sized equal to the request count, the wait drops to zero. This does not give the conclusion “growing the pool is always good.” The wait dropping to zero does not mean the work is done faster; it means the queue has moved from the application to the database. The number of concurrent connections and the number of concurrent queries a database can handle are limited; if the pool grows past that limit, the wait accumulates in the database instead of the application, and it becomes harder to measure there.

The second point is the multiplication. If the application runs as multiple instances, the total connection count is the number of instances multiplied by the pool size. Eight instances with a pool of twenty connections ask the database for one hundred sixty connections.

Saturation

When every connection in the pool is busy and the queue wait exceeds the timeout, the pool has reached saturation. In this case the request is rejected.

# saturation.sh — small pool, short queue timeout: rejected requests
POOL_SIZE=2 ROUND_TRIP_MS=40 TIMEOUT_MS=200 node server.mjs >/dev/null &
server=$!
sleep 0.6
requests=""
for i in $(seq 24); do
  curl -s -o /dev/null -w '%{http_code}\n' http://localhost:8080/loan >> codes.txt &
  requests="$requests $!"
done
wait $requests
echo "response code distribution:"
sort codes.txt | uniq -c
rm -f codes.txt
curl -s http://localhost:8080/report; echo
curl -s -o /dev/null http://localhost:8080/shutdown
wait $server

sh saturation.sh

response code distribution:
  12 200
  12 503
{"pool_size":2,"requests":24,"rejected":12,"wait_median_ms":200.4,"wait_p95_ms":201.6,"wait_max_ms":201.7}

Half of the twenty-four requests were answered; the other half were rejected with 503 after waiting two hundred milliseconds. The ratio of accepted to rejected requests changes from run to run depending on the machine’s load at that moment; what does not change is that the rejected requests’ waits cluster just above the timeout value. This is the sign that the limit is working.

Rejecting looks like a failure, but the alternative is worse. Without the timeout, these twelve requests would keep waiting, client-side timeouts would kick in, clients would retry, and the queue would grow even longer. A bounded queue produces a fast, readable error.

Carrying Over Session State

The pool’s second trap concerns the connection itself. A connection is not just a pipe; it can carry temporary tables, settings, and an open transaction on it. This accumulation is called session state, and it passes to the next taker along with a released connection.

// session-state.mjs — leftover state on a released connection passes to the next taker
import { DatabaseSync } from "node:sqlite";
import { Pool } from "./pool.mjs";

const pool = new Pool(() => new DatabaseSync("library.db"), 1, 1000);

const first = await pool.acquire();
first.exec("CREATE TEMP TABLE temp_report (n INTEGER)");
first.exec("INSERT INTO temp_report VALUES (42)");
pool.release(first);                       // no cleanup was done

const second = await pool.acquire();
const leftover = second.prepare(
  "SELECT count(*) AS n FROM temp.sqlite_master WHERE name = 'temp_report'").get().n;
console.log("leftover tables seen by the second taker:", leftover);
if (leftover > 0) console.log("value inside it:", second.prepare("SELECT n FROM temp_report").get().n);

second.exec("DROP TABLE IF EXISTS temp_report");   // cleanup before release
pool.release(second);
const third = await pool.acquire();
console.log("leftover tables after cleanup           :", third.prepare(
  "SELECT count(*) AS n FROM temp.sqlite_master WHERE name = 'temp_report'").get().n);

node session-state.mjs

leftover tables seen by the second taker: 1
value inside it: 42
leftover tables after cleanup           : 0

The second taker saw a table it never created, and the value inside it. This means two requests reading each other’s state. There must be a cleanup step on the pool’s release path: an open transaction is rolled back, temporary objects are dropped, and session settings return to their defaults.

The pooling levels introduced in the Relational Database Administration course are different answers to this problem. In session-level pooling, the connection belongs to the client for the whole session, and session state is preserved. In transaction-level pooling, the connection is taken back at the end of every transaction; this raises pool utilization at the cost of session-bound features such as prepared statements and temporary tables becoming unusable.

Summary

• Opening the connection on every request cost eleven times more in this measurement than reusing an open connection; this difference is the pool’s reason for existing.
• The pool keeps two structures: the idle connections and the queue of waiters. A released connection is handed to the queue before it goes back to the idle list.
• Across twenty-four parallel requests, the median wait halved every time the pool size doubled (494 → 242 → 115 → 37 ms); the wait dropped to zero once the pool was sized equal to the request count.
• Growing the pool does not eliminate the wait; it moves the queue to the database. The total connection count is multiplied by the number of application instances.
• At saturation, the queue timeout rejects the request quickly; a fast error is preferred over an unbounded queue.
• Session state on a released connection passes to the next taker; cleanup on the release path is mandatory.

Next Step

When the pool is built, every connection sees the same schema. This assumption holds until the schema changes. When a column is added, removed, or renamed, the application’s running instances and the database’s state pull apart; an old instance might see the new schema, and a new instance the old one. The next lesson breaks a schema change into versioned migration steps, runs them forward and backward, and shows how a column is renamed without an outage, by implementing a three-step scheme.