Database Connection Pooling Explained — How It Works, Why It Matters, and How to Configure It Right

import time
import psycopg2
from psycopg2 import pool

# --- Database config (adjust to your local Postgres instance) ---
DB_CONFIG = {
    "host": "localhost",
    "port": 5432,
    "dbname": "demo_db",
    "user": "demo_user",
    "password": "demo_pass"
}

NUM_QUERIES = 20

# ─────────────────────────────────────────────────────────────
# APPROACH 1: Raw connections — open and close for every query
# This is what most beginners do without realising the cost.
# ─────────────────────────────────────────────────────────────
def run_queries_without_pooling():
    total_start = time.perf_counter()

    for query_index in range(NUM_QUERIES):
        # Every iteration: full TCP handshake + auth + memory alloc
        raw_conn = psycopg2.connect(**DB_CONFIG)
        cursor = raw_conn.cursor()
        cursor.execute("SELECT pg_sleep(0);")  # Simulates a trivial query
        cursor.close()
        raw_conn.close()  # Tears down everything we just built

    total_elapsed = time.perf_counter() - total_start
    return total_elapsed

# ─────────────────────────────────────────────────────────────
# APPROACH 2: Connection pool — connections are reused
# The pool opens connections once; threads borrow and return them.
# ─────────────────────────────────────────────────────────────
def run_queries_with_pooling():
    # minconn=2: keep at least 2 connections alive always
    # maxconn=10: never open more than 10 simultaneous connections
    connection_pool = pool.SimpleConnectionPool(
        minconn=2,
        maxconn=10,
        **DB_CONFIG
    )

    total_start = time.perf_counter()

    for query_index in range(NUM_QUERIES):
        # getconn() grabs a ready-to-use connection from the pool
        pooled_conn = connection_pool.getconn()
        cursor = pooled_conn.cursor()
        cursor.execute("SELECT pg_sleep(0);")
        cursor.close()
        # putconn() returns it — does NOT close it. Ready for next caller.
        connection_pool.putconn(pooled_conn)

    total_elapsed = time.perf_counter() - total_start
    connection_pool.closeall()  # Only close when the app shuts down
    return total_elapsed


if __name__ == "__main__":
    print(f"Running {NUM_QUERIES} queries each way...\n")

    no_pool_time = run_queries_without_pooling()
    print(f"Without pooling : {no_pool_time:.3f}s  ({no_pool_time/NUM_QUERIES*1000:.1f}ms per query)")

    pool_time = run_queries_with_pooling()
    print(f"With pooling    : {pool_time:.3f}s  ({pool_time/NUM_QUERIES*1000:.1f}ms per query)")

    speedup = no_pool_time / pool_time
    print(f"\nSpeedup from pooling: {speedup:.1f}x")

import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import javax.sql.DataSource;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

public class HikariPoolConfiguration {

    /**
     * Builds a production-ready HikariCP connection pool.
     *
     * HikariCP is the de-facto standard pool in the Java ecosystem.
     * Spring Boot uses it by default. It's fast, well-tested, and
     * its configuration options map directly to real operational concerns.
     */
    public static DataSource buildProductionPool() {
        HikariConfig config = new HikariConfig();

        // --- Connection Target ---
        config.setJdbcUrl("jdbc:postgresql://localhost:5432/demo_db");
        config.setUsername("demo_user");
        config.setPassword("demo_pass");
        config.setDriverClassName("org.postgresql.Driver");

        // --- Pool Sizing (this is where most mistakes happen) ---
        // For a 4-core machine with SSD storage: (4 * 2) + 1 = 9
        // Don't just set this to 100 and hope for the best.
        config.setMaximumPoolSize(9);

        // Always keep at least this many connections warm.
        // Avoids cold-start latency spikes after periods of inactivity.
        config.setMinimumIdle(3);

        // --- Timeout Configuration ---
        // How long a thread waits for a connection before throwing an exception.
        // 30 seconds is reasonable; tune down to 3-5s for user-facing APIs.
        config.setConnectionTimeout(30_000);  // milliseconds

        // How long a connection can sit idle before the pool closes it.
        // Prevents accumulation of stale connections during quiet periods.
        config.setIdleTimeout(600_000);  // 10 minutes

        // Maximum lifetime of ANY connection, even an active one.
        // Must be shorter than your database's wait_timeout / connect_timeout.
        // If the DB kills connections after 1 hour, set this to 55 minutes.
        config.setMaxLifetime(1_800_000);  // 30 minutes

        // --- Health Check ---
        // Sent to the DB to verify a connection is still alive before lending it out.
        // This catches connections that were silently dropped by a firewall or NAT.
        config.setConnectionTestQuery("SELECT 1");

        // --- Pool Name (critical for monitoring/logs) ---
        // Always name your pools. When you have 3 pools (read, write, analytics)
        // you'll thank yourself when debugging.
        config.setPoolName("primary-write-pool");

        // Enable JMX metrics so your APM tool can track pool saturation
        config.setRegisterMbeans(true);

        return new HikariDataSource(config);
    }

