MongoDB Connection Pooling Optimization Strategies: Advanced Connection Management and Performance Tuning for High-Throughput Applications

High-throughput database applications require sophisticated connection management strategies and comprehensive pooling optimization techniques that can handle concurrent request patterns, varying workload demands, and complex scaling requirements while maintaining optimal performance and resource utilization. Traditional database connection approaches often struggle with dynamic scaling, connection overhead management, and the complexity of balancing connection availability with resource consumption, leading to performance bottlenecks, resource exhaustion, and operational challenges in production environments.

MongoDB provides comprehensive connection pooling capabilities through intelligent connection management, sophisticated monitoring features, and optimized driver implementations that enable applications to achieve maximum throughput with minimal connection overhead. Unlike traditional databases that require manual connection tuning procedures and complex pooling configuration, MongoDB drivers integrate advanced pooling algorithms directly with automatic connection scaling, real-time performance monitoring, and intelligent connection lifecycle management.

The Traditional Connection Management Challenge

Conventional approaches to database connection management in enterprise applications face significant limitations in scalability and resource optimization:

-- Traditional PostgreSQL connection management - manual pooling with limited optimization capabilities

-- Basic connection tracking table with minimal functionality
CREATE TABLE connection_pool_stats (
    pool_id SERIAL PRIMARY KEY,
    application_name VARCHAR(100) NOT NULL,
    database_name VARCHAR(100) NOT NULL,
    host_address VARCHAR(255) NOT NULL,
    port_number INTEGER DEFAULT 5432,

    -- Basic pool configuration (static settings)
    min_connections INTEGER DEFAULT 5,
    max_connections INTEGER DEFAULT 20,
    connection_timeout INTEGER DEFAULT 30,
    idle_timeout INTEGER DEFAULT 600,

    -- Simple usage statistics (very limited visibility)
    active_connections INTEGER DEFAULT 0,
    idle_connections INTEGER DEFAULT 0,
    total_connections_created BIGINT DEFAULT 0,
    total_connections_destroyed BIGINT DEFAULT 0,

    -- Basic performance metrics
    avg_connection_wait_time DECIMAL(8,3),
    max_connection_wait_time DECIMAL(8,3),
    connection_failures BIGINT DEFAULT 0,

    -- Manual tracking timestamps
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    last_analyzed TIMESTAMP
);

