InkdownInkdown
Start writing

Study

59 files·8 subfolders

Shared Workspace

Study
core

12-connection-pooling

Shared from "Study" on Inkdown

12 - Connection Pooling & Performance

Why Connection Pooling?

Database connections are expensive to create. Connection pooling reuses connections instead of creating new ones for each request.

Plain text
┌─────────────────────────────────────────────────────────────┐
│              Without Connection Pooling                      │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  Request 1:                                                  │
│    App ──► Create Connection ──► Query ──► Close             │
│    Time: 50ms   +   5ms    +   20ms   +   5ms = 80ms       │
│                                                              │
│  Request 2:                                                  │
│    App ──► Create Connection ──► Query ──► Close             │
│    Time: 50ms   +   5ms    +   20ms   +   5ms = 80ms       │
│                                                              │
│  Problems:                                                   │
│  - 50ms connection overhead per request                     │
│  - Memory pressure (each connection uses RAM)                │
│  - Connection limits (PostgreSQL: ~100 max)                 │
│  - TCP handshake, SSL negotiation, auth every time         │
│                                                              │
└─────────────────────────────────────────────────────────────┘

┌─────────────────────────────────────────────────────────────┐
│              With Connection Pooling                         │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  Startup:                                                    │
│    Create 10 connections (500ms one-time cost)               │
│                                                              │
│  ┌───────────────────────────────────────────┐              │
│  │           Connection Pool                 │              │
│  │  ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ ┌─────┐ │              │
│  │  │ C1  │ │ C2  │ │ C3  │ │ C4  │ │ C5  │ │              │
│  │  │ C6  │ │ C7  │ │ C8  │ │ C9  │ │ C10 │ │              │
│  │  └─────┘ └─────┘ └─────┘ └─────┘ └─────┘ │              │
│  └───────────────────────────────────────────┘              │
│                                                              │
│  Request 1:                                                  │
│    App ──► Borrow C1 ──► Query ──► Return C1                │
│    Time:  1ms   +   20ms   +   0ms = 21ms                  │
│                                                              │
│  Request 2 (concurrent):                                     │
│    App ──► Borrow C2 ──► Query ──► Return C2                │
│    Time:  1ms   +   20ms   +   0ms = 21ms                  │
│                                                              │
│  Savings: 80ms → 21ms (4x faster!)                          │
│                                                              │
└─────────────────────────────────────────────────────────────┘
programming-language-concepts.md
zero-language-explanation.md
DB
01-introduction.md
02-relational-databases.md
03-database-design.md
04-indexing.md
05-transactions-acid.md
06-nosql-databases.md
07-query-optimization.md
08-replication-ha.md
09-sharding-partitioning.md
10-caching-strategies.md
11-cap-theorem.md
12-connection-pooling.md
13-backup-recovery.md
14-monitoring.md
15-database-selection.md
README.md
JS
Event loop
Merlin Backend
01-Orchestration.md
02-DeepResearch.md
03-Search.md
04-Scraping.md
05-Streaming.md
06-MultiProviderLLM.md
07-MemoryAndContext.md
08-ErrorHandling.md
09-RateLimiting.md
10-TaskQueue.md
11-SecurityAndAuth.md
Orchestration-2nd-draft
OpenAI Agents Python
00_OVERVIEW.md
01_AGENT_SYSTEM.md
02_RUNNER_SYSTEM.md
03_TOOL_SYSTEM.md
04_ITEMS_SYSTEM.md
05_GUARDRAILS.md
06_HANDOFFS.md
07_MEMORY_SESSIONS.md
08_MODEL_PROVIDERS.md
09_SANDBOX_SYSTEM.md
10_TRACING.md
11_RUN_STATE.md
12_CONTEXT.md
13_LIFECYCLE_HOOKS.md
14_CONFIGURATION.md
15_ERROR_HANDLING.md
16_STREAMING.md
17_EXTENSIONS.md
18_MCP_INTEGRATION.md
19_BEST_PRACTICES.md
20_ARCHITECTURE_PATTERNS.md
opencode-study
context-handling
core
Python
Alembic
Basics
sqlalchemy - fastapi
SQLAlchemy overview
tweets
system_design_for_agentic_apps.md

Connection Lifecycle

