GORM Connection Pool Settings That Actually Matter at Scale

GORM does not have a connection pool of its own. It sits on the one in database/sql, and every knob you need is on the *sql.DB that db.DB() returns. The defaults are unlimited open connections and two idle ones, which work for a script and fail for a service in both directions: a traffic spike opens hundreds of connections and exhausts the server, and a steady load churns connections because only two stay warm. This article measures what the settings do, using 50 goroutines running 20 ms queries against PostgreSQL 18.6.

It applies the settings from database/sql pool settings to GORM, and it pairs with pgxpool for the same decisions when you use pgx directly. Detecting a pool that is leaking connections is in finding connection leaks.

The four settings

sqlDB, _ := db.DB()
sqlDB.SetMaxOpenConns(25)
sqlDB.SetMaxIdleConns(25)
sqlDB.SetConnMaxLifetime(30 * time.Minute)
sqlDB.SetConnMaxIdleTime(5 * time.Minute)

• SetMaxOpenConns is the hard cap on connections in use plus idle. When all are busy, the next caller waits. The default of 0 means unlimited.
• SetMaxIdleConns is how many unused connections are kept open for reuse. The default is 2. It cannot usefully exceed MaxOpenConns.
• SetConnMaxLifetime closes a connection after it has been open this long, whether busy or idle. It lets a pool pick up DNS changes and spread load across a rotating set of backends.
• SetConnMaxIdleTime closes a connection idle for this long, so a quiet service shrinks back down.

The lab

The program runs the same work with five pool sizes: 50 goroutines, each issuing 10 queries of pg_sleep(0.02), so 500 queries of about 20 ms. After each run it reads sqlDB.Stats(), whose WaitCount and WaitDuration count how often callers had to wait for a free connection.

package main

