Database Connection Management

Design database connection strategies that maximize throughput without exhausting resources. Covers connection pooling, pool sizing formulas, connection lifecycle management, prepared statement caching, and diagnosing connection-related failures.

Database connection management is the discipline of establishing, maintaining, and releasing connections between an application and a database. It matters when an application must efficiently serve many concurrent users, especially in systems with high latency, variable load, or transactional workloads. A poorly managed connection pool leads to increased latency, database overloads, and unresponsive services. This page details the mechanics, patterns, and common pitfalls of managing database connections across the stack.

Establishing Connections

A database connection begins when an application process resolves a database endpoint and authenticates with the database server. This phase includes DNS resolution, TLS handshake, and protocol negotiation.

The client sends a STARTUP message over TCP to the PostgreSQL server, including key-value pairs in a parameters section. For example, a Go service connecting to a PostgreSQL database might use:

conn, err := pgx.Connect(context.Background(), "host=database.example.com port=5432 dbname=app user=app password=secret sslmode=require")

The pgx library sends the following STARTUP message:

STARTUP MESSAGE
  parameter: user = app
  parameter: database = app
  parameter: client_encoding = UTF8
  parameter: application_name = my-service
  parameter: sslmode = require
  parameter: options = -c statement_timeout=30000

The server responds with a READY_FOR_QUERY message, indicating it is ready to accept queries. If the sslmode=require parameter is used, the client must perform a TLS handshake before sending the STARTUP message. The pgx library uses crypto/tls with rootCAs and certificates to validate the server certificate chain.

For MySQL, the client sends a HandshakeResponse packet that includes the client capabilities, authentication data, and connection attributes. The client must define the client_flags, max_allowed_packet, and charset in the packet. A client using mysql in Go sends:

config := &mysql.Config{
    User:                 "app",
    Passwd:               "secret",
    Net:                  "tcp",
    Addr:                 "database.example.com:3306",
    DBName:               "app",
    Timeout:              30 * time.Second,
    ReadTimeout:          10 * time.Second,
    WriteTimeout:         10 * time.Second,
    AllowNativePasswords: true,
    AllowCleartextPasswords: true,
    InterpolateParams:    true,
}
conn, err := mysql.Connect(config.FormatDSN())

The server responds with a HandshakeResponse2 packet, and the client must authenticate using either AuthSwitchRequest or AuthMoreData if the authentication method requires additional data.

Key Failure Modes in Connection Establishment

Managing the Connection Lifecycle

Once a connection is established, the application must manage its lifecycle: keep it alive, detect failures, and reuse it efficiently.

Heartbeats and Idle Timeouts

Database clients use idle_timeout and keepalive to detect and recover from stale connections. PostgreSQL supports idle_in_transaction_session_timeout and idle_session_timeout in the server configuration.

The client sends PING messages periodically to keep the connection alive. For MySQL, the client can enable server_status in the HandshakeResponse to receive SERVER_STATUS flags in the OK response. The client can detect if the connection is idle by checking the server_status for SERVER_SESSION_STATE_CHANGED.

In PostgreSQL, a client can send a NOTIFY message to trigger a LISTEN on a channel:

LISTEN user_events;

The server sends a NOTIFY message when a new event is available. The client polls the connection using SELECT pg_sleep(1) in a loop, but this consumes a connection. To reduce overhead, the client can use POLL on the socket directly:

for {
    pollEvents, err := conn.Poll(context.Background(), 100*time.Millisecond)
    if err != nil {
        log.Printf("Poll error: %v", err)
        break
    }
    for _, event := range pollEvents {
        if event.Type == pgx.Notification {
            log.Printf("Received: %s", event.Payload)
        }
    }
}

Connection Failover and Reconnection

When a connection fails, the application must detect the failure and reconnect. The detection mechanism depends on the protocol.

PostgreSQL uses ASYNC_MESSAGE to send NOTIFY and LISTEN events. The client can use pgx's Conn interface to detect connection loss:

conn := pgx.Connect(context.Background(), dsn)
defer conn.Close(context.Background())

go func() {
    for {
        select {
        case <-conn.Conn().Context().Done():
            log.Println("Connection lost")
            conn, err := pgx.Connect(context.Background(), dsn)
            if err != nil {
                log.Fatal(err)
            }
            // Reattach listeners
            conn.Query(context.Background(), "LISTEN user_events")
            conn.Query(context.Background(), "LISTEN order_updates")
        }
    }
}()

MySQL uses COM_PING and COM_QUIT commands. The client can send a PING command at regular intervals:

for {
    _, err := conn.Ping()
    if err != nil {
        log.Printf("Connection lost: %v", err)
        // Reconnect
        conn, err = mysql.Connect(config.FormatDSN())
        if err != nil {
            log.Fatal(err)
        }
    }
    time.Sleep(5 * time.Second)
}

