The simplest multi-tenant design in Go is also the most dangerous: one *sql.DB per tenant, looked up from a map at request time. It works beautifully for ten tenants and kills you at ten thousand. The first symptom is subtle — connections stop being reused because every tenant’s pool holds a few idle connections, and before long too many open files shows up in a random request. We learned this the hard way: a pool-per-tenant service with ~1,200 tenants, default settings everywhere, OOM’d a 1 GB container because every *sql.DB carries its own idle connection budget and sql.Open happily creates pools that never close. The fix isn’t “fewer tenants” — it’s engineering the pool lifecycle.
Why per-tenant pools blow up
sql.DB is not a connection; it’s a pool. With defaults, it opens connections lazily and keeps them alive. A tenant pool holding even two idle connections means 1,200 tenants × 2 = 2,400 live Postgres backends, each consuming several MB on the server and one file descriptor per socket on your side — before anyone makes a request. The kernel and the database both have limits, and you’ll hit both long before the code looks busy. So a correct pool-per-tenant manager needs three things: explicit small idle limits, a lifetime cap so sockets rotate, and a reaper that closes pools for tenants that stopped showing up.
A manager that doesn’t leak
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
package tenantdb
import (
"context"
"database/sql"
"sync"
"time"
_ "github.com/jackc/pgx/v5/stdlib"
"golang.org/x/sync/singleflight"
)
type Manager struct {
sf singleflight.Group
mu sync.Mutex
dbs map[string]*sql.DB
touched map[string]time.Time
dsnFor func(ctx context.Context, tenantID string) (string, error)
}
func New(dsnFor func(ctx context.Context, tenantID string) (string, error)) *Manager {
m := &Manager{
dbs: make(map[string]*sql.DB),
touched: make(map[string]time.Time),
dsnFor: dsnFor,
}
go m.reap(30 * time.Minute)
return m
}
func (m *Manager) DB(ctx context.Context, tenantID string) (*sql.DB, error) {
m.mu.Lock()
if db, ok := m.dbs[tenantID]; ok {
m.touched[tenantID] = time.Now()
m.mu.Unlock()
return db, nil
}
m.mu.Unlock()
v, err, _ := m.sf.Do(tenantID, func() (any, error) {
// double-check inside the singleflight after the concurrent openers collapse
m.mu.Lock()
if db, ok := m.dbs[tenantID]; ok {
m.touched[tenantID] = time.Now()
m.mu.Unlock()
return db, nil
}
m.mu.Unlock()
dsn, err := m.dsnFor(ctx, tenantID)
if err != nil {
return nil, err
}
db, err := sql.Open("pgx", dsn)
if err != nil {
return nil, err
}
db.SetMaxOpenConns(20)
db.SetMaxIdleConns(2)
db.SetConnMaxLifetime(30 * time.Minute)
db.SetConnMaxIdleTime(5 * time.Minute)
if err := db.PingContext(ctx); err != nil {
db.Close()
return nil, err
}
m.mu.Lock()
m.dbs[tenantID] = db
m.touched[tenantID] = time.Now()
m.mu.Unlock()
return db, nil
})
if err != nil {
return nil, err
}
return v.(*sql.DB), nil
}
func (m *Manager) reap(idleFor time.Duration) {
for range time.Tick(time.Minute) {
cutoff := time.Now().Add(-idleFor)
m.mu.Lock()
for id, db := range m.dbs {
if m.touched[id].Before(cutoff) {
db.Close()
delete(m.dbs, id)
delete(m.touched, id)
}
}
m.mu.Unlock()
}
}
Two details here are the entire difference between working and broken. singleflight.Group collapses N concurrent first-requests for the same tenant into one sql.Open — without it, a traffic spike to a cold tenant opens N duplicate pools and the race-avoidance logic turns into a stampede. And PingContext is mandatory: sql.Open doesn’t dial the database, it only validates the DSN. A bad tenant credential that skips the ping gives you a pool that fails on the first real query — late, in the middle of a request, with no context. The ping moves that failure to a place you can handle.
Sizing the whole system
Pool-per-tenant only stays viable if the aggregate is bounded, not just each pool. With MaxIdleConns: 2 and a 30-minute reaper, a 10,000-tenant shard holds at most ~20,000 idle backends only if every tenant is hot; in practice the 80/20 rule means a few hundred pools are live at once, which Postgres tolerates. That’s the constraint to design for: Postgres max_connections and your ulimit, not the pool config of any single tenant. If your hot set exceeds a few hundred, pool-per-tenant stops being the right shape and you’re really building a router.
The alternatives that scale further
Once per-tenant pools become too heavy, the options, in escalating order:
- Pool-per-shard. Tenants share a database (schema per tenant, or a
tenant_idcolumn) and you keep one pool per shard. Routing picks a pool, not a connection. This is what most of us actually want — pooling by where data lives, not by who owns it. - Single pool plus Postgres Row Level Security. One database, one pool, and
SET app.tenant_id = ?per request with RLS policies enforcing isolation. No per-tenant pools at all; the database does the firewalling. This is the right answer for thousands of tenants on a few servers. pgbouncerin transaction mode. One Go pool to PgBouncer, which multiplexes many transactions over few real Postgres connections. Useful when tenant count explodes faster than you can re-architect.
Pool-per-tenant is a legitimate stage, not a destination. Our rule of thumb: under a few hundred active tenants it’s simple and fine; above that, shard-pool or RLS and keep the connection math boring.
Monitoring that catches the leak early
db.Stats() is the leak detector and it’s free — OpenConnections, Idle, WaitCount, MaxIdleClosed, MaxLifetimeClosed. We export it per tenant on an interval and alert on WaitCount growth (queued for a connection that never comes) and on the number of live pools. The two alerts that would have caught our original outage: pool count growing past the daily-active-tenant baseline, and file descriptor count tracking 1:1 with pool count. Expose both before you need them — every connection leak we’ve had was invisible until it was an incident.