MongoDB Connection Pooling and Performance Optimization: Production-Scale Database Connection Management and High-Performance Application Design

High-performance applications require efficient database connection management to handle concurrent user requests, maintain low latency, and scale effectively under varying load conditions. Poor connection management leads to resource exhaustion, application timeouts, and degraded user experience, particularly in microservices architectures where multiple services compete for database connections.

MongoDB connection pooling provides sophisticated connection lifecycle management with intelligent pool sizing, connection health monitoring, and automatic failover capabilities. Unlike traditional databases that require complex external connection pooling solutions, MongoDB drivers include built-in connection pool management that integrates seamlessly with MongoDB's replica sets and sharded clusters.

The Traditional Connection Management Challenge

Managing database connections efficiently in traditional environments requires complex infrastructure and careful tuning:

-- Traditional PostgreSQL connection management - complex and resource-intensive

-- Connection monitoring and management requires external tools
-- PostgreSQL connection stats (requires pg_stat_activity monitoring)
SELECT 
    pid,
    usename as username,
    application_name,
    client_addr,
    state,
    state_change,

    -- Connection timing
    backend_start,
    query_start,
    EXTRACT(EPOCH FROM (NOW() - backend_start)) as connection_age_seconds,
    EXTRACT(EPOCH FROM (NOW() - query_start)) as query_duration_seconds,

    -- Current activity
    query,
    wait_event_type,
    wait_event,

    -- Resource usage estimation
    CASE 
        WHEN state = 'active' THEN 'high'
        WHEN state = 'idle in transaction' THEN 'medium'
        WHEN state = 'idle' THEN 'low'
        ELSE 'unknown'
    END as resource_impact

FROM pg_stat_activity
WHERE pid != pg_backend_pid() -- Exclude current monitoring connection
ORDER BY backend_start DESC;

-- PostgreSQL connection limits and configuration
-- Must be configured at database server level in postgresql.conf:
-- max_connections = 200          -- Hard limit on concurrent connections
-- shared_buffers = 256MB         -- Memory allocation affects connection overhead
-- effective_cache_size = 4GB     -- Query planning memory estimates
-- work_mem = 4MB                 -- Per-operation memory limit
-- maintenance_work_mem = 256MB   -- Maintenance operation memory

-- Application-level connection pooling setup (using PgBouncer configuration)
-- /etc/pgbouncer/pgbouncer.ini configuration file:
-- [databases]
-- production_db = host=localhost port=5432 dbname=production user=app_user
-- 
-- [pgbouncer]  
-- pool_mode = transaction        -- Connection reuse strategy
-- max_client_conn = 1000         -- Maximum client connections
-- default_pool_size = 20         -- Connections per database/user
-- min_pool_size = 5              -- Minimum maintained connections
-- reserve_pool_size = 5          -- Emergency connection reserve
-- max_db_connections = 50        -- Maximum per database
-- 
-- # Connection lifecycle settings
-- server_lifetime = 3600         -- Server connection max age (seconds)
-- server_idle_timeout = 600      -- Idle server connection timeout
-- client_idle_timeout = 0        -- Client idle timeout (0 = disabled)
-- 
-- # Performance tuning
-- listen_backlog = 128           -- TCP listen queue size
-- server_connect_timeout = 15    -- Server connection timeout
-- server_login_retry = 15        -- Login retry interval

-- Complex connection pool monitoring query
WITH connection_stats AS (
    SELECT 
        datname as database_name,
        usename as username,
        application_name,
        client_addr,
        state,

        -- Connection lifecycle analysis
        CASE 
            WHEN backend_start > NOW() - INTERVAL '1 minute' THEN 'new'
            WHEN backend_start > NOW() - INTERVAL '5 minutes' THEN 'recent'
            WHEN backend_start > NOW() - INTERVAL '30 minutes' THEN 'established'
            ELSE 'long_running'
        END as connection_age_category,

        -- Query activity analysis
        CASE 
            WHEN query_start IS NULL THEN 'no_query_executed'
            WHEN query_start > NOW() - INTERVAL '1 second' THEN 'active_query'
            WHEN query_start > NOW() - INTERVAL '30 seconds' THEN 'recent_query'
            ELSE 'old_query'
        END as query_activity_category,

        -- Resource usage indicators
        CASE 
            WHEN wait_event_type = 'Lock' THEN 'blocking'
            WHEN wait_event_type = 'IO' THEN 'io_intensive'
            WHEN state = 'idle in transaction' THEN 'transaction_holding'
            WHEN state = 'active' THEN 'cpu_intensive'
            ELSE 'idle'
        END as resource_usage_pattern,

        EXTRACT(EPOCH FROM (NOW() - backend_start)) as connection_duration_seconds,
        EXTRACT(EPOCH FROM (NOW() - query_start)) as current_query_seconds

    FROM pg_stat_activity
    WHERE pid != pg_backend_pid()
),

connection_summary AS (
    SELECT 
        database_name,
        state,
        connection_age_category,
        query_activity_category,
        resource_usage_pattern,

        -- Connection counts by category
        COUNT(*) as connection_count,
        AVG(connection_duration_seconds) as avg_connection_age,
        MAX(connection_duration_seconds) as max_connection_age,
        AVG(current_query_seconds) as avg_query_duration,
        MAX(current_query_seconds) as max_query_duration,

        -- Resource impact assessment
        COUNT(*) FILTER (WHERE resource_usage_pattern = 'blocking') as blocking_connections,
        COUNT(*) FILTER (WHERE resource_usage_pattern = 'io_intensive') as io_intensive_connections,
        COUNT(*) FILTER (WHERE resource_usage_pattern = 'transaction_holding') as transaction_holding_connections,
        COUNT(*) FILTER (WHERE state = 'idle') as idle_connections,
        COUNT(*) FILTER (WHERE state = 'active') as active_connections

    FROM connection_stats
    GROUP BY database_name, state, connection_age_category, query_activity_category, resource_usage_pattern
),

database_connection_health AS (
    SELECT 
        database_name,
        SUM(connection_count) as total_connections,
        SUM(active_connections) as total_active,
        SUM(idle_connections) as total_idle,
        SUM(blocking_connections) as total_blocking,

        -- Health indicators
        ROUND(AVG(avg_connection_age)::numeric, 2) as avg_connection_age_seconds,
        ROUND(MAX(max_connection_age)::numeric, 2) as oldest_connection_seconds,
        ROUND(AVG(avg_query_duration)::numeric, 2) as avg_query_duration_seconds,

        -- Connection efficiency ratio
        ROUND(
            (SUM(active_connections)::decimal / NULLIF(SUM(connection_count), 0) * 100)::numeric, 
            2
        ) as connection_utilization_percent,

        -- Performance assessment
        CASE 
            WHEN SUM(blocking_connections) > 5 THEN 'critical_blocking'
            WHEN SUM(connection_count) > 150 THEN 'high_connection_count'
            WHEN AVG(avg_query_duration) > 30 THEN 'slow_queries'
            WHEN SUM(idle_connections)::decimal / SUM(connection_count) > 0.7 THEN 'excessive_idle'
            ELSE 'healthy'
        END as connection_health_status

    FROM connection_summary
    GROUP BY database_name
)

