HLD · Data
Database Scaling
SQL vs NoSQL, replication, sharding, and when each pattern applies.
1. Vertical vs horizontal scaling
- Vertical (scale up): Bigger machine — simpler, hits hardware ceiling.
- Horizontal (scale out): More machines — required at large scale; adds complexity.
Example: Postgres on 64GB RAM handles 5k TPS. At 50k TPS you need read replicas + sharding or specialized stores.
2. SQL vs NoSQL (interview framework)
| SQL (Postgres, MySQL) | NoSQL |
|---|---|
| ACID transactions, joins, fixed schema | Flexible schema; optimized for access patterns |
| Strong consistency (single node) | Often eventual consistency at scale |
| Orders, payments, inventory | Feeds, sessions, metrics, document stores |
NoSQL types: Document (MongoDB), Key-value (DynamoDB/Redis), Wide-column (Cassandra), Graph (Neo4j).
Rule: Start SQL unless you have a clear reason (massive write throughput, flexible schema, geo-distribution).
3. Read replicas
Primary handles writes; one or more replicas stream WAL/binlog and serve reads.
- Replication lag: User posts tweet, refresh may not show it for 100ms — acceptable for many apps.
- Routing: Writes → primary; reads → replica (or sticky session after write).
Example: Amazon product catalog — 100:1 read:write ratio. 1 primary + 5 read replicas.
4. Sharding (partitioning)
Split data across multiple DB instances by shard key (e.g., user_id % 16).
- Pros: Write throughput scales; each shard smaller, faster.
- Cons: Cross-shard joins expensive; resharding painful; hot shards.
Good shard keys: High cardinality, even distribution (user_id). Bad: country_code (India shard overload).
5. Indexing essentials
B-tree index on user_id turns full table scan O(n) into O(log n) lookup.
- Index columns in WHERE, JOIN, ORDER BY
- Composite index:
(user_id, created_at)for "user's recent posts" - Over-indexing slows writes — balance read vs write cost
6. Federation vs sharding
Federation: Split by feature (users DB, products DB) — simpler ops, each DB can scale independently.
Sharding: Split same table by key — for single massive table (messages, events).
7. Example: messaging app storage
- Start: Single Postgres with index on
(conversation_id, sent_at) - 10M users: Read replicas for chat history loads
- 1B messages: Shard by
conversation_idhash across 32 Cassandra nodes - Recent messages hot in Redis; archive old to S3
8. CAP reminder at DB layer
Single-node SQL = CP. Multi-region async replica = AP with eventual consistency. Pick consciously (see CAP note).
Quick revision
- Scale path: Vertical → read replicas → shard → specialized store.
- SQL: ACID, joins, default choice for transactional data.
- Replicas: Scale reads; watch replication lag.
- Sharding: Scale writes; pick high-cardinality shard key.
- Indexes: Match query patterns; composite for multi-column filters.
- Interview: State read:write ratio, growth, consistency needs before picking DB.