Your Go service has 600 Postgres connections and uses 6 of them

Last month a routine scale-up took down our orders database. Not CPU, not disk. Connections. Postgres said FATAL: sorry, too many clients already and every service that touched it fell over together.

How we got there

40 pods of one Go service, each with a pgx pool. Nobody had set MaxConns, so pgxpool used its default: the larger of 4 and runtime.NumCPU(). Our nodes have 16 cores. 40 times 16 is 640 possible connections against a max_connections of 500. It worked for months only because the pools rarely filled up. Then we scaled to 40.

A detail that surprised a few people: newer Go versions are smarter about setting GOMAXPROCS from container CPU limits, but runtime.NumCPU() still reports the CPUs the process can see, which in most containers is the whole node. pgxpool looks at NumCPU. Fixing GOMAXPROCS does not fix this.

Measure first

SELECT state, count(*)
FROM pg_stat_activity
WHERE datname = 'orders'
GROUP BY state;

612 idle, 6 active. Each of those idle connections is a full Postgres backend process with its own memory. We were paying for 600 cashiers to serve 6 customers.

Fix 1: size the pool on purpose

cfg, err := pgxpool.ParseConfig(os.Getenv("DATABASE_URL"))
if err != nil {
	return nil, err
}
cfg.MaxConns = 8
cfg.MinConns = 1
cfg.MaxConnIdleTime = 5 * time.Minute
cfg.MaxConnLifetime = time.Hour
cfg.HealthCheckPeriod = time.Minute

pool, err := pgxpool.NewWithConfig(ctx, cfg)

The budget rule we use now: replicas × MaxConns must stay below max_connections minus room for migrations, admin sessions and the next service someone adds. The pool size comes from the Helm values next to the replica count, so the person who changes one sees the other. For the total number of active connections, the old PostgreSQL wiki rule of thumb (around twice the database cores, plus a bit for IO) is still a sane starting point. More active connections than that and they mostly wait on each other.

Fix 2: PgBouncer in transaction mode

[databases]
orders = host=10.0.3.12 port=5432 dbname=orders

[pgbouncer]
listen_port = 6432
pool_mode = transaction
default_pool_size = 20
max_client_conn = 2000
max_prepared_statements = 200
auth_type = scram-sha-256
auth_file = /etc/pgbouncer/userlist.txt

In transaction mode a server connection is only held for the length of a transaction, so 2,000 client connections share 20 real ones.

The error that follows you there

The moment we switched, we got prepared statement "stmtcache_..." already exists. pgx by default prepares and caches statements per connection. In transaction mode your next query may land on a different server connection, where that statement does not exist, or does under the same name. Two ways out:

• PgBouncer 1.21 and newer can track prepared statements in transaction mode. Set max_prepared_statements (above) and keep pgx's default mode.
• On an older PgBouncer, tell pgx not to rely on server-side statements: cfg.ConnConfig.DefaultQueryExecMode = pgx.QueryExecModeExec (or the simple protocol).

And know what transaction mode breaks: session-level SET, session advisory locks, LISTEN/NOTIFY, and temp tables that live across transactions.

Result

612 server connections down to about 160 across all services, p99 latency unchanged, and the database got back about 5 GB of memory it was spending on idle processes. My father ran a dry cleaner with two counters and one presser for 31 years. He never hired a person to stand next to the presser in case it got busy.