Database Sharding Strategies: Horizontal Scaling Done Right

snippet-2.go

package sharding

import "fmt"

const shardCount = 4
const rangeSize = 1_000_000

// RangeSharder routes based on numeric ID ranges.
type RangeSharder struct {
	shards []string // DSN per shard
}

func NewRangeSharder(dsns []string) *RangeSharder {
	return &RangeSharder{shards: dsns}
}

func (s *RangeSharder) ShardForID(userID int64) (string, error) {
	index := int(userID-1) / rangeSize
	if index < 0 || index >= len(s.shards) {
		return "", fmt.Errorf("user_id %d out of range for %d shards", userID, len(s.shards))
	}
	return s.shards[index], nil
}

snippet-3.sql

-- Partition the orders table by user_id range (PostgreSQL 11+)
CREATE TABLE orders_partitioned (
    order_id    BIGSERIAL,
    user_id     BIGINT      NOT NULL,
    total_cents INT         NOT NULL,
    created_at  TIMESTAMPTZ NOT NULL DEFAULT NOW()
) PARTITION BY RANGE (user_id);

CREATE TABLE orders_shard_0 PARTITION OF orders_partitioned
    FOR VALUES FROM (1) TO (1000001);

CREATE TABLE orders_shard_1 PARTITION OF orders_partitioned
    FOR VALUES FROM (1000001) TO (2000001);

CREATE TABLE orders_shard_2 PARTITION OF orders_partitioned
    FOR VALUES FROM (2000001) TO (3000001);

CREATE TABLE orders_shard_3 PARTITION OF orders_partitioned
    FOR VALUES FROM (3000001) TO (MAXVALUE);

snippet-4.go

package sharding

import (
	"hash/fnv"
	"fmt"
)

// HashSharder routes using FNV-1a hash mod shard count.
type HashSharder struct {
	shards []string
}

func NewHashSharder(dsns []string) *HashSharder {
	return &HashSharder{shards: dsns}
}

func (s *HashSharder) ShardForKey(key string) string {
	h := fnv.New32a()
	h.Write([]byte(key))
	index := int(h.Sum32()) % len(s.shards)
	return s.shards[index]
}

func (s *HashSharder) ShardForID(userID int64) string {
	key := fmt.Sprintf("%d", userID)
	return s.ShardForKey(key)
}

snippet-5.go

package sharding

import (
	"fmt"
	"hash/fnv"
	"sort"
	"sync"
)

// ConsistentHashRing implements a simple consistent hash ring.
type ConsistentHashRing struct {
	mu       sync.RWMutex
	replicas int
	ring     map[uint32]string
	keys     []uint32
}

func NewConsistentHashRing(replicas int) *ConsistentHashRing {
	return &ConsistentHashRing{replicas: replicas, ring: make(map[uint32]string)}
}

func (r *ConsistentHashRing) AddNode(node string) {
	r.mu.Lock()
	defer r.mu.Unlock()
	for i := 0; i < r.replicas; i++ {
		h := r.hash(fmt.Sprintf("%s#%d", node, i))
		r.ring[h] = node
		r.keys = append(r.keys, h)
	}
	sort.Slice(r.keys, func(i, j int) bool { return r.keys[i] < r.keys[j] })
}

func (r *ConsistentHashRing) GetNode(key string) string {
	r.mu.RLock()
	defer r.mu.RUnlock()
	h := r.hash(key)
	idx := sort.Search(len(r.keys), func(i int) bool { return r.keys[i] >= h })
	if idx == len(r.keys) {
		idx = 0
	}
	return r.ring[r.keys[idx]]
}

func (r *ConsistentHashRing) hash(key string) uint32 {
	h := fnv.New32a()
	h.Write([]byte(key))
	return h.Sum32()
}

snippet-6.go

package sharding

import (
	"context"
	"fmt"
	"github.com/redis/go-redis/v9"
)

// DirectorySharder looks up shard assignments from Redis.
type DirectorySharder struct {
	rdb    *redis.Client
	shards map[string]string // shard name -> DSN
}

func NewDirectorySharder(rdb *redis.Client, shards map[string]string) *DirectorySharder {
	return &DirectorySharder{rdb: rdb, shards: shards}
}

// ShardForTenant returns the DSN for the shard that owns a given tenant.
func (d *DirectorySharder) ShardForTenant(ctx context.Context, tenantID string) (string, error) {
	shardName, err := d.rdb.HGet(ctx, "shard_directory", tenantID).Result()
	if err == redis.Nil {
		return "", fmt.Errorf("no shard assignment for tenant %s", tenantID)
	}
	if err != nil {
		return "", fmt.Errorf("directory lookup failed: %w", err)
	}
	dsn, ok := d.shards[shardName]
	if !ok {
		return "", fmt.Errorf("unknown shard name %q for tenant %s", shardName, tenantID)
	}
	return dsn, nil
}

// AssignTenant writes a tenant-to-shard mapping into the directory.
func (d *DirectorySharder) AssignTenant(ctx context.Context, tenantID, shardName string) error {
	return d.rdb.HSet(ctx, "shard_directory", tenantID, shardName).Err()
}

snippet.sql

-- A naive shard key choice: user_id on an orders table
-- Works well if queries are "all orders for user X"
-- Works poorly if you frequently query "all orders in region Y"

CREATE TABLE orders (
    order_id    BIGSERIAL PRIMARY KEY,
    user_id     BIGINT        NOT NULL,
    region      TEXT          NOT NULL,
    total_cents INT           NOT NULL,
    created_at  TIMESTAMPTZ   NOT NULL DEFAULT NOW()
);

-- Index on the shard key is non-negotiable
CREATE INDEX idx_orders_user_id ON orders (user_id);