Database connection management

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

```typescript
// 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