pgx Connection Pools With pgxpool and Sensible Settings

A connection pool is the part of a database client that decides how many requests run at once, and the setting that decides it is usually left at the default and found out during an incident. pgx's pool, pgxpool, is small and has few moving parts, but each has an effect worth seeing once. This article configures it on Go 1.25.6 with pgx v5.11.0 and PostgreSQL 18.6, reads what it reports, and shows what happens when it is full, when its connections age, and when you shut it down.

It belongs to the database cornerstone. The standard library's pool has equivalent settings, measured in connection pool settings, and pgxpool's place in the toolkit is in the pgx tour. Leaks and hooks follow in pgx mistakes and connection lifecycle.

Defaults, settings and statistics

package main

import (
	"context"
	"errors"
	"fmt"
	"log"
	"sync"
	"time"

	"github.com/jackc/pgx/v5/pgxpool"
)

const base = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

func must(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

func newPool(opts string) *pgxpool.Pool {
	p, err := pgxpool.New(context.Background(), base+opts)
	must(err)
	return p
}

func main() {
	ctx := context.Background()

	// 1. The defaults pgxpool applies when a setting is missing.
	cfg, err := pgxpool.ParseConfig(base)
	must(err)
	fmt.Printf("1. defaults: MaxConns %d (the larger of 4 and the CPU count), MinConns %d, MaxConnLifetime %v, MaxConnIdleTime %v, HealthCheckPeriod %v\n",
		cfg.MaxConns, cfg.MinConns, cfg.MaxConnLifetime, cfg.MaxConnIdleTime, cfg.HealthCheckPeriod)

	// 2. Settings go in the connection string, with the pool_ prefix.
	p := newPool("&pool_max_conns=3&pool_min_conns=1&pool_max_conn_lifetime=1h&pool_max_conn_idle_time=30m&pool_health_check_period=1m")
	s := p.Stat()
	fmt.Printf("2. from the string: max %d, and %d connection already open because min_conns is 1\n", s.MaxConns(), s.TotalConns())
	time.Sleep(300 * time.Millisecond)
	s = p.Stat()
	fmt.Printf("   after a moment: total %d idle %d\n", s.TotalConns(), s.IdleConns())

	// 3. More goroutines than connections: the rest wait, and the pool counts how long.
	var wg sync.WaitGroup
	start := time.Now()
	for i := 0; i < 12; i++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			_, err := p.Exec(ctx, "select pg_sleep(0.1)")
			must(err)
		}()
	}
	wg.Wait()
	s = p.Stat()
	fmt.Printf("3. 12 queries of 100ms on a pool of 3: %v elapsed (4 rounds), %d of %d acquires had to wait, waited %v in total\n",
		time.Since(start).Round(50*time.Millisecond), s.EmptyAcquireCount(), s.AcquireCount(), s.AcquireDuration().Round(100*time.Millisecond))
	fmt.Printf("   connections ever opened: %d, the most in use at once was %d\n", s.NewConnsCount(), s.MaxConns())

	// 4. A deadline on Acquire turns a full pool into an error you can handle.
	hold := make([]*pgxpool.Conn, 0, 3)
	for i := 0; i < 3; i++ {
		c, err := p.Acquire(ctx)
		must(err)
		hold = append(hold, c)
	}
	short, cancel := context.WithTimeout(ctx, 200*time.Millisecond)
	defer cancel()
	_, err = p.Acquire(short)
	fmt.Println("4. every connection held, Acquire with a 200ms deadline:", err, "| deadline exceeded:", errors.Is(err, context.DeadlineExceeded))
	s = p.Stat()
	fmt.Printf("   stats: acquired %d, canceled acquires %d\n", s.AcquiredConns(), s.CanceledAcquireCount())
	for _, c := range hold {
		c.Release()
	}

	// 5. MaxConnLifetime replaces old connections.
	short2 := newPool("&pool_max_conns=1&pool_max_conn_lifetime=300ms&pool_health_check_period=100ms")
	var pid1, pid2 int
	must(short2.QueryRow(ctx, "select pg_backend_pid()").Scan(&pid1))
	time.Sleep(900 * time.Millisecond)
	must(short2.QueryRow(ctx, "select pg_backend_pid()").Scan(&pid2))
	fmt.Println("5. MaxConnLifetime 300ms: the session behind the pool changed after 900ms:", pid1 != pid2, "| connections destroyed for age:", short2.Stat().MaxLifetimeDestroyCount() > 0)
	short2.Close()

	// 6. Pool.Close waits for connections in use.
	closed := make(chan time.Duration)
	c, err := p.Acquire(ctx)
	must(err)
	go func() {
		t := time.Now()
		p.Close()
		closed <- time.Since(t)
	}()
	time.Sleep(300 * time.Millisecond)
	c.Release()
	fmt.Println("6. Close blocked until the last connection was released:", (<-closed).Round(100*time.Millisecond) >= 300*time.Millisecond)
}

1. defaults: MaxConns 10 (the larger of 4 and the CPU count), MinConns 0, MaxConnLifetime 1h0m0s, MaxConnIdleTime 30m0s, HealthCheckPeriod 1m0s
2. from the string: max 3, and 0 connection already open because min_conns is 1
   after a moment: total 1 idle 1
3. 12 queries of 100ms on a pool of 3: 400ms elapsed (4 rounds), 11 of 12 acquires had to wait, waited 1.9s in total
   connections ever opened: 3, the most in use at once was 3
