Recap — Sharding Strategies and Trade-offs

# Sharding Strategies and Trade-offs Sharding (horizontal partitioning) splits a table across multiple databases. Each shard holds a subset of rows. Necessary at scale; expensive in complexity. ## When sharding becomes necessary Signs: - Table is huge (100M+ rows) and growing. - Single DB can't keep up with reads/writes. - Backups take too long. - Replicas can't handle full dataset. Common alternatives to try first: - Vertical scaling (bigger DB box). - Read replicas (scale reads). - Caching (reduce DB load). - Archiving old data (smaller hot set). Sharding is the answer when these aren't enough. ## Sharding strategies ### 1. Range-based sharding Split by ranges of the shard key. ``` Shard 1: user_id 1 to 1M Shard 2: user_id 1M to 2M Shard 3: user_id 2M to 3M ``` Pros: simple; range queries efficient. Cons: hotspots if data is unevenly distributed (recent users get all activity). ### 2. Hash-based sharding Hash the shard key; modulo by shard count. ``` shard = hash(user_id) % N ``` Pros: even distribution; no hotspots. Cons: range queries scan all shards; rebalancing on shard count change is expensive (naive hash → consistent hashing solves). ### 3. Geographic sharding Split by user location. ``` Shard US-East: US east users Shard EU: European users Shard ASIA: Asian users ``` Pros: data near users (low latency); compliance (data residency). Cons: imbalance (some regions have more users); cross-region queries painful. ### 4. Directory-based sharding (lookup table) Separate service maps each key to a shard. ``` key → directory service → shard ``` Pros: flexible rebalancing; logical structure. Cons: extra service to maintain; directory itself can be a bottleneck. ## Choosing a shard key The most important decision. Bad choice = pain forever. Good shard keys: - **High cardinality** (many distinct values). - **Evenly distributed** (no skew). - **Aligned with query patterns** (queries can be answered by hitting few shards). Bad shard keys: - **Low cardinality** (boolean status, country). Few shards; uneven load. - **Skewed distribution** (timestamp; new data overloads "current" shard). - **Misaligned with queries** (sharding by user_id but querying by order_id). Example for an e-commerce DB: - Sharding by user_id: queries for a user's orders hit one shard ✓. Queries by order_id hit all shards ✗. - Sharding by order_id: opposite trade-off. - Multi-tenant SaaS often shards by tenant_id (queries scoped per-tenant naturally). ## Operational pain ### Cross-shard queries If a query needs data from multiple shards, you either: - Fan out: query all shards, merge results in app. - Denormalize: duplicate data so queries fit one shard. - Maintain a separate index (e.g., search service). Cross-shard JOINs are slow or impossible. Aggregations (COUNT, SUM) require fan-out. ### Transactions ACID transactions across shards = distributed transactions = slow and fragile. Options: - Avoid cross-shard transactions. Design data layout to keep related data together. - Two-phase commit (2PC) — works but complex; performance hit. - Saga pattern — compensating actions across services; eventual consistency. Most pragmatic: design schema so transactions are within one shard. ### Rebalancing Adding/removing shards: - Naive hash modulo: every key reshuffles. Painful. - Consistent hashing: only affected shard's neighbors rebalance. - Range-based: split a range; migrate one shard's worth. Plan rebalancing capacity upfront; it's a slow process at scale. ### Schema migrations Each shard must migrate. Tools (Vitess, Yugabyte) coordinate; manually it's painful. ## Sharding vs partitioning Often used interchangeably. Strict use: - **Partitioning**: dividing data within ONE database (e.g., Postgres native partitioning by date). - **Sharding**: dividing data across MULTIPLE databases. Partitioning is simpler (one DB, query planner handles cross-partition). Sharding is harder (multiple DBs, app handles routing). ## NoSQL and built-in sharding Cassandra, DynamoDB, MongoDB: sharding is automatic / built-in. Specify a partition key; system handles routing. SQL: traditionally manual. Newer tools: - **Vitess** for MySQL — Slack, GitHub use it. - **Citus** for Postgres — extension that adds sharding. - **CockroachDB / Yugabyte** — distributed SQL with built-in sharding. ## Common sharding mistakes 1. **Sharding too early.** Operational cost massive; vertical + caching + replicas first. 2. **Wrong shard key.** Causes hotspots and rebalancing pain. 3. **Cross-shard queries everywhere.** Application slow; design to colocate. 4. **No plan for rebalancing.** Migrations become emergency operations. 5. **Mixing sharded and unsharded code paths.** Hard to reason about. ## Takeaway Sharding = split one DB into many. Strategies: range, hash, geo, directory. Choose shard key by cardinality, distribution, query alignment. Cross-shard queries and transactions are painful — design to avoid. NoSQL has built-in sharding; S