SELECT 
    database_name,
    total_connections,
    total_active,
    total_idle,
    connection_utilization_percent,

    -- Performance indicators
    avg_connection_age_seconds,
    avg_query_duration_seconds,
    connection_health_status,

    -- Recommendations
    CASE connection_health_status
        WHEN 'critical_blocking' THEN 'Investigate blocking queries and deadlocks'
        WHEN 'high_connection_count' THEN 'Consider connection pooling or scaling'
        WHEN 'slow_queries' THEN 'Optimize query performance and indexing'
        WHEN 'excessive_idle' THEN 'Tune connection pool idle timeout settings'
        ELSE 'Connection pool operating within normal parameters'
    END as optimization_recommendation,

    -- Connection pool sizing recommendations
    CASE 
        WHEN connection_utilization_percent > 90 THEN 'Increase pool size'
        WHEN connection_utilization_percent < 30 THEN 'Reduce pool size'
        ELSE 'Pool size appropriate'
    END as pool_sizing_recommendation

FROM database_connection_health
ORDER BY total_connections DESC;

-- Problems with traditional connection management:
-- 1. External connection pooler configuration and maintenance complexity
-- 2. Limited visibility into connection pool health and performance metrics
-- 3. Manual tuning of pool sizes and timeout configurations
-- 4. Complex failover and high availability connection management
-- 5. Difficulty coordinating connection pools across multiple application instances
-- 6. Limited integration with database cluster topology changes
-- 7. Resource overhead from maintaining separate connection pooling infrastructure
-- 8. Complex monitoring and alerting setup for connection pool health
-- 9. Manual configuration management across different environments
-- 10. Poor integration with modern microservices and containerized deployments

MongoDB provides native connection pooling with intelligent management and monitoring:

// MongoDB Connection Pooling - native high-performance connection management
const { MongoClient } = require('mongodb');

// Advanced MongoDB Connection Pool Manager
class MongoConnectionPoolManager {
  constructor() {
    this.clients = new Map();
    this.poolConfigurations = new Map();
    this.connectionMetrics = new Map();
    this.healthCheckIntervals = new Map();
  }

  async createOptimizedConnectionPools() {
    console.log('Creating optimized MongoDB connection pools...');

    // Production connection pool configuration
    const productionPoolConfig = {
      // Core pool sizing
      minPoolSize: 5,           // Minimum connections maintained
      maxPoolSize: 50,          // Maximum concurrent connections
      maxIdleTimeMS: 30000,     // 30 seconds idle timeout

      // Connection lifecycle management
      maxConnecting: 5,         // Maximum concurrent connection attempts
      serverSelectionTimeoutMS: 30000,  // Server selection timeout
      socketTimeoutMS: 45000,   // Socket operation timeout
      connectTimeoutMS: 10000,  // Initial connection timeout

      // Health monitoring
      heartbeatFrequencyMS: 10000,  // Heartbeat interval
      serverMonitoringMode: 'auto', // Automatic server monitoring

      // Performance optimization
      compressors: ['zlib'],    // Enable compression
      zlibCompressionLevel: 6,  // Compression efficiency

      // Error handling and retries
      retryWrites: true,        // Enable retryable writes
      retryReads: true,         // Enable retryable reads
      maxWriteRetries: 3,       // Maximum write retry attempts

      // Connection security
      tls: true,               // Enable TLS
      tlsInsecure: false,      // Require certificate validation

      // Application metadata
      appName: 'ProductionApp', // Application identifier
      driverInfo: {
        name: 'MongoDB Connection Pool Manager',
        version: '1.0.0'
      }
    };

    // Read-only replica connection pool (for analytics)
    const analyticsPoolConfig = {
      ...productionPoolConfig,
      minPoolSize: 2,
      maxPoolSize: 20,
      readPreference: 'secondary', // Prefer secondary for reads
      readConcern: { level: 'available' }, // Relaxed read concern
      appName: 'AnalyticsApp'
    };

    // High-throughput batch processing pool
    const batchProcessingConfig = {
      ...productionPoolConfig,
      minPoolSize: 10,
      maxPoolSize: 100,         // Higher concurrency for batch jobs
      maxIdleTimeMS: 60000,     // Longer idle timeout
      maxConnecting: 10,        // More concurrent connections
      writeConcern: { w: 1, j: false }, // Optimized write concern
      appName: 'BatchProcessor'
    };

    try {
      // Create primary production client
      const productionClient = new MongoClient(
        process.env.MONGODB_PRODUCTION_URI || 'mongodb://localhost:27017/production',
        productionPoolConfig
      );
      await productionClient.connect();
      this.clients.set('production', productionClient);
      this.poolConfigurations.set('production', productionPoolConfig);

      // Create analytics replica client
      const analyticsClient = new MongoClient(
        process.env.MONGODB_REPLICA_URI || 'mongodb://localhost:27017/production',
        analyticsPoolConfig
      );
      await analyticsClient.connect();
      this.clients.set('analytics', analyticsClient);
      this.poolConfigurations.set('analytics', analyticsPoolConfig);

      // Create batch processing client
      const batchClient = new MongoClient(
        process.env.MONGODB_BATCH_URI || 'mongodb://localhost:27017/production',
        batchProcessingConfig
      );
      await batchClient.connect();
      this.clients.set('batch', batchClient);
      this.poolConfigurations.set('batch', batchProcessingConfig);

      // Initialize connection pool monitoring
      await this.initializePoolMonitoring();

      console.log('✅ MongoDB connection pools created successfully');
      return {
        success: true,
        pools: ['production', 'analytics', 'batch'],
        configurations: Object.fromEntries(this.poolConfigurations)
      };

    } catch (error) {
      console.error('Error creating connection pools:', error);
      return { success: false, error: error.message };
    }
  }

  async initializePoolMonitoring() {
    console.log('Initializing connection pool monitoring...');

    for (const [poolName, client] of this.clients) {
      // Initialize metrics tracking
      this.connectionMetrics.set(poolName, {
        connectionEvents: [],
        performanceMetrics: {
          totalConnections: 0,
          activeConnections: 0,
          availableConnections: 0,
          connectionsCreated: 0,
          connectionsDestroyed: 0,
          operationTime: [],
          errorRate: 0
        },
        healthStatus: 'healthy'
      });

      // Set up connection pool event monitoring
      client.on('connectionPoolCreated', (event) => {
        console.log(`Pool created: ${poolName}`, event);
        this.recordPoolEvent(poolName, 'pool_created', event);
      });

      client.on('connectionCreated', (event) => {
        console.log(`Connection created: ${poolName}`, event.connectionId);
        this.recordPoolEvent(poolName, 'connection_created', event);
        this.updatePoolMetrics(poolName, 'connection_created');
      });

      client.on('connectionReady', (event) => {
        this.recordPoolEvent(poolName, 'connection_ready', event);
      });

      client.on('connectionClosed', (event) => {
        console.log(`Connection closed: ${poolName}`, event.connectionId, event.reason);
        this.recordPoolEvent(poolName, 'connection_closed', event);
        this.updatePoolMetrics(poolName, 'connection_closed');
      });

      client.on('connectionCheckOutStarted', (event) => {
        this.recordPoolEvent(poolName, 'checkout_started', event);
      });

      client.on('connectionCheckedOut', (event) => {
        this.recordPoolEvent(poolName, 'checkout_completed', event);
        this.updatePoolMetrics(poolName, 'checkout_completed');
      });

      client.on('connectionCheckedIn', (event) => {
        this.recordPoolEvent(poolName, 'checkin_completed', event);
        this.updatePoolMetrics(poolName, 'checkin_completed');
      });

      client.on('connectionCheckOutFailed', (event) => {
        console.warn(`Connection checkout failed: ${poolName}`, event.reason);
        this.recordPoolEvent(poolName, 'checkout_failed', event);
        this.updatePoolMetrics(poolName, 'checkout_failed');
      });

      // Server monitoring events
      client.on('serverOpening', (event) => {
        console.log(`Server opening: ${poolName}`, event.address);
        this.recordPoolEvent(poolName, 'server_opening', event);
      });

      client.on('serverClosed', (event) => {
        console.log(`Server closed: ${poolName}`, event.address);
        this.recordPoolEvent(poolName, 'server_closed', event);
      });

      client.on('serverDescriptionChanged', (event) => {
        this.recordPoolEvent(poolName, 'server_description_changed', event);
        this.assessPoolHealth(poolName, event);
      });

      // Set up periodic health checks
      const healthCheckInterval = setInterval(() => {
        this.performPoolHealthCheck(poolName, client);
      }, 30000); // Every 30 seconds

      this.healthCheckIntervals.set(poolName, healthCheckInterval);
    }

    console.log('✅ Connection pool monitoring initialized');
  }

