Topic Overview

Connection Pooling

Understand connection pooling to reuse database and network connections efficiently, reducing overhead and improving performance.

What is Connection Pooling?

Connection pooling maintains a pool of connections:

Reuse connections: Don't create new connection for each request
Reduce overhead: Avoid connection establishment cost
Limit connections: Control number of concurrent connections
Improve performance: Faster requests (no connection setup)

Why needed:

Connection overhead: Creating connections is expensive
Resource limits: Databases have connection limits
Performance: Reusing connections is faster

How Connection Pooling Works

Without Pooling

Request 1: Create connection → Use → Close
Request 2: Create connection → Use → Close
Request 3: Create connection → Use → Close

Overhead: Connection setup/teardown for each request

With Pooling

Pool: [Connection1, Connection2, Connection3]

Request 1: Get connection from pool → Use → Return to pool
Request 2: Get connection from pool → Use → Return to pool
Request 3: Get connection from pool → Use → Return to pool

Benefits: Reuse connections, reduce overhead

Connection Pool Lifecycle

1. Initialize pool (create connections)
2. Request: Get connection from pool
3. Use connection
4. Return connection to pool
5. Cleanup: Close idle connections

Examples

Database Connection Pool

import psycopg2
from psycopg2 import pool

class DatabaseConnectionPool:
    def __init__(self, min_connections=2, max_connections=10):
        self.pool = psycopg2.pool.SimpleConnectionPool(
            min_connections,
            max_connections,
            host='localhost',
            database='mydb',
            user='user',
            password='password'
        )
    
    def get_connection(self):
        """Get connection from pool"""
        return self.pool.getconn()
    
    def return_connection(self, conn):
        """Return connection to pool"""
        self.pool.putconn(conn)
    
    def execute_query(self, query):
        """Execute query using pooled connection"""
        conn = self.get_connection()
        try:
            cursor = conn.cursor()
            cursor.execute(query)
            result = cursor.fetchall()
            return result
        finally:
            self.return_connection(conn)

HTTP Connection Pool

import requests
from requests.adapters import HTTPAdapter
from urllib3.util.retry import Retry

class HTTPConnectionPool:
    def __init__(self):
        self.session = requests.Session()
        
        # Configure connection pool
        adapter = HTTPAdapter(
            pool_connections=10,  # Number of connection pools
            pool_maxsize=20,      # Max connections per pool
            max_retries=Retry(total=3)
        )
        
        self.session.mount('http://', adapter)
        self.session.mount('https://', adapter)
    
    def get(self, url):
        """GET request using pooled connection"""
        return self.session.get(url)
    
    def post(self, url, data):
        """POST request using pooled connection"""
        return self.session.post(url, data)

Custom Connection Pool

import threading
import queue

class ConnectionPool:
    def __init__(self, factory, max_size=10, min_size=2):
        self.factory = factory  # Function to create connection
        self.max_size = max_size
        self.min_size = min_size
        self.pool = queue.Queue(maxsize=max_size)
        self.lock = threading.Lock()
        self.active = 0
        
        # Initialize pool
        for _ in range(min_size):
            conn = self.factory()
            self.pool.put(conn)
    
    def get_connection(self, timeout=5):
        """Get connection from pool"""
        try:
            # Get from pool (non-blocking)
            conn = self.pool.get(timeout=timeout)
            self.active += 1
            return conn
        except queue.Empty:
            # Pool empty: Create new if under max
            if self.active < self.max_size:
                conn = self.factory()
                self.active += 1
                return conn
            else:
                raise PoolExhaustedError("Connection pool exhausted")
    
    def return_connection(self, conn):
        """Return connection to pool"""
        # Check if connection is still valid
        if self.is_valid(conn):
            self.pool.put(conn)
        else:
            # Create new connection
            new_conn = self.factory()
            self.pool.put(new_conn)
        
        self.active -= 1
    
    def is_valid(self, conn):
        """Check if connection is still valid"""
        try:
            # Ping connection
            conn.ping()
            return True
        except:
            return False

Common Pitfalls

Pool exhaustion: Too many connections. Fix: Set appropriate max size, timeout
Stale connections: Connections become invalid. Fix: Validate connections, recreate if needed
Connection leaks: Connections not returned. Fix: Use try-finally, connection managers
Too small pool: Not enough connections. Fix: Size pool based on load

Interview Questions

Beginner

Q: What is connection pooling and why is it used?

Connection pooling maintains a pool of reusable connections.

Why used:

Reduce overhead: Avoid creating connections for each request
Improve performance: Reusing connections is faster
Resource limits: Databases have connection limits
Efficiency: Better resource utilization

How it works:

Without pooling:
  Request → Create connection → Use → Close
  (Overhead: Connection setup/teardown)

