Go and PostgreSQL: How Not to Kill the Database with a Connection Pool

A connection pool is not a performance feature by itself. In a Go service talking to PostgreSQL, it is a concurrency control mechanism, a backpressure boundary, and sometimes the fastest way to take the database down.

The common failure mode is simple: every service replica gets its own pool, every pool is configured generously, Kubernetes scales the replicas, PostgreSQL accepts too many connections, and the database spends more time managing sessions than executing useful queries. The application sees timeouts, engineers raise limits, and the system becomes less predictable.

The right question is not “how many connections can my app use?” The right question is “how many concurrent database operations can PostgreSQL safely execute for this workload?”

The pool is part of the database architecture

In Go, *sql.DB from database/sql is not a single database connection. It is a concurrency-safe handle that owns a pool of connections. The same idea applies to pgxpool in the pgx ecosystem, although the APIs and behavior are different.

That distinction matters because connection pool settings are not local implementation details. They affect:

• how many PostgreSQL backends are created

• how long requests wait before reaching the database

• how quickly overload is detected

• whether transactions block other work

• whether PgBouncer helps or creates incompatibilities

• how deadlocks and lock waits surface in application logs

A service can have clean Go code and still overload PostgreSQL if each deployment unit creates too many database sessions.

What teams usually get wrong

The most common mistake is treating max_connections as a scaling knob. It is not. Raising it may postpone errors, but it also increases the amount of memory, scheduling, and coordination PostgreSQL must handle for open sessions. Past a certain point, more connections often mean less useful throughput.

The second mistake is sizing pools per application instance without accounting for total fleet concurrency.

For example, if one service replica has MaxOpenConns = 50 and production runs 20 replicas, that service alone can open up to 1000 database connections. Add background workers, migrations, admin tools, and other services, and the real number becomes higher than anyone intended.

The third mistake is relying on request timeouts while leaving database waits unbounded. A user request may be canceled, but a transaction, query, or lock wait can continue longer than expected unless the code and database settings are designed to stop it.

A connection pool should be sized from the database outward, not from the application inward.

database/sql: the minimum safe configuration

With database/sql, the default behavior is too open-ended for many production services. You should set explicit pool limits and lifetimes.

db, err := sql.Open("pgx", dsn)
if err != nil {
    return err
}

db.SetMaxOpenConns(20)
db.SetMaxIdleConns(10)
db.SetConnMaxIdleTime(5 * time.Minute)
db.SetConnMaxLifetime(30 * time.Minute)

ctx, cancel := context.WithTimeout(context.Background(), 3*time.Second)
defer cancel()

if err := db.PingContext(ctx); err != nil {
    return err
}

These values are examples, not universal defaults. A write-heavy service, a read-mostly API, and a background job processor need different limits. The important part is that the numbers are deliberate and calculated together with deployment scale.

A practical starting formula is:

service_total_connections =
  replicas * max_open_connections_per_replica

Then compare that total with the database capacity reserved for that service, not with PostgreSQL’s global max_connections.

pgx and pgxpool: more control, same responsibility

Many Go teams use pgx directly because it provides a PostgreSQL-focused driver, richer type handling, and a pool designed explicitly for pgx connections. That does not remove the need for sizing. It just makes some controls more direct.

config, err := pgxpool.ParseConfig(dsn)
if err != nil {
    return err
}

config.MaxConns = 20
config.MinConns = 2
config.MaxConnIdleTime = 5 * time.Minute
config.MaxConnLifetime = 30 * time.Minute
config.HealthCheckPeriod = time.Minute

pool, err := pgxpool.NewWithConfig(context.Background(), config)
if err != nil {
    return err
}
defer pool.Close()

The same production rule applies: MaxConns is per process. If your service runs in multiple containers, multiply it.

Pool sizing by runtime behavior

A decision-useful pool design starts with workload shape. You do not need fake precision, but you do need explicit assumptions.

The goal is not to make every service conservative. The goal is to make concurrency intentional.

PgBouncer is not a license to ignore pools

PgBouncer can be useful when many application processes would otherwise create too many PostgreSQL connections. It can also hide pressure until the queue moves elsewhere.

The key distinction is the pooling mode.

Transaction pooling is attractive because it reduces server connection pressure, but it changes assumptions. Session-level settings, temporary tables, advisory locks scoped to a session, and some prepared statement patterns can behave differently than developers expect.

In Go applications, this means PgBouncer should be introduced as an architectural decision, not as a transparent network hop.

Timeouts must exist at every layer