Common Pitfalls in Lifecycle Management

Connection Pooling

Connection pooling is the practice of reusing a pool of pre-established connections to serve multiple application requests. It reduces the overhead of connection establishment and improves throughput.

The pool maintains a set of idle connections and assigns them to requests. When a request needs a connection, it either takes one from the pool or creates a new one. When the request finishes, it returns the connection to the pool.

Pool Configuration and Behavior

A typical connection pool configuration includes:

The pool uses lifecycle events to manage connections:

For example, a PostgreSQL pool using pgxpool has the following configuration:

connection_pool:
  max_connections: 100
  min_connections: 10
  idle_timeout: 300s
  max_lifetime: 1h
  max_idle_time: 30m
  acquire_timeout: 30s

When a client acquires a connection, it calls:

conn, err := pool.Acquire(context.Background())
if err != nil {
    return err
}
defer pool.Release(conn)

The pool returns a pgx.Conn from the pool. The connection is automatically returned to the pool when the client calls pool.Release(conn).

Pooling Failure Modes

Reconnecting After Failures

When a connection fails, the pool must reconnect and recover. The recovery process includes:

A robust connection management system must handle both transient and persistent failures.

Handling Transient Failures

A transient failure is a short-lived connection loss. The client retries the query until the connection is restored.

func executeWithRetry(ctx context.Context, pool *pgxpool.Pool, query string, args ...interface{}) (pgx.Rows, error) {
    for attempts := 0; attempts < 5; attempts++ {
        conn, err := pool.Acquire(ctx)
        if err != nil {
            log.Printf("Acquire failed: %v", err)
            time.Sleep(time.Second * time.Duration(attempts+1))
            continue
        }

        rows, err := conn.Query(ctx, query, args...)
        if err != nil {
            pool.Release(conn)
            log.Printf("Query failed: %v", err)
            time.Sleep(time.Second * time.Duration(attempts+1))
            continue
        }

        return rows, nil
    }

    return nil, fmt.Errorf("failed to execute query after 5 attempts")
}

Handling Persistent Failures

A persistent failure is a long-lived connection loss. The pool must reconnect all connections and rebuild state.

The pool uses a reconnect function that:

  1. Closes all connections in the pool.
  2. Creates new connections from the configuration.
  3. Sets up session state (e.g., SET search_path TO app).
  4. Re-establishes listeners (LISTEN channels).
  5. Prepares statements (PREPARE).
func (p *Pool) Reconnect(ctx context.Context) error {
    var wg sync.WaitGroup
    var mu sync.Mutex
    var errors []error

    // Close all connections
    p.mu.Lock()
    for _, conn := range p.connections {
        wg.Add(1)
        go func(c *pgx.Conn) {
            defer wg.Done()
            if err := c.Close(ctx); err != nil {
                mu.Lock()
                errors = append(errors, err)
                mu.Unlock()
            }
        }(conn)
    }
    p.mu.Unlock()

    wg.Wait()

    // Create new connections
    for i := 0; i < p.maxConnections; i++ {
        conn, err := pgx.Connect(ctx, p.dsn)
        if err != nil {
            errors = append(errors, err)
            continue
        }

        p.mu.Lock()
        p.connections = append(p.connections, conn)
        p.mu.Unlock()
    }

    // Reinitialize connections
    for _, conn := range p.connections {
        _, err := conn.Exec(ctx, "SET search_path TO app")
        if err != nil {
            errors = append(errors, err)
        }
        _, err = conn.Exec(ctx, "LISTEN user_events")
        if err != nil {
            errors = append(errors, err)
        }
    }

    if len(errors) > 0 {
        return fmt.Errorf("reconnect failed: %v", errors)
    }

    return nil
}

Monitoring and Debugging

Monitoring connection management involves tracking:

A common error is FATAL: connection to server was lost. The client must log the error message, code, and server_error details.

The pgx library logs pgx.Conn events to a log.Logger. The client can enable debug logging to track ACQUIRE, RELEASE, CLOSE, and ERROR events.

logger := &pgxlog.Logger{
    Logger: log.New(os.Stdout, "pgx: ", log.LstdFlags),
    Level:  pgxlog.Debug,
}
conn, err := pgx.ConnectConfig(context.Background(), &pgx.ConnConfig{
    Host:     "database.example.com",
    Port:     5432,
    Database: "app",
    User:     "app",
    Password: "secret",
    Logger:   logger,
})

Summary

Database connection management is not merely about opening and closing connections. It is about maintaining a pool of reusable connections, detecting failures, and recovering gracefully. The key elements are:

A well-managed connection system ensures that database access is fast, reliable, and scalable.

This page was rewritten on 17 October 2026. It replaced a templated version that was not specific to its own title. How these pages are written and checked. If anything here is wrong, tell us at [email protected] and we will correct it.