p<string, Pool> = new Map();

constructor(shards: ShardNode[]) {
    shards.forEach(shard => this.addShard(shard));
}

private addShard(shard: ShardNode): void {
    const vNodes = shard.virtualNodes || 150;
    for (let i = 0; i < vNodes; i++) {
        const hash = this.hash(`${shard.id}:vnode:${i}`);
        this.ring.set(hash, shard);
    }
    this.pools.set(shard.id, new Pool(shard.config));
}

private hash(key: string): number {
    return parseInt(createHash('md5').update(key).digest('hex').slice(0, 8), 16);
}

getShard(shardKey: string): ShardNode {
    const keyHash = this.hash(shardKey);
    const sortedKeys = Array.from(this.ring.keys()).sort((a, b) => a - b);
    
    for (const ringKey of sortedKeys) {
        if (ringKey >= keyHash) {
            return this.ring.get(ringKey)!;
        }
    }
    return this.ring.get(sortedKeys[0])!; // Wrap around
}

async executeQuery(shardKey: string, query: string, params?: any[]): Promise<any> {
    const shard = this.getShard(shardKey);
    const pool = this.pools.get(shard.id)!;
    return pool.query(query, params);
}

}

// Usage const shards: ShardNode[] = [ { id: 'shard-01', config: { host: 'db-01.internal', database: 'app' }, virtualNodes: 150 }, { id: 'shard-02', config: { host: 'db-02.internal', database: 'app' }, virtualNodes: 150 }, { id: 'shard-03', config: { host: 'db-03.internal', database: 'app' }, virtualNodes: 150 }, ];

const router = new ConsistentHashRouter(shards);

// Route query to specific shard await router.executeQuery('user_12345', 'SELECT * FROM users WHERE id = $1', ['user_12345']);

#### 5. Resharding Strategy
Static sharding fails under growth. Implement dynamic resharding:
*   **Virtual Buckets:** Divide data into fine-grained buckets. Moving a bucket between shards requires less data movement than moving entire ranges.
*   **Background Migration:** Use a background job to copy data to the new shard, verify integrity, and then update routing metadata. Implement dual-write during migration to maintain consistency.
*   **Consistent Hashing:** Reduces data movement to approximately `1/K` of the dataset when adding `K` shards, compared to `50%` with naive modulo sharding.

## Pitfall Guide

### 1. Sequential Shard Keys
Using auto-incrementing IDs or timestamps as shard keys creates a "hot tail" shard where all new writes concentrate. This negates the benefits of sharding and causes write bottlenecks.
*   **Fix:** Use hash-based keys, UUIDs, or composite keys that distribute writes. For time-series data, combine timestamp with a high-cardinality dimension (e.g., `tenant_id`).

### 2. Cross-Shard Joins
Sharding breaks relational joins. Joining data across shards requires fetching all relevant rows to the application layer and performing the join in memory, which is inefficient and memory-intensive.
*   **Fix:** Denormalize data. Embed related data in the same shard or use a separate search index (e.g., Elasticsearch) for complex queries. Restrict joins to co-located data.

### 3. Global Unique Constraints
Enforcing uniqueness across shards is non-trivial. Local unique constraints only guarantee uniqueness within a shard.
*   **Fix:** Use a centralized sequence generator (e.g., Snowflake IDs) or a distributed lock service for uniqueness checks. Alternatively, accept eventual consistency for non-critical unique constraints.

### 4. Connection Pool Exhaustion
Sharding multiplies the number of database connections required. A connection pool per shard can quickly exhaust the database connection limit.
*   **Fix:** Implement a global connection pool manager or use a proxy that multiplexes connections. Monitor connection usage per shard and implement backpressure.

### 5. Schema Migration Complexity
Running `ALTER TABLE` across multiple shards requires coordination. A failed migration on one shard leaves the schema inconsistent.
*   **Fix:** Use migration tools that support distributed transactions or implement backward-compatible schema changes. Migrate shards sequentially with verification steps.

### 6. Ignoring Secondary Index Skew
A well-chosen primary shard key may result in skewed secondary indexes. For example, sharding by `user_id` may cause uneven distribution of `email` lookups if certain domains are popular.
*   **Fix:** Create global secondary indexes stored in a separate service or use a directory-based approach for secondary lookups. Accept that some queries will be scatter-gather.

### 7. Backup and Recovery Blind Spots
Standard backup procedures may not account for sharding topology. Restoring a single shard without context can lead to data inconsistency.
*   **Fix:** Implement shard-aware backups that capture metadata alongside data. Use consistent snapshots across shards for point-in-time recovery. Test restore procedures per shard.

