Database Sharding Architecture

## ROLE
You are a distributed systems architect who has designed sharding strategies for databases handling petabytes of data and millions of queries per second. You understand the trade-offs of different sharding approaches and have solved the hardest problems in distributed data management.

## OBJECTIVE
Design a sharding architecture for [APPLICATION]'s [DATABASE] that currently has [DATA SIZE] and needs to scale to [TARGET SCALE] while maintaining [LATENCY REQUIREMENT] query latency.

## TASK

### Sharding Strategy Selection
- Range-based sharding: partition by ranges of shard key values (good for sequential access)
- Hash-based sharding: consistent hashing for even distribution (good for random access)
- Directory-based sharding: lookup table mapping entities to shards (most flexible)
- Geographic sharding: partition by region for data locality and compliance
- Hybrid: combine strategies for different tables/access patterns

### Shard Key Selection
- Cardinality: key must have enough unique values to distribute evenly across shards
- Distribution: avoid hot shards — analyze data distribution before choosing
- Query isolation: most queries should hit a single shard, not scatter across all shards
- Growth pattern: key must support future data growth without requiring re-sharding
- Immutability: shard key should not change after record creation
- Candidates analysis: evaluate 3-5 potential shard keys with pros/cons for each

### Routing Layer
- Application-level routing: shard-aware application code that directs queries
- Proxy-based routing: middleware (Vitess, ProxySQL, Citus) that handles routing transparently
- Client library: SDK that encapsulates routing logic for application developers
- Connection management: connection pools per shard, health checking, failover
- Routing cache: cache shard mapping to avoid lookup overhead on every query

### Cross-Shard Operations
- Cross-shard queries: scatter-gather pattern with result merging
- Cross-shard joins: denormalize to avoid, or use application-level joining
- Cross-shard transactions: two-phase commit, saga pattern, or eventual consistency
- Cross-shard aggregations: pre-computed aggregations or parallel aggregation with merge
- Global tables: reference data replicated to all shards for local joins

### Data Rebalancing
- Shard splitting: dividing an overloaded shard into two smaller shards
- Shard merging: combining underutilized shards to reduce operational overhead
- Online rebalancing: moving data between shards without downtime
- Backfill strategy: copying data to new shard while keeping source updated
- Cutover coordination: atomically switching routing to include new shard

### Operational Considerations
- Schema changes: how to apply DDL changes across all shards consistently
- Backup and restore: per-shard backups with coordinated point-in-time recovery
- Monitoring: per-shard metrics, imbalance detection, hot shard alerting
- Failure handling: what happens when a single shard goes down
- Testing: how to test sharding logic without a full production-scale environment

## OUTPUT FORMAT
Sharding architecture document with strategy selection, shard key analysis, routing design, cross-shard handling, and operational runbook.

## CONSTRAINTS
- Sharding must be transparent to most application code — minimize shard-awareness requirements
- Design must support adding new shards without data redistribution
- Cross-shard queries must not exceed [MAX LATENCY] latency
- Include capacity planning: when to add shards based on data volume and query load
- Plan for the eventual need to re-shard if initial key choice proves suboptimal