Plain text
┌─────────────────────────────────────────────────────────────┐
│              Connection Lifecycle Costs                        │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  1. TCP Handshake: ~1 RTT                                   │
│     SYN ──► SYN-ACK ──► ACK                                │
│                                                              │
│  2. SSL/TLS Handshake (if enabled): ~2-3 RTT                │
│     Certificate exchange, key negotiation                    │
│                                                              │
│  3. Database Authentication: ~1-5ms                        │
│     Password verification, role lookup                       │
│                                                              │
│  4. Setup: ~1ms                                             │
│     Session variables, timezone, search_path                 │
│                                                              │
│  Total: 50-100ms per connection!                             │
│  Pooling: Amortize over thousands of requests                │
│                                                              │
└─────────────────────────────────────────────────────────────┘

Pool Architecture

Plain text
┌─────────────────────────────────────────────────────────────┐
│              Connection Pool Architecture                      │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  ┌─────────────────────────────────────────────────────┐   │
│  │                   Application                        │   │
│  │  ┌─────────┐  ┌─────────┐  ┌─────────┐  ┌─────────┐ │   │
│  │  │ Thread 1│  │ Thread 2│  │ Thread 3│  │ Thread 4│ │   │
│  │  └────┬────┘  └────┬────┘  └────┬────┘  └────┬────┘ │   │
│  └───────┼────────────┼────────────┼────────────┼──────┘   │
│          │            │            │            │           │
│          └────────────┴────────────┴────────────┘           │
│                        │                                     │
│           ┌────────────▼────────────┐                        │
│           │     Connection Pool     │                        │
│           ├─────────────────────────┤                        │
│           │  ┌───────────────────┐  │                        │
│           │  │ Available Queue   │  │                        │
│           │  │ ┌───┐ ┌───┐ ┌───┐ │  │                        │
│           │  │ │ C1│ │ C2│ │ C3│ │  │                        │
│           │  │ └───┘ └───┘ └───┘ │  │                        │
│           │  └───────────────────┘  │                        │
│           │  ┌───────────────────┐  │                        │
│           │  │ In-Use Map        │  │                        │
│           │  │ Thread1: C5      │  │                        │
│           │  │ Thread2: C7      │  │                        │
│           │  └───────────────────┘  │                        │
│           └─────────────────────────┘                        │
│                        │                                     │
│           ┌────────────▼────────────┐                        │
│           │      Database           │                        │
│           └─────────────────────────┘                        │
│                                                              │
│  Components:                                                 │
│  - Pool size (min, max connections)                         │
│  - Queue for waiting threads                                │
│  - Connection validation (is it still alive?)               │
│  - Connection eviction (idle timeout)                     │
│                                                              │
└─────────────────────────────────────────────────────────────┘

Pool Configuration

Pool Size Formula
Plain text
┌─────────────────────────────────────────────────────────────┐
│              Sizing the Connection Pool                        │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  Formula:                                                    │
│  connections = ((core_count * 2) + effective_spindle_count)  │
│                                                              │
│  Modern variant:                                             │
│  connections = (number_of_cores * 4)                         │
│                                                              │
│  Example:                                                    │
│  - 8 core server                                             │
│  - SSD storage (effectively 1 spindle)                       │
│  - connections = (8 * 2) + 1 = 17                            │
│  - Or with formula: 8 * 4 = 32                               │
│                                                              │
│  But wait! In cloud/container environments:                 │
│  - Multiple app instances share the database                 │
│                                                              │
│  Better approach:                                            │
│  ┌─────────────────────────────────────────────────────┐   │
│  │  Let:                                               │   │
│  │    T = Total DB connections allowed (e.g., 100)     │   │
│  │    N = Number of app instances                      │   │
│  │    C = Connections per pool = T / N                 │   │
│  │                                                     │   │
│  │  Example: 100 max, 5 app instances                │   │
│  │    C = 100 / 5 = 20 connections per pool            │   │
│  └─────────────────────────────────────────────────────┘   │
│                                                              │
│  Leave headroom:                                             │
│  - Reserve 10-20% for admin, monitoring                      │
│  - Don't set pool max = database max                         │
│                                                              │
└─────────────────────────────────────────────────────────────┘
Configuration Examples
Python
# Python - psycopg2 with connection pool
from psycopg2 import pool

db_pool = psycopg2.pool.ThreadedConnectionPool(
    minconn=5,          # Always keep 5 connections ready
    maxconn=20,         # Never exceed 20 connections
    host="localhost",
    database="mydb",
    user="app",
    password="secret"
)

