Connection_pooling

Chapter 34: Connection Pooling

Managing Database Connections Efficiently

---

34.1 Connection Pool Overview

A connection pool maintains a set of database connections that can be reused, avoiding the overhead of creating new connections for each request.

                    Connection Pool Architecture
    ================================================================================

    Application Requests
           |
           v
    +------------------------------------------+
    |           Connection Pool                 |
    |                                          |
    |  +------+ +------+ +------+ +------+    |
    |  | Conn | | Conn | | Conn | | Conn |    |
    |  |  1   | |  2   | |  3   | |  4   |    |
    |  +------+ +------+ +------+ +------+    |
    |    |        |        |        |          |
    +------------------------------------------+
           |        |        |        |
           v        v        v        v
    +------------------------------------------+
    |              Database                    |
    +------------------------------------------+

    Benefits:
    - Reduced connection overhead
    - Better resource utilization
    - Improved response times
    - Connection reuse

    ================================================================================

---

34.2 TypeORM Pool Configuration

Basic Pool Configuration

src/config/database.config.ts

import { DataSource } from 'typeorm';

export default new DataSource({
  type: 'postgres',
  host: 'localhost',
  port: 5432,
  username: 'postgres',
  password: 'postgres',
  database: 'myapp',
  entities: ['src/**/*.entity{.ts,.js}'],

  // Pool size
  poolSize: 20,  // Number of connections in pool
});

Advanced Pool Configuration

src/config/database.config.ts

import { DataSource } from 'typeorm';

export default new DataSource({
  type: 'postgres',
  host: process.env.DB_HOST,
  port: parseInt(process.env.DB_PORT, 10),
  username: process.env.DB_USERNAME,
  password: process.env.DB_PASSWORD,
  database: process.env.DB_DATABASE,
  entities: ['src/**/*.entity{.ts,.js}'],

  // Basic pool size
  poolSize: 20,

  // Advanced pool settings (driver-specific)
  extra: {
    // Maximum connections in pool
    max: 20,

    // Minimum connections to maintain
    min: 5,

    // Idle connection timeout (ms)
    idleTimeoutMillis: 30000,

    // Connection acquisition timeout (ms)
    acquireTimeoutMillis: 60000,

    // Connection creation timeout (ms)
    connectionTimeoutMillis: 5000,

    // Number of retries for connection
    reapIntervalMillis: 1000,

    // Log pool events
    log: false,
  },
});

NestJS Integration

src/app.module.ts

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { ConfigModule, ConfigService } from '@nestjs/config';

@Module({
  imports: [
    ConfigModule.forRoot({ isGlobal: true }),
    TypeOrmModule.forRootAsync({
      imports: [ConfigModule],
      useFactory: (configService: ConfigService) => ({
        type: 'postgres',
        host: configService.get('DB_HOST', 'localhost'),
        port: configService.get('DB_PORT', 5432),
        username: configService.get('DB_USERNAME', 'postgres'),
        password: configService.get('DB_PASSWORD', 'postgres'),
        database: configService.get('DB_DATABASE', 'myapp'),
        entities: [__dirname + '/**/*.entity{.ts,.js}'],
        synchronize: false,

        // Pool configuration
        poolSize: configService.get('DB_POOL_SIZE', 20),

        extra: {
          max: configService.get('DB_POOL_MAX', 20),
          min: configService.get('DB_POOL_MIN', 5),
          idleTimeoutMillis: 30000,
          acquireTimeoutMillis: 60000,
        },
      }),
      inject: [ConfigService],
    }),
  ],
})
export class AppModule {}

---

34.3 Pool Sizing Strategies

Calculating Optimal Pool Size

                    Pool Size Calculation
    ================================================================================

    Formula (PostgreSQL):
    Pool Size = (Core Count * 2) + Effective Spindle Count

    For SSD-based systems:
    Pool Size = Core Count * 2

    Example:
    - 8 CPU cores
    - SSD storage
    - Recommended pool size: 16 connections

    Considerations:
    +-------------------+------------------------------------------+
    |    Factor         | Impact                                    |
    +-------------------+------------------------------------------+
    | CPU cores         | More cores = more parallel queries        |
    | Memory            | Each connection uses memory               |
    | Query duration    | Longer queries need more connections      |
    | Concurrent users  | More users = more connections needed      |
    | Database limits   | Max connections on database server        |
    +-------------------+------------------------------------------+

    ================================================================================

Environment-Based Configuration

src/config/database.config.ts

import { DataSource } from 'typeorm';