## Production Bundle

### Action Checklist

- [ ] **Define Shard Key:** Validate cardinality and alignment with 90% of query patterns. Ensure immutability.
- [ ] **Select Strategy:** Choose Hash for writes/lookups, Range for time-series, or Directory for multi-tenant isolation.
- [ ] **Implement Virtual Nodes:** Configure at least 100 virtual nodes per shard to minimize data skew during rebalancing.
- [ ] **Abstract Routing:** Isolate sharding logic behind a repository or router interface to decouple from business logic.
- [ ] **Handle Cross-Shard:** Identify all cross-shard queries and refactor to co-located data or async aggregation.
- [ ] **Plan Resharding:** Implement background migration jobs and metadata versioning for dynamic scaling.
- [ ] **Monitor Skew:** Track data distribution and query latency per shard. Alert on skew > 15%.
- [ ] **Secure Metadata:** Encrypt shard routing metadata and restrict access to prevent data leakage.

### Decision Matrix

| Scenario | Recommended Approach | Why | Cost Impact |
|----------|---------------------|-----|-------------|
| **High Write Throughput, Point Lookups** | Hash Sharding with Virtual Nodes | Uniform distribution maximizes write capacity | Medium (Additional routing logic) |
| **Time-Series Data, Range Scans** | Range Sharding by Time | Localized data enables efficient range queries | Low (Simple implementation) |
| **Multi-Tenant SaaS** | Tenant-ID Directory Sharding | Data isolation and dynamic rebalancing per tenant | High (Metadata management) |
| **Rapid Growth, Unknown Patterns** | Consistent Hashing with Proxy | Minimizes rebalancing overhead; proxy abstracts complexity | High (Proxy infrastructure) |
| **Legacy Monolith Migration** | Read-Through Cache Sharding | Reduces write load; gradual migration path | Medium (Cache invalidation complexity) |

### Configuration Template

Use this YAML template for configuring a sharding proxy or application router.

```yaml
sharding:
  enabled: true
  strategy: consistent_hash
  key: user_id
  hash_algorithm: murmur3
  
  virtual_nodes:
    count: 200
    distribution: uniform
  
  shards:
    - id: shard-east-1
      host: db-east-1.internal
      port: 5432
      weight: 1.0
      region: us-east-1
      
    - id: shard-west-1
      host: db-west-1.internal
      port: 5432
      weight: 1.0
      region: us-west-2
      
    - id: shard-eu-1
      host: db-eu-1.internal
      port: 5432
      weight: 1.0
      region: eu-central-1

  routing:
    type: proxy
    endpoint: sharding-proxy.internal:8080
    timeout_ms: 500
    retry_policy:
      max_retries: 3
      backoff: exponential
      
  monitoring:
    metrics:
      - shard_key_distribution
      - query_latency_per_shard
      - connection_pool_usage
    alerting:
      skew_threshold: 0.15
      latency_p99_threshold_ms: 200

Quick Start Guide

1. Initialize Router: Install a sharding library or create a router instance.

   npm install pg
   

   import { ShardRouter } from './shard-router';
   const router = new ShardRouter([
     { id: 's1', host: 'localhost:5432', db: 'shard1' },
     { id: 's2', host: 'localhost:5433', db: 'shard2' }
   ]);
   

2. Define Schema: Create identical schemas on each shard. Ensure tables use the shard key as part of the primary key or unique constraints.

   -- Run on each shard
   CREATE TABLE users (
     id UUID PRIMARY KEY,
     tenant_id TEXT NOT NULL,
     data JSONB,
     CONSTRAINT unique_tenant_user UNIQUE (tenant_id, id)
   );
   

3. Insert Data: Use the router to execute writes. The router determines the target shard.

   await router.execute('user_abc', 
     'INSERT INTO users (id, tenant_id, data) VALUES ($1, $2, $3)', 
     ['user_abc', 'tenant_01', '{"name": "Alice"}']
   );
   

4. Query Data: Route reads using the shard key. For queries without the shard key, implement a scatter-gather fallback or use a secondary index.

   const result = await router.execute('user_abc', 
     'SELECT * FROM users WHERE id = $1', 
     ['user_abc']
   );
   console.log(result.rows);
   

5. Verify Distribution: Check data distribution across shards.

   -- Run on each shard
   SELECT count(*) FROM users;
   

   Ensure counts are within 10% variance. Adjust virtual node configuration if skew is detected.