Connection Pooling in Go with Postgres: Configure Max Open, Idle connections
Connection pooling helps to manage database connections efficiently by reusing existing connections instead of creating new ones for each request.
In Go, you can use the database/sql package along with a Postgres driver like pq package to implement connection pooling.
sqlx package is a popular extension to database/sql that provides additional features like struct scanning and named queries, making it easier to work with databases in Go.
Stack
| Package | Role | What it does? |
|---|---|---|
database/sql |
stdlib interface | Foundation; all drivers implement it |
github.com/lib/pq |
Postgres driver | Pure Go driver for Postgres |
github.com/jmoiron/sqlx |
Thin wrapper over database/sql |
Struct scanning via db tags; Get, Select, NamedExec |
According to Go’s documentation on managing database connections, only two connections are kept idle by default.
See below text from the documentation:
By default an sql.DB keeps two idle connections at any given moment. Raising the limit can avoid frequent reconnects in programs with significant parallelism.
Things to configure on the DB pool:
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25) // match max_open
db.SetConnMaxLifetime(300 * time.Second)
The reason max_idle should equal max_open is subtle but important. Assume; max_open is 25 and max_idle is 2 (the default). Under a burst of traffic, the system opens 25 connections. The burst ends. The pool closes 23 of them.
Next burst: open 23 again, which does TCP handshake connection each time. Setting max_idle = max_open keeps connections warm. We only pay the connection cost once.
max_lifetime exists because Load Balancers and firewalls will silently close idle connections after their timeout period. The lifetime setting helps ensure that the pool proactively recycles connections, so we never hit the situation where a query fails because the underlying TCP connection was terminated by a firewall.
The timeout should be less than the LB’s timeout.