Database connection management

e saturation.

Core Solution

Implementing production-grade connection management requires explicit lifecycle control, health validation, and backpressure handling. The following architecture uses a typed connection manager with acquisition queuing, idle pruning, and circuit-breaking logic.

Step 1: Define Pool Configuration with Backpressure

Connections must be bounded, timed, and monitored. Use explicit limits rather than framework defaults.

export interface PoolConfig {
  host: string;
  port: number;
  database: string;
  user: string;
  password: string;
  maxConnections: number;
  minConnections: number;
  idleTimeoutMs: number;
  maxLifetimeMs: number;
  acquireTimeoutMs: number;
  healthCheckIntervalMs: number;
}

Step 2: Implement Connection Lifecycle Wrapper

Wrap the driver's pool with explicit acquire/release semantics. Enforce timeouts and track usage.

import { Pool, PoolClient, QueryResult } from 'pg';

export class ConnectionManager {
  private pool: Pool;
  private acquireQueue: Array<{ resolve: (client: PoolClient) => void; reject: (err: Error) => void }> = [];
  private activeClients = new Set<PoolClient>();

  constructor(config: PoolConfig) {
    this.pool = new Pool({
      host: config.host,
      port: config.port,
      database: config.database,
      user: config.user,
      password: config.password,
      max: config.maxConnections,
      min: config.minConnections,
      idleTimeoutMillis: config.idleTimeoutMs,
      connectionTimeoutMillis: config.acquireTimeoutMs,
    });

    // Periodic health check to prune dead connections
    setInterval(() => this.healthCheck(), config.healthCheckIntervalMs);
  }

  async acquire(): Promise<PoolClient> {
    if (this.pool.totalCount >= this.pool.options.max) {
      return new Promise((resolve, reject) => {
        const timeout = setTimeout(() => {
          reject(new Error('Connection pool acquisition timed out'));
          const idx = this.acquireQueue.findIndex(q => q.resolve === resolve);
          if (idx !== -1) this.acquireQueue.splice(idx, 1);
        }, this.pool.options.connectionTimeoutMillis);

        this.acquireQueue.push({
          resolve: (client: PoolClient) => { clearTimeout(timeout); resolve(client); },
          reject: (err: Error) => { clearTimeout(timeout); reject(err); }
        });
      });
    }

    const client = await this.pool.connect();
    this.activeClients.add(client);
    return client;
  }

  async release(client: PoolClient): Promise<void> {
    this.activeClients.delete(client);
    // Process queued acquisitions
    if (this.acquireQueue.length > 0) {
      const next = this.acquireQueue.shift()!;
      try {
        const queuedClient = await this.pool.connect();
        this.activeClients.add(queuedClient);
        next.resolve(queuedClient);
      } catch (err) {
        next.reject(err as Error);
      }
    } else {
      client.release();
    }
  }

  private async healthCheck(): Promise<void> {
    const idle = this.pool.totalCount - this.pool.waitingCount;
    if (idle <= this.pool.options.min) return;

    const clients = Array.from(this.activeClients);
    for (const client of clients) {
      try {
        await client.query('SELECT 1');
      } catch {
        client.release(true); // Force destroy unhealthy connection
        this.activeClients.delete(client);
      }
    }
  }

  async drain(): Promise<void> {
    await this.pool.end();
    this.activeClients.clear();
    this.acquireQueue.forEach(q => q.reject(new Error('Pool draining')));
    this.acquireQueue = [];
  }
}

Step 3: Architecture Decisions and Rationale

• Explicit acquire/release over implicit pooling: Framework pools hide acquisition failures. Wrapping allows backpressure queuing, timeout enforcement, and precise metrics collection.
• Idle timeout vs max lifetime: idleTimeoutMs reclaims unused connections. maxLifetimeMs (enforced via health checks) prevents stale TCP sessions from persisting through network partitions or database restarts.
• Health check interval: Runs asynchronously to avoid blocking queries. Uses SELECT 1 to validate TCP state and authentication without triggering heavy execution plans.
• Circuit breaking integration: When acquire() times out, the caller should trigger fallback logic (cache, degraded mode, or request rejection) rather than retrying immediately. This prevents thundering herd amplification.
• Graceful shutdown: drain() waits for in-flight queries, rejects queued acquisitions, and closes the underlying pool. Essential for Kubernetes rolling deployments.

Pitfall Guide

1. Treating Connections as Request-Scoped Without Pooling

