pgx Mistakes That Leak Connections and How to Spot Them

A leak in a connection pool looks like nothing at first and like an outage later. Requests start timing out waiting for a connection, the database is idle, restarting the service fixes it for a few hours. With pgx it comes from three mistakes, each of which is a connection that was taken and never given back. This article makes each one on purpose against PostgreSQL 18.6 from Go 1.25.6 with pgx v5.11.0, reads what the pool and the server say about it, and lists the patterns that cannot leak. The same hazard for database/sql is in connection leaks. It belongs to the database cornerstone, and the pool statistics used here are explained in pgxpool.

The three leaks

package main

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

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

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable&application_name=leak684&pool_max_conns=4"

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

func stat(label string, p *pgxpool.Pool) {
	s := p.Stat()
	fmt.Printf("%-58s acquired=%d idle=%d total=%d\n", label, s.AcquiredConns(), s.IdleConns(), s.TotalConns())
}

func fresh() *pgxpool.Pool {
	p, err := pgxpool.New(context.Background(), dsn)
	must(err)
	return p
}

func main() {
	ctx := context.Background()
	admin, err := pgxpool.New(ctx, "postgres://postgres:[email protected]:55432/lab?sslmode=disable")
	must(err)
	defer admin.Close()

	// 1. Rows that are never closed: Query without CollectRows or Close, returning early.
	p := fresh()
	find := func() (int, error) {
		rows, err := p.Query(ctx, "select g from generate_series(1, 100) g")
		if err != nil {
			return 0, err
		}
		for rows.Next() { // no defer rows.Close()
			var g int
			_ = rows.Scan(&g)
			if g == 3 {
				return g, nil
			}
		}
		return 0, rows.Err()
	}
	for i := 0; i < 4; i++ {
		_, _ = find()
	}
	stat("1. four early returns without rows.Close", p)
	short, cancel := context.WithTimeout(ctx, 300*time.Millisecond)
	var n int
	err = p.QueryRow(short, "select 1").Scan(&n)
	cancel()
	fmt.Println("   the fifth call:", err)

	// 2. The same function with the two fixes: close, or collect.
	p2 := fresh()
	for i := 0; i < 50; i++ {
		rows, _ := p2.Query(ctx, "select g from generate_series(1, 100) g")
		for rows.Next() {
			break
		}
		rows.Close()
		rows, _ = p2.Query(ctx, "select g from generate_series(1, 100) g")
		_, _ = pgx.CollectRows(rows, pgx.RowTo[int])
	}
	stat("2. fifty rounds with Close and with CollectRows", p2)
	p2.Close()

	// 3. A transaction that is never ended.
	p3 := fresh()
	for i := 0; i < 3; i++ {
		tx, _ := p3.Begin(ctx)
		_, _ = tx.Exec(ctx, "select 1")
		// an error path returns here without tx.Rollback
	}
	stat("3. three transactions abandoned", p3)
	var idle int
	must(admin.QueryRow(ctx, "select count(*) from pg_stat_activity where application_name = 'leak684' and state = 'idle in transaction'").Scan(&idle))
	fmt.Println("   sessions the server sees idle in transaction:", idle)

	// 4. A connection from Acquire that is never released.
	p4 := fresh()
	for i := 0; i < 2; i++ {
		c, _ := p4.Acquire(ctx)
		_, _ = c.Exec(ctx, "select 1")
		// c.Release() forgotten
	}
	stat("4. two Acquire calls without Release", p4)

	// 5. Pool.Close waits for connections in use, so a leak makes shutdown hang.
	closed := make(chan struct{})
	go func() { p4.Close(); close(closed) }()
	select {
	case <-closed:
		fmt.Println("5. Close returned")
	case <-time.After(500 * time.Millisecond):
		fmt.Println("5. Close is still waiting after 500ms for the connections that were never released")
	}

	// 6. Hijack takes a connection out of the pool on purpose; it is then yours to close.
	p5 := fresh()
	c, _ := p5.Acquire(ctx)
	h := c.Hijack()
	stat("6. after Hijack the pool no longer counts that connection", p5)
	must(h.Close(ctx))

	// 7. The patterns that cannot leak.
	p6 := fresh()
	err = pgx.BeginFunc(ctx, p6, func(tx pgx.Tx) error { _, _ = tx.Exec(ctx, "select 1"); return errors.New("fail") })
	stat("7. BeginFunc returning an error leaves nothing held", p6)
	_ = err
	p6.Close()
	// p, p3 and p4 are leaking on purpose: closing them would wait for connections that are never released.
	os.Exit(0)
}

1. four early returns without rows.Close                   acquired=4 idle=0 total=4
   the fifth call: context deadline exceeded
2. fifty rounds with Close and with CollectRows            acquired=0 idle=1 total=1
3. three transactions abandoned                            acquired=3 idle=0 total=3
   sessions the server sees idle in transaction: 3
4. two Acquire calls without Release                       acquired=2 idle=0 total=2
5. Close is still waiting after 500ms for the connections that were never released
6. after Hijack the pool no longer counts that connection  acquired=0 idle=0 total=0
7. BeginFunc returning an error leaves nothing held        acquired=0 idle=1 total=1

