Database Connection Pooling yaj

database connection open expensive: TCP handshake, authentication, TLS negotiation - query wa' Qu'qaSpa' tens of milliseconds. request per connection open vaj cost Hoch pay, database new connections refuse (hard limit). Connection pooling cha' problems solve: already-open connections set reuse vaD ready.

pool nuq Qu'

connection pool database connections fixed set, application lifetime Daq live (request lifetime pagh). query Qu' poQ vaj pool vo' connection borrow, lo', return - socket open close pagh.

const { Pool } = require('pg');

const pool = new Pool({
  host: 'localhost',
  database: 'app',
  max: 10, // maximum number of connections in the pool
  idleTimeoutMillis: 30000,
});

async function getUser(id) {
  const client = await pool.connect();
  try {
    const result = await client.query('SELECT * FROM users WHERE id = $1', [id]);
    return result.rows[0];
  } finally {
    client.release(); // return the connection to the pool, don't close it
  }
}

pool.connect() idle connection existing nob (ghaj vaj) pagh new open (max Dung). client.release() people qaw part - skip vaj connection pool Daq return pagh, load Daq exhaust.

unbounded pool no pool law' Qab

max high set "safe" tempting. Qagh: database hard connection ceiling ghaj (max_connections Postgres 100 default), application instance Hoch rap shared. 10 app instances max: 50 Hoch vaj 500 connections request, database 100 accept neH - ceiling hit vaj others reject (admin tools je).

reasonable pool size starting heuristic: (CPU cores × 2) + effective spindle count, application instances Hoch divide - "concurrent requests expect" pagh. database CPU cores parallel serve laH law' connections queueing chel (throughput pagh).

common pooling bug: error release pagh

// Bad: if query() throws, the connection is never released
async function getUserBroken(id) {
  const client = await pool.connect();
  const result = await client.query('SELECT * FROM users WHERE id = $1', [id]);
  client.release();
  return result.rows[0];
}

client.query() throw vaj client.release() Qu'be', connection permanently checked out. law' vaj pool silently zero usable connections (outside healthy legh). try/finally version fix - finally query succeed throw 'ach Qu'.

one-off statements pool.query()

drivers law' shortcut: connection borrow, wa' query Qu', automatic release:

const result = await pool.query('SELECT * FROM users WHERE id = $1', [id]);

single independent queries vaD lo'. transactions vaD Dun pagh: pool.query() call Hoch pIm connection Daq Qu' laH - transaction wa' specific connection Daq whole duration neH Sov.

transactions explicit connection poQ

async function transferFunds(fromId, toId, amount) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query('UPDATE accounts SET balance = balance - $1 WHERE id = $2', [amount, fromId]);
    await client.query('UPDATE accounts SET balance = balance + $1 WHERE id = $2', [amount, toId]);
    await client.query('COMMIT');
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

wa' client BEGIN, cha' updates, COMMIT Qu' - wa' transaction Daq hold. pool.query() calls mix vaj statements silently pIm connections Daq run (transaction tlhoch).

idle timeouts 'ej stale connections

idle long connections database, load balancer, firewall silently drop laH (application Sov pagh). idleTimeoutMillis pool idle too long connections proactively close replace (dead connection use try, mid-request confusing error pagh).

const pool = new Pool({
  max: 10,
  idleTimeoutMillis: 30000, // close idle connections after 30s
  connectionTimeoutMillis: 5000, // fail fast if no connection is available within 5s
});

connectionTimeoutMillis qatlh: pagh vaj pool exhausted request indefinitely hang (clear error fail fast pagh).

serverless math choH

traditional long-running server wa' pool process lifetime live - pooling design. serverless (Lambda, Cloud Functions) invocation Hoch fresh process spin up laH, invocation per full pool create purpose Qaw' - connection overhead constantly pay, concurrent invocations own pool open database connection limit collectively blow past. usual fix external pooler (PgBouncer, RDS Proxy): functions 'ej database Dung, short-lived clients real database connections few Daq multiplex.

yaj

connection pool request per connection open cost trade fixed reused already-open connections - sizing 'ej release discipline Dun. database actually serve laH size, finally Daq reH release, transactions single explicit connection Daq pin.