Why Your Connection Pool is Lying to You

We hit a wall last month. Traffic was stable, CPU was idle, but p99 latency spiked to 2s. The PostgreSQL server looked healthy. The culprit wasn't the database; it was our Node.js connection pool configuration.

The Illusion of Saturation

Most teams set pool.max based on a static assumption: "We have 20 connections, so we can handle 20 concurrent requests." This fails because connections aren't just resources; they are locks. When you hold a connection for 50ms, you block 19 others. When you hold it for 500ms due to a slow downstream call or a complex join, you starve the queue.

The standard pg pool in Node behaves like a fixed-size barrier. If all max connections are in use, new queries wait in a FIFO queue. There is no fallback. There is no read replica offload. Just waiting.

The Bottleneck

Our logs showed queryQueueLength climbing steadily. We weren't seeing duplicate queries, but we were seeing long-held connections. Specifically, we had a service that called an external API inside a transaction. The transaction had idleInTransactionSessionTimeout set to 60s. The API was timing out at 5s, but the connection stayed open until the timeout or manual commit.

// The mistake: holding a connection during external I/O
const client = await pool.connect();
try {
  await client.query('BEGIN');
  const response = await fetch('https://external-api.com/status'); // Blocks for 1-5s
  await client.query('UPDATE users SET last_synced = NOW() WHERE id = $1', [userId]);
  await client.query('COMMIT');
} finally {
  client.release(); // Too late, the queue was already backed up
}

The fix wasn't scaling out the postgres instance. It was architectural separation.

The Tradeoff

We split the lifecycle. We run the external API call outside the database transaction. Yes, this introduces a consistency window. If the API fails after we commit the intent, we have stale data. But in our case, eventual consistency was acceptable for sync status.

// Better: Short-lived transaction, external I/O separated
const updateResult = await pool.query(
  'BEGIN; UPDATE sync_state SET status = $1 WHERE id = $2; COMMIT', 
  ['pending', userId]
);

let finalStatus = 'failed';
try {
  const response = await fetch('https://external-api.com/status');
  finalStatus = response.ok ? 'success' : 'error';
} catch (e) {
  finalStatus = 'timeout';
}

await pool.query(
  'BEGIN; UPDATE sync_state SET status = $1 WHERE id = $2; COMMIT', 
  [finalStatus, userId]
);

The p99 dropped to 80ms. The queue depth flattened.

Edge Cases to Watch

1. Nested Transactions: If your ORM supports savepoints, be careful. SAVEPOINT doesn't release the underlying connection, but it adds overhead. Avoid them in tight loops.
2. Statement Caching: In Postgres 13+, prepared statements are cached per connection. If you have high-churn connection recycling, you might miss the cache benefits. node-postgres handles this, but custom drivers might not.
3. Monitoring: Don't just watch active_connections. Watch backend_xact_start age. Long-running transactions are the real killers.

Connection pooling is not a silver bullet; it's a buffer against mismanagement. If your code holds DB connections while talking to other services, you're not pooling, you're hoarding.

How do you handle external I/O dependencies that logically require a DB transaction context? Do you use saga patterns, or do you rely on idempotency and eventual consistency?