# shard_router.py
import hashlib
import asyncio
from typing import Dict, List, Optional
import asyncpg
from dataclasses import dataclass

@dataclass
class ShardNode:
    host: str
    port: int
    db_name: str
    user: str
    password: str
    logical_id: str  # e.g., "shard-001"

@dataclass
class ShardMap:
    logical_shards: Dict[str, ShardNode]
    hash_ring: List[str]  # Consistent hashing ring

class ShardRouter:
    def __init__(self, shard_map: ShardMap):
        self.shard_map = shard_map
        self.hash_ring = sorted(shard_map.hash_ring)
        self._pool_cache: Dict[str, asyncpg.Pool] = {}

    def _hash_key(self, shard_key: str) -> str:
        """Consistent hashing: 128-bit ring mapped to logical shards"""
        h = int(hashlib.md5(shard_key.encode()).hexdigest(), 16)
        # Find first node >= hash on ring
        for node in self.hash_ring:
            if int(node, 16) >= h:
                return node
        return self.hash_ring[0]  # Wrap around

    def get_logical_shard(self, shard_key: str) -> str:
        ring_key = self._hash_key(shard_key)
        # Map ring position to logical shard ID
        for logical_id, node in self.shard_map.logical_shards.items():
            if self._hash_key(node.logical_id) == ring_key:
                return logical_id
        raise ValueError("Shard resolution failed")

    async def get_connection(self, logical_shard_id: str) -> asyncpg.Connection:
        if logical_shard_id not in self._pool_cache:
            node = self.shard_map.logical_shards[logical_shard_id]
            self._pool_cache[logical_shard_id] = await asyncpg.create_pool(
                host=node.host,
                port=node.port,
                database=node.db_name,
                user=node.user,
                password=node.password,
                min_size=5,
                max_size=50,
                command_timeout=30.0
            )
        return await self._pool_cache[logical_sha

rd_id].acquire()

async def release_connection(self, logical_shard_id: str, conn: asyncpg.Connection):
    if logical_shard_id in self._pool_cache:
        await self._pool_cache[logical_shard_id].release(conn)

### 3. Shard-Aware Query Execution

The router isolates shard selection from business logic. Queries are executed against the correct physical node without cross-shard joins.