The lab uses pools limited to four connections so a leak shows at once, and prints AcquiredConns, IdleConns and TotalConns after each experiment.

Rows that are never closed

pool.Query returns pgx.Rows and holds a connection until the rows are closed or exhausted. The find function loops over the rows and returns as soon as it sees the value it wants, with no defer rows.Close(). Running it four times left acquired=4 idle=0 total=4: every connection in the pool was held by an open result. The fifth call, QueryRow with a 300ms deadline, failed with context deadline exceeded: it waited for a free connection and gave up. That error names a harmless query; the leak is somewhere else, earlier. Reading the rows to the end closes them, which is why the bug survives tests that read everything and appears on the early return, the break and the error in the middle of the loop.

The fixes: defer rows.Close() on the line after the error check, or better, pgx.CollectRows (or ForEachRow), which closes the rows for you whatever happens. The second experiment runs both styles fifty times: acquired=0 idle=1 total=1. One connection served everything and went back each time, which is the healthy state.

Transactions that are never ended

pool.Begin takes a connection for the life of the transaction. The third experiment starts three and returns from an error path without calling Rollback. The pool shows acquired=3, and the server's view, from pg_stat_activity filtered by the application name, was 3 sessions idle in transaction. This leak hurts the database as well: those sessions hold locks and keep old row versions alive, which stops vacuum, so the table bloats while the service looks merely slow.

Use pgx.BeginFunc, which commits or rolls back for you; the last experiment ends a BeginFunc function with an error and the pool shows acquired=0 idle=1. If you must use Begin, put defer tx.Rollback(ctx) on the next line: after a successful commit it returns tx is closed, which you ignore. Set idle_in_transaction_session_timeout on the role as a backstop, so the server ends abandoned transactions: pgx context cancellation explains why a cancelled context does not end a pgx transaction by itself.

Acquire without Release

pool.Acquire gives you a *pgxpool.Conn for as long as you hold it, for session state, advisory locks or LISTEN. Two acquires without Release showed acquired=2. The fifth line adds the shutdown consequence: pool.Close() was still waiting after 500ms, because Close waits for every connection to be released. A service that does not exit when asked to is often leaking an acquired connection, and the hang is a free detector: log the pool's stats when shutdown exceeds its deadline.

Always defer conn.Release() immediately after the error check on Acquire. When you want to keep a connection out of the pool permanently, conn.Hijack() removes it: the sixth line shows acquired=0 idle=0 total=0 afterwards, and the returned *pgx.Conn is yours to close, which the lab does.

Spotting a leak in a running service

Three signals, in the order to check:

1. The pool's stats. Export AcquiredConns, IdleConns, TotalConns, EmptyAcquireCount and AcquireDuration. A leak is AcquiredConns that climbs and never falls back, even when traffic stops, with AcquireDuration growing as callers wait. A healthy pool's acquired count returns to near zero when idle.
2. The server's view. select state, count(*), max(now() - state_change) from pg_stat_activity where application_name = 'myservice' group by state. Many idle sessions that the pool counts as acquired are unclosed rows or unreleased connections (the server finished the statement, the client holds the connection). Many idle in transaction sessions with old xact_start are abandoned transactions. Set application_name per service so you can find yours.
3. Shutdown behaviour. A Close that hangs points at an unreleased connection.

To find which code leaked, record a stack at each Acquire and Begin in development (a wrapper that stores debug.Stack() in a map keyed by connection and removes it on release), and print the outstanding stacks when AcquiredConns reaches the maximum. A linter such as sqlclosecheck covers database/sql and, for pgx, review of every Query call is the practical check: each should end in CollectRows, ForEachRow, a defer rows.Close() or a loop that runs to the end with rows.Err() checked.

Patterns that cannot leak

• QueryRow(...).Scan(...) releases the connection when it returns.
• Exec releases it when it returns.
• CollectRows, CollectOneRow, CollectExactlyOneRow, ForEachRow close the rows in every path.
• BeginFunc and BeginTxFunc end the transaction in every path, including panics.
• SendBatch(...).Close() must be called, or the connection is held; keep the result and defer the close, or use the callback form and one Close.
• A small helper for the rare manual case: func withConn(ctx, pool, fn func(*pgx.Conn) error) error { c, err := pool.Acquire(ctx); if err != nil { return err }; defer c.Release(); return fn(c.Conn()) }. One defer, written once.

Safety nets

Prevention is the habit; these limit the damage. A deadline on every call turns a hang into an error, because the wait for a connection is bounded by the context. idle_in_transaction_session_timeout and statement_timeout on the database role end sessions that hold connections too long. MaxConns limits the damage to the pool and never leaves the database exhausted by one service. Metrics and alerts on acquired connections and acquire wait time notice a leak within minutes. And tests with a pool of one or two connections make the first leak block the next call, so a unit or integration test fails deterministically instead of passing by luck.

The behaviours are documented in the pgxpool reference, and the server views in the PostgreSQL manual's page on pg_stat_activity.