How do you manage database connections and sharding in a high-scale Go service?

At scale, a single database becomes a bottleneck. Go services address this through connection pool tuning, read replicas, and horizontal sharding. The database/sql pool must be sized carefully — too few connections cause queuing, too many overwhelm the DB.

// Connection pool configuration
func openDB(dsn string) (*sql.DB, error) {
    db, err := sql.Open("postgres", dsn)
    if err != nil { return nil, err }

    // Tune the pool
    db.SetMaxOpenConns(25)                  // max concurrent connections
    db.SetMaxIdleConns(25)                  // keep idle connections warm
    db.SetConnMaxLifetime(5 * time.Minute)  // close and reopen periodically
    db.SetConnMaxIdleTime(1 * time.Minute)  // close long-idle connections

    return db, db.PingContext(context.Background())
}

// Read replica routing
type DBPool struct {
    primary  *sql.DB
    replicas []*sql.DB
    rr       uint64 // round-robin counter
}

func (p *DBPool) ReadDB() *sql.DB {
    if len(p.replicas) == 0 { return p.primary }
    idx := atomic.AddUint64(&p.rr, 1) % uint64(len(p.replicas))
    return p.replicas[idx]
}

// Hash-based sharding (user ID → shard)
type ShardedDB struct {
    shards []*sql.DB
}

func (s *ShardedDB) shardFor(userID int64) *sql.DB {
    // Consistent hashing: hash(userID) mod N shards
    h := fnv32(userID) % uint32(len(s.shards))
    return s.shards[h]
}

func (s *ShardedDB) GetUser(ctx context.Context, id int64) (*User, error) {
    db := s.shardFor(id)
    row := db.QueryRowContext(ctx,
        "SELECT id, name FROM users WHERE id = $1", id)
    var u User
    return &u, row.Scan(&u.ID, &u.Name)
}

Pool sizing rule of thumb: set MaxOpenConns to the number of CPU cores on the DB server (for CPU-bound queries) or the connection limit minus connections used by other services. Postgres default connection limit is 100 — a service with 4 replicas should use at most 20 connections each.

Invest now in Acorns!!! 🚀
Join Acorns and get your $5 bonus!

Acorns is a micro-investing app that automatically invests your "spare change" from daily purchases into diversified, expert-built portfolios of ETFs. It is designed for beginners, allowing you to start investing with as little as $5. The service automates saving and investing. Disclosure: I may receive a referral bonus.

Invest now!!! Get Free equity stock (US, UK only)!

Use Robinhood app to invest in stocks. It is safe and secure. Use the Referral link to claim your free stock when you sign up!.

The Robinhood app makes it easy to trade stocks, crypto and more.

Webull! Receive free stock by signing up using the link: Webull signup.