Connection Pooling #

When developers first feel the database starting to slow down in production, the most common reaction is to look at the queries: is there one not using an index, is there an N+1, is there a full table scan. All of that is valid — but there’s another problem that’s more often the cause, less often diagnosed, and more severe in impact: poor connection management.

Every database connection isn’t just an “established link” that forms instantly. Behind it there’s a TCP handshake, TLS negotiation, authentication, and thread and memory allocation on the database server. All of this takes time — usually between 20 and 100 milliseconds per connection. Multiply that by hundreds of requests per second, and you have a very real overhead.

Connection pooling solves this problem elegantly: create a set of connections up front, keep them, and reuse them over and over. Requests don’t need to open a new connection — they borrow an existing one, use it, then return it. But a pool configured incorrectly can become a new problem more serious than the one it solves: the database gets swarmed by thousands of connections at once, memory runs out, and the system collapses.

Why Opening a Connection Is Expensive #

To understand the value of connection pooling, you first need to understand how much work actually happens every time a database connection is opened.

The process of opening one database connection:

sequenceDiagram
    participant App as Application
    participant DB as Database Server
    Note over App,DB: 1. Resolve hostname (DNS)<br/>2. Open TCP socket
    App->>DB: 3. TCP 3-way handshake
    DB-->>App: Handshake accepted
    App->>DB: 4. TLS ClientHello
    Note over DB: TLS negotiation
    DB-->>App: TLS ServerHello + Certificate
    App->>DB: 5. TLS Finished
    DB-->>App: TLS Finished
    App->>DB: 6. Send credentials
    Note over DB: Verify user + password
    DB-->>App: Auth OK, send server capabilities
    App->>DB: 7. Select database
    Note over DB: Allocate thread
    DB-->>App: Ready
    Note over App: 8. [Connection ready to use]

Estimated total time: 20–100ms. Estimated memory on the DB server: 1–8 MB per connection (depending on configuration).

For one connection, 50ms still feels reasonable. But in a system with 500 requests per second, each opening a new connection, that means 25 seconds of CPU time per second just for handshakes — before a single query runs.

Without connection pooling, this is the pattern that happens:

flowchart TD
    subgraph NoPooling["Without Pooling (a new connection per request)"]
        direction TB
        R1["Request 1"] -->|"open conn (50ms)"| Q1["Query (5ms)"] -->|"close conn"| T1["Total: 55ms"]
        R2["Request 2"] -->|"open conn (50ms)"| Q2["Query (5ms)"] -->|"close conn"| T2["Total: 55ms"]
        R3["Request 3"] -->|"open conn (50ms)"| Q3["Query (5ms)"] -->|"close conn"| T3["Total: 55ms"]
    end

    subgraph WithPooling["With Pooling (connections reused)"]
        direction TB
        R4["Request 1"] -->|"borrow conn (0.1ms)"| Q4["Query (5ms)"] -->|"return conn"| T4["Total: 5.1ms"]
        R5["Request 2"] -->|"borrow conn (0.1ms)"| Q5["Query (5ms)"] -->|"return conn"| T5["Total: 5.1ms"]
        R6["Request 3"] -->|"borrow conn (0.1ms)"| Q6["Query (5ms)"] -->|"return conn"| T6["Total: 5.1ms"]
    end

Connection overhead:

• Without pooling: 50ms out of 55ms = 91% of the time spent not on the query.
• With pooling: 0.1ms out of 5.1ms = 2% — barely noticeable.

How the Pool Works: The Lifecycle of a Connection #

The connection pool has a lifecycle you need to understand to configure it correctly.

flowchart TD
    Start["Application Startup"] -->|"Create pool (min_size: 5)"| IdlePool["Pool: conn-1..5 [idle]"]
    IdlePool -->|"Requests A & B arrive"| Borrow["Borrow conn-1 & conn-2<br/>conn-1,2 [in use]<br/>conn-3,4,5 [idle]"]
    Borrow -->|"Request A finishes"| Return["Return conn-1 to [idle]<br/>conn-1,3,4,5 [idle]<br/>conn-2 [in use]"]
    Return --> IdlePool
    Borrow -->|"Pool full (all in use)<br/>and a new request arrives"| Wait{"Waiting for a conn"}
    Wait -->|"One finishes before the timeout"| GetConn["Got a connection"]
    Wait -->|"Timeout passed"| Error["Error: pool exhausted"]

There are five configuration parameters that matter most in almost every pooling library:

Important pool parameters:

  min_connections (or initial_size)
    → The number of connections created when the app starts
    → Make sure the DB doesn't run out of resources when many instances start together

  max_connections (or pool_size)
    → The maximum number of connections allowed in the pool
    → This is the most critical number — too large can kill the DB

  max_idle_connections
    → How many idle connections are kept open
    → The rest are closed if unused for a certain time

  connection_timeout
    → How long a request waits for an available connection before erroring
    → Don't leave it too long on user-facing endpoints

  max_lifetime (or max_age)
    → How long a connection may live before being replaced
    → Important to avoid stale connections from network timeouts or
      DB configuration changes

Pooling in Non-Distributed Applications #

For single-instance applications — monoliths, internal tools, admin panels — connection pooling is relatively simple. There’s one pool in one process, and the only thing to think about is the right pool size.

// Equivalent pool configuration in Go (database/sql)
import (
    "database/sql"
    "time"
)

db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/dbname")
if err != nil {
    log.Fatal(err)
}

db.SetMaxOpenConns(15)                  // max active connections (= max_connections)
db.SetMaxIdleConns(10)                  // max idle connections maintained
db.SetConnMaxLifetime(30 * time.Minute) // recycle connections after 30 minutes (prevents stale conns)
db.SetConnMaxIdleTime(10 * time.Minute) // max idle time before the connection is closed
// Note: database/sql has no direct pool_pre_ping equivalent — stale connections
// are handled via SetConnMaxLifetime / SetConnMaxIdleTime

// Total maximum connections: SetMaxOpenConns = 15

// Example pool configuration in Go (database/sql)
import "database/sql"

db, err := sql.Open("mysql", "user:password@tcp(localhost:3306)/dbname")
if err != nil {
    log.Fatal(err)
}

db.SetMaxOpenConns(15)           // max active connections (= max_connections)
db.SetMaxIdleConns(10)           // max idle connections maintained
db.SetConnMaxLifetime(30 * time.Minute) // max connection age before replacement
db.SetConnMaxIdleTime(10 * time.Minute) // max idle time before the connection is closed

Determining the Right Pool Size #

There’s no magic number that works for every system. But there’s a simple formula as a starting point:

Initial pool size formula (non-distributed):

  For CPU-bound applications (lots of computation, few queries):
    max_pool = number_of_cpu_cores * 2

  For IO-bound applications (lots of queries, little computation):
    max_pool = number_of_cpu_cores * 2 + number_of_disk_spindles

  A more practical rule of thumb:
    Start with pool_size = 10
    Monitor pool utilization in production
    Increase if often full, decrease if many idle

  What to avoid:
    ✗ Pool size = database max_connection (one instance monopolizing the DB)
    ✗ Pool size too large (all instances summed exceeding DB capacity)
    ✗ Pool size = 1 (all requests queue one at a time)

Research from HikariCP (a popular Java connection pool) shows that for most web applications, a relatively small pool size — even between 5 and 10 — is sufficient and often performs better than a large pool. Databases are more efficient serving a few active connections than many connections competing for the same resources.

Pooling in Distributed Applications: The Most Often Ignored Source of Problems #

In a monolith system, a pool size that’s too large at worst just burdens one database with more connections than needed. In distributed systems — microservices, Kubernetes, auto-scaling — a pool configuration mistake can cause a connection explosion: the database gets swarmed by thousands of connections at once, memory runs out, and the whole system collapses.

The connection explosion scenario:

  Initial configuration (looks safe):
    max_pool per instance = 20
    Number of pods = 10
    Total connections to the DB = 200 ← still within the DB's max_connections (250)

  Auto-scaling as traffic rises:
    max_pool per instance = 20
    Number of pods = 20 (scaled up due to traffic)
    Total connections to the DB = 400 ← EXCEEDS DB capacity!

  Result:
  ┌─────────────────────────────────────────────────────────────┐
  │  The DB rejects new connections: "Too many connections"      │
  │  All pods get connection errors                              │
  │  Error cascade: every retry makes things worse               │
  │  The system goes down — even though the queries had          │
  │  no problem at all                                           │
  └─────────────────────────────────────────────────────────────┘

Ironically, this happens exactly when the system most needs capacity — when traffic is rising. The auto-scaling that was supposed to save the system becomes the trigger that destroys it.

Sizing Formula for Distributed Systems #

In a distributed system, you must think from the database side, not the instance side:

Pool sizing formula for distributed systems:

  Step 1: determine the database max_connections
    Usually visible via: SHOW VARIABLES LIKE 'max_connections';
    Reserve ~20% for admin and monitoring operations
    Usable connections = max_connections * 0.8

  Step 2: determine the maximum possible pods
    Not current pods — but the maximum pods at full auto-scaling

  Step 3: calculate the pool per instance
    max_pool_per_instance = usable_connections / max_pods

  A real example:
    DB max_connections      = 500
    Usable connections      = 500 * 0.8 = 400
    Max pods (at scale-up)  = 50
    max_pool_per_instance   = 400 / 50 = 8

  → Every pod configured with max_pool = 8
  → At 50 active pods: total connections = 400 ← safe
  → At 30 active pods: total connections = 240 ← also safe

Visual comparison before and after correct sizing:

flowchart TD
    subgraph Before["Before (large pool per instance)"]
        direction TB
        subgraph Pods1["20 Pods (max_pool = 20)"]
            direction LR
            P1_1["Pod 1"]
            P1_2["Pod 2"]
            P1_3["Pod 3"]
            P1_4["Pod ..."]
        end
        Pods1 -->|"Receives 400 connections"| DB1["Database (max: 250)"]
        style DB1 stroke:#e74c3c,stroke-width:2px
    end

    subgraph After["After (small pool + External Pooler)"]
        direction TB
        subgraph Pods2["20 Pods (max_pool = 5)"]
            direction LR
            P2_1["Pod 1"]
            P2_2["Pod 2"]
            P2_3["Pod 3"]
            P2_4["Pod ..."]
        end
        Pods2 -->|"100 connections"| Proxy["PgBouncer / ProxySQL<br/>(Multiplexing)"]
        Proxy -->|"50 controlled connections"| DB2["Database (max: 250)"]
        style DB2 stroke:#2ecc71,stroke-width:2px
    end

External Poolers: The Solution for Distributed Systems #

When the number of instances is large and unpredictable — especially in auto-scaling environments — an external pooler is the most robust solution. An external pooler is a proxy sitting between the application and the database, managing the connection pool centrally.

PgBouncer for PostgreSQL #

PgBouncer is the most popular external pooler for PostgreSQL. It supports three pooling modes:

PgBouncer pooling modes:

  Session pooling (default):
    One DB connection allocated per client session while the session is active.
    Similar to an in-app pool, but managed centrally.
    Good for: applications using session-level features (prepared statements, etc.)

  Transaction pooling:
    A DB connection is allocated only while a transaction runs.
    After COMMIT/ROLLBACK, the connection returns to the pool.
    Good for: applications where most queries are short.
    ⚠ Not compatible with: SET, advisory locks, LISTEN/NOTIFY

  Statement pooling:
    A DB connection is allocated for only one statement.
    Most efficient, but with the most limitations.
    Rarely used in modern applications.

# Example PgBouncer configuration (pgbouncer.ini)
[databases]
mydb = host=db-primary port=5432 dbname=myapp

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

pool_mode = transaction        # use transaction pooling for maximum efficiency
max_client_conn = 1000         # max connections from apps to PgBouncer
default_pool_size = 50         # connections from PgBouncer to the database
min_pool_size = 10             # minimum connections always maintained
reserve_pool_size = 5          # reserve connections for bursts
server_idle_timeout = 600      # close idle DB connections after 10 minutes

ProxySQL for MySQL #

ProxySQL is an external pooler for MySQL that also supports automatic read/write routing.

ProxySQL advantages over in-app pool configuration:

  ✓ Automatic routing: SELECT queries → replicas, write queries → primary
  ✓ Connection multiplexing: 1000 app connections can be multiplexed into 50 DB connections
  ✓ Query caching: frequently repeated query results can be cached
  ✓ Query rewriting: change queries without changing application code
  ✓ Centralized monitoring and statistics
  ✓ Automatic failover when the primary goes down

With an external pooler, the pool configuration on the application side can be significantly reduced:

// With an external pooler in front of the DB, the app-side pool can be much smaller
db.SetMaxOpenConns(5)                   // small — multiplexing happens at ProxySQL
db.SetMaxIdleConns(3)                   // minimal idle connections
db.SetConnMaxLifetime(15 * time.Minute) // 15 minutes — ProxySQL also has an idle timeout
// Timeouts are enforced with a context on each query (e.g. 10s), not on the pool itself

