Designing Database Scaling Patterns
1. Designing Horizontal Database Scaling
| Technique | Description |
|---|---|
| Read replicas | Scale read throughput |
| Sharding | Scale writes + storage |
| Federation | Split DBs by function |
| Caching tier | Offload hot reads |
| CQRS | Separate write/read models |
2. Designing Vertical Database Scaling
| Tunable | Notes |
|---|---|
| CPU cores | Helps query parallelism, connection load |
| RAM | Buffer pool / page cache; aim cache > working set |
| Storage | NVMe vs SSD; high IOPS for OLTP |
| Network | 10/25 GbE for replication |
3. Designing Database Sharding Strategy
| Decision | Recommendation |
|---|---|
| Shard key | Tenant_id, user_id (high cardinality, even) |
| Shard count | Power-of-2; over-provision (e.g., 1024 logical) |
| Routing | Smart client or proxy (Vitess/Citus/PgBouncer) |
| Cross-shard txn | Avoid; use Saga or 2PC sparingly |
4. Designing Consistent Hashing for Sharding
0 ────────────────── 2^32-1 (hash ring)
A B C D
key → hash → first node clockwise
Add node E → only ~1/N keys move
| Concept | Detail |
|---|---|
| Virtual nodes (vnodes) | Each physical node owns many points → balance |
| Replication | Walk N nodes clockwise from key |
| Used in | Cassandra, DynamoDB, Memcached clients |
5. Designing Cross-Shard Query Strategy
| Pattern | Description |
|---|---|
| Scatter-gather | Query all shards, merge in coordinator |
| Broadcast tables | Replicate small reference data to all shards |
| Co-located shards | Same shard key for joined tables |
| Materialized aggregates | Async-built rollup tables |
6. Designing Shard Rebalancing
Online Resharding
- Add new shard, mark as draining target
- Dual-write to old + new for moving keys
- Backfill historical data
- Cutover reads after verifying parity
- Stop writes to old shard, decommission
7. Designing Federation Pattern
| Element | Description |
|---|---|
| Function-based split | Users DB, Orders DB, Inventory DB |
| Pros | Smaller DBs, separate scaling, isolated failure |
| Cons | No cross-DB joins/transactions |
| Integration | API calls, events, replicated read views |
8. Designing Database Proxy Layer
| Capability | Examples |
|---|---|
| Connection pooling | PgBouncer, RDS Proxy |
| Read/write split | ProxySQL, MaxScale |
| Sharding router | Vitess, Citus |
| Query rewriting | Caching, masking PII |
| Failover management | Auto-redirect to new primary |
9. Designing Write Amplification Solutions
| Cause | Mitigation |
|---|---|
| LSM compaction | Tune levels, leveled vs tiered |
| Many indexes | Drop unused; partial indexes |
| Wide rows in column store | Smaller batches; column projection |
| Replication factor | Right-size; semi-sync |
10. Designing Hot Spot Prevention
| Hot Spot | Solution |
|---|---|
| Sequential keys (time, autoinc) | Prefix with hash / random salt |
| Power user / tenant | Sub-shard the hot tenant |
| Hot read row | Cache, replicas, request coalescing |
| Hot write row | Sharded counters, write batching |