Database connection pooling

import { Pool, PoolConfig } from 'pg';

const poolConfig: PoolConfig = {
  host: process.env.DB_HOST || 'localhost',
  port: parseInt(process.env.DB_PORT || '5432', 10),
  database: process.env.DB_NAME || 'app_db',
  user: process.env.DB_USER || 'app_user',
  password: process.env.DB_PASSWORD || 'secret',
  
  // Core pool boundaries
  max: 80,                    // Must be < DB max_connections * 0.8
  min: 10,                    // Pre-warm connections to avoid cold-start latency
  idleTimeoutMillis: 30000,   // Release idle connections after 30s
  connectionTimeoutMillis: 5000, // Fail fast if pool is exhausted
  statement_timeout: 10000,   // Query-level timeout fallback
  query_timeout: 10000,
  
  // Connection validation
  keepAlive: true,
  keepAliveInitialDelayMillis: 10000,
};

export const dbPool = new Pool(poolConfig);

// Attach error listener to prevent unhandled pool crashes
dbPool.on('error', (err, client) => {
  console.error('Unexpected pool error:', err);
  // Client is automatically removed from pool by pg
});

import { PoolClient, QueryResult } from 'pg';

// Pattern A: Single query (recommended for 90% of use cases)
export async function executeQuery<T>(text: string, params?: any[]): Promise<QueryResult<T>> {
  return dbPool.query<T>(text, params);
}