const getPoolConfig = () => {
  const env = process.env.NODE_ENV || 'development';

  switch (env) {
    case 'development':
      return {
        poolSize: 5,
        extra: { max: 5, min: 2 },
      };

    case 'test':
      return {
        poolSize: 3,
        extra: { max: 3, min: 1 },
      };

    case 'production':
      return {
        poolSize: parseInt(process.env.DB_POOL_SIZE || '20'),
        extra: {
          max: parseInt(process.env.DB_POOL_MAX || '20'),
          min: parseInt(process.env.DB_POOL_MIN || '5'),
          idleTimeoutMillis: 30000,
          acquireTimeoutMillis: 60000,
        },
      };

    default:
      return { poolSize: 10 };
  }
};

export default new DataSource({
  type: 'postgres',
  // ... connection settings
  ...getPoolConfig(),
});

---

34.4 Connection Pool Events

Monitoring Pool Events

src/database/pool-monitor.service.ts

import { Injectable, OnModuleInit, InjectDataSource } from '@nestjs/common';
import { DataSource } from 'typeorm';

@Injectable()
export class PoolMonitorService implements OnModuleInit {
  constructor(
    @InjectDataSource()
    private dataSource: DataSource,
  ) {}

  onModuleInit() {
    const driver = this.dataSource.driver as any;
    const pool = driver.pool;

    if (pool) {
      // Connection acquired from pool
      pool.on('acquire', (connection: any) => {
        console.log(`Connection acquired: ${connection.id}`);
      });

      // Connection released back to pool
      pool.on('release', (connection: any) => {
        console.log(`Connection released: ${connection.id}`);
      });

      // New connection created
      pool.on('create', (connection: any) => {
        console.log(`Connection created: ${connection.id}`);
      });

      // Connection destroyed
      pool.on('destroy', (connection: any) => {
        console.log(`Connection destroyed: ${connection.id}`);
      });

      // Pool exhausted
      pool.on('exhausted', () => {
        console.warn('Connection pool exhausted!');
      });

      // Connection error
      pool.on('error', (error: Error, connection: any) => {
        console.error(`Connection error: ${error.message}`);
      });
    }
  }

  getPoolStats() {
    const driver = this.dataSource.driver as any;
    const pool = driver.pool;

    if (pool) {
      return {
        total: pool.totalCount,
        idle: pool.idleCount,
        waiting: pool.waitingCount,
        active: pool.totalCount - pool.idleCount,
      };
    }

    return null;
  }
}

Pool Statistics Endpoint

src/database/pool.controller.ts

import { Controller, Get } from '@nestjs/common';
import { PoolMonitorService } from './pool-monitor.service';

@Controller('database/pool')
export class PoolController {
  constructor(private poolMonitor: PoolMonitorService) {}

  @Get('stats')
  getStats() {
    return this.poolMonitor.getPoolStats();
  }
}

---

34.5 Connection Pool Best Practices

Handling Pool Exhaustion

src/common/filters/pool-exhausted.filter.ts

import {
  ExceptionFilter,
  Catch,
  ArgumentsHost,
  HttpStatus,
} from '@nestjs/common';
import { Response } from 'express';
import { PoolExhaustedException } from './exceptions';

@Catch(PoolExhaustedException)
export class PoolExhaustedFilter implements ExceptionFilter {
  catch(exception: PoolExhaustedException, host: ArgumentsHost) {
    const ctx = host.switchToHttp();
    const response = ctx.getResponse<Response>();

    response.status(HttpStatus.SERVICE_UNAVAILABLE).json({
      statusCode: HttpStatus.SERVICE_UNAVAILABLE,
      message: 'Service temporarily unavailable. Please try again later.',
      error: 'Service Unavailable',
    });
  }
}

// Usage in service
async queryWithRetry<T>(
  operation: () => Promise<T>,
  retries: number = 3,
): Promise<T> {
  for (let i = 0; i < retries; i++) {
    try {
      return await operation();
    } catch (error) {
      if (this.isPoolError(error) && i < retries - 1) {
        await this.sleep(100 * (i + 1));
        continue;
      }
      throw error;
    }
  }
  throw new Error('Operation failed after retries');
}

private isPoolError(error: any): boolean {
  const poolErrorMessages = [
    'Connection pool exhausted',
    'Timeout acquiring connection',
    'Too many connections',
  ];

  return poolErrorMessages.some(msg =>
    error.message?.includes(msg)
  );
}

private sleep(ms: number): Promise<void> {
  return new Promise(resolve => setTimeout(resolve, ms));
}

Connection Health Check

