Your connection pool has no upper bound
sql.Open hands back a pool carrying two defaults that do not belong together:
an unlimited number of open connections, and two idle ones. Under any real burst
that pair opens as many backends as your traffic asks for and then throws almost
all of them away.
Four setters govern the pool, and zero means something different in each:
db.SetMaxOpenConns(0) // "there is no limit on the number of open connections"
db.SetMaxIdleConns(0) // "no idle connections are retained"
db.SetConnMaxLifetime(0) // "connections are not closed due to a connection's age"
db.SetConnMaxIdleTime(0) // "connections are not closed due to a connection's idle time"
Three of the four are the shipped defaults. The exception is the idle count:
"The default max idle connections is currently 2." The quotations and the
defaults are Go 1.27.1's, where defaultMaxIdleConns = 2 is a constant in
database/sql.
Unlimited is a decision you did not make
Postgres forks a server process per connection, and max_connections "is
typically 100 connections", of which superuser_reserved_connections holds back
three. Those are the shipped defaults on Postgres 18. So the ceiling is not a
per-process concern. It is:
MaxOpenConns × instances + migrations + workers + whatever psql you left open
An unlimited MaxOpenConns means your traffic decides that sum, and the failure
mode is not a slow query. It is FATAL: sorry, too many clients already, which
arrives for every application on that database at once, including the one you
would use to go and look.
Scale the number down from what the database can afford, not up from what one instance would enjoy. For a single small service, ten is generous:
db.SetMaxOpenConns(10)
db.SetMaxIdleConns(5)
db.SetConnMaxLifetime(time.Hour)
Two idle connections is a churn generator
A pool that opens forty and keeps two discards thirty-eight established connections, then pays for them again on the next burst — TCP, TLS, Postgres authentication, and any startup parameters your DSN carries.
There is a counter for exactly this. db.Stats().MaxIdleClosed is "the total
number of connections closed due to SetMaxIdleConns". Under steady traffic it
should be flat. If it climbs, you are closing connections you are about to want
again, and the fix is one line.
The two limits also interact: "If MaxIdleConns is greater than 0 and the new
MaxOpenConns is less than MaxIdleConns, then MaxIdleConns will be reduced to
match the new MaxOpenConns limit." SetMaxIdleConns carries the mirror image of
that clamp, so for ordinary values the order you call them in does not matter.
What does not happen is the reverse: raising MaxOpenConns later will not
restore an idle limit that was already clamped down to meet it.
Lifetime is not about the database
The reason to set ConnMaxLifetime is almost never Postgres. It is everything
between you and Postgres: a load balancer that drops idle flows without a FIN, a
failover that moved the primary to a new address an hour ago, a pooler that would
like its server-side connections recycled. A connection with no maximum age
outlives all of those and keeps handing you a socket to somewhere that no longer
answers.
The documented guarantee is worth reading precisely: "Expired connections may be closed lazily before reuse." The promise is not that a connection is closed at its deadline. It is that an expired one is not given back to you.
An hour is a fine default. Anything is better than never.
The numbers that say which way to move
s := db.Stats()
// s.WaitCount — "the total number of connections waited for"
// s.WaitDuration — "the total time blocked waiting for a new connection"
// s.MaxIdleClosed — "closed due to SetMaxIdleConns"
WaitCount climbing means requests are queueing for a connection: the maximum is
too low, or something is holding connections longer than it should. WaitDuration
divided by WaitCount gives you the average wait, which is the number to put on a
dashboard.
MaxIdleClosed climbing means the opposite problem, and the two can be true at
once — a pool that is too small and also churns is a pool where both numbers rise
together.
From the other side:
SELECT state, count(*) FROM pg_stat_activity GROUP BY state;
A large idle count is your pool holding what it reserved. A large
idle in transaction count is a bug in your code, not in your pool settings, and
no amount of tuning will fix it.
Set all three
The maximum comes from dividing what the database can afford by the number of processes that will ask for it. Idle should be close enough to the maximum that a normal burst does not churn — half of it, as above, is a bet that traffic is spiky and the connections are not worth holding between bursts, and that bet is worth making deliberately rather than by default. Lifetime is an hour because the network between you and the database is less reliable than the database.
Then watch WaitCount and MaxIdleClosed. If one of them is non-zero, it tells
you which direction you were wrong in.
If both sit flat at zero and the service is still slow, the pool is not the
problem, and raising the maximum only moves the queue into Postgres, where it is
harder to see and shared with everyone else. That is the case worth exporting
both counters for. Almost every pool-sizing argument I have seen would have been
settled in a minute by a WaitCount graph.