With pooling:
  Pool: [Connection1, Connection2, Connection3]
  Request → Get from pool → Use → Return to pool
  (Reuse connections, reduce overhead)

Benefits:

Faster: No connection setup per request
Efficient: Reuse connections
Controlled: Limit number of connections

Intermediate

Q: Explain how connection pooling works. How do you handle connection validation and pool sizing?

Connection Pool Lifecycle:

Initialize pool

# Create initial connections
pool = [conn1, conn2, conn3]

Get connection
```
conn = pool.get_connection()
```
Use connection
```
result = conn.execute(query)
```
Return connection
```
pool.return_connection(conn)
```

Connection Validation:

def return_connection(conn):
    # Validate before returning
    if conn.is_valid():
        pool.put(conn)
    else:
        # Create new connection
        new_conn = create_connection()
        pool.put(new_conn)

Pool Sizing:

# Size based on:
max_connections = (
    expected_concurrent_requests * 
    average_request_duration / 
    target_response_time
)

# Example:
# 100 concurrent requests
# 100ms average duration
# Target: 1s response time
# Max connections: 100 * 0.1 / 1 = 10

Best practices:

Min size: Keep minimum connections ready
Max size: Limit to prevent exhaustion
Validation: Check connections before reuse
Timeout: Timeout when pool exhausted

Senior

Q: Design a connection pooling system for a high-traffic application. How do you handle pool sizing, connection health checks, and prevent connection leaks?

class HighPerformanceConnectionPool {
  private pool: Connection[];
  private available: Queue<Connection>;
  private inUse: Set<Connection>;
  private healthChecker: HealthChecker;
  
  constructor(config: PoolConfig) {
    this.pool = [];
    this.available = new Queue();
    this.inUse = new Set();
    this.healthChecker = new HealthChecker();
    
    // Initialize pool
    this.initializePool(config.minSize);
  }
  
  // 1. Pool Management
  async getConnection(timeout: number = 5000): Promise<Connection> {
    // Try to get from available
    let conn = this.available.dequeue();
    
    if (conn) {
      // Validate connection
      if (await this.healthChecker.isHealthy(conn)) {
        this.inUse.add(conn);
        return conn;
      } else {
        // Invalid: Create new
        conn = await this.createConnection();
      }
    } else {
      // Pool empty: Create new if under max
      if (this.pool.length < this.maxSize) {
        conn = await this.createConnection();
        this.pool.push(conn);
      } else {
        // Wait for available connection
        conn = await this.waitForConnection(timeout);
      }
    }
    
    this.inUse.add(conn);
    return conn;
  }
  
  async returnConnection(conn: Connection): Promise<void> {
    this.inUse.delete(conn);
    
    // Validate before returning
    if (await this.healthChecker.isHealthy(conn)) {
      this.available.enqueue(conn);
    } else {
      // Remove invalid connection
      this.removeConnection(conn);
      // Create replacement
      const newConn = await this.createConnection();
      this.pool.push(newConn);
      this.available.enqueue(newConn);
    }
  }
  
  // 2. Health Checking
  class HealthChecker {
    async isHealthy(conn: Connection): Promise<boolean> {
      try {
        // Ping connection
        await conn.ping();
        return true;
      } catch {
        return false;
      }
    }
    
    async checkAll(): Promise<void> {
      // Periodically check all connections
      for (const conn of this.pool) {
        if (!await this.isHealthy(conn)) {
          await this.replaceConnection(conn);
        }
      }
    }
  }
  
  // 3. Leak Detection
  class LeakDetector {
    detectLeaks(): void {
      // Check for connections in use too long
      for (const conn of this.inUse) {
        const age = Date.now() - conn.acquiredAt;
        if (age > 60000) { // 1 minute
          // Potential leak
          this.alert(`Connection ${conn.id} in use for ${age}ms`);
        }
      }
    }
  }
  
  // 4. Dynamic Sizing
  class DynamicPoolSizer {
    async adjustSize(): Promise<void> {
      const utilization = this.inUse.size / this.pool.length;
      
      if (utilization > 0.8) {
        // High utilization: Increase pool
        await this.increasePool();
      } else if (utilization < 0.3) {
        // Low utilization: Decrease pool
        await this.decreasePool();
      }
    }
  }
}

Features:

Pool management: Get, return, validate connections
Health checking: Validate connections before reuse
Leak detection: Detect connections in use too long
Dynamic sizing: Adjust pool size based on utilization

Key Takeaways

Connection pooling: Maintain pool of reusable connections
Benefits: Reduce overhead, improve performance, control resources
Lifecycle: Initialize → Get → Use → Return → Cleanup
Validation: Check connections before reuse
Pool sizing: Based on load, concurrent requests, response time
Best practices: Validate connections, detect leaks, size appropriately