Database Sharding

Also Known As

TL;DR

Explanation

Sharding splits a dataset across multiple independent database nodes (shards) using a partition key (user_id % num_shards, geographic region, etc.). Each shard holds a subset of rows and handles reads/writes for its partition independently. Benefits: linear write scalability, smaller index sizes per shard, geographic distribution. Costs: cross-shard queries and joins become application-level operations, transactions span shard boundaries only with distributed protocols (2PC), rebalancing shards as data grows is operationally complex. For most PHP applications, read replicas, caching, and query optimisation should be exhausted before sharding.

Diagram

flowchart TD
    APP[Application] --> ROUTER[Shard Router<br/>which shard?]
    ROUTER -->|user_id 0-33pct| S1[(Shard 1<br/>users 0-9999)]
    ROUTER -->|user_id 34-66pct| S2[(Shard 2<br/>users 10k-19k)]
    ROUTER -->|user_id 67-100pct| S3[(Shard 3<br/>users 20k-30k)]
    subgraph Shard_Keys
        HASH[Hash sharding<br/>uniform distribution]
        RANGE[Range sharding<br/>range queries efficient]
        DIR[Directory sharding<br/>flexible lookup]
    end
    CROSS[Cross-shard queries<br/>expensive - avoid] -.->|design to prevent| ROUTER
style ROUTER fill:#6e40c9,color:#fff
style S1 fill:#1f6feb,color:#fff
style S2 fill:#238636,color:#fff
style S3 fill:#d29922,color:#fff
style CROSS fill:#f85149,color:#fff

Common Misconception

Why It Matters

Common Mistakes

• Sharding before exhausting vertical scaling and read replicas — premature sharding adds enormous complexity.
• Choosing a shard key that creates hot shards — all traffic to one shard defeats the purpose.
• Cross-shard queries and JOINs — they require scatter-gather operations that negate sharding benefits.
• Not planning for shard rebalancing — adding a new shard requires migrating data, which is operationally painful.

Code Examples

// Shard key on auto-increment ID — hot shard problem:
// All new writes go to the last shard (highest IDs)
// Shard 1: ids 1-1M (cold), Shard 2: ids 1M-2M (cold), Shard 3: ids 2M+ (all writes)
// Better shard key: hash(user_id) — distributes writes evenly

// Horizontal sharding — route by user_id modulo shard count
class ShardRouter {
    private array $shards; // array of PDO connections

    public function getConnection(int $userId): PDO {
        $shardIndex = $userId % count($this->shards);
        return $this->shards[$shardIndex];
    }
}

// Usage
$pdo = $router->getConnection($userId);
$pdo->prepare('SELECT * FROM orders WHERE user_id = ?')->execute([$userId]);

// Key design decisions:
// - Choose a shard key with high cardinality and even distribution
// - Avoid cross-shard JOINs — denormalise or use an aggregation layer
// - Consistent hashing handles shard rebalancing with minimal data movement

References

• ↗ https://www.postgresql.org/docs/current/ddl-partitioning.html

Tags

Related Terms