Database Sharding

Database sharding partitions rows (or documents) across multiple database instances—shards—so that no single node holds the entire dataset or absorbs all writes. Each shard is a self-contained database for its slice of keys. Applications (or a proxy) route operations to the correct shard.

Why shard

Vertical scaling eventually fails on dataset size, write QPS, or blast radius. Sharding addresses:

• Capacity — disk and memory per node stay bounded
• Write throughput — parallel primaries (scaling writes)
• Isolation — noisy tenants can be pinned to dedicated shards

Costs include cross-shard queries, harder transactions, operational complexity, and remapping when you reshard.

Shard key

The attribute (or hash of attributes) that determines which shard owns a row—e.g. tenant_id, user_id, or hash(order_id).

Choosing a shard key

A good key:

1. Appears on almost every query path that must stay single-shard
2. Distributes load evenly
3. Keeps entities that must be consistent together

Routing

Routers may live in the app, a proxy (Vitess, Citus coordinator, custom), or a client library. They need a shard map: key → shard (and primary endpoint). Keep the map versioned and cacheable.

Hash-based maps often use consistent hashing so adding a shard remaps roughly 1/N of keys instead of everything.

Range vs hash partitioning

Range sharding assigns contiguous key ranges (A–F, G–N, …). Great for ordered scans and split/merge of ranges; vulnerable to hotspots if keys are skewed.

Hash sharding assigns hash(key) → shard. Evens load; destroys locality for range queries.

Many production systems combine them: hash for balance, with tenant overrides for large customers.

Transactions and queries

-- Single-shard friendly: shard key in the predicate
SELECT * FROM orders
WHERE tenant_id = @tenant AND id = @orderId;

-- Cross-shard: no tenant predicate → fan-out
SELECT * FROM orders WHERE status = 'open';

Resharding

Growth forces splits. Strategies:

1. Consistent hash / vnode reshard — move only affected keys; dual-write or replay during cutover
2. Range split — bisect a hot range onto a new node
3. Tenant move — migrate one large tenant wholesale

Plan for:

• Backfill copy
• Catch-up replication of new writes
• Atomic cutover of the shard map
• Rollback if verification fails

This is where naive hash % N placement becomes operationally lethal—see the remapping discussion under consistent hashing.

Application patterns

public sealed class ShardRouter
{
    private readonly IReadOnlyList<DbConnectionFactory> _shards;

    public DbConnectionFactory ForTenant(TenantId tenant)
    {
        var index = ConsistentHash.NodeIndex(tenant.Value, _shards.Count);
        return _shards[index];
    }
}

public async Task<Order?> GetOrderAsync(TenantId tenant, OrderId id, CancellationToken ct)
{
    await using var conn = await _router.ForTenant(tenant).OpenAsync(ct);
    return await conn.QuerySingleOrDefaultAsync<Order>(
        "select * from orders where tenant_id = @tenant and id = @id",
        new { tenant = tenant.Value, id = id.Value });
}

Keep the shard key on every write path—including background jobs and event-driven consumers—so workers do not guess.

Operational checklist

• Backups and schema migrations per shard (or orchestrated rolling migrations)
• Observability labeled by shard id
• Capacity alarms per shard, not only global averages
• Runbooks for hot-tenant isolation