    public static void main(String[] args) throws SQLException {
        DataSource pool = buildProductionPool();

        // Borrowing a connection uses try-with-resources — ALWAYS do this.
        // It guarantees the connection returns to the pool even if an exception occurs.
        try (Connection conn = pool.getConnection();
             PreparedStatement stmt = conn.prepareStatement(
                 "SELECT current_database(), pg_backend_pid()"
             );
             ResultSet results = stmt.executeQuery()) {

            if (results.next()) {
                String dbName = results.getString(1);
                int backendPid = results.getInt(2);  // The actual OS process ID on Postgres
                System.out.println("Connected to database : " + dbName);
                System.out.println("PostgreSQL backend PID: " + backendPid);
                System.out.println("Connection returned to pool after this block exits.");
            }
        }
        // At this point, the connection is back in the pool — NOT closed.
        // The pool is still holding it warm for the next caller.

        System.out.println("Pool is still running. App continues serving requests.");
    }
}

import threading
import time
from psycopg2 import pool as pg_pool
from psycopg2 import OperationalError

DB_CONFIG = {
    "host": "localhost",
    "port": 5432,
    "dbname": "demo_db",
    "user": "demo_user",
    "password": "demo_pass"
}

# A deliberately small pool to demonstrate exhaustion
SMALL_POOL_MAX = 3
NUM_CONCURRENT_THREADS = 8  # More threads than pool slots

results_log = []  # Shared list to track what each thread experienced
results_lock = threading.Lock()

# ─────────────────────────────────────────────────────────────
# Simulates a worker thread that holds a connection for a while
# (like a slow report query or a transaction with multiple steps)
# ─────────────────────────────────────────────────────────────
def simulate_slow_worker(thread_id: int, connection_pool: pg_pool.ThreadedConnectionPool):
    request_start = time.perf_counter()
    acquired = False

    try:
        # getconn() will block until a connection is free, OR raise if the
        # pool implementation times out. ThreadedConnectionPool raises
        # PoolError if the pool is exhausted and blocking isn't configured.
        conn = connection_pool.getconn()
        acquired = True
        wait_time = time.perf_counter() - request_start

        # Simulates a query that takes 2 seconds (e.g. a complex report)
        cursor = conn.cursor()
        cursor.execute("SELECT pg_sleep(2);")
        cursor.close()

        connection_pool.putconn(conn)  # Return it — critical!

        with results_lock:
            results_log.append(
                f"Thread-{thread_id:02d} | SUCCESS | waited {wait_time:.2f}s for connection"
            )

    except Exception as pool_error:
        wait_time = time.perf_counter() - request_start
        with results_lock:
            results_log.append(
                f"Thread-{thread_id:02d} | FAILED  | {type(pool_error).__name__} after {wait_time:.2f}s"
            )


def main():
    print(f"Pool size: {SMALL_POOL_MAX} | Concurrent threads: {NUM_CONCURRENT_THREADS}")
    print(f"Each query holds its connection for ~2 seconds\n")

    # ThreadedConnectionPool is the thread-safe variant for multi-threaded apps
    connection_pool = pg_pool.ThreadedConnectionPool(
        minconn=1,
        maxconn=SMALL_POOL_MAX,
        **DB_CONFIG
    )

    threads = [
        threading.Thread(
            target=simulate_slow_worker,
            args=(thread_id, connection_pool)
        )
        for thread_id in range(NUM_CONCURRENT_THREADS)
    ]

    # Launch all threads simultaneously to maximise pool pressure
    for thread in threads:
        thread.start()

    for thread in threads:
        thread.join()

    connection_pool.closeall()

    print("Results:")
    print("-" * 60)
    for result in sorted(results_log):
        print(result)

    failures = sum(1 for r in results_log if "FAILED" in r)
    print(f"\n{failures}/{NUM_CONCURRENT_THREADS} threads failed due to pool exhaustion")
    print("Fix: increase maxconn, reduce query time, or use a queue to limit concurrency")


