Go database/sql Connection Pool Settings for Production APIs

Quick answer: the Go database/sql package keeps a connection pool for you, but its defaults are unlimited open connections and only two idle ones. In production, set a maximum number of open connections sized against your database limit and instance count, keep a reasonable number idle, and set connection lifetimes so stale connections are recycled.

What Do the Pool Settings Control?

A sql.DB is not a single connection. It is a pool that opens connections on demand, hands them to queries, and returns them afterward. Four settings shape its behavior, and understanding each one prevents the two classic failures: running out of database connections and paying constant reconnect costs.

• SetMaxOpenConns: the cap on total connections, in use plus idle. The default is zero, meaning unlimited.
• SetMaxIdleConns: how many unused connections are kept ready. The default is two.
• SetConnMaxLifetime: the longest a connection may be reused before it is closed and replaced.
• SetConnMaxIdleTime: how long an idle connection may sit before it is closed.

Confirm these defaults in the documentation for your Go version, but they explain why an unconfigured service behaves badly under load. With no cap, a traffic spike opens as many connections as there are concurrent queries, which can overwhelm the database. With only two idle connections, bursts constantly open and close connections, adding latency.

db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25)
db.SetConnMaxLifetime(30 * time.Minute)
db.SetConnMaxIdleTime(5 * time.Minute)

How Do You Choose the Numbers?

Start from the database, not the application. A database server has a maximum number of connections, and every application instance multiplies your pool size. If the limit is 100 and you run four instances, a pool of 25 per instance already uses everything, leaving nothing for migrations, monitoring, or administrators. Reserve headroom and divide what remains.

Larger pools are not faster. Databases perform best when the number of concurrently active queries is close to a small multiple of their CPU cores, and extra connections mostly add contention. Many services run well with pools in the tens, not hundreds. Load test with realistic traffic and watch query latency as you change the cap.

Setting idle connections equal to the maximum avoids churn under steady load, while a lower idle count saves resources on quiet services. Choose based on traffic patterns, and make the values configurable through environment settings so you can tune without a rebuild.

Why Do Lifetimes Matter?

Network devices, proxies, and database servers close idle connections after a timeout, and failovers change which server is behind an address. A pooled connection that was silently closed produces an error on its next use. Setting a maximum lifetime shorter than those infrastructure timeouts makes the pool recycle connections before they become stale.

Lifetime also helps spread load after a database failover or scale-up, because connections are replaced over time instead of staying pinned to old servers forever. Avoid setting it so low that connections are recreated constantly, and add some randomness at the infrastructure level if many instances start simultaneously, so they do not all recycle together.

How Do You Detect Pool Problems?

The db.Stats method reports open connections, in-use and idle counts, the number of waits for a connection, and the total wait time. Export these as metrics. A growing wait count means requests are queueing for connections, which points to a pool that is too small, slow queries holding connections, or leaks.

Leaks are common. Forgetting to close rows after a query, or leaving a transaction open on an error path, keeps a connection checked out forever. Always defer rows.Close, always roll back or commit transactions, and pass contexts with timeouts so a stuck query releases its connection. Never hold a connection while calling slow external services.

Frequently Asked Questions

What is a good default pool size for a Go API?

There is no universal number. Start small, such as 10 to 25 per instance, and adjust using load tests, the database limit, and the number of instances.

Should max idle equal max open?

Often yes for steady traffic, because it avoids reconnect churn. For spiky or low-traffic services, a smaller idle count saves resources.

Why do I see bad connection errors?

Usually a connection was closed by the database or a network device while idle. Set a connection lifetime shorter than the infrastructure timeouts so the pool recycles it first.

Does the pool work with a connection pooler in front of the database?

Yes, but size both layers together. Application pools feed the pooler, so the total across instances should fit within the pooler's limits.

Summary

Configure max open connections, idle connections, and lifetimes explicitly, size them against the database limit and instance count, and monitor db.Stats for waits. Close rows and transactions reliably and use context timeouts. Check the database/sql documentation for defaults in your Go version.