Designing a Database Connection Pool: Connection Lifecycle, Pool Sizing, and Failover Strategies at Scale

Most engineers treat the connection pool as a configuration detail: set max to something reasonable, move on. That works until it does not. Pool exhaustion during a traffic spike, stale connections after a database failover, or a slow connection leak that only shows up after 48 hours in production are all pool design failures, not application bugs.

This article covers how connection pools actually work, how to size them correctly, what goes wrong in production, and how to design failover behavior that does not make the problem worse.

How a Connection Pool Works

A TCP connection to PostgreSQL is expensive. The database forks a process per connection (or uses worker threads in PgBouncer), performs TLS handshake if configured, and exchanges authentication packets. This takes 10-50ms for a local connection and 80-200ms over a VPC. For a service handling 500 req/s, paying that cost on every request is not viable.

A pool pre-creates connections and lends them to application code. The lifecycle has five distinct states.

Connection Lifecycle

Create: the pool opens a new TCP connection, authenticates, and optionally runs a setup query (SET search_path, SET application_name, etc.). This is the slow path.

Validate: before lending an idle connection, the pool checks if it is still alive. A lightweight approach is sending a SELECT 1 query. A cheaper approach is checking if the socket has received an EOF or RST (passive health check). Many pools support both.

Borrow: the caller receives a handle. From the pool’s perspective, the connection is now checked out. If no idle connection exists and the pool is below max, a new one is created. If the pool is at max, the caller waits in a queue up to a configured timeout.

Return: the caller releases the handle. The pool runs any teardown (rolling back uncommitted transactions, clearing session-level state) and moves the connection back to idle.

Evict: idle connections older than idleTimeoutMillis are closed. Connections that fail validation are also evicted. This prevents the pool from holding stale connections indefinitely.

Here is a typed wrapper around pg-pool that makes these states observable:

import { Pool, PoolClient, PoolConfig } from 'pg';

interface PoolMetrics {
  total: number;
  idle: number;
  waiting: number;
}

interface ManagedPool {
  query<T>(text: string, values?: unknown[]): Promise<T[]>;
  withClient<T>(fn: (client: PoolClient) => Promise<T>): Promise<T>;
  metrics(): PoolMetrics;
  end(): Promise<void>;
}

function createPool(config: PoolConfig): ManagedPool {
  const pool = new Pool({
    ...config,
    // Log connection creation and teardown events
    allowExitOnIdle: false,
  });

  pool.on('connect', (client) => {
    // Run session-level setup on every new physical connection
    client.query("SET application_name = 'myservice'").catch(() => {
      // If this fails, the connection will be evicted on next validation
    });
  });

  pool.on('error', (err, client) => {
    // An idle client emitted an error (e.g., server reset the connection)
    // pg-pool will remove it from the pool automatically
    console.error('Pool idle client error', { err: err.message });
  });

  pool.on('remove', () => {
    // A connection was closed and removed from the pool
  });

  return {
    async query<T>(text: string, values?: unknown[]): Promise<T[]> {
      const res = await pool.query<T>(text, values);
      return res.rows;
    },

    async withClient<T>(fn: (client: PoolClient) => Promise<T>): Promise<T> {
      const client = await pool.connect();
      try {
        return await fn(client);
      } finally {
        // Always release, even on throw
        client.release();
      }
    },

    metrics(): PoolMetrics {
      return {
        total: pool.totalCount,
        idle: pool.idleCount,
        waiting: pool.waitingCount,
      };
    },

    async end(): Promise<void> {
      await pool.end();
    },
  };
}

The withClient pattern with finally is important. If the application code throws before calling client.release(), the connection stays checked out. After enough of these, the pool hits max and all new requests queue indefinitely.

Pool Sizing

The formula most teams reach for is: pool_size = num_cores * 2 + effective_spindle_count. This comes from HikariCP’s research on PostgreSQL query concurrency. For solid-state storage and a typical OLTP workload, this is a reasonable starting point. For a 4-core database host, that suggests a pool of 9-10 connections per application instance.

The intuition: a database query is not purely CPU-bound. It waits on I/O, lock grants, and network round-trips. A single core can usefully service roughly two concurrent queries. More than that, and queries start competing for CPU, causing latency to increase without improving throughput.

A more principled sizing model uses Little’s Law:

L = λ × W