  recordPoolEvent(poolName, eventType, eventData) {
    const metrics = this.connectionMetrics.get(poolName);
    if (metrics) {
      metrics.connectionEvents.push({
        timestamp: new Date(),
        type: eventType,
        data: eventData
      });

      // Keep only last 1000 events to prevent memory leaks
      if (metrics.connectionEvents.length > 1000) {
        metrics.connectionEvents = metrics.connectionEvents.slice(-1000);
      }
    }
  }

  updatePoolMetrics(poolName, eventType) {
    const metrics = this.connectionMetrics.get(poolName);
    if (!metrics) return;

    const performance = metrics.performanceMetrics;

    switch (eventType) {
      case 'connection_created':
        performance.connectionsCreated++;
        performance.totalConnections++;
        break;
      case 'connection_closed':
        performance.connectionsDestroyed++;
        performance.totalConnections = Math.max(0, performance.totalConnections - 1);
        break;
      case 'checkout_completed':
        performance.activeConnections++;
        break;
      case 'checkin_completed':
        performance.activeConnections = Math.max(0, performance.activeConnections - 1);
        break;
      case 'checkout_failed':
        performance.errorRate = (performance.errorRate * 0.95) + 0.05; // Exponential moving average
        break;
    }

    // Calculate available connections
    const poolConfig = this.poolConfigurations.get(poolName);
    if (poolConfig) {
      performance.availableConnections = Math.max(0, 
        Math.min(poolConfig.maxPoolSize, performance.totalConnections) - performance.activeConnections
      );
    }
  }

  async performPoolHealthCheck(poolName, client) {
    try {
      const startTime = Date.now();

      // Perform a lightweight operation to test connectivity
      const db = client.db('admin');
      const result = await db.command({ ping: 1 });

      const operationTime = Date.now() - startTime;

      // Record operation time for performance tracking
      const metrics = this.connectionMetrics.get(poolName);
      if (metrics) {
        metrics.performanceMetrics.operationTime.push({
          timestamp: new Date(),
          duration: operationTime
        });

        // Keep only last 100 operation times
        if (metrics.performanceMetrics.operationTime.length > 100) {
          metrics.performanceMetrics.operationTime = metrics.performanceMetrics.operationTime.slice(-100);
        }

        // Update health status based on performance
        if (operationTime > 5000) {
          metrics.healthStatus = 'degraded';
        } else if (operationTime > 1000) {
          metrics.healthStatus = 'warning';
        } else {
          metrics.healthStatus = 'healthy';
        }
      }

    } catch (error) {
      console.error(`Health check failed for pool ${poolName}:`, error);
      const metrics = this.connectionMetrics.get(poolName);
      if (metrics) {
        metrics.healthStatus = 'unhealthy';
        metrics.performanceMetrics.errorRate = Math.min(1.0, metrics.performanceMetrics.errorRate + 0.1);
      }
    }
  }

  assessPoolHealth(poolName, serverEvent) {
    const metrics = this.connectionMetrics.get(poolName);
    if (!metrics) return;

    const { newDescription } = serverEvent;

    // Assess server health based on description
    if (newDescription.type === 'Unknown' || newDescription.error) {
      metrics.healthStatus = 'unhealthy';
    } else if (newDescription.type === 'RSSecondary' || newDescription.type === 'RSPrimary') {
      metrics.healthStatus = 'healthy';
    }
  }

  async getPoolMetrics(poolName) {
    const metrics = this.connectionMetrics.get(poolName);
    const config = this.poolConfigurations.get(poolName);

    if (!metrics || !config) {
      return { error: `Pool ${poolName} not found` };
    }

    const recentEvents = metrics.connectionEvents.slice(-10); // Last 10 events
    const recentOperationTimes = metrics.performanceMetrics.operationTime.slice(-20); // Last 20 operations

    return {
      poolName: poolName,
      healthStatus: metrics.healthStatus,

      // Connection metrics
      connections: {
        total: metrics.performanceMetrics.totalConnections,
        active: metrics.performanceMetrics.activeConnections,
        available: metrics.performanceMetrics.availableConnections,
        created: metrics.performanceMetrics.connectionsCreated,
        destroyed: metrics.performanceMetrics.connectionsDestroyed,

        // Pool configuration
        minPoolSize: config.minPoolSize,
        maxPoolSize: config.maxPoolSize,
        utilization: metrics.performanceMetrics.totalConnections / config.maxPoolSize
      },

      // Performance metrics
      performance: {
        averageOperationTime: recentOperationTimes.length > 0 ?
          recentOperationTimes.reduce((sum, op) => sum + op.duration, 0) / recentOperationTimes.length : 0,
        errorRate: metrics.performanceMetrics.errorRate,
        recentOperationTimes: recentOperationTimes
      },

      // Recent events
      recentActivity: recentEvents,

      // Health recommendations
      recommendations: this.generatePoolRecommendations(metrics, config)
    };
  }

