Connection Leaks in Go and How to Spot Them Quickly

Few production incidents in Go services with databases look as confusing as a connection leak. The service runs fine for hours. Then requests start timing out, CPU is idle, the database is quiet and max_connections is nowhere near its limit. Restarting fixes it for a few hours more. The cause is a connection that was taken from the pool and never returned, repeated until the pool is empty. This guide shows the three ways it happens, measures each from the pool's own counters and from the server's view, and lays out how to find a leak in a running service, how to stop it and how to make the next one visible within minutes.

It belongs to the database cornerstone. The types and what each holds are explained in how database/sql works, and the pool numbers in connection pool settings.

What leaks

database/sql hands a connection to a call and takes it back when the call finishes. Most calls finish themselves. Three types keep the connection until you tell them you are done: sql.Rows until Close or until iteration ends, sql.Tx until Commit or Rollback, and sql.Conn until Close. Forget the release on any path through the function, the happy one or an error one, and the connection is lost until the process ends.

The experiments

The lab uses pools limited to four connections, so a leak shows quickly. The output after each scenario prints the pool's counters: connections open, in use, idle, and how many callers had to wait.

package main

import (
	"context"
	"database/sql"
	"errors"
	"fmt"
	"log"
	"time"

	_ "github.com/jackc/pgx/v5/stdlib"
)

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

func stats(label string, db *sql.DB) {
	s := db.Stats()
	fmt.Printf("%-52s open=%d inUse=%d idle=%d waitCount=%d\n", label, s.OpenConnections, s.InUse, s.Idle, s.WaitCount)
}

func newDB(max int) *sql.DB {
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		log.Fatal(err)
	}
	db.SetMaxOpenConns(max)
	return db
}

func serverView(ctx context.Context, admin *sql.DB) string {
	var out int
	_ = admin.QueryRowContext(ctx, "select count(*) from pg_stat_activity where application_name = 'leak629' and state = 'idle in transaction'").Scan(&out)
	return fmt.Sprintf("%d sessions idle in transaction", out)
}

func main() {
	ctx := context.Background()
	admin, _ := sql.Open("pgx", "postgres://postgres:[email protected]:55432/lab?sslmode=disable")
	defer admin.Close()

	// Leak 1: rows from an early return are never closed.
	db := newDB(4)
	find := func() (int, error) {
		rows, err := db.QueryContext(ctx, "select g from generate_series(1, 100) g")
		if err != nil {
			return 0, err
		}
		// no defer rows.Close()
		for rows.Next() {
			var g int
			_ = rows.Scan(&g)
			if g == 3 {
				return g, nil // early return with rows still open
			}
		}
		return 0, rows.Err()
	}
	for i := 0; i < 4; i++ {
		_, _ = find()
	}
	stats("1. four calls that returned early without Close", db)

	short, cancel := context.WithTimeout(ctx, 300*time.Millisecond)
	_, err := db.QueryContext(short, "select 1")
	cancel()
	fmt.Println("   the fifth call:", err, "| deadline exceeded:", errors.Is(err, context.DeadlineExceeded))
	stats("   after the failed call", db)
	db.Close()

	// Leak 2: a transaction that is neither committed nor rolled back on an error path.
	db = newDB(4)
	for i := 0; i < 3; i++ {
		tx, _ := db.BeginTx(ctx, nil)
		_, _ = tx.ExecContext(ctx, "select 1")
		// error path returns without tx.Rollback()
	}
	stats("2. three transactions abandoned", db)
	fmt.Println("   what the server sees:", serverView(ctx, admin))
	db.Close()

	// Leak 3: a *sql.Conn that is not closed.
	db = newDB(4)
	for i := 0; i < 2; i++ {
		conn, _ := db.Conn(ctx)
		_, _ = conn.ExecContext(ctx, "select 1")
	}
	stats("3. two *sql.Conn never closed", db)
	db.Close()

	// The fixes cost one line each.
	db = newDB(4)
	for i := 0; i < 50; i++ {
		rows, _ := db.QueryContext(ctx, "select g from generate_series(1, 100) g")
		for rows.Next() {
			break
		}
		rows.Close()
		tx, _ := db.BeginTx(ctx, nil)
		_, _ = tx.ExecContext(ctx, "select 1")
		_ = tx.Rollback()
	}
	stats("4. fifty rounds with Close and Rollback", db)
	db.Close()

	// A watcher that turns Stats into an alert: the number that never falls.
	db = newDB(4)
	for i := 0; i < 3; i++ {
		rows, _ := db.QueryContext(ctx, "select 1")
		_ = rows
	}
	var lastInUse int
	var stuck int
	for tick := 0; tick < 5; tick++ {
		in := db.Stats().InUse
		if in > 0 && in >= lastInUse {
			stuck++
		} else {
			stuck = 0
		}
		lastInUse = in
		time.Sleep(50 * time.Millisecond)
	}
	fmt.Printf("5. watcher: inUse stayed at %d for %d samples in a row with no traffic, so connections are leaking\n", lastInUse, stuck)
	db.Close()

	// Server side guard: idle in transaction sessions are ended after a timeout.
	db = newDB(2)
	conn, _ := db.Conn(ctx)
	_, _ = conn.ExecContext(ctx, "set idle_in_transaction_session_timeout = '200ms'")
	_, _ = conn.ExecContext(ctx, "begin")
	_, _ = conn.ExecContext(ctx, "select 1")
	time.Sleep(500 * time.Millisecond)
	_, err = conn.ExecContext(ctx, "select 1")
	fmt.Println("6. after the idle in transaction timeout:", err)
	db.Close()
}