Where L is the number of requests in the system (connections in use), λ is the arrival rate (queries per second), and W is the average service time (query latency). For a service handling 200 queries/s with an average latency of 20ms:

L = 200 * 0.020 = 4 connections

Double that for headroom: max = 8. For a service with 4 application instances, each instance needs a pool of 2. Most engineers size this far larger than necessary, which wastes database server resources (each idle connection consumes shared_buffers allocation and a backend process).

The relationship between pool size and throughput is not linear. At low concurrency, adding connections increases throughput. Past a breakeven point, adding connections increases contention and latency. The breakeven is database-specific but is usually around the number of cores on the database host.

function recommendedPoolSize(params: {
  queriesPerSecond: number;
  avgQueryMs: number;
  appInstances: number;
  headroomMultiplier?: number;
}): number {
  const { queriesPerSecond, avgQueryMs, appInstances, headroomMultiplier = 2 } = params;

  // Little's Law: L = lambda * W
  const connectionsInFlight = queriesPerSecond * (avgQueryMs / 1000);
  const totalPoolSize = Math.ceil(connectionsInFlight * headroomMultiplier);
  const perInstanceSize = Math.ceil(totalPoolSize / appInstances);

  // Minimum of 2 per instance to handle burst
  return Math.max(perInstanceSize, 2);
}

This is a model, not a law. Measure actual pool.waitingCount and pool.idleCount under production load. If waiting is consistently above zero, the pool is undersized. If idle is consistently 80%+ of total, it is oversized.

Common Failure Modes

Connection Leaks

A connection leak happens when code borrows a connection and never returns it. The pool does not reclaim checked-out connections automatically. The failure mode is gradual: waiting rises slowly over hours or days, then suddenly all requests start timing out.

Detection requires tracking how long each connection has been checked out:

interface CheckoutRecord {
  checkedOutAt: number;
  stack: string;
}

function createLeakDetectingPool(config: PoolConfig, leakThresholdMs = 5000): ManagedPool {
  const pool = new Pool(config);
  const checkouts = new Map<PoolClient, CheckoutRecord>();

  const originalConnect = pool.connect.bind(pool);

  const connect = async (): Promise<PoolClient> => {
    const client = await originalConnect();
    const record: CheckoutRecord = {
      checkedOutAt: Date.now(),
      stack: new Error().stack ?? '',
    };
    checkouts.set(client, record);

    const originalRelease = client.release.bind(client);
    client.release = (err?: Error | boolean) => {
      checkouts.delete(client);
      return originalRelease(err as Error);
    };

    return client;
  };

  // Periodic scan for long-held connections
  const leakScanner = setInterval(() => {
    const now = Date.now();
    for (const [, record] of checkouts) {
      const heldMs = now - record.checkedOutAt;
      if (heldMs > leakThresholdMs) {
        console.warn('Possible connection leak detected', {
          heldMs,
          stack: record.stack,
        });
      }
    }
  }, leakThresholdMs);

  leakScanner.unref(); // Do not prevent process exit

  return {
    async withClient<T>(fn: (client: PoolClient) => Promise<T>): Promise<T> {
      const client = await connect();
      try {
        return await fn(client);
      } finally {
        client.release();
      }
    },
    // ... other methods
  } as ManagedPool;
}

The stack capture is expensive but invaluable during debugging. Disable it in production with a flag; enable it when investigating a leak.

Stale Connections

The database server can close a connection without the client knowing. This happens after a server restart, a network device timing out idle connections, or a database maintenance window. The pool is holding a socket that is no longer connected on the other end.

The next query on that connection fails with a socket error. If the pool does not handle this transparently, the error propagates to the caller.

pg-pool handles this by default: when a query fails with a connection error, it marks the client as errored and the error propagates to the caller. The client is removed from the pool. The next request creates a new connection.

The configuration option connectionTimeoutMillis controls how long a connect() call waits for an idle connection. Setting this is non-optional: without it, requests can wait indefinitely.

const pool = new Pool({
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  max: 10,
  idleTimeoutMillis: 30_000,       // Evict idle connections after 30s
  connectionTimeoutMillis: 3_000,  // Fail fast if no connection available in 3s
  // keepAlive sends TCP keepalive probes to detect dead connections
  keepAlive: true,
  keepAliveInitialDelayMillis: 10_000,
});

