PostgreSQL Connection Pool Exhaustion: PgBouncer Tuning and Leak Detection

Introduction

PostgreSQL has a finite number of connections it can handle, and each connection consumes memory and OS resources. When I deployed a new version of my Go API server, it started exhausting the PostgreSQL connection pool within minutes of startup. Users saw "FATAL: too many connections" errors, and the entire application became unresponsive.

The root cause was a connection leak in the Go code — a database connection was being acquired but never returned to the pool under certain error conditions. Combined with a missing PgBouncer configuration, this leak quickly consumed all available connections. This post covers how I detected the leak, fixed the code, and configured PgBouncer as a safety net.

Environment

• PostgreSQL: 16.1 on Ubuntu 22.04
• Go driver: pgx v5.5.0
• Connection pool: database/sql standard pool
• PgBouncer: 1.21.0 (installed but not configured)
• Max connections: 100 (PostgreSQL default)
• Application instances: 3 replicas

Problem

After deploying the new API version, the PostgreSQL logs showed a rapid increase in connections:

LOG: connection received: host=10.0.0.11 port=43210 user=api_server dbname=trading
LOG: connection received: host=10.0.0.11 port=43211 user=api_server dbname=trading
LOG: connection received: host=10.0.0.11 port=43212 user=api_server dbname=trading
FATAL: too many connections for role "api_server"

Within 5 minutes of startup, all 100 connections were consumed. Each of the 3 API instances was holding approximately 33 connections, which was far more than expected.

I checked the connection count per application instance:

SELECT client_addr, state, count(*)
FROM pg_stat_activity
WHERE usename = 'api_server'
GROUP BY client_addr, state;

 client_addr  | state        | count
--------------+--------------+------
 10.0.0.11    | idle         |    28
 10.0.0.11    | active       |     5
 10.0.0.12    | idle         |    31
 10.0.0.12    | active       |     4
 10.0.0.13    | idle         |    29
 10.0.0.13    | active       |     3

There were 88 idle connections and only 12 active connections. The idle connections were the problem — they were acquired from the pool but never returned.

Analysis

I examined the Go code for connection handling:

func processOrder(db *sql.DB, orderID string) error {
    tx, err := db.BeginTx(context.Background(), nil)
    if err != nil {
        return fmt.Errorf("begin tx: %w", err)
    }

    // Check if order exists
    var exists bool
    err = tx.QueryRow("SELECT EXISTS(SELECT 1 FROM orders WHERE id = $1)", orderID).Scan(&exists)
    if err != nil {
        return fmt.Errorf("check order: %w", err) // BUG: tx not rolled back
    }

    if !exists {
        return fmt.Errorf("order %s not found", orderID) // BUG: tx not rolled back
    }

    // Process order...
    _, err = tx.Exec("UPDATE orders SET status = 'processed' WHERE id = $1", orderID)
    if err != nil {
        tx.Rollback()
        return fmt.Errorf("update order: %w", err)
    }

    return tx.Commit()
}

The bug was clear: when the order does not exist, the function returns an error without rolling back the transaction. This leaves the connection in an open transaction state, and it is never returned to the pool. Over time, these leaked connections accumulate and exhaust the pool.

The standard database/sql pool in Go has a MaxOpenConns limit, but it does not automatically close connections that have been idle for too long. I also discovered that the Go code had SetMaxIdleConns set too high:

db, _ := sql.Open("pgx", connString)
db.SetMaxOpenConns(50)   // Too high for 3 instances sharing 100 connections
db.SetMaxIdleConns(25)   // Too many idle connections kept alive
db.SetConnMaxLifetime(0)  // No lifetime limit — connections live forever
db.SetConnMaxIdleTime(0)  // No idle timeout

Solution

I made three changes to fix the connection exhaustion:

1. Fix the transaction leak in the Go code:

func processOrder(db *sql.DB, orderID string) error {
    tx, err := db.BeginTx(context.Background(), nil)
    if err != nil {
        return fmt.Errorf("begin tx: %w", err)
    }
    defer tx.Rollback() // Always roll back — no-op if committed

    var exists bool
    err = tx.QueryRow("SELECT EXISTS(SELECT 1 FROM orders WHERE id = $1)", orderID).Scan(&exists)
    if err != nil {
        return fmt.Errorf("check order: %w", err)
    }

    if !exists {
        return fmt.Errorf("order %s not found", orderID)
    }

    _, err = tx.Exec("UPDATE orders SET status = 'processed' WHERE id = $1", orderID)
    if err != nil {
        return fmt.Errorf("update order: %w", err)
    }

    return tx.Commit()
}

The defer tx.Rollback() ensures the transaction is always cleaned up. If Commit() succeeds, Rollback() is a no-op.

2. Configure proper pool settings:

db, _ := sql.Open("pgx", connString)
db.SetMaxOpenConns(25)              // 100 total / 3 instances / 2 for safety
db.SetMaxIdleConns(10)              // Keep fewer idle connections
db.SetConnMaxLifetime(5 * time.Minute)  // Close connections after 5 minutes
db.SetConnMaxIdleTime(3 * time.Minute)  // Close idle connections after 3 minutes

3. Configure PgBouncer as a connection pooler:

; /etc/pgbouncer/pgbouncer.ini
[databases]
trading = host=127.0.0.1 port=5432 dbname=trading

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

; Pool mode — transaction mode is best for Go applications
pool_mode = transaction

; Connection limits
default_pool_size = 20
max_client_conn = 200
max_db_connections = 50

; Timeouts
server_idle_timeout = 300
client_idle_timeout = 600
query_timeout = 30

; Logging
log_connections = 1
log_disconnections = 1
stats_period = 60

Key PgBouncer settings:

• pool_mode = transaction — Connections are returned to the pool after each transaction, not after the client disconnects
• default_pool_size = 20 — Maximum connections to the backend per database
• max_client_conn = 200 — Maximum client connections PgBouncer accepts
• server_idle_timeout = 300 — Close idle backend connections after 5 minutes

I then updated the application to connect through PgBouncer:

// Connect through PgBouncer instead of directly to PostgreSQL
db, _ := sql.Open("pgx", "postgresql://api_server:password@pgbouncer:6432/trading?sslmode=disable")

After deploying all fixes:

SELECT client_addr, state, count(*)
FROM pg_stat_activity
WHERE usename = 'api_server'
GROUP BY client_addr, state;

 client_addr  | state  | count
--------------+--------+------
 10.0.0.11    | idle   |     5
 10.0.0.11    | active |     3
 10.0.0.12    | idle   |     4
 10.0.0.12    | active |     3
 10.0.0.13    | idle   |     5
 10.0.0.13    | active |     2

Only 24 total connections instead of 90. No more connection exhaustion errors.

I also added monitoring for connection pool health:

-- Monitor connection usage
SELECT
    datname,
    numbackends,
    numbackends - (SELECT count(*) FROM pg_stat_activity WHERE state = 'active') as idle_connections,
    max_conn as max_connections,
    round(100.0 * numbackends / max_conn, 1) as usage_pct
FROM pg_stat_database
JOIN (SELECT setting::int as max_conn FROM pg_settings WHERE name = 'max_connections') m ON true
WHERE datname = 'trading';

Lessons Learned

1. Always defer Rollback — In Go, use defer tx.Rollback() immediately after db.Begin(). It is a no-op after a successful commit but prevents connection leaks on error paths.
2. Set ConnMaxIdleTime — Without an idle timeout, connections remain open indefinitely. Set it to a reasonable value (1-5 minutes) to reclaim idle connections.
3. Use PgBouncer in transaction mode — PgBouncer in transaction mode returns connections to the pool after each transaction, preventing idle connection accumulation from application bugs.
4. Monitor connection counts — Set up alerts for connection usage above 80%. Detecting exhaustion early prevents cascading failures.
5. Calculate per-instance limits — If you have N instances sharing a connection pool of M connections, each instance should use at most M/N connections.

This blog does not accept any external sponsorships, affiliate marketing, or ad revenue.