1. four calls that returned early without Close      open=4 inUse=4 idle=0 waitCount=0
   the fifth call: context deadline exceeded | deadline exceeded: true
   after the failed call                             open=4 inUse=4 idle=0 waitCount=1
2. three transactions abandoned                      open=3 inUse=3 idle=0 waitCount=0
   what the server sees: 3 sessions idle in transaction
3. two *sql.Conn never closed                        open=2 inUse=2 idle=0 waitCount=0
4. fifty rounds with Close and Rollback              open=1 inUse=0 idle=1 waitCount=0
5. watcher: inUse stayed at 3 for 5 samples in a row with no traffic, so connections are leaking
6. after the idle in transaction timeout: FATAL: terminating connection due to idle-in-transaction timeout (SQLSTATE 25P03)

Rows that are never closed

find runs a query and returns as soon as it sees the value it wants. It never calls rows.Close(), and it returns before rows.Next() has reached the end, so nothing closes the rows. Calling it four times left open=4 inUse=4 idle=0: every connection in the pool was held by a result set nobody would read again.

The fifth call, with a 300 millisecond deadline, waited for a free connection and gave up with context deadline exceeded; the pool's WaitCount rose to 1. That error is the usual symptom in production, and it is confusing because the query it names is innocent. The code that leaked was somewhere else, earlier. Iterating rows to the end closes it automatically, which is why this bug survives tests that read everything; it appears on the early return, the break and the error in the middle of the loop. The fix is a line directly after the error check, defer rows.Close(), as in closing rows. Also check rows.Err() after the loop.

Transactions that are never ended

Three transactions were started and abandoned, as when an error path returns without calling tx.Rollback(). The pool showed open=3 inUse=3. The server's view, from pg_stat_activity filtered by the application name the lab sets in the connection string, was 3 sessions idle in transaction. That state is the tell: a session that began a transaction, ran something and is waiting, and it holds locks and a snapshot too. Idle in transaction sessions block vacuum from removing dead rows and can hold row locks that stall other writers, so this leak damages the database as well as the pool.

The idiom that prevents it is defer tx.Rollback() right after BeginTx. Calling Rollback after a successful Commit is harmless and returns sql.ErrTxDone, so the deferred call costs nothing on the happy path and covers every other. The pattern is in transactions and deferred rollback, and a unit of work, as in unit of work, writes it once for the whole service.

Conns that are never closed

db.Conn(ctx) takes a connection for as long as you hold it, which is useful for session state and advisory locks and dangerous for exactly this reason. Two connections that were taken and not closed left inUse=2. Always write defer conn.Close() after acquiring one; Close returns the connection to the pool and does not close the network connection.

The fixes, measured

The fourth scenario runs fifty rounds that each close their rows and roll back their transaction, and ends with open=1 inUse=0 idle=1: one connection did all the work and went back to the pool every time. That is what a healthy pool looks like under sequential use, and it is the target to compare against.