Timeouts should form a chain. The outer layer protects users and upstream callers. The application layer protects goroutines and pool capacity. The database layer protects PostgreSQL from work that no longer has business value.

A typical request path needs:

• an HTTP request timeout or upstream deadline

• a Go context.Context passed into every query

• a pool acquisition timeout through context deadline

• PostgreSQL-side statement_timeout

• optionally, lock_timeout for code paths sensitive to lock waits

In Go, the context must reach the query call. Creating a timeout and then calling non-context methods defeats the purpose.

func LoadAccount(ctx context.Context, db *sql.DB, id int64) (Account, error) {
    ctx, cancel := context.WithTimeout(ctx, 500*time.Millisecond)
    defer cancel()

    var account Account

    err := db.QueryRowContext(ctx, `
        SELECT id, email, status
        FROM accounts
        WHERE id = $1
    `, id).Scan(&account.ID, &account.Email, &account.Status)

    if err != nil {
        return Account{}, err
    }

    return account, nil
}

Database-side timeouts are also important because not every failure starts in Go. Lock waits, inefficient plans, and overloaded disks can all make a query sit longer than intended.

ALTER ROLE app_user SET statement_timeout = '2s';
ALTER ROLE app_user SET lock_timeout = '500ms';
ALTER ROLE app_user SET idle_in_transaction_session_timeout = '10s';

These values are workload-dependent. Reporting jobs, migrations, and interactive APIs should not automatically share the same limits.

Transactions pin connections

A transaction uses one connection until it commits or rolls back. In a small pool, a few long transactions can block unrelated requests from acquiring connections.

This is where many production incidents start. The code looks harmless, but it holds a transaction while doing too much work.

tx, err := db.BeginTx(ctx, nil)
if err != nil {
    return err
}
defer tx.Rollback()

if _, err := tx.ExecContext(ctx, `
    UPDATE orders
    SET status = 'processing'
    WHERE id = $1
`, orderID); err != nil {
    return err
}

// Bad idea: remote call while transaction is open.
// The connection and row locks remain held.
if err := paymentClient.Charge(ctx, orderID); err != nil {
    return err
}

if err := tx.Commit(); err != nil {
    return err
}

The safer pattern is to keep transactions focused on database state changes. Do external calls before or after the transaction, or use an outbox pattern when the workflow requires reliable coordination.

A transaction should be short not because short code is prettier, but because it reduces lock duration, connection occupancy, retry complexity, and deadlock probability.

Deadlocks are usually design feedback

PostgreSQL can detect deadlocks and abort one transaction. That is helpful, but it does not make deadlocks harmless. They still increase latency, trigger retries, and can produce user-visible failures.

In Go services, deadlocks often come from inconsistent update order. One code path updates accounts then orders; another updates orders then accounts. Under concurrency, both can wait on each other.

The practical mitigation is boring and effective:

1. update shared resources in a consistent order

2. keep transactions small

3. avoid user or network waits inside transactions

4. index foreign keys used in concurrent updates and deletes

5. retry only transactions that are safe to retry

A retry loop must be narrow. Retrying a whole HTTP handler with external side effects can duplicate work. Retrying a small database transaction that is idempotent is easier to reason about.

What to adopt first

For an existing Go and PostgreSQL system, start with the changes that improve safety without rewriting the service.

• Inventory every application that connects to PostgreSQL.

• Multiply pool size by replica count.

• Reserve connection budgets per service.

• Set explicit pool limits in database/sql or pgxpool.

• Add context deadlines to all query paths.

• Set database-side statement and lock timeouts by role.

• Audit transactions that include network calls, loops, or user-driven waits.

• Track pool wait duration, open connections, slow queries, lock waits, and deadlock errors.

This work is usually more valuable than tuning one query in isolation, because it changes how the whole system behaves under load.

If this is part of your day-to-day work, the most relevant certification to review is PostgreSQL Architect, especially if you are responsible for production database behavior, query architecture, and operational limits.

Conclusion

A Go connection pool can protect PostgreSQL, or it can multiply traffic until the database becomes the bottleneck. The difference is not the library choice. database/sql, pgx, and PgBouncer can all work well when the pool is sized against real database capacity, timeouts are layered, and transactions are kept short.

Treat the pool as a production boundary. Make its limits explicit. Count connections across replicas. Use PgBouncer only with a clear understanding of pooling mode. Pass contexts into queries. Put database-side limits in place. Most importantly, design transactions so they do not hold connections and locks while waiting on the outside world.

That is how a Go service remains fast without turning PostgreSQL into the queue it was never meant to be.