pgx Connection Lifecycle Health Checks and Max Lifetime

A pooled connection lives a longer life than any request that uses it: it is created, handed out hundreds of thousands of times, returned dirty or clean, and eventually retired. pgxpool gives you hooks at each point of that life, and using them correctly is the difference between a pool that behaves like a stack of identical connections and one that quietly leaks one caller's session settings into the next. This article logs every hook on PostgreSQL 18.6 from Go 1.25.6 with pgx v5.11.0, shows the order they run in, and demonstrates the leak that most pools have.

It belongs to the database cornerstone. The pool's size and timing settings are in pgxpool, and the failure modes that connection management prevents are in pgx mistakes that leak connections and bad connection errors.

The hooks

BeforeConnect and AfterConnect run once per connection. BeforeAcquire and AfterRelease run once per use. The first returns an error to fail the connection and the other two return a boolean to discard it.

The order

package main

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

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

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

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

type events struct {
	mu sync.Mutex
	e  []string
}

func (ev *events) add(s string) {
	ev.mu.Lock()
	ev.e = append(ev.e, s)
	ev.mu.Unlock()
}

func (ev *events) take() string {
	ev.mu.Lock()
	defer ev.mu.Unlock()
	out := strings.Join(ev.e, " -> ")
	ev.e = nil
	return out
}

func main() {
	ctx := context.Background()
	ev := &events{}
	var refuseAcquire, refuseRelease bool
	var mu sync.Mutex
	cfg, err := pgxpool.ParseConfig(dsn)
	must(err)
	cfg.MaxConns = 1
	cfg.HealthCheckPeriod = 50 * time.Millisecond
	cfg.MaxConnLifetime = time.Hour
	cfg.BeforeConnect = func(ctx context.Context, cc *pgx.ConnConfig) error {
		ev.add("BeforeConnect")
		cc.RuntimeParams["application_name"] = "lifecycle681" // BeforeConnect can change the settings of the next connection
		return nil
	}
	cfg.AfterConnect = func(ctx context.Context, c *pgx.Conn) error {
		ev.add("AfterConnect")
		_, err := c.Exec(ctx, "set timezone = 'UTC'") // session setup belongs here: it runs once per connection
		return err
	}
	cfg.BeforeAcquire = func(ctx context.Context, c *pgx.Conn) bool {
		mu.Lock()
		defer mu.Unlock()
		if refuseAcquire {
			ev.add("BeforeAcquire(false)")
			refuseAcquire = false
			return false // the connection is discarded and another is taken or opened
		}
		ev.add("BeforeAcquire")
		return true
	}
	cfg.AfterRelease = func(c *pgx.Conn) bool {
		mu.Lock()
		defer mu.Unlock()
		if refuseRelease {
			ev.add("AfterRelease(false)")
			refuseRelease = false
			return false // the connection is closed instead of returning to the pool
		}
		ev.add("AfterRelease")
		return true
	}
	cfg.BeforeClose = func(c *pgx.Conn) { ev.add("BeforeClose") }

	pool, err := pgxpool.NewWithConfig(ctx, cfg)
	must(err)
	defer pool.Close()
	fmt.Println("1. creating the pool:", ev.take())

	var tz, app string
	must(pool.QueryRow(ctx, "show timezone").Scan(&tz))
	must(pool.QueryRow(ctx, "show application_name").Scan(&app))
	fmt.Printf("2. first query: %s | session timezone %q, application_name %q\n", ev.take(), tz, app)

	var one int
	must(pool.QueryRow(ctx, "select 1").Scan(&one))
	fmt.Println("3. second query on the same connection:", ev.take())

	mu.Lock()
	refuseRelease = true
	mu.Unlock()
	must(pool.QueryRow(ctx, "select 1").Scan(&one))
	time.Sleep(100 * time.Millisecond)
	fmt.Println("4. AfterRelease returns false:", ev.take())

	must(pool.QueryRow(ctx, "select 1").Scan(&one))
	mu.Lock()
	refuseAcquire = true
	mu.Unlock()
	must(pool.QueryRow(ctx, "select 1").Scan(&one))
	fmt.Println("5. BeforeAcquire returns false:", ev.take())

	// Settings that last for the connection are reset by hand when a caller changes them.
	c, err := pool.Acquire(ctx)
	must(err)
	_, err = c.Exec(ctx, "set timezone = 'Asia/Tokyo'")
	must(err)
	c.Release()
	must(pool.QueryRow(ctx, "show timezone").Scan(&tz))
	fmt.Println("6. a caller changed the time zone and released the connection; the next caller sees:", tz)

	pool.Close()
	ev.take()
	short, err := pgxpool.ParseConfig(dsn)
	must(err)
	short.MaxConns = 1
	short.MaxConnLifetime = 200 * time.Millisecond
	short.HealthCheckPeriod = 50 * time.Millisecond
	short.AfterConnect = func(ctx context.Context, c *pgx.Conn) error { ev.add("AfterConnect"); return nil }
	short.BeforeClose = func(c *pgx.Conn) { ev.add("BeforeClose") }
	p2, err := pgxpool.NewWithConfig(ctx, short)
	must(err)
	defer p2.Close()
	must(p2.QueryRow(ctx, "select 1").Scan(&one))
	time.Sleep(600 * time.Millisecond)
	must(p2.QueryRow(ctx, "select 1").Scan(&one))
	fmt.Println("7. MaxConnLifetime of 200ms, queried before and after 600ms:", ev.take())
}

