Explicit Connection Pooling in Go: Architecting Physical Tenant Isolation

From Shared Pools to Resource Silos

When building a standard Go application, *sql.DB is not a single connection; it is a thread-safe connection pool manager. I usually initialize one global pool and share it across all goroutines. While efficient, this assumes a single target database.

However, when a system must handle multiple tenants with dedicated databases, the architecture must evolve from a single manager to an "Array of Managers." In this model, we give every tenant its own isolated *sql.DB instance, creating a physical silo where internal mutexes and idle connection lists are completely separated.

The Registry Pattern: How It Works

The core of this architecture is the shift from "Mutation" (changing a global state) to "Selection " (resolving a resource from a registry). I manage this via a thread-safe lookup table. To prevent the "Thundering Herd" problem—where a spike in requests for an uninitialized tenant causes lock contention—we use Go's singleflight package to ensure the initialization only happens once, even under heavy concurrent load.

go

type TenantRegistry struct {
    pools        map[string]*sql.DB
    mu           sync.RWMutex
    requestGroup singleflight.Group
}

func (r *TenantRegistry) GetPool(ctx context.Context, id string) (*sql.DB, error) {
    r.mu.RLock()
    pool, exists := r.pools[id]
    r.mu.RUnlock()

    if exists { return pool, nil }

    // singleflight ensures only one initialization occurs per tenant ID
    v, err, _ := r.requestGroup.Do(id, func() (interface{}, error) {
        // Double-check in case it was created while waiting
        r.mu.RLock()
        if p, ok := r.pools[id]; ok {
            r.mu.RUnlock()
            return p, nil
        }
        r.mu.RUnlock()

        newPool, err := sql.Open("postgres", getDSN(id))
        if err != nil {
            return nil, err
        }

        // Use a background context for initialization ping.
        // If we use the request context, a short timeout could cancel
        // the pool creation and cause cascading failures.
        initCtx, cancel := context.WithTimeout(context.Background(), 5 * time.Second)
        defer cancel()

        if err := newPool.PingContext(initCtx); err != nil {
            return nil, err
        }

        r.mu.Lock()
        r.pools[id] = newPool
        r.mu.Unlock()

        return newPool, nil
    })

    if err != nil {
        return nil, err
    }
    return v.(*sql.DB), nil
}

Enforcement via Dependency Injection

The registry provides safety, but I rely on Dependency Injection for enforcement. By binding the repository to a specific *sql.DB instance at instantiation, we create an architecturally enforced silo. While the compiler ensures type safety, careful middleware scoping is required to prevent injecting the wrong tenant pool at runtime.

In the service layer or middleware, the pool is resolved and injected into the repository. Once injected, the repository is physically incapable of querying the wrong database because it has no access to any other state.

go

type OrderRepository struct {
    db *sql.DB // Scoped to a specific tenant
}

func (repo *OrderRepository) Fetch(ctx context.Context) {
    // Architecturally enforced: no global state is accessed
    repo.db.QueryContext(ctx, "SELECT * FROM orders")
}

The "Silo Tax": Pragmatic Trade-offs

Maintaining multiple simultaneous pools involves trading Resident Set Size (RSS) memory for reliability and speed. It is a classic engineering calculation.

Handshake Elimination: By successfully pinging and keeping pools "warm," we eliminate the 20ms-100ms TCP/TLS handshake latency for subsequent requests. Queries start instantly.
Granular Governance: We can set MaxOpenConns(50) for a high-traffic tenant while restricting a trial tenant to MaxOpenConns(2).
The Multiplier Effect: $N$ tenants $\times$ $M$ idle connections can lead to socket exhaustion. Tuning SetMaxIdleConns(1) and SetConnMaxLifetime() is mandatory.
The Eviction Challenge: A growing registry will leak memory if tenants are never removed. Implementing an LRU (Least Recently Used) eviction policy is the most difficult architectural challenge here. Safely calling pool.Close() on a dormant tenant requires carefully draining active connections and managing state concurrently without blocking new incoming requests.

Scaling for Exponential Growth

A single registry works well for hundreds of tenants, but scaling to thousands requires Horizontal Sharding . In this scenario, we group tenants into clusters and deploy sharded backend instances.

Each shard only manages the "warm" pools for its specific subset of tenants. This maintains the explicit pooling model while keeping the memory footprint of individual instances manageable.

The Bottom Line

In my experience, explicit is always safer than implicit. Shifting to siloed connection pooling aligns the architecture with Go’s concurrency model. We trade a higher memory footprint for a system that is fundamentally honest, faster, and establishes strict physical isolation boundaries between tenants.