Why should I bother limiting the total number of open connections?

If you're using sql.Open, Go handles the pool for you automatically, which is great. But by default, there is no limit to how many connections the pool can open. I've seen developers let this slide in early development, only to have their app crash in production because it tried to open 2,000 connections to a Postgres instance that was configured to only allow 100.

When you hit that database limit, the DB will just start rejecting new connections, and your app will start throwing errors everywhere. You use SetMaxOpenConns to put a hard ceiling on this. It's basically a way of saying, "I don't care how busy the app gets; never try to open more than X connections."

db, err := sql.Open("postgres", dsn)
if err != nil {
    log.Fatal(err)
}

// Limit the total connections to 25. 
// This prevents the app from overwhelming the DB during a traffic spike.
db.SetMaxOpenConns(25)

What's the difference between open and idle connections, and why does it matter?

This is where people usually get tripped up. SetMaxOpenConns is the absolute ceiling. SetMaxIdleConns, on the other hand, controls how many connections Go keeps sitting around in the background, ready to be reused after a query finishes.

Here is the catch: if you set MaxOpenConns to 100 but leave MaxIdleConns at the default (which is 2), you're creating a performance bottleneck. Imagine your app handles a burst of 50 concurrent requests. Go opens 50 connections. As soon as those requests finish, Go kills 48 of them because you told it to only keep 2 idle. A millisecond later, 50 more requests hit, and Go has to perform the expensive TCP handshake to open 48 brand new connections all over again. It's a waste of CPU and latency.

I generally recommend setting SetMaxIdleConns equal to SetMaxOpenConns if you expect consistent traffic. It keeps the pool "warm."

// Keep the pool warm so we aren't constantly reconnecting
db.SetMaxOpenConns(25)
db.SetMaxIdleConns(25)

Why am I getting "connection reset by peer" errors even though my pool is configured?

If you've set your max connections and you're still seeing weird "broken pipe" or "connection reset" errors after the app has been idling for a while, you're likely fighting a network timeout. Most firewalls, load balancers (like AWS NLB), or the database servers themselves will kill a connection that has been sitting idle for too long.

The problem is that Go's pool thinks the connection is still valid, tries to use it, and then the network tells it, "Actually, I closed this ten minutes ago." To fix this, you use SetConnMaxLifetime. This tells Go to retire a connection after a certain amount of time, regardless of whether it's idle or active, forcing it to open a fresh one.

Don't set this to something crazy like a year. I usually set it to slightly less than the database's own timeout—often 5 or 30 minutes is the sweet spot.

import "time"

// Retire connections after 5 minutes to avoid stale connections 
// caused by infrastructure timeouts.
db.SetConnMaxLifetime(5 * time.Minute)

📋 Practical Task

Exercise: Tuning a High-Concurrency User API Connection Pool

You are maintaining a User Profile API that connects to a MySQL database. The database is configured with a max_connections limit of 50. Currently, the Go app is using default settings, and during peak hours, you are seeing too many connections errors from MySQL and high latency caused by constant connection churning.

Your Task:

• Modify the database initialization code to ensure the application never exceeds 40 connections (leaving some room for administrative tools).
• Configure the pool to maintain those 40 connections as idle to eliminate the overhead of repeated TCP handshakes.
• Set a connection lifetime of 3 minutes to ensure that the load balancer doesn't silently drop the connections.

Implement these three configuration calls on the *sql.DB instance in your main.go or database setup package.