4. every connection held, Acquire with a 200ms deadline: context deadline exceeded | deadline exceeded: true
   stats: acquired 3, canceled acquires 1
5. MaxConnLifetime 300ms: the session behind the pool changed after 900ms: true | connections destroyed for age: true
6. Close blocked until the last connection was released: true

pgxpool.ParseConfig(dsn) returns the configuration before the pool exists, so you can read what it would use. The first line shows the defaults on the test machine: MaxConns 10, which is max(4, runtime.NumCPU()), MinConns 0, MaxConnLifetime 1 hour, MaxConnIdleTime 30 minutes and HealthCheckPeriod 1 minute. A default that depends on the CPU count is a trap in containers: a service with a CPU limit of 0.5 may still see all the host's cores and open 64 connections per instance, and ten instances then ask the database for 640. Always set MaxConns yourself.

Settings can be given in code, on the config, or in the connection string with a pool_ prefix, which keeps them with the rest of the configuration: pool_max_conns, pool_min_conns, pool_max_conn_lifetime, pool_max_conn_idle_time and pool_health_check_period. The second block builds a pool from pool_max_conns=3&pool_min_conns=1&... and reads it back: max 3.

MinConns asks the pool to keep at least that many connections open. The pool created it in the background: right after New returned there were 0 connections, and a moment later 1 total and 1 idle. Do not assume the first request is fast; if it matters, run a query at start-up to warm the pool.

Sizing MaxConns

The right number is smaller than most people expect. A PostgreSQL server does its best with a number of active connections near its CPU count times a small factor, and past that, extra connections add memory use (a few megabytes each, as backend processes) and contention without adding throughput. Work out the budget from the database side: take max_connections, subtract what administrators and migrations need, divide by the number of service instances, and use that. If one instance needs more concurrency than its share, the answer is usually a pooler such as PgBouncer in front, not a bigger pool; see pgx with PgBouncer.

A pool larger than the work it can do helps nothing: if each request holds a connection for 5ms and you serve 200 requests a second, you need about one connection busy at a time, not fifty. Start at 10 to 20 per instance, watch Stat, and raise it only when waits show up in the numbers below.

What waiting looks like

The third block starts 12 goroutines that each run pg_sleep(0.1) on a pool of 3. The queries took 100ms each, so three at a time means four rounds, and the program finished in 400ms. The pool's own statistics tell the same story: 11 of 12 acquires had to wait (EmptyAcquireCount, the number of times a caller found no idle connection), the total time callers spent waiting was 1.9s (AcquireDuration), and only 3 connections were ever opened (NewConnsCount). The three that started first did not wait; the other nine queued.

These counters are the ones to export as metrics: AcquiredConns, IdleConns, TotalConns, EmptyAcquireCount, AcquireDuration and CanceledAcquireCount. Alert on AcquireDuration per request growing: it is the cost of an undersized pool or of slow queries holding connections, and it is invisible in query timings, because the clock starts after the connection is obtained.

A full pool and a deadline

When every connection is in use, Acquire waits, without limit unless the context has one. The fourth block holds all three connections and calls Acquire with a 200 millisecond deadline: it returned context deadline exceeded, errors.Is(err, context.DeadlineExceeded) was true, and CanceledAcquireCount went to 1. That error is the one to expect under overload, and it is the right behaviour: a request that cannot get a connection in time should fail fast with a 503 instead of queueing behind a thousand others. The same applies to the pool's own methods: pool.Query(ctx, ...) acquires internally, so its context's deadline bounds the wait as well as the query. Always give them a context with a deadline.

Lifetimes and health checks

MaxConnLifetime closes a connection after it has existed that long, whether idle or not, which keeps the pool from holding sessions that are old enough to have been dropped by a proxy or a firewall, and spreads load after a database failover. The fifth block sets it to 300 milliseconds with a 100 millisecond health check period, queries the backend process id, waits 900 milliseconds, and queries again: the session had changed, and MaxLifetimeDestroyCount was positive. In production use minutes, below the shortest idle timeout between you and the database, as in bad connection errors. pgxpool adds a little jitter to lifetimes so connections created together do not all expire together.

MaxConnIdleTime closes connections that have not been used for that long, down to MinConns, which gives back resources after a traffic peak. HealthCheckPeriod is how often the background worker looks for connections to close for age or idleness and to top up to the minimum. A shorter period reacts faster and does more work; the default of a minute suits most services.

Closing

The last block acquires a connection, calls Close in a goroutine and releases the connection 300 milliseconds later. Close blocked until that release: it waits for connections in use to come back, then closes them. A forgotten Release therefore makes shutdown hang, which is also a quick way to find one: if a service does not exit, look for a leaked connection. On shutdown, stop accepting requests first, wait for in flight work, and only then close the pool, ideally with your own timeout around it.

Settings to review for any service

1. pool_max_conns set from the database's budget, not the default.
2. pool_max_conn_lifetime below the shortest idle timeout on the path, with pool_max_conn_idle_time to shed idle connections.
3. pool_min_conns of a small number if cold start latency matters, and a warm up query at start-up.
4. A context deadline on every call, so the wait for a connection is bounded.
5. The pool statistics exported as metrics, with an alert on acquire wait time.
6. statement_timeout and idle_in_transaction_session_timeout on the role, as a backstop for code that holds connections too long.
7. A shutdown that closes the pool after requests drain.

The settings are documented in the pgxpool package, which lists the statistics and their meanings, and PostgreSQL's cost per connection in the manual's page on connection settings.