Tuning pgxpool for High-Concurrency Go Services

This guide is part of Go database/sql Pool Internals. pgxpool is pgx’s native pool, used instead of database/sql when a Go service wants PostgreSQL-specific types, the binary protocol, batching and COPY without a translation layer. It is a different pool from the standard library’s, with its own defaults, its own statistics and one behaviour that catches people out: MaxConns defaults to the greater of four and the number of CPUs, so the same binary opens a different number of connections depending on the machine it lands on.

ERROR  failed to acquire connection: context deadline exceeded
# with, on the database:
SELECT count(*) FROM pg_stat_activity WHERE application_name='orders-api';   -- 8 on a laptop, 64 in production

The objective is a pool whose size comes from measured concurrency rather than from the host’s core count, whose lifetimes suit the network path, and whose statistics are exported so saturation is visible before it becomes a deadline error.

Rapid incident diagnosis

Read the pool’s statistics. pgxpool exposes more than database/sql does, including the share of acquisitions that had to wait:

s := pool.Stat()
log.Printf("acquired=%d idle=%d total=%d max=%d constructing=%d acquire_count=%d empty_acquire=%d canceled=%d wait=%s",
    s.AcquiredConns(), s.IdleConns(), s.TotalConns(), s.MaxConns(), s.ConstructingConns(),
    s.AcquireCount(), s.EmptyAcquireCount(), s.CanceledAcquireCount(), s.AcquireDuration())

Three derived numbers decide the diagnosis:

wait_share   = EmptyAcquireCount ÷ AcquireCount          -- how often callers found no idle connection
mean_acquire = AcquireDuration ÷ AcquireCount            -- average time to get one
cancel_rate  = CanceledAcquireCount over time            -- deadlines expiring during acquisition

CanceledAcquireCount rising is the direct counterpart of the context deadline exceeded errors in the log: those requests gave up while waiting.

Check what the connections are doing. With application_name set in the connection string, the server distinguishes busy from idle:

SELECT state, count(*), max(now() - state_change) AS longest
FROM pg_stat_activity WHERE application_name = 'orders-api' GROUP BY state;

AcquiredConns equal to MaxConns with sessions active and fast queries means the pool is too small; with sessions idle, some code path holds connections without using them — usually a pgx.Tx kept open across an HTTP call, or a Rows that is never closed.

Look for unclosed rows. In pgx, a Rows holds the connection until it is fully read or closed. A single missing defer rows.Close() leaks one connection per call:

grep -rn "\.Query(" --include=*.go . | wc -l
grep -rn "defer rows.Close()" --include=*.go . | wc -l    # the two counts should be close

Check the effective maximum. Because the default depends on runtime.NumCPU(), the value in production may differ from every other environment:

log.Printf("MaxConns=%d MinConns=%d", pool.Config().MaxConns, pool.Config().MinConns)

Mathematical sizing or parameter formula

Size from concurrent queries, not cores:

MaxConns = ceil( queries_per_second × mean_query_seconds ÷ 0.7 )
fleet    = replicas × MaxConns ≤ usable_connections_for_this_service

A service running 3,000 queries per second with a mean server-side time of 2.5 ms has 3000 × 0.0025 = 7.5 in flight; MaxConns = 11. Sixteen replicas claim 176 connections.

MinConns keeps warm connections; because pgx opens them lazily and closes them only when idle beyond MaxConnIdleTime, a small minimum avoids first-request latency without reserving much:

MinConns ≈ average in-flight at the daily trough, typically 1–4

Lifetimes need to sit inside the network and server limits:

MaxConnLifetime      < shortest upstream lifetime limit; 30–60 min is typical
MaxConnLifetimeJitter ≈ 10 % of MaxConnLifetime        -- avoids a cohort expiring together
MaxConnIdleTime      < shortest network idle timeout   -- 2–5 min behind NAT
HealthCheckPeriod    ≤ MaxConnIdleTime ÷ 2             -- the background check also refreshes MinConns

MaxConnLifetimeJitter deserves a note: without it, every connection created during a deployment expires at the same moment 30 minutes later, producing a reconnect spike. With 10 % jitter, the same cohort spreads over three minutes.

Acquisition is bounded by the context, not by a pool setting. There is no acquire_timeout; the caller’s context deadline is the bound, which makes deadline propagation the natural pattern:

acquire_budget = remaining_request_time × 0.4

Exact remediation & configuration

Configure through pgxpool.ParseConfig so the connection string and the pool settings live together:

cfg, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL") +
    "?application_name=orders-api&statement_cache_capacity=100")
if err != nil {
    log.Fatal(err)
}

cfg.MaxConns = 11
cfg.MinConns = 2
cfg.MaxConnLifetime = 30 * time.Minute
cfg.MaxConnLifetimeJitter = 3 * time.Minute
cfg.MaxConnIdleTime = 2 * time.Minute
cfg.HealthCheckPeriod = 1 * time.Minute

// Runs once per new connection: session settings, type registration
cfg.AfterConnect = func(ctx context.Context, c *pgx.Conn) error {
    _, err := c.Exec(ctx, "SET statement_timeout = '2s'")
    return err
}