src/database/health.service.ts

import { Injectable, InjectDataSource } from '@nestjs/common';
import { DataSource } from 'typeorm';

@Injectable()
export class DatabaseHealthService {
  constructor(
    @InjectDataSource()
    private dataSource: DataSource,
  ) {}

  async checkConnection(): Promise<{
    status: 'ok' | 'error';
    latency?: number;
    error?: string;
  }> {
    const start = Date.now();

    try {
      await this.dataSource.query('SELECT 1');

      return {
        status: 'ok',
        latency: Date.now() - start,
      };
    } catch (error) {
      return {
        status: 'error',
        error: error.message,
      };
    }
  }

  async checkPool(): Promise<{
    status: 'ok' | 'warning' | 'critical';
    stats: {
      total: number;
      idle: number;
      active: number;
      waiting: number;
    };
    message?: string;
  }> {
    const driver = this.dataSource.driver as any;
    const pool = driver.pool;

    if (!pool) {
      return {
        status: 'error',
        stats: { total: 0, idle: 0, active: 0, waiting: 0 },
        message: 'Pool not available',
      };
    }

    const stats = {
      total: pool.totalCount,
      idle: pool.idleCount,
      active: pool.totalCount - pool.idleCount,
      waiting: pool.waitingCount,
    };

    // Determine status
    const utilization = stats.active / stats.total;

    if (utilization > 0.9 || stats.waiting > 5) {
      return {
        status: 'critical',
        stats,
        message: 'Pool near exhaustion',
      };
    }

    if (utilization > 0.7 || stats.waiting > 0) {
      return {
        status: 'warning',
        stats,
        message: 'Pool under high load',
      };
    }

    return {
      status: 'ok',
      stats,
    };
  }
}

---

34.6 Multiple Connection Pools

Read/Write Splitting

src/app.module.ts

import { Module } from '@nestjs/common';
import { TypeOrmModule } from '@nestjs/typeorm';
import { ConfigModule, ConfigService } from '@nestjs/config';

@Module({
  imports: [
    ConfigModule.forRoot({ isGlobal: true }),

    // Write connection (primary)
    TypeOrmModule.forRootAsync({
      name: 'write',
      imports: [ConfigModule],
      useFactory: (configService: ConfigService) => ({
        type: 'postgres',
        host: configService.get('DB_WRITE_HOST'),
        port: configService.get('DB_PORT', 5432),
        username: configService.get('DB_USERNAME'),
        password: configService.get('DB_PASSWORD'),
        database: configService.get('DB_DATABASE'),
        entities: [__dirname + '/**/*.entity{.ts,.js}'],
        poolSize: 10,
      }),
      inject: [ConfigService],
    }),

    // Read connection (replica)
    TypeOrmModule.forRootAsync({
      name: 'read',
      imports: [ConfigModule],
      useFactory: (configService: ConfigService) => ({
        type: 'postgres',
        host: configService.get('DB_READ_HOST'),
        port: configService.get('DB_PORT', 5432),
        username: configService.get('DB_USERNAME'),
        password: configService.get('DB_PASSWORD'),
        database: configService.get('DB_DATABASE'),
        entities: [__dirname + '/**/*.entity{.ts,.js}'],
        poolSize: 20,  // More connections for read
      }),
      inject: [ConfigService],
    }),
  ],
})
export class AppModule {}

// Usage in service
@Injectable()
export class UsersService {
  constructor(
    @InjectRepository(User, 'write')
    private writeRepository: Repository<User>,
    @InjectRepository(User, 'read')
    private readRepository: Repository<User>,
  ) {}

  // Read from replica
  async findAll(): Promise<User[]> {
    return this.readRepository.find();
  }

  // Write to primary
  async create(data: CreateUserDto): Promise<User> {
    return this.writeRepository.save(data);
  }
}

Multi-Tenant Connection Pools

src/database/tenant-connection.service.ts

import { Injectable, OnModuleDestroy } from '@nestjs/common';
import { DataSource } from 'typeorm';

@Injectable()
export class TenantConnectionService implements OnModuleDestroy {
  private connections = new Map<string, DataSource>();

  async getConnection(tenantId: string): Promise<DataSource> {
    if (this.connections.has(tenantId)) {
      return this.connections.get(tenantId)!;
    }

    const connection = new DataSource({
      type: 'postgres',
      host: process.env.DB_HOST,
      port: parseInt(process.env.DB_PORT, 10),
      username: process.env.DB_USERNAME,
      password: process.env.DB_PASSWORD,
      database: `tenant_${tenantId}`,
      entities: ['src/**/*.entity{.ts,.js}'],
      poolSize: 5,
    });

    await connection.initialize();
    this.connections.set(tenantId, connection);

    return connection;
  }

