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: If a PostgreSQL server is limited by memory, moving from 16 GB to 64 GB may help first. If reads later become the bottleneck, add read replicas; consider sharding only when simpler changes are not enough.

2. SQL vs NoSQL (interview framework)

NoSQL types: Document (MongoDB), Key-value (DynamoDB/Redis), Wide-column (Cassandra), Graph (Neo4j).

Rule of thumb: Start with SQL when the data has relationships or needs transactions. Choose a NoSQL model when a verified access pattern, scale need, or schema need fits it better.

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: For a hypothetical product catalog with a 100:1 read-to-write ratio, one primary can handle writes while several replicas handle browsing traffic. The replica count must come from measured capacity and load.

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).

Concrete example: Sharding orders by customer_id keeps one customer's orders together and usually spreads customers well. Sharding by order_status is poor because most active orders may collect on one shard.

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

1. Start: Single Postgres with index on (conversation_id, sent_at)
2. 10M users: Read replicas for chat history loads
3. 1B messages: Shard by conversation_id hash across 32 Cassandra nodes
4. Recent messages hot in Redis; archive old to S3

8. CAP reminder at DB layer

CAP applies when replicated nodes are separated by a network partition; it does not classify a single-node database as CP. With multiple regions, synchronous replication may reject some requests to preserve consistency, while asynchronous replication may serve stale data to remain available.