1. creating the pool: 
2. first query: BeforeConnect -> AfterConnect -> BeforeAcquire -> AfterRelease -> BeforeAcquire | session timezone "UTC", application_name "lifecycle681"
3. second query on the same connection: AfterRelease -> BeforeAcquire
4. AfterRelease returns false: AfterRelease -> BeforeAcquire -> AfterRelease(false) -> BeforeClose
5. BeforeAcquire returns false: BeforeConnect -> AfterConnect -> BeforeAcquire -> AfterRelease -> BeforeAcquire(false) -> BeforeClose -> BeforeConnect -> AfterConnect -> BeforeAcquire
6. a caller changed the time zone and released the connection; the next caller sees: Asia/Tokyo
7. MaxConnLifetime of 200ms, queried before and after 600ms: AfterConnect -> BeforeClose -> AfterConnect

The program records each hook call and prints the sequence for a series of scenarios, using a pool of one connection so that every event concerns the same connection.

Creating the pool opened nothing. The first line is empty: pgxpool.NewWithConfig returns without connecting (unless MinConns asks for connections, and then it does so in the background). The first connection appears when the first query needs it.

The first queries. Two queries ran, show timezone and show application_name, and the events were BeforeConnect -> AfterConnect -> BeforeAcquire -> AfterRelease -> BeforeAcquire: the first query created the connection (the two connect hooks), acquired it and released it; the second acquired the same connection again. Both results show the hooks did their work: the session timezone was UTC, set by AfterConnect with set timezone = 'UTC', and the application name was lifecycle681, which BeforeConnect wrote into the connection's runtime parameters before it was opened. BeforeConnect is the place for credentials that expire: fetch an IAM token or a rotated password there, set it on the config, and every new connection uses it, without recreating the pool.

A second query on the same connection shows the steady state: AfterRelease -> BeforeAcquire per use, with the connect hooks silent. Keep both of these hooks cheap, since they run on the hot path of every query.

Discarding connections

Returning false from a per use hook removes the connection from the pool. The lab turns that on for one call each.

With AfterRelease returning false, the sequence was AfterRelease -> BeforeAcquire -> AfterRelease(false) -> BeforeClose: the connection was released, found unfit, and closed. Use it when a caller leaves a connection in a state you cannot clean: a transaction left open, a LISTEN, a failed state. The pool opens a replacement when needed.

With BeforeAcquire returning false, the sequence was ... AfterRelease -> BeforeAcquire(false) -> BeforeClose -> BeforeConnect -> AfterConnect -> BeforeAcquire: the idle connection was rejected and closed, the pool opened a new one (both connect hooks ran) and handed that out. This is the hook for health checks on checkout: ping the connection or verify a property, and return false if it is bad. The cost is a check per acquire, so keep it to something cheap, or rely on the pool's ShouldPing setting and its periodic health checks instead.

Session state leaks

The sixth line is the one to take from this article. A caller acquired the connection, ran set timezone = 'Asia/Tokyo' and released it. The next caller, on the same connection, ran show timezone and got Asia/Tokyo. SET changes the session, and the pool does not reset sessions between uses. The same applies to SET search_path, SET ROLE, SET statement_timeout, temporary tables, advisory locks taken with the session variants, LISTEN subscriptions and open transactions or cursors.

Three defences, from best to worst:

1. Do not change session state from request code. Use SET LOCAL inside a transaction, which ends with it, and pass settings as part of the statement where possible. This is the same rule that makes row level security safe with a pool.
2. Reset in AfterRelease. Run reset all, or discard all on a connection that may have been abused, and return false if the reset fails. discard all also drops prepared statements and temp tables, so the statement cache is lost: pgx handles that, at the cost of re-preparing.
3. Close dirty connections. If you cannot clean the session, return false and let the pool replace the connection.

A caller that takes a connection with pool.Acquire and changes state owns the cleanup: use defer conn.Release() and, for anything that cannot be undone, conn.Hijack() to take the connection out of the pool, which then closes it on your terms.

Lifetime

The last scenario sets MaxConnLifetime to 200 milliseconds and a health check every 50, queries, waits 600 milliseconds and queries again. The events: AfterConnect -> BeforeClose -> AfterConnect. The first connection was set up, then retired for its age, and the second query created a new one. The pool's background health check does the closing, and you can see BeforeClose run before it.

In production, set MaxConnLifetime to minutes, below any idle timeout between you and the database, and MaxConnIdleTime to shed idle connections after a peak. Lifetimes have jitter built in so connections created together do not die together. When a lifetime ends, a connection is closed only when it is idle; one in use finishes its work first, which is why hooks never see a connection killed in mid query by the pool itself.

What to put where

• BeforeConnect: rotating credentials, per connection config. Not session settings: the connection does not exist yet.
• AfterConnect: SET of defaults (timezone, statement_timeout, application_name), LoadType and RegisterType for custom types as in custom types, prepared statements you want on every connection. An error here fails the connection, and the pool retries, so a mistake shows up as every query failing.
• BeforeAcquire: only cheap checks. Prefer the pool's built in health checking.
• AfterRelease: reset of session state, and closing of connections that cannot be reset.
• BeforeClose: logging and metrics.

Log and count these events in production: connections created, discarded by each hook, closed for age. A rising number of discarded connections is an early warning that callers are leaving connections in a bad state or that the network is dropping them.

Habits

1. Treat the session as shared state: never leave it changed.
2. Put one time setup in AfterConnect, per use reset in AfterRelease.
3. Keep per use hooks fast; they run on every query.
4. Use BeforeConnect for credentials that rotate.
5. Test with a pool of one connection, so that a leak between callers shows up deterministically, as in the lab.
6. Remember that a pooler such as PgBouncer in transaction mode makes session state unsafe regardless of hooks; see pgx with PgBouncer.

The hooks are documented in the pgxpool configuration reference, and the reset commands in the PostgreSQL manual's pages on RESET and DISCARD.