Finding a leak in a running service

The counters are the first tool. db.Stats() returns InUse, Idle, OpenConnections, WaitCount and WaitDuration. Export them as metrics, as in instrumenting database/sql, and the leak shows as a staircase: InUse climbs and never falls back, even at night when traffic stops. A healthy pool's InUse returns to near zero when idle.

The fifth scenario turns that into an alert. A watcher samples InUse and counts consecutive samples where it is above zero and has not fallen. With three connections leaked and no traffic, it reported inUse stuck at 3 for five samples in a row. In a service, run it every few seconds and alarm when InUse stays at or near MaxOpenConns while the request rate is low, and when WaitCount keeps rising.

Then find the culprit, and for that you need a stack. Two practical approaches:

1. From the server. pg_stat_activity lists every session with state, state_change, xact_start and query, the last statement it ran. A session idle in transaction for ten minutes with the query select ... from orders where id = $1 points at the code that runs it. Give each service a distinct application_name in the connection string, as the lab does, so you can tell whose leak it is.
2. From the program. Wrap the acquisition points in development: record the stack when Query, BeginTx or Conn hands out a resource and remove it on release, then dump the outstanding stacks when the pool is exhausted. A small wrapper type does it in fifty lines, and it points straight at the function that forgot. The goroutine profile shows goroutines parked in database/sql.(*DB).conn waiting for a connection, which confirms exhaustion but not who holds them.

Testing for leaks

A leak is a property you can assert in a test, and that is cheaper than finding it in production. After the code under test returns, no connection should still be checked out. The lab includes the helper and two tests:

package main

import (
	"context"
	"database/sql"
	"testing"
)

func leakyLookup(ctx context.Context, db *sql.DB) {
	rows, err := db.QueryContext(ctx, "select g from generate_series(1, 10) g")
	if err != nil {
		return
	}
	for rows.Next() {
		return // leaves the rows open
	}
}

func fixedLookup(ctx context.Context, db *sql.DB) {
	rows, err := db.QueryContext(ctx, "select g from generate_series(1, 10) g")
	if err != nil {
		return
	}
	defer rows.Close()
	for rows.Next() {
		return
	}
}

// assertNoLeak runs fn and fails the test if a connection is still checked out afterwards.
func assertNoLeak(t *testing.T, db *sql.DB, fn func()) {
	t.Helper()
	fn()
	if in := db.Stats().InUse; in != 0 {
		t.Errorf("%d connections still in use after the call", in)
	}
}

func TestFixedLookupReturnsItsConnection(t *testing.T) {
	db := newDB(2)
	defer db.Close()
	assertNoLeak(t, db, func() { fixedLookup(context.Background(), db) })
}

func TestLeakIsDetected(t *testing.T) {
	db := newDB(2)
	defer db.Close()
	leakyLookup(context.Background(), db)
	if in := db.Stats().InUse; in != 1 {
		t.Fatalf("expected the leaky lookup to hold 1 connection, found %d", in)
	}
	t.Log("the leaky lookup holds 1 connection, which assertNoLeak would have reported")
}

=== RUN   TestFixedLookupReturnsItsConnection
--- PASS: TestFixedLookupReturnsItsConnection (0.02s)
=== RUN   TestLeakIsDetected
    leak_test.go:52: the leaky lookup holds 1 connection, which assertNoLeak would have reported
--- PASS: TestLeakIsDetected (0.01s)
PASS
ok  	example.test/lab	1.350s

assertNoLeak runs a function and fails if db.Stats().InUse is not zero afterwards. TestFixedLookupReturnsItsConnection passes: the lookup that defers rows.Close() hands its connection back even though it returns after the first row. TestLeakIsDetected runs the leaky version and checks that the pool reports one connection in use, which is what assertNoLeak would have flagged as a failure. The point of the second test is the instrument itself: it proves the detector can see the bug, so a green result from the first means something.

Use the same helper around handler tests. Run the handler with a real pool of two connections against a test database, and call it more than twice; a leak makes the third call hang or time out, so the test fails fast instead of passing by luck. A pool size of one is the most sensitive setting for tests, because the first leaked connection already blocks the next call. Keep that pool small for tests only, never in production.

