Go's database/sql Pool: SetConnMaxLifetime Behind a Firewall

The bug report goes like this: the service is fine all day, then at 08:00 the first request of the morning either takes ages to fail or fails instantly with write: broken pipe or connection reset by peer. The second request works. Nothing in the database logs. Nothing wrong with the database at all, in fact.

Something in the middle forgot about your TCP connection while your pool was still convinced it was perfectly good.

What the pool does by default

A fresh sql.DB has no limit on connection age, no limit on idle time, no limit on open connections, and keeps at most two idle ones. Once a connection is open it is reused until something closes it. If your traffic is bursty, the pool sits on a couple of idle connections overnight and hands one to the first query in the morning without a second thought.

The pool cannot tell whether the far end is still there. A TCP connection is just state at two ends, and nobody is obliged to announce it when state in the middle disappears.

The middlebox forgets

Stateful firewalls, NAT gateways and cloud load balancers track flows in a table and evict idle entries. Timeouts vary a lot: many cloud load balancers and NAT gateways sit somewhere between a few minutes and ten or so, and a self-managed conntrack table has its own settings. Look up the number for your environment rather than trusting mine.

After the entry is gone, what your client sees depends on the device:

• It sends a RST for the next packet: you get an immediate error, connection reset or similar.
• It silently drops the packet: your write succeeds (it only reached the local kernel's send buffer), then nothing comes back. The kernel retransmits with backoff and, with Linux defaults, gives up after roughly a quarter of an hour. Your request just hangs.

The silent case is the nasty one, and it is common, because a firewall that drops unknown packets is doing what it was configured to do.

Why database/sql's retry doesn't save you

The pool does retry. If a driver returns driver.ErrBadConn when it tries to use a connection, database/sql discards it and tries again, ultimately with a brand new connection. Good, but it only helps if the driver notices in time. Some drivers do a cheap liveness check when a connection is taken from the pool, which catches a peer that closed cleanly (a FIN or RST that has already arrived). It cannot catch a peer that has vanished without a word, because there is nothing to read.

Once the query has actually been written to the socket, the driver generally can't retry on its own either: it does not know whether the server executed it. Blindly retrying an INSERT is how you get duplicate rows.

The fix: don't let connections get old

Two knobs. They are different and people mix them up.

• SetConnMaxIdleTime(d): close a connection that has sat unused for d. Added in Go 1.15. This targets the firewall problem directly.
• SetConnMaxLifetime(d): close a connection d after it was created, however busy it is. The docs say expired connections may be closed lazily before reuse, so the age is a ceiling, not an alarm clock.

package main

import (
	"context"
	"database/sql"
	"log"
	"time"
)

func openDB(dsn string) (*sql.DB, error) {
	db, err := sql.Open("pgx", dsn) // any driver; sql.Open does not connect
	if err != nil {
		return nil, err
	}

	// Set both below the shortest idle timeout on the path.
	db.SetConnMaxIdleTime(2 * time.Minute)
	db.SetConnMaxLifetime(10 * time.Minute)

	db.SetMaxOpenConns(20)
	db.SetMaxIdleConns(10)

	ctx, cancel := context.WithTimeout(context.Background(), 5*time.Second)
	defer cancel()
	if err := db.PingContext(ctx); err != nil {
		db.Close()
		return nil, err
	}
	return db, nil
}

func main() {
	db, err := openDB("postgres://[email protected]/app")
	if err != nil {
		log.Fatal(err)
	}
	defer db.Close()

	s := db.Stats()
	log.Printf("open=%d idle=%d closedIdle=%d closedLifetime=%d",
		s.OpenConnections, s.Idle, s.MaxIdleTimeClosed, s.MaxLifetimeClosed)
}

(The "pgx" name assumes you have imported the pgx stdlib adapter; swap in your own driver.)

The important thing is the ordering. The idle time has to be shorter than the middlebox's idle timeout, otherwise the pool is still holding a connection the firewall has already dropped. Lifetime alone is a weaker fix: a 30 minute lifetime does nothing about a 5 minute firewall timeout, because the connection can be idle for 29 of those minutes.

Actually, the idle setting also interacts with SetMaxIdleConns in a way that is easy to miss. Idle connections above the idle cap are closed straight away, so with the default of two, a burst of twenty connections leaves eighteen to be torn down and reopened next burst. That is churn, not staleness, but if you are tuning one you are probably tuning the other.

Why bother with lifetime at all, then?

Because a connection can be old and busy, and old connections have other problems that a firewall timer doesn't touch:

• A load balancer or DNS record in front of the database changes, and long-lived connections keep talking to the old backend after a failover.
• Some middleboxes cap total flow age regardless of activity.
• Server-side limits such as MySQL's wait_timeout close connections from the server end. Keep your lifetime and idle time under that too, or you will meet the same error from the other direction.

One caveat: Go does not add jitter. If a service opens all its connections at startup they all reach their lifetime together and reconnect together. For a pool of twenty that is fine; for a fleet of hundreds of replicas against one primary, think about it.

Belt and braces

Two more things worth doing regardless:

1. Use the Context variants (QueryContext, ExecContext) with a deadline. Without one, the silent-drop case blocks until the kernel gives up. With one, you fail in seconds and the broken connection is discarded rather than reused.
2. Check what TCP keepalive your driver sets. Keepalive probes count as traffic, so a short enough interval keeps the firewall entry alive and detects a dead peer. Go's net.Dialer enables keepalives by default, but a driver may configure its own dialer, so read its docs rather than assuming.

To reproduce the whole thing on purpose, run a database behind a Linux box, add an nftables rule that drops established flows after a short timeout, open a pool, wait, and query. Then set SetConnMaxIdleTime below that timeout and watch MaxIdleTimeClosed tick up instead of your error rate.