// Pattern B: Transactional workflow with guaranteed release
export async function withTransaction<T>(
  callback: (client: PoolClient) => Promise<T>
): Promise<T> {
  const client = await dbPool.connect();
  try {
    await client.query('BEGIN');
    const result = await callback(client);
    await client.q

uery('COMMIT'); return result; } catch (err) { await client.query('ROLLBACK'); throw err; } finally { client.release(); // Critical: returns client to pool regardless of outcome } }

### Step 3: Add Health Validation and Idle Pruning

Pools accumulate stale connections when network partitions, firewall rules, or database restarts occur silently. Validate connections before use and prune idle ones proactively.

```typescript
// Periodic health check (run via cron or background worker)
export async function validatePoolHealth(): Promise<void> {
  const client = await dbPool.connect();
  try {
    await client.query('SELECT 1');
  } catch {
    console.warn('Pool health check failed: draining stale connections');
    await dbPool.end();
    // Re-initialize pool or trigger alerting
  } finally {
    client.release();
  }
}

Step 4: Graceful Shutdown

Abrupt process termination leaves connections in TIME_WAIT or half-closed states. Implement drain-on-signal to flush in-flight queries before releasing resources.

async function gracefulShutdown(signal: string): Promise<void> {
  console.log(`Received ${signal}. Draining pool...`);
  try {
    await dbPool.end();
    console.log('Pool drained successfully');
    process.exit(0);
  } catch (err) {
    console.error('Pool drain failed:', err);
    process.exit(1);
  }
}

process.on('SIGTERM', () => gracefulShutdown('SIGTERM'));
process.on('SIGINT', () => gracefulShutdown('SIGINT'));

Architecture Decisions and Rationale

• max capped at 80% of DB limit: Leaves headroom for administrative connections, replication, and background maintenance tasks. Exceeding this threshold causes connection queueing and FATAL: too many connections errors.
• min pre-warming: Eliminates cold-start latency during traffic spikes. The cost of maintaining 10 idle connections is negligible compared to the latency penalty of creating 50 connections simultaneously under load.
• connectionTimeoutMillis over thread starvation: Fails fast when the pool is exhausted. Without this, application threads block indefinitely, causing cascading timeouts across the entire service mesh.
• Explicit finally blocks: Guarantees client release even when callbacks throw. JavaScript's garbage collector does not track native connection sockets; leaked clients permanently reduce pool capacity.
• Statement-level timeouts: Pools manage connections, not query execution. statement_timeout prevents long-running queries from holding connections hostage, which would otherwise exhaust the pool.

Pitfall Guide

1. Connection Leaks from Missing release()

Mistake: Acquiring a client via pool.connect() and forgetting to call client.release() in error paths or early returns. Impact: Pool capacity shrinks monotonically until max is reached, causing all subsequent requests to timeout. Best Practice: Always wrap connect() in try/finally. Use pool.query() for single statements to avoid manual lifecycle management entirely.

2. Ignoring idleTimeoutMillis

Mistake: Leaving idle connections open indefinitely. Impact: Database memory bloat, increased TIME_WAIT states, and stale connection errors when firewalls or load balancers drop silent TCP streams. Best Practice: Set idleTimeoutMillis between 20,000–60,000ms. Align with your infrastructure's TCP keepalive and idle timeout policies.

3. Hardcoding max Without Database Awareness

Mistake: Setting max: 100 when PostgreSQL's max_connections is 100. Impact: Connection queueing, authentication failures, and database OOM kills when background processes (autovacuum, replication) consume the remaining slots. Best Practice: Query SHOW max_connections or check cloud provider limits. Set pool max to floor(DB_MAX * 0.75). Document this ratio in infrastructure runbooks.

4. Skipping Connection Validation

Mistake: Assuming pooled connections remain valid after network blips or database restarts. Impact: ECONNRESET, SSL connection has been closed, or server closed the connection unexpectedly errors during peak traffic. Best Practice: Enable keepAlive and run periodic SELECT 1 health checks. Use testOnBorrow equivalents if your driver supports them.

5. Poor Error Handling Masking Pool State

Mistake: Catching database errors and returning generic messages without logging pool metrics. Impact: Silent pool exhaustion, inability to distinguish between query failures and connection failures, delayed incident response. Best Practice: Emit structured logs on pool.on('error'), track pool.totalCount, pool.idleCount, and pool.waitingCount. Alert when waitingCount > 0 for >5 seconds.

6. Serverless Incompatibility

Mistake: Using traditional connection pools in AWS Lambda, Cloud Functions, or Cloudflare Workers. Impact: Cold starts create new pools per invocation. Concurrent executions quickly exhaust database connections. Pools cannot survive container reuse boundaries reliably. Best Practice: Use serverless-native proxies (RDS Proxy, Supavisor, PgBouncer with transaction mode) or connection-per-request with aggressive timeouts. Never maintain long-lived pools in ephemeral runtimes.

Production Bundle

Action Checklist

• Audit database max_connections and set pool max to 70–80% of limit
• Configure idleTimeoutMillis between 20–60s to prune stale connections
• Implement try/finally blocks for all pool.connect() calls
• Add pool.on('error') listener with structured logging and alerting
• Set connectionTimeoutMillis to 3–5s to prevent thread starvation
• Run periodic SELECT 1 health checks or enable driver keepalive
• Test graceful shutdown with SIGTERM/SIGINT handlers
• Monitor waitingCount and alert when connections queue for >5s

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Low traffic (<50 req/s)	Static pool (max=20, min=5)	Predictable resource usage, minimal overhead	Low (fixed connection count)
High concurrency (1k+ req/s)	Dynamic pool + PgBouncer	Offloads connection multiplexing, reduces app memory	Medium (proxy infrastructure)
Read-heavy workloads	Read replica pool + separate writer pool	Isolates read/write contention, scales reads independently	High (additional replica)
Write-heavy/transactional	Strict max + statement timeouts	Prevents long transactions from starving pool	Low (configuration only)
Serverless/ephemeral	RDS Proxy or connection-per-request	Avoids pool lifecycle mismatch with container reuse	Medium (proxy or connection overhead)

Configuration Template

// db/pool.ts
import { Pool, PoolConfig } from 'pg';

export function createPool(env: NodeJS.ProcessEnv): Pool {
  const config: PoolConfig = {
    host: env.DB_HOST,
    port: Number(env.DB_PORT) || 5432,
    database: env.DB_NAME,
    user: env.DB_USER,
    password: env.DB_PASSWORD,
    ssl: env.DB_SSL === 'true' ? { rejectUnauthorized: false } : false,
    
    // Production tuning
    max: Number(env.DB_POOL_MAX) || 60,
    min: Number(env.DB_POOL_MIN) || 10,
    idleTimeoutMillis: Number(env.DB_IDLE_TIMEOUT) || 30000,
    connectionTimeoutMillis: Number(env.DB_CONN_TIMEOUT) || 5000,
    statement_timeout: Number(env.DB_STMT_TIMEOUT) || 10000,
    query_timeout: Number(env.DB_QUERY_TIMEOUT) || 10000,
    
    // Network resilience
    keepAlive: true,
    keepAliveInitialDelayMillis: 10000,
  };

  const pool = new Pool(config);

  pool.on('error', (err, client) => {
    console.error({
      event: 'pool_error',
      message: err.message,
      code: err.code,
      client_pid: client?.pid,
      pool_stats: {
        total: pool.totalCount,
        idle: pool.idleCount,
        waiting: pool.waitingCount,
      },
    });
  });

  return pool;
}

// Usage:
// import { createPool } from './db/pool';
// export const db = createPool(process.env);

Quick Start Guide

1. Install driver and types: npm install pg @types/pg
2. Create pool instance: Copy the configuration template into src/db/pool.ts. Set environment variables matching your database credentials and limits.
3. Replace raw queries: Swap client.query() or ORM connection calls with dbPool.query() or withTransaction() wrapper. Ensure all pool.connect() calls use try/finally with client.release().
4. Add shutdown handler: Register SIGTERM/SIGINT listeners that call await dbPool.end() before process exit.
5. Verify: Run node -e "require('./src/db/pool').dbPool.connect().then(c => { console.log(c.query('SELECT 1'); c.release(); process.exit(0); })" to confirm pool initialization and connection release. Monitor pool.waitingCount during load testing to validate sizing.