Database connection pooling

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): Promi

se<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.

```typescript
// 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);
   });