  generatePoolRecommendations(metrics, config) {
    const recommendations = [];
    const performance = metrics.performanceMetrics;

    // Pool size recommendations
    const utilization = performance.totalConnections / config.maxPoolSize;
    if (utilization > 0.9) {
      recommendations.push({
        type: 'pool_sizing',
        priority: 'high',
        message: 'Pool utilization > 90%. Consider increasing maxPoolSize.',
        suggestedValue: Math.ceil(config.maxPoolSize * 1.5)
      });
    } else if (utilization < 0.3) {
      recommendations.push({
        type: 'pool_sizing',
        priority: 'low',
        message: 'Pool utilization < 30%. Consider reducing maxPoolSize for resource efficiency.',
        suggestedValue: Math.ceil(config.maxPoolSize * 0.7)
      });
    }

    // Performance recommendations
    const avgOpTime = metrics.performanceMetrics.operationTime.length > 0 ?
      metrics.performanceMetrics.operationTime.reduce((sum, op) => sum + op.duration, 0) / 
      metrics.performanceMetrics.operationTime.length : 0;

    if (avgOpTime > 2000) {
      recommendations.push({
        type: 'performance',
        priority: 'high',
        message: 'Average operation time > 2 seconds. Check network latency and server performance.',
        currentValue: Math.round(avgOpTime)
      });
    }

    // Error rate recommendations
    if (performance.errorRate > 0.1) {
      recommendations.push({
        type: 'reliability',
        priority: 'high',
        message: 'High error rate detected. Check server connectivity and timeouts.',
        currentValue: Math.round(performance.errorRate * 100)
      });
    }

    // Health status recommendations
    if (metrics.healthStatus === 'unhealthy') {
      recommendations.push({
        type: 'health',
        priority: 'critical',
        message: 'Pool health is unhealthy. Immediate investigation required.'
      });
    }

    return recommendations.length > 0 ? recommendations : [
      { type: 'status', priority: 'info', message: 'Pool operating within normal parameters.' }
    ];
  }

  async performConnectionLoadTest(poolName, options = {}) {
    console.log(`Performing connection load test for pool: ${poolName}`);

    const {
      concurrentOperations = 20,
      operationDuration = 60000, // 1 minute
      operationType = 'ping'
    } = options;

    const client = this.clients.get(poolName);
    if (!client) {
      return { error: `Pool ${poolName} not found` };
    }

    const testStartTime = Date.now();
    const operationResults = [];
    const activeOperations = [];

    // Create concurrent operations
    for (let i = 0; i < concurrentOperations; i++) {
      const operation = this.performSingleOperation(client, operationType, i)
        .then(result => {
          operationResults.push(result);
        })
        .catch(error => {
          operationResults.push({ 
            operationId: i, 
            error: error.message, 
            success: false,
            timestamp: new Date()
          });
        });

      activeOperations.push(operation);
    }

    // Wait for all operations to complete or timeout
    try {
      await Promise.all(activeOperations);
    } catch (error) {
      console.warn('Some operations failed during load test:', error);
    }

    const testDuration = Date.now() - testStartTime;

    // Analyze results
    const successfulOperations = operationResults.filter(r => r.success);
    const failedOperations = operationResults.filter(r => !r.success);

    const loadTestResults = {
      poolName: poolName,
      testConfiguration: {
        concurrentOperations,
        operationDuration,
        operationType
      },
      results: {
        totalOperations: operationResults.length,
        successfulOperations: successfulOperations.length,
        failedOperations: failedOperations.length,
        successRate: (successfulOperations.length / operationResults.length) * 100,

        // Performance metrics
        averageResponseTime: successfulOperations.length > 0 ?
          successfulOperations.reduce((sum, op) => sum + op.responseTime, 0) / successfulOperations.length : 0,
        minResponseTime: successfulOperations.length > 0 ?
          Math.min(...successfulOperations.map(op => op.responseTime)) : 0,
        maxResponseTime: successfulOperations.length > 0 ?
          Math.max(...successfulOperations.map(op => op.responseTime)) : 0,

        // Test duration
        totalTestDuration: testDuration,
        operationsPerSecond: (operationResults.length / testDuration) * 1000
      },

      // Pool state during test
      poolMetrics: await this.getPoolMetrics(poolName),

      // Recommendations based on load test
      recommendations: this.generateLoadTestRecommendations(operationResults, concurrentOperations)
    };

    console.log(`Load test completed for ${poolName}:`, loadTestResults.results);
    return loadTestResults;
  }

  async performSingleOperation(client, operationType, operationId) {
    const startTime = Date.now();

    try {
      const db = client.db('admin');

      switch (operationType) {
        case 'ping':
          await db.command({ ping: 1 });
          break;
        case 'serverStatus':
          await db.command({ serverStatus: 1 });
          break;
        case 'listCollections':
          await db.listCollections().toArray();
          break;
        default:
          await db.command({ ping: 1 });
      }

      const responseTime = Date.now() - startTime;

      return {
        operationId,
        success: true,
        responseTime,
        timestamp: new Date()
      };

    } catch (error) {
      return {
        operationId,
        success: false,
        error: error.message,
        responseTime: Date.now() - startTime,
        timestamp: new Date()
      };
    }
  }

  generateLoadTestRecommendations(operationResults, concurrency) {
    const recommendations = [];
    const successRate = (operationResults.filter(r => r.success).length / operationResults.length) * 100;
    const avgResponseTime = operationResults
      .filter(r => r.success)
      .reduce((sum, op) => sum + op.responseTime, 0) / Math.max(1, operationResults.filter(r => r.success).length);

    if (successRate < 95) {
      recommendations.push({
        type: 'reliability',
        priority: 'high',
        message: `Success rate ${successRate.toFixed(1)}% is below target. Investigate connection failures.`
      });
    }

    if (avgResponseTime > 1000) {
      recommendations.push({
        type: 'performance', 
        priority: 'medium',
        message: `Average response time ${avgResponseTime.toFixed(0)}ms is high. Check network and server performance.`
      });
    }

    if (successRate > 99 && avgResponseTime < 100) {
      recommendations.push({
        type: 'scaling',
        priority: 'info',
        message: `Pool handles ${concurrency} concurrent operations well. Consider testing higher concurrency.`
      });
    }

    return recommendations;
  }

  async getAllPoolsStatus() {
    const poolsStatus = {};

    for (const poolName of this.clients.keys()) {
      try {
        poolsStatus[poolName] = await this.getPoolMetrics(poolName);
      } catch (error) {
        poolsStatus[poolName] = { error: error.message };
      }
    }

    return {
      timestamp: new Date(),
      pools: poolsStatus,
      summary: this.generateSystemSummary(poolsStatus)
    };
  }

  generateSystemSummary(poolsStatus) {
    const activePools = Object.keys(poolsStatus).length;
    let totalConnections = 0;
    let healthyPools = 0;
    let warnings = [];

    for (const [poolName, status] of Object.entries(poolsStatus)) {
      if (status.error) continue;

      totalConnections += status.connections?.total || 0;

      if (status.healthStatus === 'healthy') {
        healthyPools++;
      } else if (status.healthStatus !== 'healthy') {
        warnings.push(`Pool ${poolName} status: ${status.healthStatus}`);
      }

      // Check for high utilization
      if (status.connections?.utilization > 0.8) {
        warnings.push(`Pool ${poolName} utilization high: ${(status.connections.utilization * 100).toFixed(1)}%`);
      }
    }

    return {
      totalPools: activePools,
      healthyPools,
      totalConnections,
      systemHealth: healthyPools === activePools ? 'healthy' : 'degraded',
      warnings: warnings.length > 0 ? warnings : ['All systems operating normally']
    };
  }

  async shutdown() {
    console.log('Shutting down connection pool manager...');

    // Clear health check intervals
    for (const interval of this.healthCheckIntervals.values()) {
      clearInterval(interval);
    }
    this.healthCheckIntervals.clear();

    // Close all client connections
    for (const [poolName, client] of this.clients) {
      try {
        await client.close();
        console.log(`✅ Closed connection pool: ${poolName}`);
      } catch (error) {
        console.error(`Error closing pool ${poolName}:`, error);
      }
    }

    this.clients.clear();
    this.connectionMetrics.clear();
    console.log('Connection pool manager shutdown completed');
  }
}

