Database Design & Scaling
Your application is growing. Reads are slow. Writes are backing up. Your single database can't keep up. Time to scale.
SQL vs NoSQL: The Trade-offs
SQL (PostgreSQL, MySQL)
- ACID transactions
- Complex JOINs and queries
- Fixed schema
- Vertical scaling primarily
- Strong consistency
NoSQL (MongoDB, Cassandra)
- Flexible schema
- Horizontal scaling built-in
- High throughput
- Eventual consistency (often)
- No complex JOINs
When to Use What
| Use Case | Choice | Why |
|---|---|---|
| Banking / Finance | SQL | ACID transactions critical |
| Social media posts | NoSQL | High volume, flexible schema |
| E-commerce inventory | SQL | Strong consistency needed |
| IoT sensor data | NoSQL (time-series) | High write throughput |
Replication: Scaling Reads
Create copies of your database. Writes go to the primary (master), reads go to replicas.
- Scale reads horizontally
- Fault tolerance (promote replica if primary fails)
- Geographic distribution
Sharding: Scaling Writes
Split your data across multiple databases. Each shard holds a subset of the data.
Shard Key Selection
The shard key determines which shard stores which data.
# Simple approach: modulo
shard_id = hash(user_id) % num_shards- user_id (for user-centric data)
- geographic region
- time-based (for time-series)
- Anything that creates hotspots (e.g., celebrity user IDs)
Consistent Hashing
The problem with key % num_shards:
Before: 3 shards → key 10 → shard 1 (10 % 3 = 1)
After: 4 shards → key 10 → shard 2 (10 % 4 = 2)Adding one shard remaps almost all keys. Massive data migration!
Consistent hashing solves this by only remapping ~1/N keys when adding/removing a shard.Real-World: Uber's Schemaless
In 2014, Uber was running out of database space due to trip growth. They built Schemaless:
- Key-value store built on sharded MySQL
- Append-only writes with buffered updates
- Migrated from Python to Go: 85% latency reduction
Real-World: Twitter's Vitess Adoption
Twitter scaled their users database with Vitess:
Migrated from Gizzard (old MySQL framework) to Vitess to handle strict SLOs on QPS, latency, success rates, and cross data center consistency.
Key Takeaways
- SQL for ACID, NoSQL for scale and flexibility
- Replication scales reads but not writes
- Sharding scales both reads and writes by partitioning data
- Consistent hashing minimizes data migration when scaling
Next up: CAP Theorem - Understanding the fundamental trade-offs in distributed systems.