Database connection pooling

alculate max connections based on database CPU cores and available memory. A common heuristic is (core_count * 2) + effective_spindle_count, but for cloud databases, start with 20-50 per application instance and tune based on metrics. 2. Initialize Pool with Safety Bounds: Set connectionTimeoutMillis to prevent requests from hanging indefinitely if the pool is exhausted. Configure idleTimeoutMillis to release unused connections and maxUses to rotate connections and prevent memory leaks within the database backend. 3. Create Acquisition Wrapper: Abstract pool acquisition to ensure connections are always released, even in error scenarios. Use a helper function that accepts a callback or returns a client with a guaranteed release mechanism. 4. Implement Health Checks: Configure the pool to test connections before use (testOnBorrow equivalent) or periodically. This prevents the application from attempting to use stale connections caused by network partitions or database restarts. 5. Graceful Shutdown: Register shutdown hooks to close the pool cleanly, allowing in-flight queries to complete while rejecting new requests.

Code Example: Production-Grade Pool Manager

import { Pool, PoolConfig, PoolClient, QueryResult } from 'pg';
import { Logger } from './logger'; // Assume standard logger implementation

interface QueryParams {
  text: string;
  values?: any[];
}

class DatabasePoolManager {
  private pool: Pool;
  private isShuttingDown: boolean = false;

  constructor(config: PoolConfig) {
    // Production defaults and overrides
    const poolConfig: PoolConfig = {
      max: config.max || 20, // Limit concurrent connections
      idleTimeoutMillis: config.idleTimeoutMillis || 30000, // Release idle connections after 30s
      connectionTimeoutMillis: config.connectionTimeoutMillis || 2000, // Fail fast if pool exhausted
      maxUses: config.maxUses || 7500, // Rotate connections to prevent backend memory leaks
      keepAlive: true, // Enable TCP keepalive
      statement_timeout: config.statement_timeout || 5000, // Safety limit for queries
      ...config,
    };

    this.pool = new Pool(poolConfig);

    // Error handling for idle connections
    this.pool.on('error', (err: Error, client: PoolClient) => {
      Logger.error('Unexpected error on idle client', { error: err.message, client });
      client.release();
    });

    Logger.info('Database pool initialized', { 
      maxConnections: poolConfig.max,
      idleTimeout: poolConfig.idleTimeoutMillis 
    });
  }

  /**
   * Acquires a client from the pool, executes a query, and ensures release.
   * Prevents connection leaks by handling errors and cleanup automatically.
   */
  async query<T = any>(params: QueryParams): Promise<QueryResult<T>> {
    if (this.isShuttingDown) {
      throw new Error('Database pool is shutting down');
    }

    const client = await this.pool.connect();
    try {
      const result = await client.query<T>(params);
      return result;
    } catch (error) {
      Logger.error('Query execution failed', { 
        error: error instanceof Error ? error.message : String(error),
        query: params.text 
      });
      throw error;
    } finally {
      // Critical: Always release the client back to the pool
      client.release();
    }
  }

  /**
   * Transaction wrapper ensuring atomic execution and resource release.
   */
  async transaction<T>(callback: (client: PoolClient) => Promise<T>): Promise<T> {
    if (this.isShuttingDown) {
      throw new Error('Database pool is shutting down');
    }

    const client = await this.pool.connect();
    try {
      await client.query('BEGIN');
      const result = await callback(client);
      await client.query('COMMIT');
      return result;
    } catch (error) {
      await client.query('ROLLBACK');
      Logger.error('Transaction rolled back', { error: error instanceof Error ? error.message : String(error) });
      throw error;
    } finally {
      client.release();
    }
  }

  /**
   * Graceful shutdown: Stops accepting new requests and drains existing connections.
   */
  async shutdown(timeoutMs: number = 10000): Promise<void> {
    Logger.info('Initiating database pool shutdown');
    this.isShuttingDown = true;

    // Set a deadline for draining
    const deadline = Date.now() + timeoutMs;

    try {
      await this.pool.end();
      Logger.info('Database pool closed successfully');
    } catch (error) {
      Logger.error('Error during pool shutdown', { error: error instanceof Error ? error.message : String(error) });
    }
  }