// Export the connection pool manager
module.exports = { MongoConnectionPoolManager };

// Benefits of MongoDB Connection Pooling:
// - Native integration with MongoDB drivers eliminates external pooler complexity
// - Automatic connection lifecycle management with intelligent pool sizing
// - Built-in monitoring and health checking with comprehensive event tracking
// - Seamless integration with MongoDB replica sets and sharded clusters
// - Advanced performance optimization with compression and retry logic
// - Real-time connection pool metrics and health assessment
// - Production-ready failover and error handling capabilities
// - Integrated load testing and performance validation tools
// - Comprehensive configuration management across different environments
// - SQL-compatible connection management patterns through QueryLeaf integration

Understanding MongoDB Connection Pool Architecture

Advanced Connection Management Patterns

Implement sophisticated connection pool strategies for production deployments:

// Advanced connection pool patterns for production systems
class ProductionConnectionManager {
  constructor() {
    this.environmentPools = new Map();
    this.serviceRoutingMap = new Map();
    this.performanceProfiles = new Map();
    this.alertingSystem = null;
  }

  async initializeMultiEnvironmentPools() {
    console.log('Initializing multi-environment connection pools...');

    const environments = {
      // Production environment - high availability, strict consistency
      production: {
        primary: {
          uri: process.env.MONGODB_PRODUCTION_PRIMARY,
          options: {
            minPoolSize: 10,
            maxPoolSize: 100,
            maxIdleTimeMS: 30000,
            serverSelectionTimeoutMS: 5000,
            readPreference: 'primary',
            readConcern: { level: 'majority' },
            writeConcern: { w: 'majority', j: true },
            retryWrites: true,
            compressors: ['zlib'],
            appName: 'ProductionPrimary'
          }
        },
        secondary: {
          uri: process.env.MONGODB_PRODUCTION_SECONDARY,
          options: {
            minPoolSize: 5,
            maxPoolSize: 50,
            maxIdleTimeMS: 60000,
            readPreference: 'secondary',
            readConcern: { level: 'available' },
            writeConcern: { w: 1, j: false },
            compressors: ['zlib'],
            appName: 'ProductionSecondary'
          }
        }
      },

      // Staging environment - production-like with relaxed constraints
      staging: {
        primary: {
          uri: process.env.MONGODB_STAGING_URI,
          options: {
            minPoolSize: 3,
            maxPoolSize: 30,
            maxIdleTimeMS: 45000,
            serverSelectionTimeoutMS: 10000,
            readPreference: 'primaryPreferred',
            readConcern: { level: 'local' },
            writeConcern: { w: 'majority', j: false },
            appName: 'StagingEnvironment'
          }
        }
      },

      // Development environment - minimal resources, fast iteration
      development: {
        primary: {
          uri: process.env.MONGODB_DEV_URI || 'mongodb://localhost:27017/development',
          options: {
            minPoolSize: 1,
            maxPoolSize: 10,
            maxIdleTimeMS: 120000,
            serverSelectionTimeoutMS: 15000,
            readPreference: 'primaryPreferred',
            readConcern: { level: 'local' },
            writeConcern: { w: 1, j: false },
            appName: 'DevelopmentEnvironment'
          }
        }
      }
    };

    for (const [envName, envConfig] of Object.entries(environments)) {
      const envPools = new Map();

      for (const [poolType, poolConfig] of Object.entries(envConfig)) {
        try {
          const client = new MongoClient(poolConfig.uri, poolConfig.options);
          await client.connect();

          envPools.set(poolType, {
            client: client,
            config: poolConfig.options,
            healthStatus: 'initializing',
            createdAt: new Date()
          });

          console.log(`✅ Connected to ${envName}.${poolType}`);

        } catch (error) {
          console.error(`Failed to connect to ${envName}.${poolType}:`, error);
          envPools.set(poolType, {
            client: null,
            config: poolConfig.options,
            healthStatus: 'failed',
            error: error.message,
            createdAt: new Date()
          });
        }
      }

      this.environmentPools.set(envName, envPools);
    }

    // Initialize service-specific routing
    await this.setupServiceRouting();

    console.log('Multi-environment connection pools initialized');
    return this.getEnvironmentSummary();
  }

  async setupServiceRouting() {
    console.log('Setting up service-specific connection routing...');

    // Define service routing patterns
    const serviceRoutingConfig = {
      // Write-heavy services use primary connections
      'user-service': { 
        environment: 'production', 
        pool: 'primary',
        profile: 'write-heavy'
      },
      'order-service': { 
        environment: 'production', 
        pool: 'primary',
        profile: 'transactional'
      },

      // Read-heavy services can use secondary connections
      'analytics-service': { 
        environment: 'production', 
        pool: 'secondary',
        profile: 'read-heavy'
      },
      'reporting-service': { 
        environment: 'production', 
        pool: 'secondary',
        profile: 'batch-read'
      },

      // Development services
      'test-service': { 
        environment: 'development', 
        pool: 'primary',
        profile: 'development'
      }
    };

    for (const [serviceName, routing] of Object.entries(serviceRoutingConfig)) {
      this.serviceRoutingMap.set(serviceName, routing);
    }

    // Define performance profiles for different service types
    this.performanceProfiles.set('write-heavy', {
      expectedOperationsPerSecond: 1000,
      maxAcceptableLatency: 50,
      errorThreshold: 0.01,
      poolUtilizationTarget: 0.7
    });

    this.performanceProfiles.set('read-heavy', {
      expectedOperationsPerSecond: 5000,
      maxAcceptableLatency: 20,
      errorThreshold: 0.005,
      poolUtilizationTarget: 0.8
    });

    this.performanceProfiles.set('transactional', {
      expectedOperationsPerSecond: 500,
      maxAcceptableLatency: 100,
      errorThreshold: 0.001,
      poolUtilizationTarget: 0.6
    });

    this.performanceProfiles.set('batch-read', {
      expectedOperationsPerSecond: 100,
      maxAcceptableLatency: 1000,
      errorThreshold: 0.05,
      poolUtilizationTarget: 0.9
    });

    console.log('Service routing configuration completed');
  }

  getConnectionForService(serviceName) {
    const routing = this.serviceRoutingMap.get(serviceName);
    if (!routing) {
      throw new Error(`No routing configuration found for service: ${serviceName}`);
    }

    const envPools = this.environmentPools.get(routing.environment);
    if (!envPools) {
      throw new Error(`Environment ${routing.environment} not found`);
    }

    const poolInfo = envPools.get(routing.pool);
    if (!poolInfo || !poolInfo.client) {
      throw new Error(`Pool ${routing.pool} not available in ${routing.environment}`);
    }

    if (poolInfo.healthStatus !== 'healthy' && poolInfo.healthStatus !== 'initializing') {
      console.warn(`Using potentially unhealthy connection for ${serviceName}: ${poolInfo.healthStatus}`);
    }

    return {
      client: poolInfo.client,
      routing: routing,
      profile: this.performanceProfiles.get(routing.profile),
      poolInfo: poolInfo
    };
  }