Opening a new TCP connection per request incurs handshake, TLS, and authentication overhead. At scale, this saturates file descriptors and database max_connections. Always use a bounded pool with explicit lifecycle control.

2. Ignoring Idle Timeout and Max Lifetime

Connections left open indefinitely consume memory and hold locks. Databases may drop idle sessions without notifying the client, causing ECONNRESET errors. Configure idleTimeoutMillis (typically 30s) and validate max lifetime via periodic health checks.

3. Failing to Handle Pool Exhaustion Gracefully

When the pool reaches max, subsequent acquire() calls block indefinitely unless timeout is enforced. Implement backpressure: reject requests after timeout, route to fallback, or scale horizontally. Never retry immediately without exponential backoff.

4. Mixing Transaction Boundaries with Connection Lifecycle

Transactions must hold a single connection for their entire duration. Releasing a client mid-transaction breaks atomicity. Always acquire, begin, execute, commit/rollback, and release within the same scope. Use explicit transaction wrappers that guarantee release in finally blocks.

5. Skipping Network-Level Health Checks

Application-level queries may succeed while the underlying TCP connection is half-closed. Relying solely on query errors delays failure detection. Periodic SELECT 1 probes catch silent network degradation before it impacts latency-critical paths.

6. Over-Provisioning Max Connections Without Monitoring

Setting maxConnections to match database limits ignores per-service concurrency. A single pod can exhaust the database limit if multiple services share credentials. Use service-level limits, connection tagging, and monitor pg_stat_activity or equivalent metrics.

7. Blocking the Event Loop During Acquisition

Synchronous pool acquisition or heavy retry logic stalls the event loop, causing cascading timeouts across unrelated requests. Always use async acquisition with configurable timeouts and non-blocking queuing.

Best Practices from Production:

• Wrap all database calls in a try/finally that guarantees release()
• Tag connections with service/request IDs for debugging (SET application_name = 'svc-auth')
• Monitor pool.waitingCount, pool.totalCount, and pool.idleCount via Prometheus/Grafana
• Implement exponential backoff with jitter for transient failures
• Separate read and write pools when using replica routing

Production Bundle

Action Checklist

• Define explicit pool configuration with bounded max/min connections and timeouts
• Implement acquire/release wrapper with acquisition queuing and timeout enforcement
• Add periodic health checks to detect and prune half-closed TCP connections
• Integrate circuit breaker or fallback logic for pool exhaustion scenarios
• Enforce transaction scoping with guaranteed release in finally blocks
• Configure connection tagging and structured logging for observability
• Implement graceful shutdown handler that drains active queries and rejects queued requests
• Set up monitoring dashboards for pool utilization, wait time, and failure rate

Decision Matrix

Scenario	Recommended Approach	Why	Cost Impact
Single-region microservice	Fixed pool with idle timeout and health checks	Predictable concurrency, minimal overhead	Low
High-concurrency API	Dynamic pool with backpressure queue and circuit breaker	Prevents saturation, maintains p99 SLA	Medium
Multi-region read-heavy	Separate read/write pools with replica routing	Reduces primary load, improves read latency	Medium-High
Legacy monolith migration	Gradual pool adoption with connection limiting per module	Avoids database overload during refactoring	Low-Medium
Serverless/short-lived functions	Connection proxy (e.g., PgBouncer, RDS Proxy)	Bypasses cold-start TCP overhead	High (proxy cost)

Configuration Template

// db/pool.config.ts
import { PoolConfig } from './connection-manager';

export const productionPoolConfig: 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 || '',
  maxConnections: 25,
  minConnections: 5,
  idleTimeoutMs: 30_000,
  maxLifetimeMs: 600_000,
  acquireTimeoutMs: 5_000,
  healthCheckIntervalMs: 15_000,
};

// Usage
const manager = new ConnectionManager(productionPoolConfig);

// Graceful shutdown
process.on('SIGTERM', async () => {
  await manager.drain();
  process.exit(0);
});

Quick Start Guide

1. Install dependencies: npm install pg @types/pg
2. Copy the ConnectionManager class and PoolConfig interface into your codebase
3. Replace framework-level database calls with manager.acquire() / manager.release(client)
4. Run a load test with artillery or k6 to validate acquisition queue behavior and p99 latency
5. Expose pool metrics (waitingCount, totalCount, idleCount) to your monitoring stack and set alerts at 80% utilization