Sharding (Horizontal Partitioning) Concepts

When a relational database table grows to hundreds of millions of rows, and write volume exceeds 50,000 writes per second, even the largest enterprise cloud instance (e.g. AWS r6i.32xlarge with 128 vCPUs and 1TB RAM) runs out of CPU cycles, buffer pool memory, and disk IOPS.

Sharding (Horizontal Partitioning) is the architectural technique of dividing a monolithic dataset horizontally into smaller, independent physical database instances called Shards:

• Each shard runs on a completely separate physical server (or cloud VM) with its own dedicated CPU, RAM, and disk storage.
• Together, all shards form a single logical database.
• Throughput Impact: If 1 database node handles 20,000 writes/sec, sharding across 10 nodes scales the cluster to 200,000 writes/sec (Linear Horizontal Scaling).

The Shard Key is the specific column (or combination of columns) used by the routing layer to determine which physical shard holds a given row.

Characteristics of an Ideal Shard Key:

1. High Cardinality: The key must have millions of distinct values (e.g., user_id, tenant_id, account_id) to distribute rows evenly across thousands of shards.
2. Even Distribution (No Hotspots): A poor shard key like country_code will dump 60% of all data onto the 'US' shard, causing that single server to crash while other shards sit idle.
3. Query Colocation: Choose a shard key that aligns with the application's most frequent read and write queries, ensuring queries can be satisfied by routing to a single physical shard.

While sharding provides near-infinite write scalability, it introduces severe distributed systems complexities:

1. Cross-Shard Queries (Scatter-Gather):
   • If a query filters by the Shard Key (WHERE user_id = 42), the router directs the query to 1 specific shard (Fast!).
   • If a query filters on a non-shard key (WHERE status = 'PAID'), the router must execute a Scatter-Gather Broadcast: send the query to all 50 shards simultaneously, collect all 50 response streams over the network, merge the rows in memory, sort, and paginate.
2. Loss of ACID Multi-Table JOINs: You cannot execute SQL JOINs between two tables if their rows live on different physical shard servers.
3. Loss of Global Referential Integrity: Foreign key constraints cannot cross physical database server boundaries.
4. Resharding Complexity: When adding 10 new shards to a live production cluster, rebalancing terabytes of data without downtime requires complex online data migration tooling (Vitess, Citus).

-- Convert standard PostgreSQL table into a distributed sharded table
CREATE TABLE orders (
    order_id UUID,
    user_id BIGINT, -- SHARD KEY (Distribution Column)
    total_amount DECIMAL(10,2),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (user_id, order_id)
);

-- Distribute table across 16 worker nodes hashed by user_id
SELECT create_distributed_table('orders', 'user_id');