  async performComprehensiveHealthCheck() {
    console.log('Performing comprehensive health check across all connection pools...');

    const healthReport = {
      timestamp: new Date(),
      environments: {},
      overallHealth: 'healthy',
      criticalIssues: [],
      warnings: [],
      recommendations: []
    };

    for (const [envName, envPools] of this.environmentPools) {
      const envHealth = {
        pools: {},
        healthStatus: 'healthy',
        totalConnections: 0,
        activeConnections: 0
      };

      for (const [poolType, poolInfo] of envPools) {
        if (!poolInfo.client) {
          envHealth.pools[poolType] = {
            status: 'failed',
            error: poolInfo.error || 'Client not initialized',
            lastChecked: new Date()
          };
          envHealth.healthStatus = 'degraded';
          continue;
        }

        try {
          const startTime = Date.now();
          const db = poolInfo.client.db('admin');

          // Perform health check operations
          await db.command({ ping: 1 });
          const serverStatus = await db.command({ serverStatus: 1 });
          const responseTime = Date.now() - startTime;

          // Extract connection metrics from server status
          const connections = serverStatus.connections || {};
          const opcounters = serverStatus.opcounters || {};

          envHealth.pools[poolType] = {
            status: responseTime < 1000 ? 'healthy' : 'slow',
            responseTime: responseTime,
            connections: {
              current: connections.current || 0,
              available: connections.available || 0,
              totalCreated: connections.totalCreated || 0
            },
            operations: {
              insert: opcounters.insert || 0,
              query: opcounters.query || 0,
              update: opcounters.update || 0,
              delete: opcounters.delete || 0
            },
            serverInfo: {
              version: serverStatus.version,
              uptime: serverStatus.uptime,
              host: serverStatus.host
            },
            lastChecked: new Date()
          };

          envHealth.totalConnections += connections.current || 0;

          if (responseTime > 2000) {
            healthReport.warnings.push(`Slow response time in ${envName}.${poolType}: ${responseTime}ms`);
          }

          if ((connections.available || 0) < 10) {
            healthReport.criticalIssues.push(`Low available connections in ${envName}.${poolType}: ${connections.available}`);
            envHealth.healthStatus = 'critical';
          }

        } catch (error) {
          envHealth.pools[poolType] = {
            status: 'unhealthy',
            error: error.message,
            lastChecked: new Date()
          };
          envHealth.healthStatus = 'degraded';
          healthReport.criticalIssues.push(`Health check failed for ${envName}.${poolType}: ${error.message}`);
        }
      }

      healthReport.environments[envName] = envHealth;
    }

    // Determine overall system health
    const hasCriticalIssues = healthReport.criticalIssues.length > 0;
    const hasWarnings = healthReport.warnings.length > 0;

    if (hasCriticalIssues) {
      healthReport.overallHealth = 'critical';
    } else if (hasWarnings) {
      healthReport.overallHealth = 'warning';
    } else {
      healthReport.overallHealth = 'healthy';
    }

    // Generate recommendations
    healthReport.recommendations = this.generateSystemRecommendations(healthReport);

    console.log(`Health check completed. Overall status: ${healthReport.overallHealth}`);
    return healthReport;
  }

  generateSystemRecommendations(healthReport) {
    const recommendations = [];

    // Check for connection pool sizing issues
    for (const [envName, envHealth] of Object.entries(healthReport.environments)) {
      for (const [poolType, poolHealth] of Object.entries(envHealth.pools)) {
        if (poolHealth.status === 'healthy' && poolHealth.connections) {
          const utilization = poolHealth.connections.current / 
            (poolHealth.connections.current + poolHealth.connections.available);

          if (utilization > 0.9) {
            recommendations.push({
              type: 'scaling',
              priority: 'high',
              environment: envName,
              pool: poolType,
              message: `Pool utilization ${(utilization * 100).toFixed(1)}% is very high. Consider increasing pool size.`
            });
          } else if (utilization < 0.2) {
            recommendations.push({
              type: 'optimization',
              priority: 'low',
              environment: envName,
              pool: poolType,
              message: `Pool utilization ${(utilization * 100).toFixed(1)}% is low. Consider reducing pool size for efficiency.`
            });
          }
        }
      }
    }

    // Performance-based recommendations
    if (healthReport.overallHealth === 'warning') {
      recommendations.push({
        type: 'monitoring',
        priority: 'medium',
        message: 'System has performance warnings. Increase monitoring frequency and consider scaling.'
      });
    }

    if (healthReport.criticalIssues.length > 0) {
      recommendations.push({
        type: 'immediate_action',
        priority: 'critical',
        message: 'Critical issues detected. Immediate investigation and resolution required.'
      });
    }

    return recommendations.length > 0 ? recommendations : [
      { type: 'status', priority: 'info', message: 'All connection pools operating optimally.' }
    ];
  }

  getEnvironmentSummary() {
    const summary = {
      environments: Array.from(this.environmentPools.keys()),
      totalPools: 0,
      healthyPools: 0,
      services: Array.from(this.serviceRoutingMap.keys()),
      profiles: Array.from(this.performanceProfiles.keys())
    };

    for (const envPools of this.environmentPools.values()) {
      for (const poolInfo of envPools.values()) {
        summary.totalPools++;
        if (poolInfo.healthStatus === 'healthy' || poolInfo.healthStatus === 'initializing') {
          summary.healthyPools++;
        }
      }
    }

    return summary;
  }
}

// Export the production connection manager
module.exports = { ProductionConnectionManager };

SQL-Style Connection Management with QueryLeaf

QueryLeaf provides familiar SQL syntax for MongoDB connection pool configuration and monitoring:

-- QueryLeaf connection pool management with SQL-familiar syntax

-- Configure connection pool settings
SET connection_pool_min_size = 5;
SET connection_pool_max_size = 50;
SET connection_pool_idle_timeout = 30000; -- milliseconds
SET connection_pool_server_selection_timeout = 10000;

-- Connection pool status monitoring
SELECT 
  pool_name,
  environment,

  -- Connection metrics
  total_connections,
  active_connections,
  available_connections,

  -- Pool configuration
  min_pool_size,
  max_pool_size,
  idle_timeout_ms,

  -- Performance metrics
  ROUND(connection_utilization::NUMERIC * 100, 2) as utilization_percent,
  ROUND(avg_operation_time::NUMERIC, 2) as avg_operation_time_ms,

  -- Health status
  health_status,
  last_health_check,

  -- Connection lifecycle
  connections_created,
  connections_destroyed,

  -- Error metrics
  ROUND(error_rate::NUMERIC * 100, 4) as error_rate_percent,
  checkout_failures,

  -- Status classification
  CASE 
    WHEN health_status = 'healthy' AND connection_utilization < 0.8 THEN 'optimal'
    WHEN health_status = 'healthy' AND connection_utilization >= 0.8 THEN 'high_load'
    WHEN health_status = 'warning' THEN 'needs_attention'  
    WHEN health_status = 'unhealthy' THEN 'critical'
    ELSE 'unknown'
  END as pool_status_category

FROM mongodb_connection_pools
ORDER BY environment, pool_name;