  getPoolStats() {
    return {
      totalCount: this.pool.totalCount,
      idleCount: this.pool.idleCount,
      waitingCount: this.pool.waitingCount,
    };
  }
}

export const db = new DatabasePoolManager({
  host: process.env.DB_HOST,
  port: Number(process.env.DB_PORT),
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASS,
  max: 25,
  idleTimeoutMillis: 20000,
  connectionTimeoutMillis: 1500,
});

Architecture Decisions

• connectionTimeoutMillis: Set to 1.5-2 seconds. If the pool is exhausted, requests should fail fast rather than blocking indefinitely, allowing the application to return a 503 Service Unavailable status or trigger circuit breaking.
• maxUses: Rotating connections after a number of uses mitigates memory fragmentation and hidden state accumulation in database backends. This is particularly relevant for PostgreSQL where backend processes can retain memory over long lifespans.
• idleTimeoutMillis: Releasing idle connections reduces the memory footprint on the database during low-traffic periods. However, set this higher than the expected idle gap to avoid excessive churn during traffic valleys.
• Wrapper Pattern: The query and transaction methods encapsulate connect and release. This eliminates the risk of connection leaks caused by developers forgetting to release clients in catch blocks or early returns.

Pitfall Guide

1. Connection Leaks via Missing Release

Mistake: Acquiring a client but failing to call release() in all code paths, especially error handlers. Impact: The pool exhausts, and all subsequent requests hang until connectionTimeoutMillis triggers. The application becomes unresponsive. Fix: Always use a try/finally block or a wrapper function that guarantees release. Monitor waitingCount in pool metrics to detect leaks early.

2. Pool Max Exceeds Database Limit

Mistake: Setting max connections higher than the database's max_connections setting, or setting the same max across multiple application instances without accounting for total concurrency. Impact: The database rejects new connections, causing FATAL: too many connections errors. This can cascade to other services sharing the database. Fix: Calculate total connections as (app_instances * pool_max) + reserved_connections. Ensure pool_max fits within the database limit. Use connection proxies to share pools across instances.

3. Ignoring Network Partitions

Mistake: Assuming connections remain valid indefinitely. Network equipment may drop idle TCP connections without notifying endpoints. Impact: The pool holds "zombie" connections. When acquired, queries fail with network errors, causing transaction rollbacks and retries. Fix: Enable TCP keepalive. Configure idleTimeoutMillis to proactively close connections before network equipment drops them. Implement retry logic with exponential backoff for transient network errors.

4. Thundering Herd on Startup

Mistake: Allowing the pool to grow dynamically from zero without pre-warming or rate-limiting connection creation. Impact: On startup or after a burst, the pool attempts to open many connections simultaneously, overwhelming the database authentication phase and causing timeouts. Fix: Configure min connections to pre-warm the pool. Implement rate-limiting on connection creation if the driver supports it. Use a connection proxy to buffer connection requests.

5. Blocking Event Loop with Pool Acquisition

Mistake: Using synchronous pool acquisition methods or blocking operations in an asynchronous runtime. Impact: In Node.js/TypeScript, blocking the event loop prevents handling of other requests, effectively halting the application. Fix: Always use asynchronous pool.connect(). Ensure no synchronous database operations are performed. Monitor event loop lag metrics.

6. Pool Per Request Anti-Pattern

Mistake: Instantiating a new pool instance for every request or handler invocation. Impact: Defeats the purpose of pooling. Creates maximum overhead, exhausts database connections instantly, and consumes excessive application memory. Fix: Instantiate the pool once at application startup and share the instance across all handlers. Use dependency injection or a singleton pattern.

7. Lack of Graceful Shutdown

Mistake: Killing the application process without closing the pool. Impact: In-flight queries are aborted, potentially leaving transactions in an indeterminate state. Database connections may remain open until timeout, wasting resources. Fix: Register SIGTERM and SIGINT handlers. Call pool.end() to drain connections and close the pool before exiting. Implement a shutdown timeout to force exit if draining takes too long.

