Sharding & Distributed SQL

-- SHARDING: splitting data across MULTIPLE database servers
-- vs PARTITIONING: splitting within a SINGLE server

-- Why shard?
-- • Single server can't handle the write load
-- • Dataset exceeds one server's storage
-- • Need geographic distribution for latency

-- Common shard key strategies:

-- 1. HASH-BASED SHARDING
-- shard_id = hash(user_id) % num_shards
-- Pros: even distribution
-- Cons: cross-shard queries, resharding is painful

-- Example: 4 database servers
-- User 101 → hash(101) 
...

-- Choosing a shard key is the MOST important decision

-- ✅ GOOD shard keys:
-- • user_id (most queries are per-user)
-- • tenant_id (multi-tenant SaaS)
-- • region (geographic locality)

-- ❌ BAD shard keys:
-- • created_at (all new writes hit one shard)
-- • auto-increment ID (sequential = hotspot)
-- • status (low cardinality, uneven distribution)

-- The "co-location" principle:
-- Data that's queried together should live together

-- Example: E-commerce
-- Shard by customer_id:
-- ✅ Orders
...

-- Vitess: MySQL sharding middleware (used by YouTube, Slack)
-- Sits between your app and MySQL, handles routing

-- VSchema defines how tables are sharded:
-- {
--   "sharded": true,
--   "vindexes": {
--     "hash": { "type": "hash" }
--   },
--   "tables": {
--     "users": {
--       "column_vindexes": [{
--         "column": "id",
--         "name": "hash"
--       }]
--     },
--     "orders": {
--       "column_vindexes": [{
--         "column": "user_id",
--         "name": "hash"
--   
...

-- CockroachDB / YugabyteDB: Distributed SQL
-- PostgreSQL-compatible, auto-sharding, auto-replication

-- Create a table — CockroachDB handles distribution
CREATE TABLE products (
    id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    category VARCHAR(50),
    price DECIMAL(10,2),
    region VARCHAR(20)
);

-- Data is automatically split into "ranges" (~512MB each)
-- and distributed across nodes

-- Pin data to specific regions for compliance:
ALTER TABLE produc
...

-- Real-world sharding challenges and solutions

-- CHALLENGE 1: Resharding (adding more shards)
-- Problem: hash(id) % 4 ≠ hash(id) % 8
-- Solution A: Consistent hashing (minimize data movement)
-- Solution B: Double-write during migration
-- Solution C: Use Vitess/CockroachDB (automatic)

-- CHALLENGE 2: Cross-shard JOINs
-- Problem: orders on shard A, products on shard B
-- Solutions:
-- A) Denormalize: copy product_name into orders table
-- B) Reference tables: small tables replicated to all
...