TCP keepalive probes (keepAlive: true) detect dead connections before the next query attempt. The OS sends probes on the idle socket and will surface an error if the server does not respond. This is faster than waiting for the next query to fail.

Thundering Herd on Reconnect

After a database restart or brief outage, all connections in the pool are dead simultaneously. The moment the database comes back online, all application instances try to reconnect at once. For a service with 20 instances and a pool of 10 each, that is 200 simultaneous connection attempts. PostgreSQL’s default max_connections is 100. This can cause the reconnect to fail, making the outage look longer than it was.

The mitigation is jittered backoff when creating new connections after a failure:

async function connectWithBackoff(
  pool: Pool,
  maxAttempts = 5,
  baseDelayMs = 200
): Promise<PoolClient> {
  for (let attempt = 0; attempt < maxAttempts; attempt++) {
    try {
      return await pool.connect();
    } catch (err) {
      if (attempt === maxAttempts - 1) throw err;

      // Full jitter: random delay between 0 and cap
      const cap = baseDelayMs * Math.pow(2, attempt);
      const delay = Math.floor(Math.random() * cap);

      await new Promise((resolve) => setTimeout(resolve, delay));
    }
  }
  throw new Error('Unreachable');
}

A related issue: when the pool is exhausted and requests are queuing, a short blip in database latency can cascade. Queries take longer, connections stay checked out longer, the queue grows, and connectionTimeoutMillis starts triggering. The fix is not a larger pool; it is shorter query timeouts and circuit breaking at the application layer.

Failover Strategies for Multi-Region Setups

In a multi-region deployment, you have a primary and one or more read replicas. Failover scenarios:

1. Primary goes down, a replica is promoted
2. Network partition between application and primary
3. Planned maintenance requiring a primary switch

In all three, the pool is holding connections to the old primary. Those connections need to be closed and new ones opened to the new primary.

Host Resolution via DNS

Most managed databases (RDS, Cloud SQL, PlanetScale) update a DNS entry to point to the new primary after a failover. The pool needs to re-resolve DNS for this to work. TCP connections are bound to an IP address, not a hostname, so existing connections continue pointing to the old primary until they are evicted.

interface PoolWithFailover extends ManagedPool {
  reconnect(): Promise<void>;
}

function createFailoverPool(
  getConfig: () => Promise<PoolConfig>
): PoolWithFailover {
  let pool: Pool;
  let currentConfig: PoolConfig;

  async function initialize(): Promise<void> {
    currentConfig = await getConfig();
    pool = new Pool(currentConfig);
  }

  async function reconnect(): Promise<void> {
    // Drain the old pool: reject new requests, wait for in-flight to finish
    const oldPool = pool;
    const newConfig = await getConfig();
    pool = new Pool(newConfig);
    currentConfig = newConfig;

    // End the old pool after a grace period
    setTimeout(() => {
      oldPool.end().catch(() => {});
    }, 5_000);
  }

  return {
    async query<T>(text: string, values?: unknown[]): Promise<T[]> {
      const res = await pool.query<T>(text, values);
      return res.rows;
    },

    async withClient<T>(fn: (client: PoolClient) => Promise<T>): Promise<T> {
      const client = await pool.connect();
      try {
        return await fn(client);
      } finally {
        client.release();
      }
    },

    metrics(): PoolMetrics {
      return {
        total: pool.totalCount,
        idle: pool.idleCount,
        waiting: pool.waitingCount,
      };
    },

    reconnect,
    end: async () => pool.end(),
  };
}

The reconnect() method is triggered by an external event: a health check failure, a received SIGTERM, or a control plane notification from the managed database service.

Read Replica Routing

A separate pool per replica allows routing read-heavy queries away from the primary. The tricky part is replication lag: a query on a replica may see stale data. Applications that cannot tolerate any lag (reading immediately after a write) must route to primary.

interface PoolRouter {
  primary(): ManagedPool;
  replica(lagToleranceMs?: number): ManagedPool;
}

async function getReplicaLagMs(replicaPool: ManagedPool): Promise<number> {
  const rows = await replicaPool.query<{ lag_ms: string }>(
    `SELECT EXTRACT(EPOCH FROM (now() - pg_last_xact_replay_timestamp())) * 1000 AS lag_ms`
  );
  return parseFloat(rows[0]?.lag_ms ?? '0');
}

