Database Scaling Strategy

## ROLE
You are a database architect who has scaled databases from thousands to billions of rows while maintaining sub-100ms query performance.

## OBJECTIVE
Design a database scaling strategy for [APPLICATION] currently at [CURRENT SIZE] growing to [TARGET SIZE] over [TIMEFRAME].

## TASK

### Current State Assessment
- Query patterns: read-heavy vs write-heavy, OLTP vs OLAP
- Hot spots: which tables, queries, and access patterns cause bottlenecks
- Data growth: rate of growth, data lifecycle, retention requirements
- Current performance: p50, p95, p99 query latencies
- Infrastructure: current database type, size, configuration

### Vertical Scaling (Quick Wins)
- Hardware upgrades: CPU, memory, storage IOPS
- Query optimization: EXPLAIN ANALYZE, index optimization, query rewriting
- Connection pooling: PgBouncer, ProxySQL
- Configuration tuning: buffer pool, work memory, connection limits
- Archival: move old data to cold storage

### Read Scaling
- Read replicas: route reads to replicas, writes to primary
- Replication lag: monitor and handle stale reads
- Caching: Redis/Memcached for frequently accessed data
- Cache invalidation: time-based, event-based, write-through
- CDN: cache API responses at the edge for global latency

### Write Scaling
- Sharding: distribute data across multiple database instances
- Shard key selection: high cardinality, even distribution, query alignment
- Sharding strategies: range, hash, directory-based
- Cross-shard queries: how to handle joins and aggregations
- Resharding: strategy for adding shards without downtime

### Data Partitioning
- Table partitioning: range (time-based), list, hash
- Partition pruning: queries only scan relevant partitions
- Partition maintenance: creating, dropping, archiving partitions
- Hybrid approach: partition within shards for maximum scalability

### Migration Path
- Phase 1: optimize current setup (indexing, caching, config)
- Phase 2: read replicas for read scaling
- Phase 3: application-level sharding or managed distributed database
- Phase 4: specialized databases for specific workloads (time-series, graph, search)
- Zero-downtime migration: dual-write, shadow reads, gradual cutover

## OUTPUT FORMAT
Scaling strategy document with current analysis, phased approach, technical implementation guides, and decision criteria for each phase.

## CONSTRAINTS
- Avoid premature optimization: scale only what's actually bottlenecked
- Data integrity is non-negotiable: no data loss during migrations
- Include cost analysis: infrastructure vs engineering time
- Plan for rollback at every phase
- Consider managed vs self-hosted trade-offs