Sharding

Design and implement horizontal database sharding strategies to distribute data across multiple database instances, enabling applications to scale beyond single-server limitations with consistent hashing, automatic rebalancing, and cross-shard query coordination.

When to Use This Command

Use /sharding when you need to:

• Scale beyond single database server capacity (>10TB or >100k QPS)
• Distribute write load across multiple database servers
• Improve query performance through data locality
• Implement geographic data distribution for GDPR/data residency
• Reduce blast radius of database failures (isolate tenant data)
• Support multi-tenant SaaS with tenant-level isolation

DON'T use this when:

• Database is small (<1TB) and performing well
• Can solve with read replicas and caching instead
• Application can't handle distributed transactions complexity
• Team lacks expertise in distributed systems
• Cross-shard queries are majority of workload (use partitioning instead)

Design Decisions

This command implements consistent hashing with virtual nodes because:

• Minimizes data movement when adding/removing shards (only K/n keys move)
• Distributes load evenly across shards with virtual nodes
• Supports gradual shard addition without downtime
• Enables geographic routing for data residency compliance
• Provides automatic failover with shard replica promotion

Alternative considered: Range-based sharding

• Simple to implement and understand
• Predictable data distribution
• Prone to hotspots if key distribution uneven
• Recommended for time-series data with sequential IDs

Alternative considered: Directory-based sharding

• Flexible shard assignment with lookup table
• Easy to move individual records
• Single point of failure (directory lookup)
• Recommended for small-scale or initial implementations

Prerequisites

Before running this command:

1. Application supports sharding-aware database connections
2. Clear understanding of sharding key (immutable, high cardinality)
3. Strategy for handling cross-shard queries and joins
4. Monitoring infrastructure for shard health
5. Migration plan from single database to sharded architecture

Implementation Process

Step 1: Choose Sharding Strategy

Select sharding approach based on data access patterns and scale requirements.

Step 2: Design Shard Key

Choose immutable, high-cardinality key that distributes data evenly (user_id, tenant_id).

Step 3: Implement Shard Routing Layer

Build connection pooling and routing logic to direct queries to correct shard.

Step 4: Migrate Data to Shards

Perform zero-downtime migration from monolithic to sharded architecture.

Step 5: Monitor and Rebalance

Track shard load distribution and rebalance data as needed.

Output Format

The command generates:

• sharding/shard_router.py - Consistent hashing router implementation
• sharding/shard_manager.js - Shard connection pool manager
• migration/shard_migration.sql - Data migration scripts per shard
• monitoring/shard_health.sql - Per-shard metrics and health checks
• docs/sharding_architecture.md - Architecture documentation and runbooks

Code Examples

Example 1: Consistent Hashing Shard Router with Virtual Nodes

# sharding/consistent_hash_router.py
import hashlib
import bisect
from typing import List, Dict, Optional, Any
from dataclasses import dataclass
import logging

logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

@dataclass
class ShardConfig:
    """Configuration for a database shard."""
    shard_id: int
    host: str
    port: int
    database: str
    weight: int = 1  # Relative weight for load distribution
…

Example 2: Shard-Aware Database Connection Pool

// sharding/shard_connection_pool.js
const { Pool } = require('pg');
const crypto = require('crypto');

class ShardConnectionPool {
    constructor(shardConfigs) {
        this.shards = new Map();
        this.virtualNodes = 150;
        this.ring = [];
        this.ringMap = new Map();

        // Initialize connection pools for each shard
        shardConfigs.forEach(config => {
            const pool = new Pool({
                host: config.host,
                port: config.port,
                database: config.database,
                user: config.user,
…

Example 3: Geographic Sharding with Data Residency

# sharding/geo_shard_router.py
from typing import Dict, Optional
from dataclasses import dataclass
from enum import Enum

class Region(Enum):
    """Geographic regions for data residency compliance."""
    US_EAST = 'us-east'
    US_WEST = 'us-west'
    EU_WEST = 'eu-west'
    ASIA_PACIFIC = 'asia-pacific'

@dataclass
class GeoShardConfig:
    region: Region
    shard_id: int
    host: str
    port: int
…