if __name__ == "__main__":
    main()

// Node.js connection pooling with the 'pg' library (node-postgres)
// Install: npm install pg

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

// ─────────────────────────────────────────────────────────────
// Create ONE pool per application process.
// This is typically done in a module (db.js) and imported everywhere.
// Creating a new Pool per request is a classic mistake — see Gotchas section.
// ─────────────────────────────────────────────────────────────
const databasePool = new Pool({
  host: 'localhost',
  port: 5432,
  database: 'demo_db',
  user: 'demo_user',
  password: 'demo_pass',

  // In Node.js, the event loop handles concurrency, so
  // a pool of 10-20 is typical. You're limited more by
  // the DB server's max_connections than your own CPU.
  max: 10,

  // Min connections to keep alive between request bursts
  min: 2,

  // How long (ms) a client can sit idle before being closed
  idleTimeoutMillis: 30_000,

  // How long (ms) to wait for a connection before rejecting
  connectionTimeoutMillis: 5_000,
});

// ─────────────────────────────────────────────────────────────
// Always attach an error listener to the pool.
// Without this, an unexpected error on an idle connection
// will crash the entire Node.js process.
// ─────────────────────────────────────────────────────────────
databasePool.on('error', (unexpectedError, idleClient) => {
  console.error('Unexpected error on idle database client:', unexpectedError.message);
  // Don't crash — the pool will remove this connection and create a new one
});

databasePool.on('connect', (newClient) => {
  // Fired each time the pool opens a brand new connection to the DB.
  // Good place to set session-level settings like timezone.
  newClient.query("SET TIME ZONE 'UTC';");
  console.log('[Pool] New connection established to database');
});

// ─────────────────────────────────────────────────────────────
// Helper function: wraps pool.query() for one-shot queries.
// For transactions, you need pool.connect() instead (see below).
// ─────────────────────────────────────────────────────────────
async function getUserById(userId) {
  // pool.query() auto-acquires a connection, runs the query, and releases it.
  // Safe for single queries. NOT for multi-statement transactions.
  const queryResult = await databasePool.query(
    'SELECT id, username, email, created_at FROM users WHERE id = $1',
    [userId]  // Parameterised — never concatenate user input into SQL
  );
  return queryResult.rows[0] ?? null;
}

// ─────────────────────────────────────────────────────────────
// For transactions, you MUST hold onto one client for all steps.
// Using pool.query() for a transaction would run each statement
// on a DIFFERENT connection — breaking atomicity entirely.
// ─────────────────────────────────────────────────────────────
async function transferFunds(fromAccountId, toAccountId, amountInCents) {
  // Manually acquire a client — this 'checks out' one connection from the pool
  const client = await databasePool.connect();

  try {
    await client.query('BEGIN');

    await client.query(
      'UPDATE accounts SET balance_cents = balance_cents - $1 WHERE id = $2',
      [amountInCents, fromAccountId]
    );

    await client.query(
      'UPDATE accounts SET balance_cents = balance_cents + $1 WHERE id = $2',
      [amountInCents, toAccountId]
    );

    await client.query('COMMIT');
    console.log(`Transfer of ${amountInCents} cents completed successfully.`);

  } catch (transactionError) {
    // Something went wrong — roll back every change in this transaction
    await client.query('ROLLBACK');
    console.error('Transaction failed, rolled back:', transactionError.message);
    throw transactionError;

  } finally {
    // This MUST run — even if commit or rollback itself throws.
    // Without this, the connection is never returned to the pool.
    client.release();
  }
}

// ─────────────────────────────────────────────────────────────
// Example usage
// ─────────────────────────────────────────────────────────────
async function main() {
  const user = await getUserById(42);
  console.log('Fetched user:', user);

  await transferFunds(accountId_A = 101, accountId_B = 202, amountInCents = 5000);

  // Graceful shutdown: drain the pool when the app stops
  await databasePool.end();
  console.log('Pool closed. All connections released.');
}

main().catch(console.error);