Connection Pooling

Status: Complete
Category: Performance
Default enforcement: Soft
Author: PushBackLog team

Tags

• Topic: performance, database
• Skillset: backend, devops
• Technology: generic
• Stage: execution, review

Summary

A connection pool maintains a set of open database (or HTTP) connections that are reused by multiple requests rather than opened and closed for each individual operation. Establishing a database connection involves TCP handshake, authentication, and session setup — costs that are significant at scale. Without pooling, applications under load exhaust connection limits, experience high latency from connection overhead, and put unnecessary pressure on the database. Pooling is not an optimisation — it is a requirement for any non-trivial application.

Rationale

Connection establishment is expensive

Opening a new database connection involves a TCP three-way handshake, TLS negotiation, database authentication, session initialisation, and sending connection parameters. This process takes 10–100ms. For an application handling 10,000 requests per second, establishing a new connection per request would consume all its capacity on connection overhead before executing a single query.

Databases have connection limits

PostgreSQL has a hard connection limit (typically 100–200 for an RDS instance without PgBouncer). Each connection consumes server memory and a background worker process. Exceeding the limit causes new connection attempts to fail. Without a pool, a traffic spike creates new connections until the limit is exhausted, at which point the database begins rejecting connections from all clients.

Guidance

Database connection pooling

Application-level pooling (Node.js / pg)

import { Pool } from 'pg';

// Create one pool per application — shared across all requests
const pool = new Pool({
  host:     process.env.DB_HOST,
  database: process.env.DB_NAME,
  user:     process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  port:     5432,
  max:      20,              // Maximum connections in the pool
  min:      2,               // Keep at least 2 connections open
  idleTimeoutMillis: 30000,  // Close idle connections after 30s
  connectionTimeoutMillis: 5000, // Fail if a connection can't be acquired in 5s
});

// Usage — acquire a connection from the pool, run query, release automatically
const { rows } = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);

PgBouncer (database-level proxy pooler)

For high-concurrency applications, a connection pooler at the database proxy level (PgBouncer) multiplexes many application connections onto fewer database connections:

# pgbouncer.ini
[databases]
myapp = host=postgres user=myapp dbname=myapp

[pgbouncer]
pool_mode = transaction     # Connection returned to pool after each transaction
max_client_conn = 1000      # Application connections PgBouncer accepts
default_pool_size = 20      # Connections PgBouncer maintains to PostgreSQL
max_db_connections = 50     # Total PostgreSQL connections across all pools

transaction mode is appropriate for most applications — the connection is returned to the pool after each transaction, not held for the duration of the connection’s lifetime.

Pool sizing

Too small: requests queue for a connection; latency increases
Too large: database is overloaded; connection overhead degrades all queries

A starting formula:

pool_size = (cpu_cores × 2) + effective_spindle_count

For a production PostgreSQL on a 4-vCPU RDS instance, start around 10–20 connections per application pod, capped at the database’s total connection limit divided by the number of pods.

Max DB connections: 100
Number of app pods: 5
Pool size per pod: 100 / 5 = 20 (leave headroom for admin connections)

HTTP connection pooling

HTTP clients should also pool connections (HTTP keep-alive / connection reuse):

import https from 'https';
import axios from 'axios';

// Global axios instance with connection keep-alive
const httpClient = axios.create({
  httpsAgent: new https.Agent({
    keepAlive: true,
    maxSockets: 50,        // Max concurrent connections to a single host
    maxFreeSockets: 10,    // Free connections to keep for reuse
    timeout: 60000,
  }),
  timeout: 5000,
});

Without keepAlive, each HTTP request opens a new TCP connection — expensive under load.

Monitoring pool health

Key metrics to observe:

• Pool utilisation: what percentage of connections are currently in use
• Queue depth: how many requests are waiting for a connection
• Wait time: how long requests wait for a connection from the pool
• Connection errors: timeouts acquiring a connection from the pool

// Observe pool metrics (node-postgres)
pool.on('connect', () => logger.debug('New DB connection opened'));
pool.on('acquire', () => metrics.increment('db.pool.acquire'));
pool.on('remove', () => metrics.increment('db.pool.remove'));

// Periodic pool stats
setInterval(() => {
  logger.info({
    total: pool.totalCount,
    idle: pool.idleCount,
    waiting: pool.waitingCount,
  }, 'DB pool stats');
}, 60_000);

A consistently high waitingCount means the pool is undersized or the database is overloaded.

Review checklist

• Application uses a connection pool — no per-request connection creation
• Pool is a module-level singleton — not created per request or per function call
• Pool size is configured explicitly, not left to the default
• Connection acquisition timeout is set — requests don’t wait indefinitely
• Pool health metrics are instrumented and visible in dashboards
• PgBouncer or equivalent is used if multiple application pods would otherwise exhaust DB connections