Database connection pooling

ecycle management, metric instrumentation, and graceful degradation. The following implementation uses pg (node-postgres) in TypeScript, which provides a battle-tested pool implementation with built-in queuing, validation, and event hooks.

Step 1: Install Dependencies

npm install pg
npm install -D @types/pg

Step 2: Pool Initialization with Production Defaults

import { Pool, PoolConfig } from 'pg';

export function createDatabasePool(config?: Partial<PoolConfig>): Pool {
  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 || '',
    // Production sizing formula: min = CPU cores * 2, max = min + (RTT_ms * target_rps)
    min: 4,
    max: 20,
    // Connection lifecycle
    idleTimeoutMillis: 30_000,      // Drop idle connections after 30s
    connectionTimeoutMillis: 2_000, // Fail fast if pool is exhausted
    maxLifetimeMillis: 600_000,     // Recycle connections after 10m to prevent stale state
    // Validation
    keepAlive: true,
    keepAliveInitialDelayMillis: 10_000,
    ...config,
  };

  const pool = new Pool(poolConfig);

  // Instrumentation hooks
  pool.on('connect', () => {
    // Emit metric: pool.connection.created
  });

  pool.on('remove', () => {
    // Emit metric: pool.connection.destroyed
  });

  pool.on('error', (err, client) => {
    // Critical: log and alert on pool-level errors
    console.error('Pool error:', err.message);
  });

  return pool;
}

Step 3: Query Execution with Explicit Release

import { Pool, QueryResult } from 'pg';

export async function executeQuery<T = any>(
  pool: Pool,
  text: string,
  values?: any[]
): Promise<QueryResult<T>> {
  const client = await pool.connect();
  try {
    const result = await client.query<T>(text, values);
    return result;
  } finally {
    // Mandatory: release back to pool even on error
    client.release();
  }
}

Step 4: Graceful Shutdown

Application termination must drain active queries before destroying the pool.

export async function shutdownPool(pool: Pool): Promise<void> {
  console.log('Draining connection pool...');
  try {
    await pool.end();
    console.log('Pool drained successfully.');
  } catch (err) {
    console.error('Failed to drain pool:', err);
    process.exit(1);
  }
}

// Hook into process signals
process.on('SIGTERM', async () => {
  await shutdownPool(pool);
  process.exit(0);
});

process.on('SIGINT', async () => {
  await shutdownPool(pool);
  process.exit(0);
});

Architecture Decisions & Rationale

1. Pool Sizing Formula: min = CPU cores * 2 ensures baseline concurrency matches compute capacity. max = min + (network_RTТ_ms * target_rps) prevents queue saturation while respecting database I/O limits. Exceeding this ratio increases context switching without improving throughput.
2. Idle vs Max Lifetime: idleTimeoutMillis reclaims unused connections to free database resources. maxLifetimeMillis forces periodic recycling to prevent memory leaks, session variable drift, and stale TLS sessions.
3. Connection Validation: pg automatically validates connections on checkout. In cloud environments with aggressive load balancers or proxy termination (e.g., AWS RDS Proxy, PgBouncer), add explicit SELECT 1 health checks if the provider drops silent connections.
4. Error Routing: Pool-level errors are logged and forwarded to alerting systems. Query-level errors are isolated per request to prevent cascade failures.
5. Metric Integration: Emit pool.active, pool.idle, pool.waiting, and pool.size to Prometheus/Grafana or Datadog. Alert when waiting > 0 for >5 seconds.

Pitfall Guide

1. Equating Pool Size to Thread or Request Count

Mistake: Setting max: 500 because the app handles 500 concurrent requests. Reality: Database connections are I/O-bound, not CPU-bound. Excessive connections cause context switching, lock contention, and memory exhaustion on the database host. The bottleneck shifts from network to DB scheduler. Best Practice: Size pools based on database capacity, not application concurrency. Use queueing theory: max_connections ≤ DB_max_connections × 0.7 to leave headroom for admin connections and replication.

2. Neglecting Connection Release on Exceptions

Mistake: Using pool.query() without try/finally or forgetting client.release() in error paths. Reality: Leaked connections reduce pool availability. Under load, the pool exhausts, requests queue, and latency spikes. The database host remains unaware of orphaned sessions. Best Practice: Always wrap pool.connect() in try/finally. Prefer pool.query() for simple cases, as it handles checkout/release automatically.

3. Ignoring Connection Lifecycle Boundaries

Mistake: Leaving idleTimeoutMillis and maxLifetimeMillis at defaults or disabling them. Reality: Long-lived connections accumulate session state, memory fragmentation, and stale TLS sessions. Cloud providers and proxies aggressively terminate idle sockets, causing silent failures on checkout. Best Practice: Set idleTimeoutMillis between 15–60s. Set maxLifetimeMillis between 5–15m. Align with infrastructure timeout policies.

4. Assuming Pools Survive Network Partitions

