tion

1. Shard Key Selection

The shard key is the most critical decision. It must satisfy three criteria:

• High Cardinality: The key must have sufficient unique values to distribute data evenly. Low cardinality keys (e.g., status, region) create immediate hotspots.
• Access Pattern Alignment: The key should match the primary query pattern. If 90% of queries filter by tenant_id, tenant_id is the optimal shard key.
• Immutability: Shard keys should rarely change. Updating a shard key requires data migration across shards, which is expensive and risky.

2. Sharding Strategy Implementation

Choose the strategy based on workload characteristics:

• Hash Sharding: Use CRC32 or MurmurHash3 for deterministic mapping. Apply modulo arithmetic or consistent hashing to map hash values to shard IDs.
• Range Sharding: Divide the key space into contiguous ranges. Suitable for time-series data where queries are bounded by time. Requires proactive range splitting to prevent tail hotspots.
• Directory Sharding: Maintain a lookup table mapping shard keys to shard locations. Allows flexible data placement and easy rebalancing but requires a highly available metadata store.

3. Routing Architecture

Implement routing via one of two patterns:

• Client-Side Routing: The application logic determines the shard and opens a connection. Reduces latency by eliminating a proxy hop but couples the application to the sharding topology.
• Proxy-Based Routing: A middleware layer (e.g., Vitess, Citus, ProxySQL) handles routing. Decouples the application from topology changes but adds latency and operational complexity.

Rationale: For greenfield projects with complex cross-shard requirements, proxy-based routing is recommended. For latency-sensitive microservices with simple access patterns, client-side routing minimizes overhead.

4. Code Implementation: Shard Router

The following TypeScript example demonstrates a client-side shard router using consistent hashing with virtual nodes.

import { createHash } from 'crypto';
import { Pool, PoolConfig } from 'pg';

interface ShardNode {
    id: string;
    config: PoolConfig;
    virtualNodes: number;
}

class ConsistentHashRouter {
    private ring: Map<number, ShardNode> = new Map();
    private pools: Map<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.

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.