Connection Pooling

Read the Incident From the Metrics That Already Exist

At 09:14 during a marketing push, api-gateway's error rate on every organization-service route spikes to 8%. The errors are not database errors — they are Go's own context deadline exceeded, surfaced from pgxpool.Pool.Acquire, which means requests are timing out waiting for a connection the pool does not have, not waiting for a slow query to finish. Observability added services/organization/internal/pgxmetrics to organization-service's registry — and only organization-service's, which is why this incident is visible on a dashboard at all rather than being guessed at. This is the first time anyone has read that collector under real load rather than at rest.

hoa_organization_postgres_pool_acquired_connections
/
hoa_organization_postgres_pool_max_connections

MaxConns Must Be Sized Against Postgres's Ceiling, Not Chosen Per Service

pkg/postgres/pool.go exposes MaxConnections as a per-service config value, and every service that adopted it has defaulted to 20 without anyone doing the arithmetic across the whole platform. Staging runs one shared PostgreSQL instance with max_connections = 100. Eleven services, each running two replicas for availability, each configured for MaxConns=20, is a theoretical ceiling of 440 connections against a server that accepts 100.

max_connections = 100
# reserved_connections for superuser/replication, not available to services
superuser_reserved_connections = 3

The marketing spike did not change the ceiling; it changed how many services simultaneously ran near their own configured maximum at the same moment, which had never happened together before. The fix is not raising organization's MaxConns — that makes the shared ceiling worse for every other service — it is giving each service a budget that sums to something the server can actually serve.

// DatabaseMaxConns is derived, not hard-coded: (shared budget) / (services * replicas),
// rounded down, with headroom left for migrations and manual psql sessions.
// 97 available / 11 services / avg 2.3 replicas ≈ 3.8 → 3, with a documented
// per-service override for auth, which legitimately needs more due to token
// verification's higher request volume.
const DatabaseMaxConns = 3

func Load() (Config, error) {
	// ...
	maxConns, err := platformenv.Int32("DATABASE_MAX_CONNS", DatabaseMaxConns)
	if err != nil {
		return Config{}, err
	}
	// ...
}

A Missing Statement Timeout Turns One Slow Query Into a Pool-Wide Incident

Lowering MaxConns without a statement timeout trades one incident for a worse one: with fewer connections available, a single query that never finishes — the N+1 loop from Lesson 2, before its fix shipped, held a connection open for the full duration of 41 sequential round trips — now starves a proportionally larger share of the pool's already-smaller budget.

func Open(ctx context.Context, input PoolConfig) (*pgxpool.Pool, error) {
	config, err := pgxpool.ParseConfig(input.URL)
	if err != nil {
		return nil, fmt.Errorf("parse database URL: %w", err)
	}
	config.MaxConns = input.MaxConnections
	config.MinConns = input.MinConnections
	config.MaxConnLifetime = input.MaxLifetime
	config.MaxConnIdleTime = input.MaxIdleTime
	config.HealthCheckPeriod = input.HealthCheckTime

	// Every connection this pool opens carries a server-side statement_timeout.
	// A runaway query now fails after 5s instead of holding a connection
	// (and, transitively, one eleventh of the platform's shared budget) forever.
	config.ConnConfig.RuntimeParams["statement_timeout"] = fmt.Sprintf(
		"%dms", input.StatementTimeout.Milliseconds(),
	)

	pool, err := pgxpool.NewWithConfig(ctx, config)
	if err != nil {
		return nil, fmt.Errorf("create database pool: %w", err)
	}
	if err := pool.Ping(ctx); err != nil {
		pool.Close()
		return nil, fmt.Errorf("ping database: %w", err)
	}
	return pool, nil
}

Consider PgBouncer When Replica Count, Not Query Load, Is the Constraint

Even correctly sized, eleven services times several replicas each is a lot of idle-but-reserved connections during quiet periods, because MinConns keeps a floor open per replica regardless of traffic. PgBouncer in transaction-pooling mode sits between every service and PostgreSQL, multiplexing many application-level connections onto a much smaller number of real backend connections that are only held for the duration of a single transaction.

Verify the Fix Under the Same Concurrency That Caused the Incident

A config change to MaxConns and a new statement_timeout are both unverified until they are exercised by the same concurrent load pattern that produced the original incident — not just confirmed present in a config file.

# before: MaxConns=20 per replica, no statement_timeout
hoa_organization_postgres_pool_acquired_connections  20 (pegged for 90s)
hoa_organization_postgres_pool_canceled_acquires_total  184

# after: MaxConns=3 per replica, statement_timeout=5s, PgBouncer transaction pooling in front
hoa_organization_postgres_pool_acquired_connections  2.1 (average, brief peaks to 3)
hoa_organization_postgres_pool_canceled_acquires_total  0