Database Sharding — Why Sequential IDs Cause Hot Shards

# Package: io.thecodeforge.sharding.router

import hashlib

class ShardRouter:
    def __init__(self, shard_dsn_list: list[str]):
        self.shards = shard_dsn_list
        self.n = len(shards)

    def _hash(self, key: str) -> int:
        # Use consistent hashing in production, not plain modulo
        return int(hashlib.md5(key.encode()).hexdigest(), 16) % self.n

    def get_shard_for_key(self, key: str) -> str:
        return self.shards[self._hash(key)]

    def execute_query(self, key: str, sql: str, params: dict):
        if "WHERE" not in sql:
            raise ValueError("Query must include shard key filter")
        shard_id = self.get_shard_for_key(key)
        # Execute on the single shard
        return self._query_shard(shard_id, sql, params)

    def execute_cross_shard(self, sql: str, params: dict):
        # Broadcast to all shards and merge
        results = []
        for shard in self.shards:
            results.extend(self._query_shard(shard, sql, params))
        return results

# Package: io.thecodeforge.sharding.strategies

import hashlib

class HashShard:
    def __init__(self, n_shards: int):
        self.n = n_shards

    def route(self, user_id: int) -> int:
        return user_id % self.n

class RangeShard:
    def __init__(self, boundaries: list[int]):
        # boundaries like [0, 1_000_000, 2_000_000]
        self.boundaries = boundaries

    def route(self, user_id: int) -> int:
        for i in range(1, len(self.boundaries)):
            if user_id < self.boundaries[i]:
                return i - 1
        return len(self.boundaries) - 1

class ConsistentHashRing:
    def __init__(self, nodes: list[str], replicas: int = 150):
        self.ring = {}
        self.sorted_keys = []
        for node in nodes:
            for i in range(replicas):
                key = hashlib.md5(f"{node}:{i}".encode()).hexdigest()
                hash_val = int(key, 16)
                self.ring[hash_val] = node
            self.sorted_keys = sorted(self.ring.keys())

    def get_node(self, key: str) -> str:
        if not self.ring:
            return None
        hash_val = int(hashlib.md5(key.encode()).hexdigest(), 16)
        idx = bisect_left(self.sorted_keys, hash_val)
        if idx == len(self.sorted_keys):
            idx = 0
        return self.ring[self.sorted_keys[idx]]

-- Schema for shard map lookup table
CREATE TABLE io_thecodeforge_sharding.shard_map (
    shard_key_hash BIGINT PRIMARY KEY,
    shard_id INT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (shard_id) REFERENCES io_thecodeforge_sharding.shard_config(shard_id)
);

-- When a new entity is created, insert into the map
INSERT INTO io_thecodeforge_sharding.shard_map (shard_key_hash, shard_id)
VALUES (MD5(CONCAT('user_', :user_id)) MOD 1000000, :target_shard);

-- Query to find which shard holds a given user
SELECT shard_id FROM io_thecodeforge_sharding.shard_map
WHERE shard_key_hash = MD5(CONCAT('user_', :user_id)) MOD 1000000;

# Package: io.thecodeforge.sharding.cross_shard

# Example of a saga that transfers funds across shards
import asyncio

class SagaCoordinator:
    async def transfer(self, from_account, to_account, amount):
        # Step 1: Prepare both shards
        from_shard = self.get_shard(from_account.user_id)
        to_shard = self.get_shard(to_account.user_id)
        
        from_ok = await self.prepare_debit(from_shard, from_account, amount)
        if not from_ok:
            return abort
        to_ok = await self.prepare_credit(to_shard, to_account, amount)
        if not to_ok:
            await self.rollback_debit(from_shard, from_account, amount)
            return abort
        
        # Step 2: Commit
        try:
            await self.commit_debit(from_shard, from_account, amount)
            await self.commit_credit(to_shard, to_account, amount)
        except Exception:
            # Compensating transaction
            await self.compensate(from_shard, to_shard)
            raise
        return success

# Package: io.thecodeforge.sharding.consistent_hash

import hashlib
from bisect import bisect_left

class ConsistentHash:
    def __init__(self, nodes: list[str], vnodes: int = 150):
        self.ring = {}
        self.sorted_keys = []
        for node in nodes:
            for i in range(vnodes):
                key = hashlib.md5(f"{node}:{i}".encode()).hexdigest()
                h = int(key, 16)
                self.ring[h] = node
                self.sorted_keys.append(h)
        self.sorted_keys.sort()

    def get_node(self, key: str) -> str:
        if not self.ring:
            return None
        h = int(hashlib.md5(key.encode()).hexdigest(), 16)
        idx = bisect_left(self.sorted_keys, h)
        if idx == len(self.sorted_keys):
            idx = 0
        return self.ring[self.sorted_keys[idx]]

    def add_node(self, new_node: str, vnodes: int = 150):
        # Adding only affects adjacent ring keys
        for i in range(vnodes):
            key = hashlib.md5(f"{new_node}:{i}".encode()).hexdigest()
            h = int(key, 16)
            self.ring[h] = new_node
            self.sorted_keys.append(h)
        self.sorted_keys.sort()