def get_user(user_id):
    conn = None
    try:
        conn = db_pool.getconn()  # Borrow from pool
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM users WHERE id = %s", (user_id,))
        result = cursor.fetchone()
        return result
    finally:
        if conn:
            db_pool.putconn(conn)   # Return to pool

# SQLAlchemy with pooling
from sqlalchemy import create_engine

engine = create_engine(
    "postgresql://app:secret@localhost/mydb",
    pool_size=10,              # Default connections
    max_overflow=5,            # Extra when needed
    pool_timeout=30,           # Wait for connection
    pool_recycle=3600,         # Recycle after 1 hour
    pool_pre_ping=True         # Validate before use
)
Java
// Java - HikariCP (fastest connection pool)
HikariConfig config = new HikariConfig();
config.setJdbcUrl("jdbc:postgresql://localhost/mydb");
config.setUsername("app");
config.setPassword("secret");

// Pool sizing
config.setMinimumIdle(10);           // Minimum idle connections
config.setMaximumPoolSize(20);       // Maximum connections

// Timeouts
config.setConnectionTimeout(30000);    // Max wait for connection (ms)
config.setIdleTimeout(600000);         // Max idle time (ms)
config.setMaxLifetime(1800000);        // Max connection age (ms)

// Validation
config.setConnectionTestQuery("SELECT 1");

HikariDataSource dataSource = new HikariDataSource(config);
JavaScript
// Node.js - pg-pool
const { Pool } = require('pg');

const pool = new Pool({
    host: 'localhost',
    database: 'mydb',
    user: 'app',
    password: 'secret',
    
    // Pool options
    max: 20,              // Maximum pool size
    min: 5,               // Minimum pool size
    idleTimeoutMillis: 30000,  // Close idle after 30s
    connectionTimeoutMillis: 2000,  // Timeout for new connection
});

// Usage
const result = await pool.query('SELECT * FROM users WHERE id = $1', [userId]);

Pool Strategies

1. Fixed Pool Size
Plain text
┌─────────────────────────────────────────────────────────────┐
│  Always maintain min connections, never exceed max            │
│                                                              │
│  When all connections in use:                                │
│  - Block until one available                                │
│  - Or throw error (if timeout)                              │
│                                                              │
│  Best for: Predictable load, resource-constrained           │
└─────────────────────────────────────────────────────────────┘
2. Elastic Pool (Grow/Shrink)
Plain text
┌─────────────────────────────────────────────────────────────┐
│  Start with min, grow to max as needed                       │
│  Shrink back to min after idle timeout                       │
│                                                              │
│  When all connections in use:                                │
│  - Create new connection (up to max)                        │
│  - Block if at max                                          │
│                                                              │
│  Best for: Variable load, bursty traffic                   │
└─────────────────────────────────────────────────────────────┘
3. Per-User/Per-Schema Pools
Plain text
┌─────────────────────────────────────────────────────────────┐
│  Separate pools for different workloads                      │
│                                                              │
│  Pool 1 (OLTP):  20 connections - fast, small queries       │
│  Pool 2 (Batch): 10 connections - long-running reports      │
│  Pool 3 (Admin):  5 connections - DDL, maintenance          │
│                                                              │
│  Prevents slow queries from starving fast queries           │
└─────────────────────────────────────────────────────────────┘

Connection Pool Issues

1. Pool Exhaustion
Plain text
┌─────────────────────────────────────────────────────────────┐
│              Pool Exhaustion                                 │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  Symptoms:                                                   │
│  - "Timeout waiting for connection"                         │
│  - Requests pile up                                          │
│  - Eventually application crash                              │
│                                                              │
│  Causes:                                                     │
│  1. Connections not returned to pool                         │
│     - Missing putconn() in finally block                    │
│     - Exception before return                                │
│                                                              │
│  2. Long-running queries holding connections                │
│     - No timeout set                                         │
│     - N+1 queries in loop                                    │
│                                                              │
│  3. Pool too small                                          │
│     - More threads than connections                         │
│     - Sudden traffic spike                                   │
│                                                              │
│  Solutions:                                                  │
│  - Always use try/finally or context managers               │
│  - Set query timeouts                                        │
│  - Monitor pool usage                                        │
│  - Increase pool size or optimize queries                    │
│                                                              │
└─────────────────────────────────────────────────────────────┘
2. Connection Leaks
Python
# BAD: Connection leak on exception
def bad_example():
    conn = pool.getconn()
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    result = cursor.fetchall()
    # If exception occurs here, connection never returned!
    pool.putconn(conn)  # Might not execute
    return result

