Grok 4.1 Fast· xai
Frontier Reasoning Traces
Create a database connection storm absorber: warm pools, jitter, and exponential backoff.
- User
Create a database connection storm absorber: warm pools, jitter, and exponential backoff.
- Assistant
import time import random import threading import logging from typing import Optional, ContextManager from psycopg2 import connect, OperationalError, ProgrammingError from psycopg2.extras import RealDictCursor from psycopg2.pool import AbstractConnectionPool logger = logging.getLogger(__name__) class DBStormAbsorber(ContextManager): """ A database connection storm absorber for PostgreSQL using psycopg2. Features: - **Warm Pools**: Maintains a pool of pre-warmed (tested) connections. Connections are periodically tested and recreated if stale. - **Exponential Backoff**: Retries connection acquisition with exponentially increasing delays (capped). - **Jitter**: Adds randomness to backoff intervals to prevent synchronized retries (thundering herd / connection storms). - Thread-safe with locks. - Context manager support for automatic connection return. Usage: absorber = DBStormAbsorber(dsn="postgresql://user:pass@host/db") with absorber.get_connection() as conn: cursor = conn.cursor() cursor.execute("SELECT 1") Background maintenance thread keeps the pool warm. """ def __init__( self, dsn: str, pool_size: int = 20, min_idle: int = 5, max_retries: int = 10, backoff_base: float = 0.1, # Initial backoff in seconds backoff_max: float = 30.0, # Cap on backoff jitter: bool = True, test_query: str = "SELECT 1", maintenance_interval: float = 30.0, # Seconds between pool checks ): self.dsn = dsn self.pool_size = pool_size self.min_idle = min_idle self.max_retries = max_retries self.backoff_base = backoff_base self.backoff_max = backoff_max self.jitter = jitter self.test_query = test_query self.maintenance_interval = maintenance_interval self._pool: list[Optional['psycopg2.connection']] = [None] * pool_size self._lock = threading.RLock() self._maintenance_thread: Optional[threading.Thread] = None self._stop_maintenance = threading.Event() # Start background maintenance self._start_maintenance() def _create_connection(self) -> 'psycopg2.connection': """Create a new raw connection.""" return connect(self.dsn, cursor_factory=RealDictCursor) def _test_connection(self, conn: 'psycopg2.connection') -> bool: """Test if connection is alive.""" try: with conn.cursor() as cur: cur.execute(self.test_query) cur.fetchone() return True except (OperationalError, ProgrammingError, Exception) as e: logger.debug(f"Connection test failed: {e}") return False def _exp_backoff(self, attempt: int) -> float: """Calculate exponential backoff with optional jitter.""" base_delay = min(self.backoff_base * (2 ** attempt), self.backoff_max) if self.jitter: # Full jitter: random between 0 and base_delay (prevents synchronization) delay = random.uniform(0, base_delay) else: delay = base_delay logger.debug(f"Backoff attempt {attempt}: {delay:.2f}s") return delay def _warm_pool(self) -> None: """Fill/refresh the pool up to pool_size with healthy connections.""" with self._lock: idle_count = sum(1 for c in self._pool if c is not None) to_create = min(self.pool_size - idle_count, self.pool_size // 2) # Batch create for _ in range(to_create): try: idx = self._pool.index(None) conn = self._create_connection() if self._test_connection(conn): self._pool[idx] = conn logger.debug(f"Warmed pool slot {idx}") else: conn.close() except OperationalError as e: logger.warning(f"Failed to warm pool: {e}") except ValueError: # No None slots break def _cleanup_stale(self) -> None: """Remove and close stale connections.""" with self._lock: for i, conn in enumerate(self._pool): if conn is not None and not self._test_connection(conn): logger.debug(f"Closing stale connection at slot {i}") conn.close() self._pool[i] = None def _maintenance_loop(self) -> None: """Background thread to maintain warm pool.""" while not self._stop_maintenance.wait(self.maintenance_interval): self._cleanup_stale() if sum(1 for c in self._pool if c is not None) < self.min_idle: self._warm_pool() def _start_maintenance(self) -> None: """Start the maintenance thread.""" self._stop_maintenance.clear() self._maintenance_thread = threading.Thread(target=self._maintenance_loop, daemon=True) self._maintenance_thread.start() # Initial warm-up time.sleep(0.1) # Let thread start self._warm_pool() def get_connection(self) -> ContextManager['psycopg2.connection']: """ Acquire a healthy connection with retries, backoff, and jitter. Use as context manager to auto-return. """ class ConnectionGuard(ContextManager['psycopg2.connection']): def __init__(self, absorber: 'DBStormAbsorber'): self.absorber = absorber self.conn: Optional['psycopg2.connection'] = None def __enter__(self) -> 'psycopg2.connection': for attempt in range(self.absorber.max_retries): # Try to get from pool with self.absorber._lock: for i, conn in enumerate(self.absorber._pool): if conn is not None and self.absorber._test_connection(conn): # Healthy, pop it self.conn = self.absorber._pool[i] self.absorber._pool[i] = None logger.debug(f"Acquired connection from slot {i}") return self.conn # No healthy conn, backoff and retry if attempt < self.absorber.max_retries - 1: wait_time = self.absorber._exp_backoff(attempt) time.sleep(wait_time) # Try to warm during backoff self.absorber._warm_pool() # Final attempt: create one directly try: self.conn = self.absorber._create_connection() if self.absorber._test_connection(self.conn): logger.info("Acquired new connection after retries") return self.conn except OperationalError as e: pass raise OperationalError("Failed to acquire connection after retries") def __exit__(self, exc_type, exc_val, exc_tb): if self.conn: # Return to pool (will be tested next time) with self.absorber._lock: idx = self._pool.index(None) self.absorber._pool[idx] = self.conn self.conn = None return ConnectionGuard(self) def __enter__(self): return self def __exit__(self, exc_type, exc_val, exc_tb): self.close() def close(self) -> None: """Shutdown: stop maintenance and close all connections.""" self._stop_maintenance.set() if self._maintenance_thread: self._maintenance_thread.join(timeout=1.0) with self._lock: for conn in self._pool: if conn: conn.close() self._pool = [] # Example usage if __name__ == "__main__": logging.basicConfig(level=logging.DEBUG) # Replace with your DSN dsn = "postgresql://user:password@localhost:5432/mydb" absorber = DBStormAbsorber(dsn, pool_size=5) try: with absorber.get_connection() as conn: with conn.cursor() as cur: cur.execute("SELECT version()") print(cur.fetchone()) except Exception as e: print(f"Error: {e}") finally: absorber.close()Key Features Explained
- Warm Pools: Connections are pre-created and tested (
_warm_pool). A background thread maintains at leastmin_idlehealthy connections and cleans stale ones everymaintenance_interval. - Jitter + Exponential Backoff: On failure to acquire, retries use
random.uniform(0, min(cap, base * 2**attempt))(full jitter, AWS-recommended). - Storm Absorption: Each
get_connection()retries independently with staggered delays, preventing all clients from hammering the DB simultaneously. - Thread-Safe:
RLockfor pool access. - Context Manager: Auto-returns connections to pool.
- Production-Ready: Logging, configurable, handles
OperationalError, tests on checkout/return.
This self-contained module absorbs connection storms effectively. Test under load (e.g., with
locustor simulate DB downtime). Extend for async (e.g., asyncpg) if needed. - Warm Pools: Connections are pre-created and tested (
Other models, same or similar prompt
Responses were produced in different, uncontrolled contexts (system prompts, settings, prior turns). This is not a benchmark.