The Limits of Single-Server Scaling
Even with read replicas and memory caching, a single database server hits hard write limits.
- ◆The Bottleneck: Disk I/O bottlenecks and write lock contentions.
- ◆The Solution: Database Sharding—partitioning your database tables across multiple independent servers.
Sharding database systems requires careful planning, as queries must route to correct nodes programmatically.
Scale Rule: Partition tables using a consistent shard key (e.g.
TenantIdorUserId) to distribute write operations evenly across servers.
Core Sharding Algorithms
Architects utilize specific partitioning patterns:
1. Range Sharding
Partitioning data based on ranges of values (e.g. Users A-M go to Shard 1, N-Z go to Shard 2).
- ◆*Con:* Causes hotspots if user registrations are uneven.
2. Hash Sharding
Applying a hash function to the shard key, dividing the result by the number of shards to locate the target database node:
// Shard routing calculation in C#
int shardIndex = Math.Abs(tenantId.GetHashCode()) % totalShards;| Sharding Pattern | Operational Pros | Operational Cons |
|---|---|---|
| Hash Sharding | Distributes data and writes evenly. | Difficult to add new shards. |
| Directory Routing | Flexible. Shards can be moved dynamically. | Requires database lookup on routing. |
Managing Cross-Shard Queries
Queries that cross shard boundaries (e.g. listing all users globally) are slow. Avoid these by designing tables so that all related child records share the parent tenant key, localizing queries to a single shard database.