-- Query execution tracking table (basic functionality)
CREATE TABLE query_execution_log (
    execution_id SERIAL PRIMARY KEY,
    pool_id INTEGER REFERENCES connection_pool_stats(pool_id),
    session_id VARCHAR(100),

    -- Query identification
    query_hash VARCHAR(64),
    query_type VARCHAR(50), -- SELECT, INSERT, UPDATE, DELETE
    query_text TEXT, -- Usually truncated for storage

    -- Basic timing information
    start_time TIMESTAMP NOT NULL,
    end_time TIMESTAMP,
    execution_duration DECIMAL(10,3),

    -- Connection usage
    connection_acquired_at TIMESTAMP,
    connection_released_at TIMESTAMP,
    connection_wait_time DECIMAL(8,3),

    -- Simple result metrics
    rows_affected INTEGER,
    rows_returned INTEGER,

    -- Status tracking
    execution_status VARCHAR(20), -- success, error, timeout
    error_message TEXT,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Manual connection pool configuration (static and inflexible)
CREATE TABLE pool_configuration (
    config_id SERIAL PRIMARY KEY,
    pool_name VARCHAR(100) UNIQUE NOT NULL,

    -- Static configuration parameters
    initial_pool_size INTEGER DEFAULT 5,
    maximum_pool_size INTEGER DEFAULT 50,
    minimum_idle_connections INTEGER DEFAULT 2,

    -- Timeout settings (fixed values)
    connection_timeout_seconds INTEGER DEFAULT 30,
    idle_connection_timeout_seconds INTEGER DEFAULT 1800,
    validation_timeout_seconds INTEGER DEFAULT 5,

    -- Simple retry configuration
    max_retry_attempts INTEGER DEFAULT 3,
    retry_delay_seconds INTEGER DEFAULT 1,

    -- Basic health check
    validation_query VARCHAR(500) DEFAULT 'SELECT 1',
    validate_on_borrow BOOLEAN DEFAULT true,
    validate_on_return BOOLEAN DEFAULT false,

    -- Manual maintenance
    test_while_idle BOOLEAN DEFAULT true,
    time_between_eviction_runs INTEGER DEFAULT 300,
    num_tests_per_eviction_run INTEGER DEFAULT 3,

    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Complex query to analyze connection performance (expensive and limited)
WITH connection_performance AS (
    SELECT 
        cps.application_name,
        cps.database_name,
        cps.host_address,

        -- Basic pool utilization
        CASE 
            WHEN cps.max_connections > 0 THEN 
                (cps.active_connections::DECIMAL / cps.max_connections) * 100
            ELSE 0 
        END as pool_utilization_percent,

        -- Simple connection metrics
        cps.active_connections,
        cps.idle_connections,
        cps.total_connections_created,
        cps.total_connections_destroyed,

        -- Basic performance statistics
        cps.avg_connection_wait_time,
        cps.max_connection_wait_time,
        cps.connection_failures,

        -- Query performance (limited aggregation)
        COUNT(qel.execution_id) as total_queries_24h,
        AVG(qel.execution_duration) as avg_query_duration,
        AVG(qel.connection_wait_time) as avg_connection_wait,

        -- Simple error tracking
        COUNT(CASE WHEN qel.execution_status = 'error' THEN 1 END) as error_count,
        COUNT(CASE WHEN qel.execution_status = 'timeout' THEN 1 END) as timeout_count,

        -- Basic connection efficiency (limited insights)
        CASE 
            WHEN COUNT(qel.execution_id) > 0 THEN
                COUNT(CASE WHEN qel.connection_wait_time < 0.100 THEN 1 END)::DECIMAL / 
                COUNT(qel.execution_id) * 100
            ELSE 0
        END as fast_connection_percentage

    FROM connection_pool_stats cps
    LEFT JOIN query_execution_log qel ON cps.pool_id = qel.pool_id
        AND qel.start_time >= CURRENT_TIMESTAMP - INTERVAL '24 hours'

    WHERE cps.last_updated >= CURRENT_TIMESTAMP - INTERVAL '1 hour'

    GROUP BY cps.pool_id, cps.application_name, cps.database_name, 
             cps.host_address, cps.active_connections, cps.idle_connections,
             cps.max_connections, cps.total_connections_created, 
             cps.total_connections_destroyed, cps.avg_connection_wait_time,
             cps.max_connection_wait_time, cps.connection_failures
),

pool_health_analysis AS (
    SELECT *,
        -- Simple health scoring (limited factors)
        CASE 
            WHEN pool_utilization_percent > 90 THEN 'Critical'
            WHEN pool_utilization_percent > 75 THEN 'Warning'
            WHEN avg_connection_wait > 1.0 THEN 'Warning'
            WHEN error_count > total_queries_24h * 0.05 THEN 'Warning'
            ELSE 'Healthy'
        END as pool_health_status,

        -- Basic recommendation logic (very limited)
        CASE 
            WHEN pool_utilization_percent > 85 THEN 
                'Consider increasing max_connections'
            WHEN avg_connection_wait > 0.5 THEN 
                'Review connection timeout settings'
            WHEN error_count > 10 THEN 
                'Investigate connection failures'
            ELSE 'Pool configuration appears adequate'
        END as basic_recommendation

    FROM connection_performance
)

SELECT 
    application_name,
    database_name,
    host_address,

    -- Pool status overview
    pool_utilization_percent,
    pool_health_status,
    active_connections,
    idle_connections,

    -- Performance metrics
    total_queries_24h,
    avg_query_duration,
    avg_connection_wait,
    fast_connection_percentage,

    -- Error tracking
    error_count,
    timeout_count,

    -- Basic recommendations
    basic_recommendation,

    CURRENT_TIMESTAMP as analysis_timestamp

FROM pool_health_analysis
ORDER BY 
    CASE pool_health_status
        WHEN 'Critical' THEN 1
        WHEN 'Warning' THEN 2
        ELSE 3
    END,
    pool_utilization_percent DESC;

-- Problems with traditional connection pooling approach:
-- 1. Static configuration cannot adapt to changing workloads
-- 2. Limited visibility into connection lifecycle and performance
-- 3. Manual tuning required for optimal performance
-- 4. No automatic scaling based on demand patterns
-- 5. Basic health checking with limited diagnostic capabilities
-- 6. Inefficient connection distribution across database instances
-- 7. No built-in monitoring for connection pool performance
-- 8. Difficult to troubleshoot connection-related performance issues
-- 9. Limited integration with application performance monitoring
-- 10. Manual intervention required for pool optimization

MongoDB's intelligent connection pooling eliminates these limitations:

// MongoDB optimized connection pooling - intelligent and performance-focused
// Advanced connection management with automatic optimization

const { MongoClient } = require('mongodb');

// Comprehensive connection pool configuration
class MongoConnectionPoolManager {
  constructor(connectionUri, options = {}) {
    this.connectionUri = connectionUri;
    this.poolOptions = {
      // Intelligent pool sizing
      minPoolSize: options.minPoolSize || 5,
      maxPoolSize: options.maxPoolSize || 100,
      maxIdleTimeMS: options.maxIdleTimeMS || 30000,

      // Advanced connection management
      waitQueueTimeoutMS: options.waitQueueTimeoutMS || 2500,
      serverSelectionTimeoutMS: options.serverSelectionTimeoutMS || 5000,
      socketTimeoutMS: options.socketTimeoutMS || 45000,
      connectTimeoutMS: options.connectTimeoutMS || 10000,

      // Intelligent retry logic
      retryWrites: true,
      retryReads: true,
      maxStalenessSeconds: options.maxStalenessSeconds || 90,

      // Advanced monitoring capabilities
      monitorCommands: true,

      // Intelligent load balancing
      loadBalanced: options.loadBalanced || false,

      // Connection compression
      compressors: options.compressors || ['snappy', 'zlib'],

      // SSL/TLS optimization
      ssl: options.ssl || true,
      sslValidate: options.sslValidate || true,

      // Advanced read preferences
      readPreference: options.readPreference || 'secondaryPreferred',
      readConcern: { level: options.readConcernLevel || 'majority' },

      // Write concern optimization
      writeConcern: {
        w: options.writeConcernW || 'majority',
        j: options.writeConcernJ || true,
        wtimeout: options.writeConcernTimeout || 5000
      }
    };

    this.client = null;
    this.connectionMetrics = new Map();
    this.performanceStats = {
      totalConnections: 0,
      activeConnections: 0,
      connectionWaitTimes: [],
      queryExecutionTimes: [],
      connectionErrors: 0,
      poolHealthScore: 100
    };
  }

  async initializeConnectionPool() {
    try {
      console.log('Initializing MongoDB connection pool with intelligent optimization...');

      // Create client with advanced pooling options
      this.client = new MongoClient(this.connectionUri, this.poolOptions);

      // Set up comprehensive event listeners for monitoring
      this.setupConnectionMonitoring();

      // Connect with retry logic and health checking
      await this.connectWithHealthCheck();

      // Initialize performance monitoring
      this.startPerformanceMonitoring();

      // Setup automatic pool optimization
      this.setupAutomaticOptimization();

      console.log('MongoDB connection pool initialized successfully');
      return this.client;

    } catch (error) {
      console.error('Failed to initialize MongoDB connection pool:', error);
      throw error;
    }
  }

  setupConnectionMonitoring() {
    // Connection pool monitoring events
    this.client.on('connectionPoolCreated', (event) => {
      console.log(`Connection pool created: ${event.address}`);
      this.logPoolEvent('pool_created', event);
    });

    this.client.on('connectionPoolReady', (event) => {
      console.log(`Connection pool ready: ${event.address}`);
      this.logPoolEvent('pool_ready', event);
    });

    this.client.on('connectionCreated', (event) => {
      this.performanceStats.totalConnections++;
      this.logPoolEvent('connection_created', event);
    });

    this.client.on('connectionReady', (event) => {
      this.performanceStats.activeConnections++;
      this.logPoolEvent('connection_ready', event);
    });

    this.client.on('connectionClosed', (event) => {
      this.performanceStats.activeConnections = Math.max(0, this.performanceStats.activeConnections - 1);
      this.logPoolEvent('connection_closed', event);
    });

    this.client.on('connectionCheckOutStarted', (event) => {
      event.startTime = Date.now();
      this.logPoolEvent('checkout_started', event);
    });

    this.client.on('connectionCheckedOut', (event) => {
      const waitTime = event.startTime ? Date.now() - event.startTime : 0;
      this.performanceStats.connectionWaitTimes.push(waitTime);
      this.logPoolEvent('connection_checked_out', { ...event, waitTime });
    });

    this.client.on('connectionCheckedIn', (event) => {
      this.logPoolEvent('connection_checked_in', event);
    });

    // Command monitoring for performance analysis
    this.client.on('commandStarted', (event) => {
      event.startTime = Date.now();
      this.logCommandEvent('command_started', event);
    });

    this.client.on('commandSucceeded', (event) => {
      const executionTime = event.startTime ? Date.now() - event.startTime : 0;
      this.performanceStats.queryExecutionTimes.push(executionTime);
      this.logCommandEvent('command_succeeded', { ...event, executionTime });
    });

    this.client.on('commandFailed', (event) => {
      this.performanceStats.connectionErrors++;
      const executionTime = event.startTime ? Date.now() - event.startTime : 0;
      this.logCommandEvent('command_failed', { ...event, executionTime });
    });

    // Server monitoring for intelligent scaling
    this.client.on('serverHeartbeatStarted', (event) => {
      this.logServerEvent('heartbeat_started', event);
    });

    this.client.on('serverHeartbeatSucceeded', (event) => {
      this.logServerEvent('heartbeat_succeeded', event);
    });

    this.client.on('serverHeartbeatFailed', (event) => {
      this.logServerEvent('heartbeat_failed', event);
    });
  }

  async connectWithHealthCheck() {
    const maxRetries = 3;
    let retryCount = 0;

    while (retryCount < maxRetries) {
      try {
        await this.client.connect();

        // Perform health check
        const healthCheck = await this.performHealthCheck();
        if (healthCheck.healthy) {
          console.log('Connection pool health check passed');
          return;
        } else {
          throw new Error(`Health check failed: ${healthCheck.issues.join(', ')}`);
        }

      } catch (error) {
        retryCount++;
        console.error(`Connection attempt ${retryCount} failed:`, error.message);

        if (retryCount >= maxRetries) {
          throw error;
        }

        // Exponential backoff
        await new Promise(resolve => setTimeout(resolve, Math.pow(2, retryCount) * 1000));
      }
    }
  }

  async performHealthCheck() {
    try {
      // Test basic connectivity
      const admin = this.client.db('admin');
      const pingResult = await admin.command({ ping: 1 });

      // Test read operations
      const testDb = this.client.db('test');
      await testDb.collection('healthcheck').findOne({}, { maxTimeMS: 5000 });

      // Check connection pool stats
      const poolStats = await this.getConnectionPoolStats();

      const issues = [];

      // Analyze pool health
      if (poolStats.availableConnections < 2) {
        issues.push('Low available connections');
      }

      if (poolStats.averageWaitTime > 1000) {
        issues.push('High average connection wait time');
      }

      if (poolStats.errorRate > 0.05) {
        issues.push('High error rate detected');
      }

      return {
        healthy: issues.length === 0,
        issues: issues,
        timestamp: new Date(),
        poolStats: poolStats
      };

    } catch (error) {
      return {
        healthy: false,
        issues: [`Health check error: ${error.message}`],
        timestamp: new Date()
      };
    }
  }

  startPerformanceMonitoring() {
    // Real-time performance monitoring
    setInterval(async () => {
      try {
        const stats = await this.getDetailedPerformanceStats();
        this.analyzePerformanceTrends(stats);
        this.updatePoolHealthScore(stats);

        // Log performance summary
        console.log(`Pool Performance - Health: ${this.performanceStats.poolHealthScore}%, ` +
                   `Active: ${stats.activeConnections}, ` +
                   `Avg Wait: ${stats.averageWaitTime}ms, ` +
                   `Avg Query: ${stats.averageQueryTime}ms`);

      } catch (error) {
        console.error('Performance monitoring error:', error);
      }
    }, 30000); // Every 30 seconds
  }

  async getDetailedPerformanceStats() {
    const now = Date.now();
    const fiveMinutesAgo = now - (5 * 60 * 1000);

    // Filter recent metrics
    const recentWaitTimes = this.performanceStats.connectionWaitTimes
      .filter(time => time.timestamp > fiveMinutesAgo);
    const recentQueryTimes = this.performanceStats.queryExecutionTimes
      .filter(time => time.timestamp > fiveMinutesAgo);

    const stats = {
      timestamp: now,
      totalConnections: this.performanceStats.totalConnections,
      activeConnections: this.performanceStats.activeConnections,

      // Connection timing analysis
      averageWaitTime: this.calculateAverage(recentWaitTimes.map(t => t.value)),
      maxWaitTime: Math.max(...(recentWaitTimes.map(t => t.value) || [0])),
      p95WaitTime: this.calculatePercentile(recentWaitTimes.map(t => t.value), 0.95),

      // Query performance analysis
      averageQueryTime: this.calculateAverage(recentQueryTimes.map(t => t.value)),
      maxQueryTime: Math.max(...(recentQueryTimes.map(t => t.value) || [0])),
      p95QueryTime: this.calculatePercentile(recentQueryTimes.map(t => t.value), 0.95),

      // Error analysis
      errorRate: this.calculateErrorRate(fiveMinutesAgo),
      connectionErrors: this.performanceStats.connectionErrors,

      // Pool utilization
      poolUtilization: (this.performanceStats.activeConnections / this.poolOptions.maxPoolSize) * 100,

      // Connection efficiency
      connectionEfficiency: this.calculateConnectionEfficiency(recentWaitTimes),

      // Server health indicators
      serverHealth: await this.getServerHealthIndicators()
    };

    return stats;
  }

  setupAutomaticOptimization() {
    // Intelligent pool optimization based on performance metrics
    setInterval(async () => {
      try {
        const stats = await this.getDetailedPerformanceStats();
        const optimizations = this.generateOptimizationRecommendations(stats);

        if (optimizations.length > 0) {
          console.log('Applying automatic optimizations:', optimizations);
          await this.applyOptimizations(optimizations);
        }

      } catch (error) {
        console.error('Automatic optimization error:', error);
      }
    }, 300000); // Every 5 minutes
  }

  generateOptimizationRecommendations(stats) {
    const recommendations = [];

    // High utilization optimization
    if (stats.poolUtilization > 85) {
      recommendations.push({
        type: 'increase_pool_size',
        current: this.poolOptions.maxPoolSize,
        recommended: Math.min(this.poolOptions.maxPoolSize * 1.2, 200),
        reason: 'High pool utilization detected'
      });
    }

    // High wait time optimization
    if (stats.averageWaitTime > 500) {
      recommendations.push({
        type: 'reduce_idle_timeout',
        current: this.poolOptions.maxIdleTimeMS,
        recommended: Math.max(this.poolOptions.maxIdleTimeMS * 0.8, 10000),
        reason: 'High connection wait times detected'
      });
    }

    // Low utilization optimization
    if (stats.poolUtilization < 20 && this.poolOptions.maxPoolSize > 20) {
      recommendations.push({
        type: 'decrease_pool_size',
        current: this.poolOptions.maxPoolSize,
        recommended: Math.max(this.poolOptions.maxPoolSize * 0.8, 10),
        reason: 'Low pool utilization detected'
      });
    }

    // Error rate optimization
    if (stats.errorRate > 0.05) {
      recommendations.push({
        type: 'increase_timeout',
        current: this.poolOptions.serverSelectionTimeoutMS,
        recommended: this.poolOptions.serverSelectionTimeoutMS * 1.5,
        reason: 'High error rate suggests timeout issues'
      });
    }

    return recommendations;
  }

  async applyOptimizations(optimizations) {
    for (const optimization of optimizations) {
      try {
        switch (optimization.type) {
          case 'increase_pool_size':
            // Note: Pool size changes require connection recreation
            console.log(`Recommending pool size increase from ${optimization.current} to ${optimization.recommended}`);
            break;

          case 'decrease_pool_size':
            console.log(`Recommending pool size decrease from ${optimization.current} to ${optimization.recommended}`);
            break;

          case 'reduce_idle_timeout':
            console.log(`Recommending idle timeout reduction from ${optimization.current} to ${optimization.recommended}`);
            break;

          case 'increase_timeout':
            console.log(`Recommending timeout increase from ${optimization.current} to ${optimization.recommended}`);
            break;
        }

        // Log optimization for operational tracking
        this.logOptimization(optimization);

      } catch (error) {
        console.error(`Failed to apply optimization ${optimization.type}:`, error);
      }
    }
  }

  async getConnectionPoolStats() {
    return {
      totalConnections: this.performanceStats.totalConnections,
      activeConnections: this.performanceStats.activeConnections,
      availableConnections: this.poolOptions.maxPoolSize - this.performanceStats.activeConnections,
      maxPoolSize: this.poolOptions.maxPoolSize,
      minPoolSize: this.poolOptions.minPoolSize,

      // Recent performance metrics
      averageWaitTime: this.calculateAverage(
        this.performanceStats.connectionWaitTimes
          .slice(-100)
          .map(t => t.value || t)
      ),

      averageQueryTime: this.calculateAverage(
        this.performanceStats.queryExecutionTimes
          .slice(-100)
          .map(t => t.value || t)
      ),

      errorRate: this.calculateErrorRate(Date.now() - (60 * 60 * 1000)), // Last hour
      poolHealthScore: this.performanceStats.poolHealthScore
    };
  }

  // Utility methods for calculations
  calculateAverage(values) {
    if (!values || values.length === 0) return 0;
    return values.reduce((sum, val) => sum + val, 0) / values.length;
  }

  calculatePercentile(values, percentile) {
    if (!values || values.length === 0) return 0;
    const sorted = [...values].sort((a, b) => a - b);
    const index = Math.ceil(sorted.length * percentile) - 1;
    return sorted[index] || 0;
  }

  calculateErrorRate(since) {
    const totalQueries = this.performanceStats.queryExecutionTimes
      .filter(t => t.timestamp > since).length;
    const errors = this.performanceStats.connectionErrors;
    return totalQueries > 0 ? errors / totalQueries : 0;
  }

  calculateConnectionEfficiency(waitTimes) {
    if (!waitTimes || waitTimes.length === 0) return 100;
    const fastConnections = waitTimes.filter(t => t.value < 100).length;
    return (fastConnections / waitTimes.length) * 100;
  }

  async getServerHealthIndicators() {
    try {
      const admin = this.client.db('admin');
      const serverStatus = await admin.command({ serverStatus: 1 });

      return {
        uptime: serverStatus.uptime,
        connections: serverStatus.connections,
        opcounters: serverStatus.opcounters,
        mem: serverStatus.mem,
        globalLock: serverStatus.globalLock
      };
    } catch (error) {
      console.error('Failed to get server health indicators:', error);
      return null;
    }
  }

  updatePoolHealthScore(stats) {
    let score = 100;

    // Penalize high utilization
    if (stats.poolUtilization > 90) score -= 30;
    else if (stats.poolUtilization > 75) score -= 15;

    // Penalize high wait times
    if (stats.averageWaitTime > 1000) score -= 25;
    else if (stats.averageWaitTime > 500) score -= 10;

    // Penalize errors
    if (stats.errorRate > 0.05) score -= 20;
    else if (stats.errorRate > 0.02) score -= 10;

    // Penalize low efficiency
    if (stats.connectionEfficiency < 70) score -= 15;
    else if (stats.connectionEfficiency < 85) score -= 5;

    this.performanceStats.poolHealthScore = Math.max(0, Math.min(100, score));
  }

  logPoolEvent(eventType, event) {
    this.connectionMetrics.set(`${eventType}_${Date.now()}`, {
      type: eventType,
      timestamp: new Date(),
      ...event
    });
  }

  logCommandEvent(eventType, event) {
    // Store command execution metrics
    const timestamp = new Date();

    if (eventType === 'command_succeeded' && event.executionTime !== undefined) {
      this.performanceStats.queryExecutionTimes.push({
        value: event.executionTime,
        timestamp: timestamp.getTime()
      });
    }

    // Keep only recent metrics to prevent memory growth
    if (this.performanceStats.queryExecutionTimes.length > 10000) {
      this.performanceStats.queryExecutionTimes = 
        this.performanceStats.queryExecutionTimes.slice(-5000);
    }
  }

  logServerEvent(eventType, event) {
    // Log server-level events for health monitoring
    console.log(`Server event: ${eventType}`, {
      timestamp: new Date(),
      ...event
    });
  }

  logOptimization(optimization) {
    console.log('Optimization Applied:', {
      timestamp: new Date(),
      ...optimization
    });
  }

  // Graceful shutdown
  async shutdown() {
    console.log('Shutting down MongoDB connection pool...');

    try {
      if (this.client) {
        await this.client.close(true); // Force close all connections
        console.log('MongoDB connection pool shut down successfully');
      }
    } catch (error) {
      console.error('Error during connection pool shutdown:', error);
    }
  }
}

// Example usage with intelligent configuration
async function createOptimizedMongoConnection() {
  const connectionManager = new MongoConnectionPoolManager(
    'mongodb://localhost:27017/production_db',
    {
      // Intelligent pool sizing based on application type
      minPoolSize: 10,           // Minimum connections for baseline performance
      maxPoolSize: 100,          // Maximum connections for peak load
      maxIdleTimeMS: 30000,      // 30 seconds idle timeout

      // Optimized timeouts for production
      waitQueueTimeoutMS: 2500,        // 2.5 seconds max wait for connection
      serverSelectionTimeoutMS: 5000,  // 5 seconds for server selection
      socketTimeoutMS: 45000,          // 45 seconds for socket operations
      connectTimeoutMS: 10000,         // 10 seconds connection timeout

      // Performance optimizations
      compressors: ['snappy', 'zlib'],
      loadBalanced: true,              // Enable load balancing
      readPreference: 'secondaryPreferred',
      readConcernLevel: 'majority',

      // Write concern for consistency
      writeConcernW: 'majority',
      writeConcernJ: true,
      writeConcernTimeout: 5000
    }
  );

  try {
    const client = await connectionManager.initializeConnectionPool();

    // Return both client and manager for full control
    return {
      client,
      manager: connectionManager
    };

  } catch (error) {
    console.error('Failed to create optimized MongoDB connection:', error);
    throw error;
  }
}

// Benefits of MongoDB intelligent connection pooling:
// - Automatic connection scaling based on demand
// - Real-time performance monitoring and optimization
// - Intelligent retry logic with exponential backoff
// - Advanced health checking and diagnostic capabilities
// - Built-in connection efficiency analysis
// - Automatic pool optimization based on performance metrics
// - Comprehensive event tracking for troubleshooting
// - Native integration with MongoDB driver optimizations
// - Load balancing and failover support
// - Zero-downtime connection management

Advanced Connection Pool Optimization Techniques

Strategic connection management patterns for production-grade performance:

// Advanced MongoDB connection pooling patterns for enterprise applications
class EnterpriseConnectionManager {
  constructor() {
    this.connectionPools = new Map();
    this.routingStrategies = new Map();
    this.performanceMetrics = new Map();
    this.healthCheckers = new Map();
  }

  // Multi-tier connection pooling strategy
  async createTieredConnectionPools(configurations) {
    const poolTiers = {
      // High-priority pool for critical operations
      critical: {
        minPoolSize: 15,
        maxPoolSize: 50,
        maxIdleTimeMS: 10000,
        waitQueueTimeoutMS: 1000,
        priority: 'high'
      },

      // Standard pool for regular operations
      standard: {
        minPoolSize: 10,
        maxPoolSize: 75,
        maxIdleTimeMS: 30000,
        waitQueueTimeoutMS: 2500,
        priority: 'normal'
      },

      // Batch pool for background operations
      batch: {
        minPoolSize: 5,
        maxPoolSize: 25,
        maxIdleTimeMS: 60000,
        waitQueueTimeoutMS: 10000,
        priority: 'low'
      },

      // Analytics pool for reporting queries
      analytics: {
        minPoolSize: 3,
        maxPoolSize: 20,
        maxIdleTimeMS: 120000,
        waitQueueTimeoutMS: 30000,
        readPreference: 'secondary',
        priority: 'analytics'
      }
    };

    for (const [tierName, config] of Object.entries(poolTiers)) {
      try {
        const connectionManager = new MongoConnectionPoolManager(
          configurations[tierName]?.uri || configurations.default.uri,
          {
            ...config,
            ...configurations[tierName]
          }
        );

        const poolInfo = await connectionManager.initializeConnectionPool();

        this.connectionPools.set(tierName, {
          manager: connectionManager,
          client: poolInfo,
          config: config,
          createdAt: new Date(),
          lastHealthCheck: null
        });

        console.log(`Initialized ${tierName} connection pool with ${config.maxPoolSize} max connections`);

      } catch (error) {
        console.error(`Failed to create ${tierName} connection pool:`, error);
        throw error;
      }
    }
  }

  // Intelligent connection routing based on operation type
  getConnectionForOperation(operationType, priority = 'normal') {
    const routingRules = {
      'user_query': priority === 'high' ? 'critical' : 'standard',
      'admin_operation': 'critical',
      'bulk_insert': 'batch',
      'bulk_update': 'batch',
      'reporting_query': 'analytics',
      'aggregation': priority === 'high' ? 'standard' : 'analytics',
      'index_operation': 'batch',
      'backup_operation': 'batch',
      'monitoring_query': 'analytics'
    };

    const preferredPool = routingRules[operationType] || 'standard';
    const poolInfo = this.connectionPools.get(preferredPool);

    if (poolInfo && this.isPoolHealthy(preferredPool)) {
      return poolInfo.client;
    }

    // Fallback to standard pool if preferred pool is unavailable
    const fallbackPool = this.connectionPools.get('standard');
    if (fallbackPool && this.isPoolHealthy('standard')) {
      console.warn(`Using fallback pool for ${operationType} (preferred: ${preferredPool})`);
      return fallbackPool.client;
    }

    throw new Error('No healthy connection pools available');
  }

  // Advanced performance monitoring across all pools
  async getComprehensivePerformanceReport() {
    const report = {
      timestamp: new Date(),
      overallHealth: 'unknown',
      pools: {},
      recommendations: [],
      alerts: []
    };

    let totalHealthScore = 0;
    let poolCount = 0;

    for (const [poolName, poolInfo] of this.connectionPools.entries()) {
      try {
        const stats = await poolInfo.manager.getDetailedPerformanceStats();

        report.pools[poolName] = {
          ...stats,
          configuration: poolInfo.config,
          uptime: Date.now() - poolInfo.createdAt.getTime(),
          healthStatus: this.calculatePoolHealth(stats)
        };

        totalHealthScore += stats.poolHealthScore || 0;
        poolCount++;

        // Generate pool-specific recommendations
        const poolRecommendations = this.generatePoolRecommendations(poolName, stats);
        report.recommendations.push(...poolRecommendations);

        // Check for alerts
        const alerts = this.checkForAlerts(poolName, stats);
        report.alerts.push(...alerts);

      } catch (error) {
        report.pools[poolName] = {
          error: error.message,
          healthStatus: 'unhealthy'
        };

        report.alerts.push({
          severity: 'critical',
          pool: poolName,
          message: `Pool health check failed: ${error.message}`
        });
      }
    }

    // Calculate overall health
    report.overallHealth = poolCount > 0 ? 
      (totalHealthScore / poolCount > 80 ? 'healthy' : 
       totalHealthScore / poolCount > 60 ? 'warning' : 'critical') : 'unknown';

    return report;
  }

  isPoolHealthy(poolName) {
    const poolInfo = this.connectionPools.get(poolName);
    if (!poolInfo) return false;

    // Simple health check - can be enhanced with more sophisticated logic
    return poolInfo.manager.performanceStats.poolHealthScore > 50;
  }

  calculatePoolHealth(stats) {
    if (stats.poolHealthScore >= 80) return 'healthy';
    if (stats.poolHealthScore >= 60) return 'warning';
    return 'critical';
  }

  generatePoolRecommendations(poolName, stats) {
    const recommendations = [];

    // High utilization recommendations
    if (stats.poolUtilization > 85) {
      recommendations.push({
        pool: poolName,
        type: 'capacity',
        severity: 'high',
        message: `${poolName} pool utilization is ${stats.poolUtilization.toFixed(1)}% - consider increasing pool size`,
        suggestedAction: `Increase maxPoolSize from ${stats.maxPoolSize} to ${Math.ceil(stats.maxPoolSize * 1.3)}`
      });
    }

    // Performance recommendations
    if (stats.averageWaitTime > 1000) {
      recommendations.push({
        pool: poolName,
        type: 'performance',
        severity: 'medium',
        message: `${poolName} pool has high average wait time: ${stats.averageWaitTime.toFixed(1)}ms`,
        suggestedAction: 'Review connection timeout settings and pool sizing'
      });
    }

    // Error rate recommendations
    if (stats.errorRate > 0.05) {
      recommendations.push({
        pool: poolName,
        type: 'reliability',
        severity: 'high',
        message: `${poolName} pool has high error rate: ${(stats.errorRate * 100).toFixed(1)}%`,
        suggestedAction: 'Investigate connection failures and server health'
      });
    }

    return recommendations;
  }

  checkForAlerts(poolName, stats) {
    const alerts = [];

    // Critical utilization alert
    if (stats.poolUtilization > 95) {
      alerts.push({
        severity: 'critical',
        pool: poolName,
        type: 'utilization',
        message: `${poolName} pool utilization critical: ${stats.poolUtilization.toFixed(1)}%`,
        threshold: 95,
        currentValue: stats.poolUtilization
      });
    }

    // High error rate alert
    if (stats.errorRate > 0.1) {
      alerts.push({
        severity: 'critical',
        pool: poolName,
        type: 'error_rate',
        message: `${poolName} pool error rate critical: ${(stats.errorRate * 100).toFixed(1)}%`,
        threshold: 10,
        currentValue: stats.errorRate * 100
      });
    }

    // Connection timeout alert
    if (stats.p95WaitTime > 5000) {
      alerts.push({
        severity: 'warning',
        pool: poolName,
        type: 'latency',
        message: `${poolName} pool 95th percentile wait time high: ${stats.p95WaitTime.toFixed(1)}ms`,
        threshold: 5000,
        currentValue: stats.p95WaitTime
      });
    }

    return alerts;
  }

  // Automatic pool rebalancing based on usage patterns
  async rebalanceConnectionPools() {
    console.log('Starting automatic pool rebalancing...');

    const report = await this.getComprehensivePerformanceReport();

    for (const [poolName, poolStats] of Object.entries(report.pools)) {
      if (poolStats.error) continue;

      const rebalanceActions = this.calculateRebalanceActions(poolName, poolStats);

      for (const action of rebalanceActions) {
        await this.executeRebalanceAction(poolName, action);
      }
    }

    console.log('Pool rebalancing completed');
  }

  calculateRebalanceActions(poolName, stats) {
    const actions = [];

    // Pool size adjustments
    if (stats.poolUtilization > 80 && stats.maxPoolSize < 200) {
      actions.push({
        type: 'increase_pool_size',
        currentSize: stats.maxPoolSize,
        newSize: Math.min(Math.ceil(stats.maxPoolSize * 1.2), 200),
        reason: 'High utilization'
      });
    } else if (stats.poolUtilization < 30 && stats.maxPoolSize > 10) {
      actions.push({
        type: 'decrease_pool_size',
        currentSize: stats.maxPoolSize,
        newSize: Math.max(Math.ceil(stats.maxPoolSize * 0.8), 10),
        reason: 'Low utilization'
      });
    }

    return actions;
  }

  async executeRebalanceAction(poolName, action) {
    console.log(`Executing rebalance action for ${poolName}:`, action);

    // Note: Actual implementation would require careful coordination
    // to avoid disrupting active connections
    switch (action.type) {
      case 'increase_pool_size':
        console.log(`Would increase ${poolName} pool size from ${action.currentSize} to ${action.newSize}`);
        break;

      case 'decrease_pool_size':
        console.log(`Would decrease ${poolName} pool size from ${action.currentSize} to ${action.newSize}`);
        break;
    }
  }

  // Graceful shutdown of all connection pools
  async shutdownAllPools() {
    console.log('Shutting down all connection pools...');

    const shutdownPromises = [];

    for (const [poolName, poolInfo] of this.connectionPools.entries()) {
      shutdownPromises.push(
        poolInfo.manager.shutdown()
          .catch(error => console.error(`Error shutting down ${poolName} pool:`, error))
      );
    }

    await Promise.all(shutdownPromises);
    this.connectionPools.clear();

    console.log('All connection pools shut down successfully');
  }
}

SQL-Style Connection Pool Management with QueryLeaf

QueryLeaf provides familiar approaches to MongoDB connection pool configuration and monitoring:

-- QueryLeaf connection pool management with SQL-familiar syntax

-- Configure connection pool settings
CONFIGURE CONNECTION POOL production_pool WITH (
  min_connections = 10,
  max_connections = 100,
  connection_timeout = 10000,
  idle_timeout = 30000,
  wait_queue_timeout = 2500,

  -- Advanced settings
  retry_writes = true,
  retry_reads = true,
  compression = ['snappy', 'zlib'],
  load_balanced = true,

  -- Read preferences
  read_preference = 'secondaryPreferred',
  read_concern_level = 'majority',

  -- Write concern
  write_concern_w = 'majority',
  write_concern_j = true,
  write_concern_timeout = 5000
);

-- Monitor connection pool performance
SELECT 
  pool_name,
  active_connections,
  idle_connections,
  total_connections,

  -- Performance metrics
  avg_connection_wait_time_ms,
  max_connection_wait_time_ms,
  p95_connection_wait_time_ms,

  -- Query performance
  avg_query_execution_time_ms,
  queries_per_second,

  -- Health indicators
  pool_utilization_percent,
  error_rate_percent,
  health_score,

  -- Efficiency metrics
  connection_efficiency_percent,
  throughput_score,

  last_updated

FROM CONNECTION_POOL_STATS('production_pool')
WHERE timestamp >= NOW() - INTERVAL '1 hour';

-- Analyze connection pool trends
WITH pool_performance_trends AS (
  SELECT 
    DATE_TRUNC('minute', timestamp) as minute_bucket,

    -- Connection metrics
    AVG(active_connections) as avg_active_connections,
    MAX(active_connections) as max_active_connections,
    AVG(pool_utilization_percent) as avg_utilization,

    -- Performance metrics
    AVG(avg_connection_wait_time_ms) as avg_wait_time,
    AVG(avg_query_execution_time_ms) as avg_query_time,
    SUM(queries_per_second) as total_qps,

    -- Health metrics
    AVG(health_score) as avg_health_score,
    AVG(error_rate_percent) as avg_error_rate,
    COUNT(*) as measurement_count

  FROM CONNECTION_POOL_STATS('production_pool')
  WHERE timestamp >= NOW() - INTERVAL '24 hours'
  GROUP BY DATE_TRUNC('minute', timestamp)
),

performance_analysis AS (
  SELECT *,
    -- Trend analysis
    LAG(avg_utilization, 5) OVER (ORDER BY minute_bucket) as utilization_5min_ago,
    LAG(avg_wait_time, 10) OVER (ORDER BY minute_bucket) as wait_time_10min_ago,

    -- Performance scoring
    CASE 
      WHEN avg_health_score >= 90 THEN 'Excellent'
      WHEN avg_health_score >= 80 THEN 'Good'
      WHEN avg_health_score >= 60 THEN 'Fair'
      ELSE 'Poor'
    END as performance_grade,

    -- Utilization trends
    CASE 
      WHEN avg_utilization > LAG(avg_utilization, 5) OVER (ORDER BY minute_bucket) + 10 
        THEN 'Increasing'
      WHEN avg_utilization < LAG(avg_utilization, 5) OVER (ORDER BY minute_bucket) - 10 
        THEN 'Decreasing'
      ELSE 'Stable'
    END as utilization_trend

  FROM pool_performance_trends
)

SELECT 
  minute_bucket,
  avg_active_connections,
  max_active_connections,
  avg_utilization,
  avg_wait_time,
  avg_query_time,
  total_qps,
  performance_grade,
  utilization_trend,
  avg_health_score

FROM performance_analysis
WHERE minute_bucket >= NOW() - INTERVAL '4 hours'
ORDER BY minute_bucket DESC;

-- Connection pool optimization recommendations
WITH current_performance AS (
  SELECT 
    pool_name,
    active_connections,
    max_connections,
    pool_utilization_percent,
    avg_connection_wait_time_ms,
    error_rate_percent,
    health_score,
    queries_per_second

  FROM CONNECTION_POOL_STATS('production_pool')
  WHERE timestamp >= NOW() - INTERVAL '5 minutes'
  ORDER BY timestamp DESC
  LIMIT 1
),

optimization_analysis AS (
  SELECT *,
    -- Pool sizing recommendations
    CASE 
      WHEN pool_utilization_percent > 85 THEN 
        CONCAT('Increase max_connections from ', max_connections, ' to ', CEIL(max_connections * 1.3))
      WHEN pool_utilization_percent < 30 AND max_connections > 20 THEN 
        CONCAT('Decrease max_connections from ', max_connections, ' to ', GREATEST(CEIL(max_connections * 0.8), 20))
      ELSE 'Pool size appears optimal'
    END as pool_sizing_recommendation,

    -- Timeout recommendations
    CASE 
      WHEN avg_connection_wait_time_ms > 1000 THEN 'Consider increasing connection timeout or pool size'
      WHEN avg_connection_wait_time_ms < 50 THEN 'Connection timeouts are optimal'
      ELSE 'Connection timeouts are acceptable'
    END as timeout_recommendation,

    -- Performance recommendations
    CASE 
      WHEN error_rate_percent > 5 THEN 'Investigate connection errors - check server health and network'
      WHEN health_score < 70 THEN 'Pool performance needs attention - review metrics and configuration'
      WHEN queries_per_second > 1000 AND pool_utilization_percent > 80 THEN 'High throughput with high utilization - consider scaling'
      ELSE 'Performance appears satisfactory'
    END as performance_recommendation,

    -- Priority scoring
    CASE 
      WHEN pool_utilization_percent > 90 OR error_rate_percent > 10 THEN 'Critical'
      WHEN pool_utilization_percent > 75 OR avg_connection_wait_time_ms > 500 THEN 'High'
      WHEN health_score < 80 THEN 'Medium'
      ELSE 'Low'
    END as optimization_priority

  FROM current_performance
)

SELECT 
  pool_name,

  -- Current status
  CONCAT(active_connections, '/', max_connections) as connection_usage,
  ROUND(pool_utilization_percent, 1) as utilization_percent,
  ROUND(avg_connection_wait_time_ms, 1) as avg_wait_ms,
  ROUND(error_rate_percent, 2) as error_rate_percent,
  ROUND(health_score, 1) as health_score,

  -- Recommendations
  pool_sizing_recommendation,
  timeout_recommendation,
  performance_recommendation,
  optimization_priority,

  -- Action items
  CASE 
    WHEN optimization_priority = 'Critical' THEN 'Immediate action required'
    WHEN optimization_priority = 'High' THEN 'Schedule optimization within 24 hours'
    WHEN optimization_priority = 'Medium' THEN 'Plan optimization within 1 week'
    ELSE 'Monitor and review monthly'
  END as recommended_timeline,

  NOW() as analysis_timestamp

FROM optimization_analysis;

-- Automated pool health monitoring with alerts
CREATE ALERT CONNECTION_POOL_HEALTH_MONITOR
ON CONNECTION_POOL_STATS('production_pool')
WHEN (
  pool_utilization_percent > 90 OR
  avg_connection_wait_time_ms > 2000 OR
  error_rate_percent > 5 OR
  health_score < 70
)
NOTIFY ['[email protected]', '[email protected]']
WITH MESSAGE TEMPLATE '''
Connection Pool Alert: {{ pool_name }}

Current Status:
- Utilization: {{ pool_utilization_percent }}%
- Active Connections: {{ active_connections }}/{{ max_connections }}
- Average Wait Time: {{ avg_connection_wait_time_ms }}ms
- Error Rate: {{ error_rate_percent }}%
- Health Score: {{ health_score }}

Recommended Actions:
{{ pool_sizing_recommendation }}
{{ timeout_recommendation }}
{{ performance_recommendation }}

Dashboard: https://monitoring.company.com/mongodb/pools/{{ pool_name }}
'''
EVERY 5 MINUTES;

-- Historical connection pool analysis
SELECT 
  DATE(timestamp) as analysis_date,

  -- Daily aggregates
  AVG(pool_utilization_percent) as avg_daily_utilization,
  MAX(pool_utilization_percent) as peak_daily_utilization,
  AVG(avg_connection_wait_time_ms) as avg_daily_wait_time,
  MAX(active_connections) as peak_daily_connections,

  -- Performance indicators
  AVG(health_score) as avg_daily_health_score,
  MIN(health_score) as lowest_daily_health_score,
  AVG(queries_per_second) as avg_daily_qps,
  MAX(queries_per_second) as peak_daily_qps,

  -- Issue tracking
  COUNT(CASE WHEN error_rate_percent > 1 THEN 1 END) as error_incidents,
  COUNT(CASE WHEN pool_utilization_percent > 85 THEN 1 END) as high_utilization_incidents,

  -- Efficiency metrics
  AVG(connection_efficiency_percent) as avg_connection_efficiency,
  AVG(throughput_score) as avg_throughput_score

FROM CONNECTION_POOL_STATS('production_pool')
WHERE timestamp >= NOW() - INTERVAL '30 days'
GROUP BY DATE(timestamp)
ORDER BY analysis_date DESC;

-- QueryLeaf connection pooling provides:
-- 1. SQL-familiar pool configuration and management
-- 2. Comprehensive performance monitoring and analysis
-- 3. Intelligent optimization recommendations
-- 4. Automated health monitoring and alerting
-- 5. Historical trend analysis and capacity planning
-- 6. Integration with MongoDB's native pooling features
-- 7. Real-time performance metrics and diagnostics
-- 8. Automated scaling recommendations based on usage patterns
-- 9. Multi-tier pooling strategies for different workload types
-- 10. Enterprise-grade monitoring and operational visibility

Best Practices for MongoDB Connection Pooling

Pool Sizing Strategy

Optimal connection pool configuration for different application types:

High-Traffic Web Applications: Large pools with aggressive timeouts for rapid response
Batch Processing Systems: Moderate pools with longer timeouts for sustained throughput
Analytics Applications: Smaller pools with secondary read preferences for reporting queries
Microservices Architecture: Multiple specialized pools for different service patterns
Real-time Applications: Priority-based pooling with guaranteed connection availability
Background Services: Separate pools to prevent interference with user-facing operations

Performance Monitoring Guidelines

Essential metrics for production connection pool management:

Utilization Metrics: Track active vs. available connections continuously
Latency Monitoring: Monitor connection wait times and query execution performance
Error Rate Analysis: Track connection failures and timeout patterns
Resource Efficiency: Analyze connection reuse rates and pool effectiveness
Capacity Planning: Use historical data to predict scaling requirements
Health Scoring: Implement composite health metrics for proactive management

Conclusion

MongoDB connection pooling optimization requires sophisticated strategies that balance performance, resource utilization, and operational reliability. By implementing intelligent pooling algorithms, comprehensive monitoring systems, and automated optimization techniques, applications can achieve maximum throughput while maintaining efficient resource usage and operational stability.

Key connection pooling benefits include:

Intelligent Scaling: Automatic pool sizing based on demand patterns and performance metrics
Performance Optimization: Real-time monitoring and tuning for optimal query execution
Resource Efficiency: Optimal connection reuse and lifecycle management
Operational Visibility: Comprehensive metrics and alerting for proactive management
High Availability: Intelligent failover and connection recovery mechanisms
Enterprise Integration: Support for complex deployment architectures and monitoring systems

Whether you're building high-throughput web applications, data processing pipelines, analytics platforms, or distributed microservices, MongoDB's intelligent connection pooling with QueryLeaf's familiar management interface provides the foundation for scalable, efficient database operations. This combination enables you to leverage advanced connection management capabilities while maintaining familiar database administration patterns and operational procedures.

QueryLeaf Integration: QueryLeaf automatically translates SQL-familiar connection pool configuration into optimal MongoDB driver settings while providing comprehensive monitoring and optimization through SQL-style queries. Advanced pooling strategies, performance analysis, and automated tuning are seamlessly managed through familiar database administration interfaces, making sophisticated connection management both powerful and accessible.

The integration of intelligent connection pooling with SQL-style database operations makes MongoDB an ideal platform for applications requiring both high-performance database access and familiar connection management patterns, ensuring your database connections remain both efficient and reliable as they scale to meet demanding production requirements.