cd ..

$ cat ~/field-notes/database-connection-pooling-explained.md

Database Connection Pooling: The Settings That Actually Matter

5 min read 0x.run

Opening a database connection looks cheap in application code. Underneath, it can require a TCP handshake, TLS negotiation, authentication, and database-side memory. Doing that for every query is slow. Letting every request keep a connection forever is worse.

A connection pool is the small, boring component that keeps both mistakes out of production.

What a Pool Does

A pool owns a bounded set of open connections:

HTTP requests:  R1  R2  R3  R4  R5  R6
                    ↓
Connection pool: [C1] [C2] [C3]
                    ↓
Database

R1-R3 borrow a connection
R4-R6 wait in a queue
finished requests return connections to the pool

Borrowing an existing connection takes almost no time. If every connection is busy, new work waits rather than opening connections until the database falls over.

That queue is not a defect. It is backpressure.

The Most Common Sizing Mistake

Pool size is configured per application process, but the database sees the sum of every pool.

10 application instances
4 worker processes per instance
20 connections per process

10 × 4 × 20 = 800 possible database connections

A max: 20 setting looked conservative until autoscaling turned it into 800.

Start with the database connection budget, reserve capacity for migrations, admin access, and background jobs, then divide what remains across the maximum number of processes that can exist.

database max_connections:       200
reserved for admin/migrations:   20
reserved for other services:     60
available to this service:       120
maximum app processes:            12

pool max per process: 120 / 12 = 10

This is a starting limit, not a performance target. More connections do not automatically mean more throughput. After the database saturates its CPU or disk, extra concurrent queries mostly create contention.

A Safe Node.js Pool

import pg from 'pg';

const pool = new pg.Pool({
    connectionString: process.env.DATABASE_URL,
    max: 10,
    min: 0,
    connectionTimeoutMillis: 2_000,
    idleTimeoutMillis: 30_000,
    maxLifetimeSeconds: 1_800,
});

pool.on('error', (err) => {
    // An idle connection failed outside a request.
    logger.error({ err }, 'database pool error');
});

export async function findUser(id) {
    // pool.query borrows and releases automatically.
    const result = await pool.query(
        'SELECT id, email FROM users WHERE id = $1',
        [id],
    );
    return result.rows[0] ?? null;
}

For a single statement, use the pool's query helper. Borrow a client explicitly only when several statements must use the same connection, usually for a transaction.

export async function transfer(fromId, toId, cents) {
    const client = await pool.connect();

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

The finally block is non-negotiable. One missing release on an error path becomes a slow connection leak.

Use Three Different Timeouts

"Database timeout" usually hides three separate clocks:

acquisition timeout — how long to wait for a pool connection
connect timeout     — how long a new network connection may take
statement timeout   — how long the database may execute a query

An acquisition timeout protects the request from a saturated pool. A connect timeout protects startup and replacement connections. A statement timeout stops a query that is already running.

In PostgreSQL, set a statement limit at the role, transaction, or session level:

-- Default for this application role
ALTER ROLE app_user SET statement_timeout = '5s';

-- Tighter limit for one transaction
BEGIN;
SET LOCAL statement_timeout = '750ms';
SELECT ...;
COMMIT;

Without a statement timeout, an HTTP request can give up while its abandoned query keeps consuming database resources.

The Metrics That Matter

Export at least these pool gauges and histograms:

pool_total_connections
pool_idle_connections
pool_in_use_connections
pool_waiting_requests
pool_acquire_duration_seconds
query_duration_seconds
query_timeout_total

Interpret them together:

  • waiting_requests > 0 with high database CPU means the database is saturated. Making the pool larger will usually hurt.
  • Long acquisition time with many idle connections suggests a leak, a pool bug, or a metric scoped to the wrong process.
  • A full pool with low database load often means slow application work is holding connections between queries.
  • Frequent connection creation points to an idle timeout that is too aggressive or unstable networking.

Do Not Hold Connections While Doing Other Work

// Bad: the connection sits idle during an external API call.
const client = await pool.connect();
try {
    const user = await client.query('SELECT ...');
    const score = await slowExternalApi(user.rows[0]);
    await client.query('UPDATE users SET score = $1 ...', [score]);
} finally {
    client.release();
}

Fetch data, release the connection, call the external service, then start a short transaction for the update. If correctness requires a lock across the network call, redesign the workflow; a long database transaction is a fragile distributed lock.

Serverless Needs Special Care

Serverless platforms can create hundreds of runtimes in seconds. A pool inside each runtime does not become one shared pool.

Options include:

  • Put a connection proxy such as PgBouncer between functions and PostgreSQL
  • Use a provider's database proxy or HTTP-based database API
  • Cap function concurrency to match the connection budget
  • Keep per-runtime pools deliberately small

Autoscaling the application cannot autoscale a primary database connection table at the same speed.

The Bottom Line

A pool is a concurrency limit with connection reuse attached.

The rules:

  • Calculate the total across all processes and maximum replicas
  • Keep the pool bounded; bigger is not automatically faster
  • Set acquisition, connection, and statement timeouts separately
  • Release borrowed clients in finally
  • Never wait on network calls while holding a database connection
  • Alert on wait time and queue depth, not just connection count
  • Use a proxy when serverless fan-out exceeds the database budget

The best pool is usually smaller than the one developers first configure. Protect the database, measure the queue, and increase concurrency only when the measurements justify it.