  async onModuleDestroy() {
    for (const [tenantId, connection] of this.connections) {
      if (connection.isInitialized) {
        await connection.destroy();
        console.log(`Closed connection for tenant: ${tenantId}`);
      }
    }
    this.connections.clear();
  }

  async closeConnection(tenantId: string): Promise<void> {
    const connection = this.connections.get(tenantId);

    if (connection?.isInitialized) {
      await connection.destroy();
      this.connections.delete(tenantId);
    }
  }
}

---

34.7 Connection Pool Troubleshooting

Common Issues and Solutions

                    Connection Pool Troubleshooting
    ================================================================================

    Issue: Pool Exhaustion
    Symptoms:
    - "Connection pool exhausted" errors
    - Timeouts waiting for connections
    - Slow response times

    Solutions:
    - Increase pool size
    - Reduce query duration
    - Check for connection leaks
    - Implement connection timeout

    Issue: Connection Leaks
    Symptoms:
    - Pool size grows over time
    - Eventually exhausts pool

    Solutions:
    - Always release connections
    - Use try/finally blocks
    - Enable pool logging

    Issue: Slow Connection Acquisition
    Symptoms:
    - Long wait times for connections
    - Intermittent timeouts

    Solutions:
    - Increase min pool size
    - Reduce idle timeout
    - Check network latency

    ================================================================================

Debugging Pool Issues

src/database/pool-debug.service.ts

import { Injectable, InjectDataSource } from '@nestjs/common';
import { DataSource } from 'typeorm';

@Injectable()
export class PoolDebugService {
  constructor(
    @InjectDataSource()
    private dataSource: DataSource,
  ) {}

  async diagnosePool(): Promise<{
    config: any;
    stats: any;
    issues: string[];
    recommendations: string[];
  }> {
    const driver = this.dataSource.driver as any;
    const pool = driver.pool;
    const issues: string[] = [];
    const recommendations: string[] = [];

    const config = {
      max: pool?.options?.max,
      min: pool?.options?.min,
      idleTimeoutMillis: pool?.options?.idleTimeoutMillis,
      acquireTimeoutMillis: pool?.options?.acquireTimeoutMillis,
    };

    const stats = {
      total: pool?.totalCount || 0,
      idle: pool?.idleCount || 0,
      waiting: pool?.waitingCount || 0,
      active: (pool?.totalCount || 0) - (pool?.idleCount || 0),
    };

    // Check for issues
    if (stats.waiting > 0) {
      issues.push('Connections waiting for pool');
      recommendations.push('Consider increasing pool size');
    }

    if (stats.active / stats.total > 0.8) {
      issues.push('High pool utilization');
      recommendations.push('Monitor for potential exhaustion');
    }

    if (config.max && stats.total >= config.max) {
      issues.push('Pool at maximum capacity');
      recommendations.push('Increase max pool size or optimize queries');
    }

    if (config.min === 0) {
      issues.push('No minimum connections configured');
      recommendations.push('Set min pool size to avoid cold start delays');
    }

    return {
      config,
      stats,
      issues,
      recommendations,
    };
  }
}

---

34.8 Summary

                    Connection Pool Quick Reference
    +------------------------------------------------------------------+
    |                                                                   |
    |    Configuration      | Description                              |
    |    -------------------|------------------------------------------|
    |    poolSize          | Total connections in pool                |
    |    max               | Maximum connections                      |
    |    min               | Minimum idle connections                 |
    |    idleTimeoutMillis | Time before idle connection closed      |
    |    acquireTimeoutMillis| Time to wait for connection            |
    |                                                                   |
    |    Sizing Guidelines  | Environment                              |
    |    -------------------|------------------------------------------|
    |    5-10              | Development                              |
    |    10-20             | Small production                         |
    |    20-50             | Medium production                        |
    |    50-100            | High-traffic production                  |
    |                                                                   |
    |    Best Practices     | Description                              |
    |    -------------------|------------------------------------------|
    |    Monitor pool       | Track utilization and wait times         |
    |    Handle exhaustion  | Implement retry logic                    |
    |    Size appropriately| Based on CPU cores and workload          |
    |    Use health checks  | Verify pool status                       |
    |                                                                   |
    +------------------------------------------------------------------+

---

Next Chapter

Chapter 35: Performance Monitoring

---

Last Updated: February 2026