What is database connection pooling?

// Discrete simulation with a 1 ms fake clock: an app sends queries to a database
// with 8 CPU cores. Each query needs 4 ms of CPU. Running more queries at once than
// there are cores does not add capacity; it adds contention (context switches, locks).
const CORES = 8, WORK = 4, CONNECT_MS = 20, CONNECT_CPU = 1, CONTENTION = 0.005;

function arrivals(light) {
  const out = [];
  if (light) { for (let t = 0; t < 1000; t += 10) out.push(t); return out; } // 100 req/s
  for (let t = 0; t < 100; t++) for (let k = 0; k < 4; k++) out.push(t); // burst: 4000 req/s for 100 ms
  for (let t = 100; t < 1000; t++) out.push(t);                         // then 1000 req/s
  return out;
}

function simulate(poolSize, light) {          // poolSize 0 means: open a new connection per request
  const reqs = arrivals(light);
  const queue = [], connecting = [], active = [], latencies = [];
  let next = 0, free = poolSize, maxConns = 0, t = 0;
  while (latencies.length < reqs.length) {
    while (next < reqs.length && reqs[next] === t) {
      if (poolSize === 0) connecting.push({ arrived: t, ready: t + CONNECT_MS });
      else queue.push({ arrived: t });
      next++;
    }
    while (connecting.length > 0 && connecting[0].ready === t) {
      const r = connecting.shift();
      active.push({ arrived: r.arrived, work: WORK + CONNECT_CPU }); // the server also pays to set up a session
    }
    while (poolSize > 0 && free > 0 && queue.length > 0) {
      active.push({ arrived: queue.shift().arrived, work: WORK });
      free--;
    }
    maxConns = Math.max(maxConns, poolSize === 0 ? connecting.length + active.length : poolSize - free);
    const k = active.length;
    if (k > 0) {
      const efficiency = 1 / (1 + CONTENTION * Math.max(0, k - CORES));
      const share = Math.min(1, (CORES * efficiency) / k);
      for (const q of active) q.work -= share;
      for (let i = active.length - 1; i >= 0; i--) {
        if (active[i].work <= 1e-9) {
          latencies.push(t + 1 - active[i].arrived);
          active.splice(i, 1);
          if (poolSize > 0) free++;
        }
      }
    }
    t++;
  }
  latencies.sort((a, b) => a - b);
  const pct = (q) => latencies[Math.ceil(q * latencies.length) - 1];
  return { p50: pct(0.5), p99: pct(0.99), max: latencies[latencies.length - 1], done: t, maxConns };
}

console.log(`Load: ${arrivals().length} queries; burst of 400 in the first 100 ms, then 1 per ms until t=1000 ms`);
console.log(`Database: ${CORES} cores, ${WORK} ms CPU per query; new connection = ${CONNECT_MS} ms handshake + ${CONNECT_CPU} ms server CPU`);
console.log("");
console.log("pool size        p50 ms  p99 ms  max ms  all done at  peak DB connections");
for (const size of [0, 2, 8, 16, 64, 400]) {
  const r = simulate(size, false);
  const name = size === 0 ? "none (connect)" : String(size);
  console.log(`${name.padEnd(15)} ${String(r.p50).padStart(7)} ${String(r.p99).padStart(7)} ${String(r.max).padStart(7)} ${String(r.done).padStart(9)} ms ${String(r.maxConns).padStart(10)}`);
}

console.log("");
console.log(`Light load: ${arrivals(true).length} queries, 1 every 10 ms (no queueing, no contention)`);
for (const size of [0, 8]) {
  const r = simulate(size, true);
  console.log(`  pool ${size === 0 ? "none (connect)" : size}: p50 ${r.p50} ms, p99 ${r.p99} ms`);
}

console.log("");
console.log("Fleet arithmetic: why per-instance pools still overload the database");
const instances = 40, perInstance = 10, maxConnections = 100;
console.log(`  ${instances} app instances x pool ${perInstance} = ${instances * perInstance} connections > max_connections ${maxConnections}`);
const poolerServerSide = 20;
console.log(`  with a transaction-mode pooler: ${instances * perInstance} client connections share ${poolerServerSide} server connections`);
console.log(`  HikariCP starting point for ${CORES} cores, data cached: ${CORES} * 2 + 0 = ${CORES * 2} connections`);