import (
	"fmt"
	"log"
	"sync"
	"time"

	"gorm.io/driver/postgres"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

func must(err error) {
	if err != nil {
		log.SetFlags(0)
		log.Fatal(err)
	}
}

// connect recreates a schema and returns a connection whose search_path points at it.
func connect(schema string, cfg *gorm.Config) *gorm.DB {
	base := "host=127.0.0.1 port=55432 user=postgres password=secret dbname=lab sslmode=disable"
	admin, err := gorm.Open(postgres.Open(base), &gorm.Config{Logger: logger.Discard})
	must(err)
	must(admin.Exec("drop schema if exists " + schema + " cascade").Error)
	must(admin.Exec("create schema " + schema).Error)
	if cfg == nil {
		cfg = &gorm.Config{Logger: logger.Discard}
	}
	db, err := gorm.Open(postgres.Open(base+" search_path="+schema), cfg)
	must(err)
	return db
}

func load(db *gorm.DB, conns int, workers, queriesEach int) (time.Duration, int64, time.Duration) {
	sqlDB, _ := db.DB()
	sqlDB.SetMaxOpenConns(conns)
	sqlDB.SetMaxIdleConns(conns)
	before := sqlDB.Stats()
	var wg sync.WaitGroup
	start := time.Now()
	for w := 0; w < workers; w++ {
		wg.Add(1)
		go func() {
			defer wg.Done()
			for i := 0; i < queriesEach; i++ {
				db.Exec("select pg_sleep(0.02)")
			}
		}()
	}
	wg.Wait()
	after := sqlDB.Stats()
	return time.Since(start), after.WaitCount - before.WaitCount, after.WaitDuration - before.WaitDuration
}

func main() {
	db := connect("l0721", nil)
	const workers, each = 50, 10 // 500 queries of 20 ms each
	fmt.Println("500 queries of 20 ms, 50 goroutines:")
	for _, conns := range []int{1, 5, 10, 25, 50} {
		total, waits, waited := load(db, conns, workers, each)
		fmt.Printf("  max open %2d: %6s total, %3d waits, %6s total wait\n", conns, total.Round(10*time.Millisecond), waits, waited.Round(100*time.Millisecond))
	}

	// What an idle limit does to a bursty service.
	sqlDB, _ := db.DB()
	sqlDB.SetMaxOpenConns(25)
	sqlDB.SetMaxIdleConns(2)
	before := sqlDB.Stats()
	for burst := 0; burst < 3; burst++ {
		var wg sync.WaitGroup
		for i := 0; i < 20; i++ {
			wg.Add(1)
			go func() { defer wg.Done(); db.Exec("select pg_sleep(0.02)") }()
		}
		wg.Wait()
	}
	after := sqlDB.Stats()
	fmt.Println("3 bursts of 20 with MaxIdleConns=2: idle closed", after.MaxIdleClosed-before.MaxIdleClosed, "open now", after.OpenConnections)

	// Lifetime settings are applied lazily; show they are set.
	sqlDB.SetConnMaxLifetime(30 * time.Minute)
	sqlDB.SetConnMaxIdleTime(5 * time.Minute)
	fmt.Println("MaxOpenConnections:", sqlDB.Stats().MaxOpenConnections)

	var maxConn string
	db.Raw("show max_connections").Scan(&maxConn)
	fmt.Println("server max_connections:", maxConn)
}

500 queries of 20 ms, 50 goroutines:
  max open  1: 11.49s total, 498 waits, 7m49.6s total wait
  max open  5:  2.34s total, 491 waits, 1m23.9s total wait
  max open 10:  1.12s total, 476 waits,  35.1s total wait
  max open 25:  490ms total, 444 waits,   9.2s total wait
  max open 50:  270ms total,   0 waits,     0s total wait
3 bursts of 20 with MaxIdleConns=2: idle closed 54 open now 2
MaxOpenConnections: 25
server max_connections: 100

Pool size is the concurrency limit

With one connection, 500 queries of 20 ms ran one after another and took 11.49 s, a little over the ideal 10 s because each call has overhead. Doubling the pool roughly halved the time, down to 270 ms at 50, where each goroutine had its own connection and nothing waited.

Two lessons. First, the pool size is a throughput limit. For a query that takes 20 ms, a pool of 10 can serve at most 500 queries a second. If your service gets more than that and the queries cannot get faster, the pool is the bottleneck, and requests queue inside database/sql where the only visible symptom is latency. Second, WaitCount is the signal. A pool that never waits is oversized, and one that waits on most requests is the limit. Export WaitCount, WaitDuration, InUse and OpenConnections as metrics.

Bigger is not better beyond a point. The queries here sleep, so they cost the server nothing. A real query uses CPU and I/O on the database, and more concurrent queries than the server has cores just divides the same capacity into slower slices. A common rule of thumb for PostgreSQL is to keep total active connections near two to four times the core count. Measure with your own query mix, not a sleep.

The server has a limit too

The last line of output is server max_connections: 100. That is the whole server's allowance, shared by every service, every replica, and every admin. A fleet of 10 application instances with MaxOpenConns(25) can ask for 250. The first time several instances are busy together, new connections are refused with too many clients already.

Budget the pool size per instance as the connection allowance divided by the number of instances, minus headroom for migrations and operators. If that number is too small for your concurrency, put PgBouncer in front in transaction mode, which lets many client connections share a few server connections. The PgBouncer article covers what breaks (prepared statements, session state). GORM's PrepareStmt: true is one of the options to check first.

MaxIdleConns and bursts

The second experiment sets MaxOpenConns(25) and MaxIdleConns(2), then runs three bursts of 20 concurrent queries. The result: idle closed 54 open now 2. Each burst needed up to 20 connections. When the burst ended, 18 of them were above the idle limit and were closed. The next burst opened them again. Over three bursts, 54 connections were closed, each of which cost a TCP handshake, an authentication, and a backend process start on the server.

For bursty traffic, set MaxIdleConns equal to MaxOpenConns so the pool keeps what it opened, and let ConnMaxIdleTime shrink it slowly when the service goes quiet. The common recommendation to keep idle low is for services with very many instances and little traffic each, where idle connections add up on the server.

Lifetimes

SetConnMaxLifetime is the setting people forget and then need. Reasons to set it:

• Load balancers and firewalls close idle TCP connections after a timeout (often 5 to 60 minutes). A connection closed silently shows up as an error on first use. A lifetime shorter than the infrastructure timeout avoids that, and database/sql retries bad connections on its own for most failures.
• Failover. After a database failover, old connections point at a demoted server. A bounded lifetime limits how long a stale connection can live.
• Memory growth on a long lived backend, from per connection caches and prepared statements.

Add jitter if you can. Connections opened together at start-up expire together, and the pool reconnects in a wave. A lifetime of 30 minutes with varied start times spreads it out. The driver does not add jitter for you.

Reading pool stats

sqlDB.Stats() returns a struct worth wiring to your metrics:

• OpenConnections, InUse, Idle: the current picture.
• WaitCount, WaitDuration: how often and how long callers waited. Rate of change is what alerts.
• MaxIdleClosed, MaxIdleTimeClosed, MaxLifetimeClosed: why connections are being closed. A high MaxIdleClosed is the symptom in the burst experiment.

A health check that opens a new connection each time is a leak in disguise; use PingContext on the pool.

A starting configuration

For a service with a handful of instances, a PostgreSQL server with 100 connections and moderately fast queries:

sqlDB.SetMaxOpenConns(20)
sqlDB.SetMaxIdleConns(20)
sqlDB.SetConnMaxLifetime(30 * time.Minute)
sqlDB.SetConnMaxIdleTime(10 * time.Minute)

Then watch WaitCount under real load and change one number at a time. Document the arithmetic (instances times pool size against max_connections) next to the settings, because the next person to add a replica needs it.

Checklist

• Set MaxOpenConns explicitly on every service. Never leave it unlimited.
• Set MaxIdleConns to the same value for steady or bursty load.
• Set ConnMaxLifetime below your network's idle timeout and ConnMaxIdleTime to shrink quiet pools.
• Budget instances times pool size against the server's max_connections.
• Export Stats() as metrics, and alert on WaitDuration growth.

The settings are described in the database/sql package documentation, and GORM's access to them in the GORM generic interface guide.