Production Bundle

Action Checklist

• Calculate Pool Size: Set max based on DB_CPU_CORES * 2 and validate against max_connections. Account for multiple app instances.
• Enable Leak Detection: Monitor waitingCount and acquisition times. Alert if waitingCount exceeds threshold or acquisition time > 500ms.
• Configure Timeouts: Set connectionTimeoutMillis to 1.5-2s, idleTimeoutMillis to 20-30s, and statement_timeout to 5s.
• Implement Wrapper: Use a centralized query and transaction wrapper to guarantee connection release.
• Add Health Checks: Configure pool to test connections on borrow or use a proxy with health checks.
• Graceful Shutdown: Implement shutdown hook to call pool.end() and drain connections.
• Monitor Metrics: Expose pool stats (totalCount, idleCount, waitingCount, queryTime) to observability platform.
• Test Failure Modes: Simulate database restarts, network partitions, and pool exhaustion to verify resilience.

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
High-Concurrency Monolith	Large Fixed Pool (20-50 per instance)	Predictable load, direct connection reuse minimizes latency.	Low. Efficient resource usage.
Serverless / Ephemeral Functions	Serverless Driver or Proxy (RDS Proxy/PgBouncer)	Functions scale to zero; long-lived pools are inefficient. Proxy maintains pool state.	Medium. Proxy adds cost but prevents DB overload.
Microservices with Variable Load	Adaptive Pool + PgBouncer	Decouples app concurrency from DB connections. PgBouncer handles pooling efficiently.	Low-Medium. PgBouncer is lightweight; reduces DB scaling costs.
Read-Heavy Workload	Read Replicas with Separate Pools	Isolates read traffic from writes. Pools can be tuned independently.	Medium. Additional replica cost, but improves write performance.
Strict Memory Constraints	Small Pool + Statement Caching	Minimizes per-connection memory. Statement caching reduces parse overhead.	Low. Optimizes memory usage.

Configuration Template

Copy and adapt this template for your environment. Adjust values based on your database capacity and traffic patterns.

// pool.config.ts
import { PoolConfig } from 'pg';

export const poolConfig: PoolConfig = {
  // Connection limits
  max: Number(process.env.DB_POOL_MAX) || 25,       // Max connections per instance
  min: Number(process.env.DB_POOL_MIN) || 5,        // Min connections to keep alive
  
  // Timeout settings
  idleTimeoutMillis: Number(process.env.DB_IDLE_TIMEOUT) || 20000, // Release idle after 20s
  connectionTimeoutMillis: Number(process.env.DB_CONN_TIMEOUT) || 1500, // Fail fast after 1.5s
  statement_timeout: Number(process.env.DB_STMT_TIMEOUT) || 5000, // Query safety limit
  
  // Connection lifecycle
  maxUses: Number(process.env.DB_MAX_USES) || 7500, // Rotate connections
  keepAlive: true,
  keepAliveInitialDelayMillis: 10000,
  
  // SSL Configuration (Required for cloud databases)
  ssl: process.env.DB_SSL === 'true' ? { rejectUnauthorized: false } : false,
  
  // Database credentials
  host: process.env.DB_HOST,
  port: Number(process.env.DB_PORT),
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASS,
};

Quick Start Guide

1. Install Dependencies:

   npm install pg
   npm install -D @types/pg
   

2. Create Pool Manager: Save the DatabasePoolManager class code to src/database/pool-manager.ts.

3. Initialize Pool: Import and instantiate the manager in your application entry point.

   import { db } from './database/pool-manager';
   

4. Execute Queries: Use the wrapper methods for all database operations.

   // Single query
   const users = await db.query({ text: 'SELECT * FROM users WHERE active = $1', values: [true] });
   
   // Transaction
   await db.transaction(async (client) => {
     await client.query('INSERT INTO orders ...');
     await client.query('UPDATE inventory ...');
   });
   

5. Add Shutdown Hook: Ensure graceful shutdown in your server setup.

   process.on('SIGTERM', async () => {
     await db.shutdown();
     process.exit(0);
   });