Reading a stuck server

When the pool is exhausted in production, a single query against pg_stat_activity tells most of the story. Filter by your application_name, group by state, and look at the oldest state_change for each. Three patterns cover most leaks:

• Many idle sessions, none active, and the app reports exhaustion. The connections are checked out of the Go pool but doing nothing on the server. This is the unclosed Rows or Conn: the server has finished the statement and is waiting for the client to ask for more rows or send the next command. The last query text names the statement that opened it.
• Several idle in transaction sessions with old xact_start. The abandoned transactions. They also show in pg_locks as holders of row locks, and in pg_stat_activity.backend_xmin they are what holds back vacuum.
• Many active sessions on the same slow statement. Not a leak but a slow query that every request is waiting for, possibly because of a lock another session holds. pg_blocking_pids(pid) shows who is blocking whom, and the guidance in zero downtime migrations explains the lock queue that can produce this.

Each pattern points at a different fix, which is why it is worth looking before restarting. Restarting the service clears all three and teaches nothing, and the leak returns.

Why the pool cannot save you

It is tempting to expect the library to reclaim leaked connections. It cannot, because it cannot know the code is done with one. A *sql.Rows can legitimately be held for minutes by a streaming export. A transaction can be open across a slow step. A *sql.Conn can hold an advisory lock for the life of a job. From the pool's side these are indistinguishable from leaks, so the contract is that you release what you acquire. The runtime does not help either: a leaked Rows is not garbage collected while the pool references it, and database/sql does not attach finalizers that return connections.

What the pool does give you are the counters, SetMaxOpenConns to bound the damage, and the context cancellation that closes rows whose context ends. That last one is useful: a Rows obtained from QueryContext is closed automatically when its context is cancelled, so passing a request context to every query turns a leak that lasts until the process dies into one that lasts until the request ends. It is another reason to use the Context variants everywhere, but it covers only code that actually cancels the context, and a background goroutine with context.Background() gets no such help.

Safety nets

Prevention is the discipline above. Safety nets limit the damage when it fails:

• Set a deadline on every call. A context with a timeout means a stuck acquisition fails after a bounded wait instead of piling up; see query timeouts. It does not release a leaked connection, but it converts a hang into an error.
• idle_in_transaction_session_timeout on the server. The sixth scenario sets it to 200ms, begins a transaction on a connection and sleeps. PostgreSQL ended the session, and the next use of that connection returned FATAL: terminating connection due to idle-in-transaction timeout (SQLSTATE 25P03). Use a larger value in production, tens of seconds, set per role. It makes abandoned transactions go away by themselves and frees the locks, and it also terminates a legitimately slow client, so choose it with that in mind.
• SetConnMaxLifetime. It recycles connections, but a connection that is checked out is not recycled, so it does not cure a leak.
• SetMaxOpenConns. A limit turns a leak into pool exhaustion, which is visible, and not into the database running out of connections, which takes everything else down. Never leave it at unlimited.

Habits that prevent most leaks

1. Write the release on the line after the acquire: defer rows.Close(), defer tx.Rollback(), defer conn.Close().
2. Prefer helpers that own the lifecycle, a withTx(ctx, func(tx) error) or a function that scans all rows into a slice, so no caller holds a Rows.
3. Use QueryRow for single rows. It closes itself in Scan.
4. Do not return a *sql.Rows from a function; return the data. If you must, document who closes it.
5. Check rows.Err() and every Close error in tests.
6. Review every early return between an acquire and its release.
7. Run a linter that knows the pattern. sqlclosecheck (a golangci-lint linter) reports Rows, Stmt and NamedStmt values that are never closed; it was not run for this article, so check its documentation for the current rules.

When it is not a leak

A pool that looks exhausted is not always leaking. A slow query that holds a connection for a minute, a burst of traffic larger than MaxOpenConns, or a downstream stall that leaves many requests waiting inside transactions produces the same symptoms. The difference is that those recover when the load or the slow query ends, and a leak does not. If InUse drops after traffic does, size the pool and speed up the queries. If it stays up, there is a leak, and the steps above find it. The behaviour of each type is specified in the database/sql package documentation, and the server side views in the PostgreSQL manual's page on pg_stat_activity.