Mistake: Expecting the pool to automatically recover from database restarts, VPC peering drops, or proxy failover. Reality: Pools cache socket references. When the underlying connection drops, the pool marks it as broken but may continue queuing requests until timeout. Best Practice: Implement circuit breakers or retry logic at the application layer. Use connection validation on checkout. Monitor pool.error events and trigger health checks.

5. Over-Reliance on ORM Defaults

Mistake: Using Prisma, TypeORM, or Sequelize without inspecting their pool configuration. Reality: ORMs often ship with conservative defaults (max: 10) or disable pooling in development. Production workloads require explicit tuning. Best Practice: Override ORM pool settings. Validate behavior under load. Use raw pool metrics to confirm reuse rates.

6. Skipping Connection Validation in Cloud Environments

Mistake: Assuming TCP keep-alive is sufficient for cloud databases behind load balancers or proxy layers. Reality: Proxies like PgBouncer, AWS RDS Proxy, or Cloud SQL Proxy terminate idle connections aggressively. Stale sockets cause ECONNRESET or Connection terminated unexpectedly. Best Practice: Enable keepAlive and keepAliveInitialDelayMillis. Add explicit SELECT 1 validation if the proxy drops connections silently. Tune proxy max_client_conn to match pool max.

7. Single Pool for Multiple Databases or Services

Mistake: Sharing one pool instance across read replicas, write masters, and analytics databases. Reality: Different workloads require different sizing, timeouts, and routing. Shared pools cause contention and misrouted queries. Best Practice: Instantiate separate pools per database role. Use read/write splitting at the query layer, not the pool layer.

Production Bundle

Action Checklist

• Instrument pool metrics: active, idle, waiting, size, and checkout latency
• Set idleTimeoutMillis and maxLifetimeMillis aligned with infrastructure policies
• Wrap all pool.connect() calls in try/finally with explicit release()
• Implement graceful shutdown using pool.end() on SIGTERM/SIGINT
• Size pools using min = CPU_cores * 2 and max = min + (RTT_ms * target_rps)
• Add connection validation or SELECT 1 health checks for cloud proxy environments
• Monitor pool.waiting and alert when queue depth exceeds threshold for >5s
• Test failover scenarios: database restart, network partition, proxy rotation

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Low traffic, predictable load	Static pool (fixed 5–10)	Simplifies configuration, avoids scaling overhead	Minimal infrastructure cost, stable DB usage
High concurrency, variable spikes	Dynamic pool (auto 2–50) + queue timeout	Prevents exhaustion during spikes, reclaims resources during lulls	Higher compute for pool manager, lower DB CPU due to reuse
Serverless / ephemeral functions	Short-lived pool per invocation or external proxy (PgBouncer/RDS Proxy)	Functions scale independently; shared pools cause connection storms	Proxy cost added, but eliminates per-function pool overhead
Multi-tenant / isolated workloads	Separate pools per tenant or shard	Prevents noisy neighbor contention, enables per-tenant sizing	Increased connection count, requires DB max_connections tuning
Read-heavy analytics workload	Dedicated read replica pool with longer idle timeout	Analytics queries hold connections longer; isolation prevents write latency	Higher replica cost, improved write latency stability

Configuration Template

import { PoolConfig } from 'pg';

export const productionPoolConfig: PoolConfig = {
  // Connection credentials (use secrets manager in production)
  host: process.env.DB_HOST!,
  port: Number(process.env.DB_PORT) || 5432,
  database: process.env.DB_NAME!,
  user: process.env.DB_USER!,
  password: process.env.DB_PASSWORD!,

  // Pool sizing
  min: 4,
  max: 20,

  // Lifecycle management
  idleTimeoutMillis: 30_000,       // Recycle idle connections after 30s
  maxLifetimeMillis: 600_000,      // Force recreation after 10m
  connectionTimeoutMillis: 2_000,  // Fail fast when pool is exhausted

  // Network resilience
  keepAlive: true,
  keepAliveInitialDelayMillis: 10_000,

  // SSL/TLS (required for cloud providers)
  ssl: process.env.NODE_ENV === 'production'
    ? { rejectUnauthorized: false } // Use managed CA in prod
    : undefined,

  // Query defaults
  statement_timeout: 5_000,        // Prevent runaway queries
  idle_in_transaction_session_timeout: 30_000,
};

Quick Start Guide

1. Install & Configure: Run npm install pg. Copy the productionPoolConfig template into your database module. Replace environment variables with your credentials.
2. Initialize Pool: Import createDatabasePool(config) at application startup. Attach metric hooks to your observability stack.
3. Execute Queries: Use executeQuery(pool, sql, params) for all database operations. Verify try/finally release patterns in your codebase.
4. Validate Under Load: Run a synthetic load test (e.g., autocannon or k6). Confirm pool.waiting stays at 0, reuse rate exceeds 85%, and p95 latency remains stable.
5. Deploy & Monitor: Ship to staging. Verify graceful shutdown on deployment. Alert on pool.error events and queue depth thresholds. Adjust max based on observed DB CPU and connection utilization.