-- Connection pool performance analysis over time
WITH pool_metrics_hourly AS (
  SELECT 
    pool_name,
    DATE_TRUNC('hour', metric_timestamp) as hour_bucket,

    -- Aggregated metrics
    AVG(active_connections) as avg_active_connections,
    MAX(active_connections) as peak_active_connections,
    AVG(connection_utilization) as avg_utilization,
    MAX(connection_utilization) as peak_utilization,

    -- Performance indicators
    AVG(avg_operation_time) as avg_operation_time,
    PERCENTILE_CONT(0.95) WITHIN GROUP (ORDER BY avg_operation_time) as p95_operation_time,

    -- Error rates
    AVG(error_rate) as avg_error_rate,
    SUM(checkout_failures) as total_checkout_failures,

    -- Connection lifecycle
    SUM(connections_created) as connections_created_hourly,
    SUM(connections_destroyed) as connections_destroyed_hourly

  FROM connection_pool_metrics
  WHERE metric_timestamp >= CURRENT_TIMESTAMP - INTERVAL '24 hours'
  GROUP BY pool_name, DATE_TRUNC('hour', metric_timestamp)
),

performance_trends AS (
  SELECT 
    *,
    -- Calculate hourly trends
    LAG(avg_utilization) OVER (PARTITION BY pool_name ORDER BY hour_bucket) as prev_utilization,
    LAG(avg_operation_time) OVER (PARTITION BY pool_name ORDER BY hour_bucket) as prev_operation_time,

    -- Performance change indicators
    CASE 
      WHEN avg_utilization - LAG(avg_utilization) OVER (PARTITION BY pool_name ORDER BY hour_bucket) > 0.1 
      THEN 'utilization_spike'
      WHEN avg_operation_time - LAG(avg_operation_time) OVER (PARTITION BY pool_name ORDER BY hour_bucket) > 100
      THEN 'latency_spike'
      ELSE 'stable'
    END as performance_change

  FROM pool_metrics_hourly
)

SELECT 
  pool_name,
  TO_CHAR(hour_bucket, 'YYYY-MM-DD HH24:00') as analysis_hour,

  -- Connection utilization
  ROUND(avg_utilization::NUMERIC * 100, 1) as avg_utilization_pct,
  ROUND(peak_utilization::NUMERIC * 100, 1) as peak_utilization_pct,

  -- Connection activity
  ROUND(avg_active_connections::NUMERIC, 1) as avg_active_connections,
  peak_active_connections,

  -- Performance metrics  
  ROUND(avg_operation_time::NUMERIC, 2) as avg_operation_time_ms,
  ROUND(p95_operation_time::NUMERIC, 2) as p95_operation_time_ms,

  -- Reliability metrics
  ROUND(avg_error_rate::NUMERIC * 100, 4) as avg_error_rate_pct,
  total_checkout_failures,

  -- Connection churn
  connections_created_hourly,
  connections_destroyed_hourly,
  connections_created_hourly - connections_destroyed_hourly as net_connection_change,

  -- Performance analysis
  performance_change,

  -- Health assessment
  CASE 
    WHEN avg_error_rate > 0.01 THEN 'high_error_rate'
    WHEN peak_utilization > 0.9 THEN 'utilization_critical'
    WHEN avg_operation_time > 500 THEN 'high_latency'
    WHEN total_checkout_failures > 10 THEN 'checkout_issues'
    ELSE 'healthy'
  END as health_indicator,

  -- Optimization recommendations
  CASE 
    WHEN peak_utilization > 0.9 AND performance_change = 'utilization_spike' 
    THEN 'Increase pool size immediately'
    WHEN avg_operation_time > 1000
    THEN 'Investigate database performance'
    WHEN total_checkout_failures > 50
    THEN 'Review timeout configuration'
    WHEN avg_utilization < 0.2 AND connections_created_hourly < 5
    THEN 'Consider reducing pool size'
    ELSE 'Performance within acceptable ranges'
  END as optimization_recommendation

FROM performance_trends
WHERE hour_bucket >= CURRENT_TIMESTAMP - INTERVAL '24 hours'
ORDER BY pool_name, hour_bucket DESC;

-- Connection pool load testing and capacity planning
CREATE VIEW connection_pool_load_test AS
WITH load_test_scenarios AS (
  SELECT 
    scenario_name,
    concurrent_connections,
    operation_type,
    test_duration_minutes,
    target_operations_per_second
  FROM (VALUES 
    ('normal_load', 20, 'mixed', 10, 100),
    ('peak_load', 50, 'mixed', 10, 500),
    ('stress_test', 100, 'write_heavy', 5, 1000),
    ('sustained_read', 30, 'read_only', 30, 200)
  ) AS scenarios(scenario_name, concurrent_connections, operation_type, test_duration_minutes, target_operations_per_second)
),

load_test_results AS (
  SELECT 
    ltr.pool_name,
    ltr.scenario_name,
    lts.concurrent_connections,
    lts.operation_type,
    lts.test_duration_minutes,

    -- Performance results
    ltr.actual_operations_per_second,
    ltr.success_rate,
    ltr.avg_response_time_ms,
    ltr.p95_response_time_ms,
    ltr.max_response_time_ms,

    -- Resource utilization during test
    ltr.peak_connections_used,
    ltr.avg_connections_used,
    ltr.connection_utilization_peak,

    -- Error analysis
    ltr.total_errors,
    ltr.timeout_errors,
    ltr.connection_errors,

    -- Performance vs target
    ROUND((ltr.actual_operations_per_second / lts.target_operations_per_second::DECIMAL * 100)::NUMERIC, 1) 
      as performance_vs_target_pct,

    -- Load test assessment
    CASE 
      WHEN ltr.success_rate >= 99.5 AND ltr.avg_response_time_ms <= 100 THEN 'excellent'
      WHEN ltr.success_rate >= 95 AND ltr.avg_response_time_ms <= 500 THEN 'good'
      WHEN ltr.success_rate >= 90 AND ltr.avg_response_time_ms <= 1000 THEN 'acceptable'
      ELSE 'poor'
    END as performance_rating

  FROM load_test_results ltr
  JOIN load_test_scenarios lts ON ltr.scenario_name = lts.scenario_name
)

SELECT 
  pool_name,
  scenario_name,
  concurrent_connections,
  operation_type,

  -- Performance metrics
  actual_operations_per_second,
  performance_vs_target_pct,
  ROUND(success_rate::NUMERIC, 2) as success_rate_pct,

  -- Response time analysis
  avg_response_time_ms,
  p95_response_time_ms,
  max_response_time_ms,

  -- Resource utilization
  peak_connections_used,
  ROUND(connection_utilization_peak::NUMERIC * 100, 1) as peak_utilization_pct,

  -- Error analysis
  total_errors,
  timeout_errors,
  connection_errors,

  -- Performance assessment
  performance_rating,

  -- Capacity recommendations
  CASE performance_rating
    WHEN 'excellent' THEN 
      CONCAT('Pool can handle ', concurrent_connections + 20, ' concurrent connections')
    WHEN 'good' THEN 
      'Current capacity appropriate for this load pattern'
    WHEN 'acceptable' THEN
      'Monitor closely under sustained load'
    ELSE 
      CONCAT('Increase pool size or optimize operations for ', concurrent_connections, ' concurrent users')
  END as capacity_recommendation,

  -- Scaling suggestions
  CASE 
    WHEN connection_utilization_peak > 0.9 AND performance_rating IN ('good', 'excellent')
    THEN 'Pool size optimally configured'
    WHEN connection_utilization_peak > 0.9 AND performance_rating = 'poor'
    THEN 'Increase pool size significantly'
    WHEN connection_utilization_peak < 0.5
    THEN 'Pool may be oversized for this workload'
    ELSE 'Pool sizing appears appropriate'
  END as sizing_recommendation

