Validate Pooled Database Connections

Combine keepalives, idle timeouts, lifetime limits, and a checkout probe so dead sockets are replaced instead of failing user requests.

~25 minpostgresqlnodejsreliability

Task

Detect stale or half-open PostgreSQL connections in the pool and replace them before a request has to fail.

When to use this

A firewall drops idle NAT mappings after ten minutes. The pool keeps the dead sockets, the next request hangs for thirty seconds, and the retry lands on the same broken connection.

Prerequisites

Node.js 22 with pg 8.11 or newer
PostgreSQL 13 or newer
Permission to terminate your own backends for the verification step

Steps

1. Bound idle time, acquisition time, and total client lifetime in the pool configuration.

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

Expected output: Idle clients close after 30 seconds, acquisition fails fast after 2 seconds, and every client is recycled within 30 minutes.

2. Enable TCP keepalives so half-open sockets are detected without waiting for a query to time out.

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  keepAlive: true,
  keepAliveInitialDelayMillis: 10_000,
});

Expected output: The operating system probes an idle connection after 10 seconds and closes it when the peer is gone.

3. Probe a checked-out client and destroy it on failure instead of returning it to the pool.

async function checkedConnect(pool) {
  const client = await pool.connect();
  try {
    await client.query('SELECT 1');
    return client;
  } catch (err) {
    client.release(err); // destroys the client, does not return it
    return pool.connect();
  }
}

Expected output: A stale client is removed from the pool and one fresh connection serves the request.

4. Kill an idle backend and confirm the next request still succeeds.

psql -d appdb -c "SELECT pg_terminate_backend(pid) FROM pg_stat_activity WHERE application_name = 'orders-api' AND state = 'idle' LIMIT 1;" && curl -s -o /dev/null -w '%{http_code}\n' http://localhost:3000/api/orders

Expected output: 200, with a validation failure counter incremented once and a new backend visible in pg_stat_activity.

Troubleshooting

Problem

Every request pays an extra round trip for SELECT 1

Solution

Probe only when a client has been idle beyond a threshold, or rely on keepalive plus idleTimeoutMillis and keep the probe for critical paths.

Problem

Requests fail with Client has encountered a connection error and is not queryable

Solution

The socket died mid-query. Retry only idempotent statements, and make sure failed clients are destroyed rather than released back into the pool.

Problem

The pool shrinks to zero after a network blip

Solution

Validation must call release(err) on failure. Releasing without an error returns the dead socket and the pool keeps handing it out.

Limitations

Keepalive timing depends on the OS and intermediate firewalls; verify on the real network path, not only locally.
A connection can die after checkout and before commit; idempotent writes and bounded retries are still required.
Lifetime recycling rotates connections gradually, so it does not immediately fix a pool full of dead sockets.

Idle connections die without telling you

NAT gateways, firewalls, and load balancers expire idle flows silently. No FIN reaches either side, so the pool still believes the socket is usable. The next query is written into a black hole, and TCP retransmission can take minutes before the driver reports an error. From the user’s perspective the API simply hangs, and a naive retry picks the same dead client.

Layer four defenses

Each mechanism covers a different failure mode. TCP keepalive detects a vanished peer at the OS level within roughly the keepalive interval. idleTimeoutMillis closes clients that are not being used, which keeps the pool small and reduces the chance of holding a stale socket. maxLifetimeSeconds rotates connections so DNS changes, credential rotations, and slow leaks resolve on their own. A checkout probe (SELECT 1) is the last resort when the other three cannot be trusted, at the cost of one round trip.

const pool = new pg.Pool({
  connectionString: process.env.DATABASE_URL,
  keepAlive: true,
  keepAliveInitialDelayMillis: 10_000,
  idleTimeoutMillis: 30_000,
  maxLifetimeSeconds: 1_800,
});

Destroy, do not return

The subtle bug in most probes is releasing a failed client normally. client.release() puts it back into the idle list, so the next request fails the same way. client.release(err) marks the client as broken and removes it, which is the behaviour you want in the catch block.

Make it observable

Count validation failures, pool errors, and waiting clients. A burst of validation failures right after a network event is expected and healthy; a steady trickle means connections are timing out routinely and the keepalive or idle settings need tuning. Alert on waitingCount > 0 for more than a few seconds, since that indicates saturation rather than staleness.

Test against the real failure

Terminating a backend from pg_stat_activity simulates the stale socket without needing a firewall. Kill an idle backend, then issue a request and assert it succeeds. If your test environment runs PostgreSQL in Docker, also test a paused container: pausing preserves the socket, which reproduces the half-open case that a simple restart does not.