// Runs before a connection is handed to a caller; returning false discards it
cfg.BeforeAcquire = func(ctx context.Context, c *pgx.Conn) bool {
    return c.Ping(ctx) == nil
}

pool, err := pgxpool.NewWithConfig(context.Background(), cfg)
if err != nil {
    log.Fatal(err)
}
defer pool.Close()

BeforeAcquire with a ping costs a round trip per acquisition; use it only when the network path is known to drop connections silently, and prefer HealthCheckPeriod plus lifetimes otherwise.

Always pass a context with a deadline:

ctx, cancel := context.WithTimeout(r.Context(), 2*time.Second)
defer cancel()

rows, err := pool.Query(ctx, "SELECT id, total FROM orders WHERE customer_id = $1", customerID)
if err != nil {
    return fmt.Errorf("query orders: %w", err)
}
defer rows.Close()          // without this, the connection is held until the Rows is drained

Use pgx.Tx narrowly. A transaction holds its connection for its whole life, so external calls belong outside it:

receipt, err := gateway.Charge(ctx, orderID)   // no connection held
if err != nil {
    return err
}
tx, err := pool.Begin(ctx)
if err != nil {
    return err
}
defer tx.Rollback(ctx)
if _, err = tx.Exec(ctx, "UPDATE orders SET receipt=$1 WHERE id=$2", receipt.ID, orderID); err != nil {
    return err
}
return tx.Commit(ctx)

Export the statistics. A short collector turns Stat() into Prometheus series:

type poolCollector struct{ p *pgxpool.Pool }

func (c poolCollector) Describe(ch chan<- *prometheus.Desc) { prometheus.DescribeByCollect(c, ch) }

func (c poolCollector) Collect(ch chan<- prometheus.Metric) {
    s := c.p.Stat()
    g := prometheus.GaugeValue
    k := prometheus.CounterValue
    ch <- prometheus.MustNewConstMetric(descAcquired, g, float64(s.AcquiredConns()))
    ch <- prometheus.MustNewConstMetric(descIdle, g, float64(s.IdleConns()))
    ch <- prometheus.MustNewConstMetric(descMax, g, float64(s.MaxConns()))
    ch <- prometheus.MustNewConstMetric(descAcquireTotal, k, float64(s.AcquireCount()))
    ch <- prometheus.MustNewConstMetric(descEmptyAcquire, k, float64(s.EmptyAcquireCount()))
    ch <- prometheus.MustNewConstMetric(descCanceled, k, float64(s.CanceledAcquireCount()))
    ch <- prometheus.MustNewConstMetric(descAcquireSeconds, k, s.AcquireDuration().Seconds())
}

Choosing between pgxpool and database/sql

Both are reasonable; the choice should follow what the service needs rather than habit.

pgxpool suits services that use PostgreSQL features directly: binary parameters and results, COPY FROM for bulk loading, SendBatch for pipelining, LISTEN/NOTIFY on a dedicated connection, and rich type mapping for arrays, JSON, ranges and composite types. Its statistics are better, and its acquisition path is slightly cheaper because there is no database/sql layer above it.

database/sql with the pgx stdlib driver suits services that need the standard interface: an ORM or query builder that expects *sql.DB, code shared across databases, or libraries — migration tools, tracing wrappers — built on the standard types. The same pgx driver sits underneath, so the wire protocol and type handling are unchanged; only the pool and the API differ.

Using both is normal. A service can hold a *pgxpool.Pool for its own queries and a *sql.DB for a migration library, at the cost of two pools whose sizes must both fit the budget. When that matters, stdlib.OpenDBFromPool wraps an existing pgxpool as a *sql.DB, so one pool serves both APIs.

Validation & verification

The configured maximum is in effect everywhere. Log pool.Config().MaxConns at startup and confirm it matches across environments; the database’s session count should equal replicas × MaxConns at peak.

Waiting is rare and short. Under peak load, EmptyAcquireCount ÷ AcquireCount should stay under a few percent, and CanceledAcquireCount should be zero. Any cancellation means a request’s deadline expired while waiting.

No connection leaks. Run the full endpoint suite several hundred times and confirm AcquiredConns() returns to zero afterwards:

if s := pool.Stat(); s.AcquiredConns() != 0 {
    t.Fatalf("connections still acquired after tests: %d", s.AcquiredConns())
}

A non-zero value is almost always a missing rows.Close() or an un-rolled-back transaction.

Jitter spreads expiry. After a deploy, watch new connections per minute on the database for the hour that follows. With jitter configured, the expiry of the initial cohort should appear as a broad low bump rather than a spike.

Frequently Asked Questions

Related

• Go database/sql Pool Internals — the parent guide to the standard library pool.
• Configuring SetMaxOpenConns and SetMaxIdleConns — the equivalent settings in database/sql.
• Exporting Go database/sql Pool Metrics to Prometheus — including a pgxpool collector.
• Understanding Connection Acquisition Timeouts in Go — how contexts bound the wait.