FROM load_test_results
ORDER BY pool_name, concurrent_connections;

-- Real-time connection pool alerting
CREATE VIEW connection_pool_alerts AS
WITH current_pool_status AS (
  SELECT 
    pool_name,
    environment,
    health_status,
    connection_utilization,
    avg_operation_time,
    error_rate,
    available_connections,
    checkout_failures,
    last_health_check,

    -- Time since last health check
    EXTRACT(EPOCH FROM (CURRENT_TIMESTAMP - last_health_check)) as seconds_since_health_check

  FROM mongodb_connection_pools
  WHERE enabled = true
),

alert_conditions AS (
  SELECT 
    *,
    -- Define alert conditions
    CASE 
      WHEN health_status = 'unhealthy' THEN 'CRITICAL'
      WHEN connection_utilization > 0.95 THEN 'CRITICAL'
      WHEN available_connections < 2 THEN 'CRITICAL'
      WHEN seconds_since_health_check > 300 THEN 'CRITICAL' -- 5 minutes
      WHEN error_rate > 0.05 THEN 'HIGH'
      WHEN connection_utilization > 0.85 THEN 'HIGH'
      WHEN avg_operation_time > 2000 THEN 'HIGH'
      WHEN checkout_failures > 10 THEN 'MEDIUM'
      WHEN connection_utilization > 0.75 THEN 'MEDIUM'
      WHEN avg_operation_time > 1000 THEN 'MEDIUM'
      ELSE 'LOW'
    END as alert_severity,

    -- Generate alert messages
    ARRAY_REMOVE(ARRAY[
      CASE WHEN health_status = 'unhealthy' THEN 'Pool health check failing' END,
      CASE WHEN connection_utilization > 0.95 THEN 'Connection utilization critical' END,
      CASE WHEN available_connections < 2 THEN 'Available connections critically low' END,
      CASE WHEN error_rate > 0.05 THEN 'High error rate detected' END,
      CASE WHEN avg_operation_time > 2000 THEN 'High operation latency' END,
      CASE WHEN checkout_failures > 10 THEN 'Connection checkout failures' END,
      CASE WHEN seconds_since_health_check > 300 THEN 'Health check timeout' END
    ], NULL) as alert_reasons

  FROM current_pool_status
)

SELECT 
  pool_name,
  environment,
  alert_severity,
  alert_reasons,

  -- Current metrics for context
  ROUND(connection_utilization::NUMERIC * 100, 1) as utilization_pct,
  available_connections,
  ROUND(avg_operation_time::NUMERIC, 0) as avg_operation_time_ms,
  ROUND(error_rate::NUMERIC * 100, 2) as error_rate_pct,
  checkout_failures,

  -- Time context
  TO_CHAR(last_health_check, 'YYYY-MM-DD HH24:MI:SS') as last_health_check,
  ROUND(seconds_since_health_check::NUMERIC, 0) as seconds_since_health_check,

  -- Alert priority for incident management
  CASE alert_severity
    WHEN 'CRITICAL' THEN 1
    WHEN 'HIGH' THEN 2  
    WHEN 'MEDIUM' THEN 3
    ELSE 4
  END as alert_priority,

  -- Immediate action recommendations
  CASE alert_severity
    WHEN 'CRITICAL' THEN 'Immediate investigation required - potential service impact'
    WHEN 'HIGH' THEN 'Investigation needed within 15 minutes'
    WHEN 'MEDIUM' THEN 'Review within 1 hour'
    ELSE 'Monitor - no immediate action required'
  END as action_required

FROM alert_conditions
WHERE alert_severity != 'LOW'
ORDER BY alert_priority, pool_name;

-- QueryLeaf provides comprehensive connection pool management:
-- 1. SQL-familiar configuration syntax for pool sizing and timeouts
-- 2. Real-time monitoring with performance metrics and health indicators
-- 3. Historical analysis with trend detection and capacity planning
-- 4. Load testing capabilities with automated performance assessment
-- 5. Intelligent alerting with severity classification and action recommendations
-- 6. Multi-environment pool management with service routing optimization
-- 7. Production-ready monitoring with comprehensive error tracking
-- 8. Automated recommendations for scaling and optimization decisions
-- 9. Integration with MongoDB's native connection pool capabilities
-- 10. Enterprise-grade connection management with familiar SQL patterns

Best Practices for MongoDB Connection Pool Implementation

Connection Pool Sizing and Configuration

Essential practices for production connection pool deployments:

Right-Size Pool Limits: Configure minPoolSize and maxPoolSize based on actual concurrent load patterns
Timeout Management: Set appropriate timeouts for connection creation, idle time, and server selection
Environment-Specific Tuning: Use different pool configurations for production, staging, and development environments
Monitoring Integration: Implement comprehensive monitoring with health checks and performance metrics
Failover Planning: Configure connection pools to handle replica set failovers gracefully
Resource Optimization: Balance connection pool sizes with available system resources and database capacity

Performance Optimization Strategies

Optimize connection pools for maximum application performance:

Connection Reuse: Design application patterns that maximize connection reuse and minimize churn
Read Preference Strategy: Use appropriate read preferences to distribute load across replica set members
Write Concern Optimization: Configure write concerns that balance durability requirements with performance
Compression Settings: Enable compression for high-latency networks to improve throughput
Application-Level Pooling: Implement service-specific connection routing for optimal resource utilization
Load Testing: Regularly validate pool performance under realistic load conditions

Conclusion

MongoDB connection pooling provides comprehensive database connection management that eliminates the complexity and overhead of external connection pooling solutions. The integration of intelligent pool sizing, automatic health monitoring, and seamless replica set integration enables high-performance applications that scale efficiently with growing user demands.

Key MongoDB connection pooling benefits include:

Native Integration: Built-in connection pool management in MongoDB drivers eliminates external infrastructure
Intelligent Sizing: Automatic pool sizing based on application load with configurable limits and behaviors
Health Monitoring: Real-time connection health tracking with automatic failover and recovery capabilities
Performance Optimization: Advanced features like compression, retry logic, and read preference routing
Production Ready: Enterprise-grade monitoring, alerting, and capacity planning capabilities
SQL Compatibility: Familiar connection management patterns accessible through SQL-style operations

Whether you're building microservices architectures, high-throughput web applications, or data-intensive analytics platforms, MongoDB connection pooling with QueryLeaf's SQL-familiar interface provides the foundation for scalable database connectivity that maintains high performance while simplifying operational complexity.

QueryLeaf Integration: QueryLeaf automatically manages MongoDB connection pools while providing SQL-familiar syntax for pool configuration, monitoring, and optimization. Advanced connection management patterns, load testing capabilities, and production-ready alerting are seamlessly accessible through familiar SQL constructs, making sophisticated database connection management both powerful and approachable for SQL-oriented teams.

The combination of MongoDB's intelligent connection pooling with familiar SQL-style management makes it an ideal platform for applications that require both high-performance database connectivity and operational simplicity, ensuring your database infrastructure scales efficiently while maintaining familiar development and operational patterns.