Connection Leaks: The Most Quietly Deadly Bug #

A connection leak happens when a connection borrowed from the pool is never returned — because of an unhandled exception, a missed return path, or a connection stored in a variable and garbage-collected without being closed.

// ANTI-PATTERN in Go: rows not closed on early returns or panics
func getUser(db *sql.DB, userID int) (*User, error) {
    rows, err := db.Query("SELECT * FROM users WHERE id = ?", userID)
    if err != nil {
        return nil, err
    }
    // If there's an early return or panic below, rows is never closed
    // The connection stays "in use" in the pool even though the function finished
    if !rows.Next() {
        return nil, nil // ← rows not closed!
    }
    // ...
    rows.Close()
    return user, nil
}

// CORRECT: defer rows.Close() called right after rows is created
func getUserSafe(db *sql.DB, userID int) (*User, error) {
    rows, err := db.Query("SELECT * FROM users WHERE id = ?", userID)
    if err != nil {
        return nil, err
    }
    defer rows.Close() // ← always called, no matter how the function ends

    if !rows.Next() {
        return nil, nil
    }
    // ...
    return user, nil
}

// ANTI-PATTERN in Go: rows not closed on early returns or panics
func getUser(db *sql.DB, userID int) (*User, error) {
    rows, err := db.Query("SELECT * FROM users WHERE id = ?", userID)
    if err != nil {
        return nil, err
    }
    // If there's an early return or panic below, rows is never closed
    // The connection stays "in use" in the pool even though the function finished
    if !rows.Next() {
        return nil, nil  // ← rows not closed!
    }
    // ...
    rows.Close()
    return user, nil
}

// CORRECT: defer rows.Close() called right after rows is created
func getUser(db *sql.DB, userID int) (*User, error) {
    rows, err := db.Query("SELECT * FROM users WHERE id = ?", userID)
    if err != nil {
        return nil, err
    }
    defer rows.Close()  // ← always called, no matter how the function ends

    if !rows.Next() {
        return nil, nil
    }
    // ...
    return user, nil
}

Signs that a connection leak has already happened:

Connection leak symptoms in production:

  ✗ The "active connections" count in the DB keeps rising over time
  ✗ Pool exhaustion happens even though traffic isn't rising
  ✗ Restarting the app fixes the problem temporarily, then it reappears
  ✗ SHOW PROCESSLIST in MySQL shows many connections in the "Sleep" state
    that have been idle for a very long time

Diagnosing Pool Problems in Production #

When there’s a problem suspected to be related to connection pooling, several queries and commands can be run to diagnose the situation.

-- Check the total active connections to the database (MySQL)
SHOW STATUS LIKE 'Threads_connected';
-- Compare with: SHOW VARIABLES LIKE 'max_connections';
-- If Threads_connected is near max_connections → the pool is in a critical state

-- View all active connections and their statuses
SHOW PROCESSLIST;
-- Look at the 'Command' and 'Time' columns:
-- 'Sleep' with high Time → idle connections that may not have been returned
-- 'Query' with high Time → slow queries holding connections

-- Connection pool statistics (for apps that expose metrics)
-- Important metrics:
-- pool_size: total connections in the pool
-- pool_checkout_count: how many times connections were borrowed
-- pool_checkin_count: how many times returned (should be close to checkout_count)
-- pool_overflow_count: how many times the pool had to create connections beyond pool_size
-- pool_timeout_count: how many times requests timed out waiting for a connection

-- Check the configured max_connections in MySQL
SHOW VARIABLES LIKE 'max_connections';

-- Check connection usage percentage
SELECT
    VARIABLE_VALUE AS threads_connected
FROM information_schema.GLOBAL_STATUS
WHERE VARIABLE_NAME = 'Threads_connected';

Anti-Patterns to Avoid #

// ✗ Anti-pattern 1: opening a new connection in every function without a pool
func getOrders(userID int) Result {
    conn := createNewConnection()   // opens a new connection every time
    result := conn.query("SELECT * FROM orders WHERE user_id = ?", userID)
    conn.close()
    return result
}
// 50ms of connection overhead added to every call of this function

// ✓ Solution: inject the pool into the function, reuse connections from the pool
func getOrdersSafe(dbPool *sql.DB, userID int) (Result, error) {
    return dbPool.QueryContext(context.Background(), "SELECT * FROM orders WHERE user_id = ?", userID)
}

// ────────────────────────────────────────────────────────────────────────────────