function createPoolRouter(
  primaryConfig: PoolConfig,
  replicaConfigs: PoolConfig[]
): PoolRouter {
  const primaryPool = createPool(primaryConfig);
  const replicaPools = replicaConfigs.map(createPool);

  return {
    primary: () => primaryPool,

    replica: (lagToleranceMs = 2_000) => {
      // Return a lazy pool that routes to primary if lag is too high
      return {
        async query<T>(text: string, values?: unknown[]): Promise<T[]> {
          // Pick a replica round-robin (simplified)
          const candidate = replicaPools[Math.floor(Math.random() * replicaPools.length)];

          const lag = await getReplicaLagMs(candidate);
          if (lag > lagToleranceMs) {
            // Replica is behind; fall back to primary for this query
            return primaryPool.query<T>(text, values);
          }

          return candidate.query<T>(text, values);
        },
        withClient: primaryPool.withClient.bind(primaryPool), // Simplified
        metrics: primaryPool.metrics.bind(primaryPool),
        end: primaryPool.end.bind(primaryPool),
      };
    },
  };
}

Checking replica lag per query adds latency. A better approach is a background job that polls lag every few seconds and caches the result, routing to primary only if the cached lag exceeds the threshold.

Tradeoffs

Strategy	Throughput	Failure isolation	Complexity	When to use
Single pool, primary only	Baseline	None: writes and reads compete	Low	Early stage, <500 req/s
Separate read replica pool	Higher read throughput	Replica failures do not affect writes	Medium	Read-heavy workloads with acceptable lag
PgBouncer in front of pool	Handles >100x connections	PgBouncer becomes a SPOF without HA setup	Medium	>100 application instances
Multi-region with DNS failover	Resilient to AZ failure	30-60s DNS TTL means short outage	High	Services with uptime SLAs
Pooler per region with replication	Near-zero regional failover	Split-brain risk if replication lags	Very high	Latency-sensitive global services

PgBouncer deserves a separate mention. If you have more than 50 application instances, each with a pool of 10, that is 500 potential connections to PostgreSQL. PostgreSQL’s default max_connections is 100, and increasing it has memory costs (each connection holds 5-10MB of shared_buffers overhead). PgBouncer multiplexes thousands of application connections onto a small number of server connections in transaction mode, where a server connection is only held for the duration of a transaction.

The tradeoff: in transaction pooling mode, session-level state (SET, prepared statements, advisory locks) does not survive across queries from the same application connection. Code that relies on session state breaks silently.

Production War Stories

A service with 12 application instances, each with max: 20, started seeing intermittent 503s after a deployment. The pool exhaustion metrics were clean. The issue: the new deployment used a long-running background job that held a transaction open for 15-30 seconds while processing messages. During that time, 20 connections per instance were tied up. Traffic spikes briefly exceeded the available connections, and connectionTimeoutMillis: 2000 started triggering.

The fix was two changes: move the background job to a separate process with its own isolated pool, and add statement_timeout to the connection setup to prevent any single query from holding a connection indefinitely.

pool.on('connect', async (client) => {
  await client.query("SET statement_timeout = '5s'");
  await client.query("SET idle_in_transaction_session_timeout = '10s'");
});

idle_in_transaction_session_timeout is the more important one. It closes sessions that have opened a transaction but gone idle, which is almost always a leaked transaction from an uncaught exception that did not roll back.

Another failure mode: after a PostgreSQL minor version upgrade with a brief restart, 8 of 12 instances reconnected successfully. 4 instances had connections stuck in a connecting state because the pool exhausted its retry window during the 15-second database restart. Those instances needed a process restart to recover, because pg-pool does not have a built-in mechanism to discard a fully failed pool and reinitialize. The reconnect() pattern above was added specifically to handle this.

Observability Checklist

What to expose as metrics:

• pool.totalCount: total connections (idle + active)
• pool.idleCount: connections available for checkout
• pool.waitingCount: requests waiting for a connection
• Checkout duration: time between pool.connect() returning and client.release() being called
• Connection age: time since the physical TCP connection was opened

Alert when waitingCount > 0 for more than 30 seconds. Alert when totalCount == max and waitingCount > 0 simultaneously. Both conditions together mean the pool is saturated and requests are degrading.

---

The pool is not a black box. It has a lifecycle, sizing constraints grounded in queueing theory, and failure modes that show up reliably under load. Understanding the mechanics is what separates a configuration guess from an informed decision.