# GOOD: Always use context manager or try/finally
def good_example():
    conn = None
    try:
        conn = pool.getconn()
        cursor = conn.cursor()
        cursor.execute("SELECT * FROM users")
        return cursor.fetchall()
    finally:
        if conn:
            pool.putconn(conn)

# BETTER: Context manager
from contextlib import contextmanager

@contextmanager
def get_db_connection():
    conn = pool.getconn()
    try:
        yield conn
    finally:
        pool.putconn(conn)

# Usage
with get_db_connection() as conn:
    cursor = conn.cursor()
    cursor.execute("SELECT * FROM users")
    return cursor.fetchall()
3. Stale Connections
Plain text
Problem:
┌─────────────┐        ┌─────────────┐        ┌─────────────┐
│   Pool      │        │ Connection  │        │  Database   │
│             │        │             │        │ (Restarted) │
├─────────────┤        ├─────────────┤        ├─────────────┤
│  ┌───────┐  │        │  Connected  │        │             │
│  │   C1  │──┼────────│  2 hours ago│        │ Connection  │
│  └───────┘  │        │             │        │   broken!   │
│             │        │             │        │             │
│  App borrows C1...   │             │        │             │
│         │            │             │        │             │
│         ▼            │             │        │             │
│  ERROR! Connection   │             │        │             │
│  already closed      │             │        │             │
│                      │             │        │             │
└──────────────────────┴─────────────┴────────┴─────────────┘

Solution:
1. Set connection max lifetime (recycle periodically)
2. Test connection on borrow (pool_pre_ping)
3. Handle stale connections gracefully in app

Advanced Pooling: PgBouncer

Plain text
┌─────────────────────────────────────────────────────────────┐
│              PgBouncer (PostgreSQL Connection Pooler)        │
├─────────────────────────────────────────────────────────────┤
│                                                              │
│  Application ──► PgBouncer ──► PostgreSQL                   │
│  1000 conn        20 conn        20 conn                     │
│                                                              │
│  Modes:                                                      │
│                                                              │
│  1. Session Mode (default)                                    │
│     - Connection tied to client session                     │
│     - Supports transactions, prepared statements            │
│     - 1:1 mapping while session active                        │
│                                                              │
│  2. Transaction Mode                                          │
│     - Connection per transaction                             │
│     - Reassign after COMMIT/ROLLBACK                        │
│     - Higher concurrency (1000 clients, 20 DB conns)        │
│     - Doesn't support prepared statements across xacts      │
│                                                              │
│  3. Statement Mode                                            │
│     - Connection per statement                               │
│     - Most aggressive pooling                                 │
│     - Rarely used (breaks many features)                    │
│                                                              │
└─────────────────────────────────────────────────────────────┘

# PgBouncer configuration
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt

# Pool settings
pool_mode = transaction
max_client_conn = 10000
default_pool_size = 20
min_pool_size = 10
reserve_pool_size = 5
reserve_pool_timeout = 3

# Timeouts
server_idle_timeout = 600
server_lifetime = 3600
server_connect_timeout = 15
query_timeout = 0
query_wait_timeout = 120

Monitoring Pool Health

Python
# Key metrics to track:

# 1. Pool utilization
active_connections = pool.size()
idle_connections = pool.checkedin()
total_connections = active_connections + idle_connections
utilization = active_connections / pool.max_size

# Alert if utilization > 80% consistently

# 2. Wait time
wait_time = pool.wait_time()  # Time waiting for connection
# Alert if wait_time > 100ms

# 3. Connection lifetime
avg_connection_age = pool.avg_connection_age()
# Monitor for stale connections

# 4. Checkout/checkin ratio
# Should be roughly equal (otherwise leaks!)

Best Practices

Plain text
✓ DO:
  - Use connection pooling (never connect per request)
  - Size pools based on: cores * 2 + disks
  - Use context managers (try/finally)
  - Set connection and query timeouts
  - Monitor pool metrics
  - Use separate pools for different workloads
  - Recycle connections periodically
  - Validate connections before use

✗ DON'T:
  - Create connections in loops
  - Hold connections while waiting for user input
  - Share connections across threads (unless pool supports it)
  - Ignore pool exhaustion errors
  - Set pool size = database max connections
  - Forget to return connections

Next: Backup & Recovery