// ✗ Anti-pattern 2: pool size = database max_connections
// DB max_connections = 200, one app's pool configured with max open conns = 200
// This app monopolizes all DB connections → other apps can't connect

// ✓ Solution: one instance must not use more than 50-70% of DB capacity

// ────────────────────────────────────────────────────────────────────────────────

// ✗ Anti-pattern 3: no connection timeout
db.SetMaxOpenConns(20)
// If the pool is full, requests wait forever → goroutine/thread leak
// → memory keeps rising → OOM

// ✓ Solution: always set a pool timeout matching the SLA
db.SetMaxOpenConns(20)
// Enforce the timeout with a context on every query:
ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
defer cancel()
db.QueryContext(ctx, "SELECT ...") // clear error after 5 seconds, not waiting forever

// ────────────────────────────────────────────────────────────────────────────────

// ✗ Anti-pattern 4: long transactions on high-traffic endpoints
// Every request holds a connection for 3 seconds (because of an HTTP call in the transaction)
// With pool_size=10: only 10 requests can run concurrently
// All other requests wait → a severe bottleneck

// ✓ Solution: external operations outside transactions, transactions as short as possible

// ────────────────────────────────────────────────────────────────────────────────

// ✗ Anti-pattern 5: scaling the app without considering total DB connections
// Before scaling: 5 pods × pool_size=20 = 100 connections (safe)
// After scaling: 20 pods × pool_size=20 = 400 connections (DB max=200 → OVERLOAD)

// ✓ Solution: calculate the total connections from all instances
// Lower the per-instance pool size or install an external pooler

Connection Pooling Review Checklist #

BASIC CONFIGURATION:
  □ Pool size calculated based on DB capacity and instance count
  □ Pool timeout configured with an SLA-appropriate value (not default or absent)
  □ max_lifetime or pool_recycle configured to prevent stale connections
  □ pool_pre_ping or a validation query enabled to detect dead connections

CONNECTION LEAKS:
  □ Every place using connections uses a context manager or finally block
  □ No connections stored in global state or class fields
  □ All rows/cursors/result sets closed after use
  □ No early return paths that can leave connections unreturned

DISTRIBUTED SYSTEMS:
  □ Pool size calculated globally: total_connections = pool_per_instance × max_instances
  □ Maximum total connections doesn't exceed the DB max_connections (reserve 20% for admin)
  □ Per-instance pool size reduced to accommodate max_instances at scale-up
  □ An external pooler (PgBouncer/ProxySQL) considered if instances > 10

TRANSACTION MANAGEMENT:
  □ Transactions as short as possible — no external operations inside
  □ No long-running queries unnecessarily holding connections
  □ Read and write pools separated if there's replication

MONITORING:
  □ Active DB connection count monitored (alert if approaching max_connections)
  □ Pool utilization monitored (checkout count, timeout count, overflow count)
  □ Slow queries holding connections long monitored via the slow query log
  □ DB idle connection count monitored (a sign of potential connection leaks)

Summary #

• Opening a database connection is expensive — 20–100ms per connection for TCP handshake, TLS, and authentication. Connection pooling eliminates this overhead by reusing existing connections.
• Pool size isn’t “bigger is better” — too large wastes DB resources and lowers efficiency. Start small (5–10) and increase based on real monitoring.
• In distributed systems, calculate from the DB side — max_pool_per_instance = usable_connections / max_instances. Don’t configure a pool based on one instance’s needs without accounting for the total instances.
• Connection explosions happen during auto-scaling — every new pod brings its own pool. Make sure the total connections at max scale-up don’t exceed DB capacity.
• External poolers are the most robust solution for distributed systems — PgBouncer or ProxySQL manage the pool centrally, letting thousands of app connections be multiplexed into dozens of database connections.
• Connection leaks slowly kill pools — always use context managers or defer to ensure connections are returned, even on exceptions or early returns.
• Long transactions block pool connections — every millisecond a transaction stays open means one connection unavailable. External operations (HTTP calls, file I/O) must happen outside transactions.
• Pool timeouts must be configured — without a timeout, requests that can’t get a connection wait forever, causing goroutine/thread leaks and OOM.
• Monitor actively, not reactively — watch Threads_connected vs max_connections, pool utilization, and connection leaks before problems become incidents.
• Read and write pools must be separated — if there’s replication, the write pool to the primary should be smaller and tighter, the read pool to replicas can be more relaxed.

← Previous: Index   Next: Backup and Restore →