```python
# repo.py
class UserRepository:
    def __init__(self, router: ShardRouter):
        self.router = router

    async def get_user(self, user_id: str) -> Optional[dict]:
        shard_id = self.router.get_logical_shard(user_id)
        conn = await self.router.get_connection(shard_id)
        try:
            return await conn.fetchrow(
                "SELECT id, email, created_at FROM users WHERE id = $1", user_id
            )
        finally:
            await self.router.release_connection(shard_id, conn)

    async def create_user(self, user_id: str, email: str) -> dict:
        shard_id = self.router.get_logical_shard(user_id)
        conn = await self.router.get_connection(shard_id)
        try:
            return await conn.fetchrow(
                "INSERT INTO users (id, email) VALUES ($1, $2) RETURNING id, email, created_at",
                user_id, email
            )
        finally:
            await self.router.release_connection(shard_id, conn)

4. Rebalancing Strategy

At scale, static hashing causes uneven growth. Implement logical shard splitting with background data migration:

1. Detect hot shard via metrics (rows_per_shard, write_ops_per_shard).
2. Split logical shard into two new logical IDs.
3. Run background migration: COPY WHERE shard_key BETWEEN X AND Y.
4. Update directory service (e.g., etcd, Consul, or managed config store).
5. Route new writes to split shards; redirect reads during transition.
6. Decommission old logical shard after verification.

This approach ensures zero-downtime rebalancing and maintains query consistency.

---

Pitfall Guide

1. Hot Shards from Monotonic Keys

Symptom: Write latency spikes on specific shards; CPU/IOPS utilization uneven. Root Cause: Using created_at, auto_increment, or timestamp-based keys causes sequential writes to concentrate on a single shard. Mitigation: Hash the key (hash(user_id + created_at)), use composite keys, or implement time-bucketed sharding with round-robin distribution.

2. Cross-Shard Joins & Transactions

Symptom: Application crashes, inconsistent state, or 500ms+ latency for simple operations. Root Cause: Relational assumptions applied to partitioned data. JOINs and multi-row transactions span shard boundaries. Mitigation: Denormalize aggressively. Use application-side aggregation for reads. For transactions, confine them to a single shard. If cross-shard ACID is mandatory, evaluate distributed SQL (CockroachDB, Yugabyte) instead of traditional sharding.

3. Shard Key Evolution Without Migration

Symptom: New queries require a different partition key; existing data becomes unqueryable efficiently. Root Cause: Business logic changes outpace initial shard key design. No dual-write or backfill strategy exists. Mitigation: Design shard keys around immutable entity identifiers. If evolution is required, implement dual-write pipelines, background re-partitioning, and read-through fallbacks. Never change shard keys in production without a phased migration.

4. Connection Pool Exhaustion

Symptom: too many connections errors, latency spikes, thread starvation. Root Cause: Creating per-request connections or misconfigured pool sizes across N shards. Mitigation: Use connection pooling per logical shard (as shown in ShardRouter). Set max_size based on (max_concurrent_queries * avg_query_time) / target_latency. Monitor active_connections / max_connections ratio; alert at 70%.

5. Monitoring Blind Spots

Symptom: "Database is slow" without knowing which shard, which query, or which tenant. Root Cause: Aggregated metrics mask per-shard degradation. Lack of shard-aware tracing. Mitigation: Instrument every query with shard_id and shard_key tags. Use Prometheus/Grafana with per-shard dashboards. Implement OpenTelemetry spans that propagate shard context. Track P50/P95/P99 latency per shard, not just cluster averages.

6. Backup & Restore Complexity

Symptom: RTO/RPO targets missed; restore process takes days; point-in-time recovery fails. Root Cause: Treating shards as independent databases without coordinated snapshots or WAL streaming alignment. Mitigation: Use logical backups (pg_dump) for small shards; physical/base backups with consistent timestamps for large ones. Implement coordinated snapshotting via a control plane. Test restores quarterly. Maintain a shard inventory with backup timestamps and retention policies.

---

Production Bundle

✅ Sharding Readiness Checklist

Phase	Item	Status
Pre-Sharding	Shard key validated against 80%+ query patterns	☐
	Data distribution simulated with production-like dataset	☐
	Cross-shard dependencies identified and eliminated	☐
	Connection pool sizing calculated per shard	☐
Implementation	Logical-to-physical mapping stored in versioned config	☐
	Routing layer idempotent and cache-aware	☐
	Background migration tooling tested (dry-run + rollback)	☐
	Observability: per-shard metrics, tracing, alerting	☐
Operations	Runbook for shard failure, rebalancing, and backup	☐
	Chaos testing performed (node loss, network partition)	☐
	Capacity planning: shard count projected 12-18 months	☐
	Security: IAM roles, encryption at rest, VPC isolation per shard	☐

🧭 Decision Matrix: When to Shard vs. Alternatives

Criteria	Traditional Sharding	Distributed SQL (Cockroach/Yugabyte)	Read Replicas + Caching	Vertical Scaling
Write Scale > 50k QPS	✅ Linear scaling	✅ Built-in horizontal writes	❌ Limited by single primary	❌ Hardware ceiling
Cross-Shard ACID Required	❌ Application-level only	✅ Strong consistency	❌ Eventual at best	✅ Native
Operational Complexity Tolerance	High (custom routing, rebalancing)	Medium (managed control plane)	Low	Low
Data Sovereignty / Compliance	✅ Shard placement per region	✅ Multi-region deployment	❌ Single region primary	❌ Single instance
Team Expertise	Distributed systems, DB internals	Distributed consensus, SQL dialects	Standard DBA skills	Standard DBA skills
Cost at 10TB+	✅ Commodity instances	⚠️ Higher licensing/infra	❌ Cache miss penalty	❌ Exponential

Rule of Thumb: Use traditional sharding when you need fine-grained control, multi-region compliance, or have existing relational workloads. Choose distributed SQL when cross-shard transactions and strong consistency are non-negotiable.

⚙️ Config Template (YAML)

sharding:
  version: 2.1
  strategy: consistent_hash_logical
  hash_function: md5_128
  
  logical_shards:
    shard-001:
      physical_node: db-primary-us-east-1
      port: 5432
      database: app_shard_001
      max_connections: 50
      tags: ["region:us-east", "tier:hot"]
    shard-002:
      physical_node: db-primary-us-west-2
      port: 5432
      database: app_shard_002
      max_connections: 50
      tags: ["region:us-west", "tier:hot"]
      
  routing:
    cache_ttl_seconds: 300
    fallback_strategy: redirect_to_directory
    health_check_interval_seconds: 10
    circuit_breaker:
      failure_threshold: 5
      reset_timeout_seconds: 60
      
  rebalancing:
    enabled: true
    trigger_metrics: ["write_ops_per_shard", "row_count_variance"]
    threshold_percent: 25
    migration_batch_size: 10000
    dry_run_mode: false
    
  observability:
    metrics_prefix: "sharding.db"
    trace_propagation: true
    alerting:
      - metric: "sharding.db.active_connections.ratio"
        threshold: 0.7
        severity: warning
      - metric: "sharding.db.query_latency.p99"
        threshold: 150ms
        severity: critical

🚀 Quick Start: 5-Step Implementation

1. Audit & Model: Extract top 50 read/write queries. Identify candidate shard keys. Validate cardinality and access patterns using query logs or synthetic load testing.
2. Deploy Routing Layer: Implement ShardRouter (or equivalent) in your application. Replace direct DB connections with shard-aware pool acquisition. Add shard_id to all logs and metrics.
3. Initialize Physical Shards: Provision N identical database instances. Apply schema migrations in parallel. Seed initial data using hash-based distribution scripts.
4. Enable Directory Service: Store logical-to-physical mapping in a versioned config store (etcd, Consul, or S3 + Lambda). Implement hot-reload for routing updates.
5. Validate & Cutover: Run shadow traffic against sharded layer. Compare results with monolithic DB. Verify P99 latency, error rates, and data consistency. Switch traffic via canary deployment. Monitor rebalancing triggers and connection pools for 72 hours.

---

Closing Notes

Database sharding at scale is not a database feature; it is an architectural contract. It demands discipline in data modeling, precision in routing, and rigor in operations. The systems that succeed treat sharding as a continuous platform capability: logical abstractions over physical infrastructure, automated rebalancing, shard-aware observability, and runbooks that assume failure.

Start with a clear shard key, isolate cross-shard dependencies, instrument everything, and automate rebalancing before you need it. When executed correctly, sharding transforms data scale from a liability into a competitive moat.