When to use SQL vs NoSQL in HLD - Scaling Approaches Compared

Scalability Analysis - When to use SQL vs NoSQL

Growth Table: SQL vs NoSQL Usage at Different Scales

Users / Data Size	SQL Usage	NoSQL Usage
100 users / small data	Simple relational DB works well; easy to manage transactions	May be overkill; simpler key-value stores possible
10K users / moderate data	Relational DB with indexing and read replicas; ACID important	Document or key-value stores for flexible schema; caching helps
1M users / large data	Sharding needed; complex joins slow; scaling write-heavy loads hard	Horizontal scaling easier; eventual consistency acceptable; flexible schema
100M users / massive data	Complex sharding and partitioning; high cost; joins expensive	Distributed NoSQL clusters; high availability; fast writes; schema evolves easily

First Bottleneck

For SQL, the first bottleneck is the database server CPU and disk I/O when handling complex joins and transactions at scale.

For NoSQL, the bottleneck is often network bandwidth and data partitioning consistency as data grows horizontally.

Scaling Solutions

• SQL: Use read replicas for scaling reads, sharding for writes, optimize indexes, and use connection pooling.
• NoSQL: Use horizontal scaling with distributed clusters, partition data by keys, use caching layers, and tune consistency levels.
• Use caching (Redis, Memcached) to reduce DB load for both.
• Use CDN for static content to reduce backend load.

Back-of-Envelope Cost Analysis

Assuming 1M users generating 10 requests per second total (10 QPS):

• SQL DB: Needs to handle 10 QPS with complex queries; may require 1-2 powerful servers.
• NoSQL DB: Can handle 10 QPS distributed across multiple nodes; easier horizontal scaling.
• Storage: SQL needs structured storage with backups; NoSQL needs distributed storage with replication.
• Bandwidth: Depends on data size per request; NoSQL often optimized for smaller, faster operations.

Interview Tip

Start by clarifying data structure and consistency needs.

Explain trade-offs: SQL for strong consistency and complex queries; NoSQL for flexibility and scale.

Discuss expected traffic and data growth to justify scaling choices.

Mention caching and replication as common scaling techniques.

Self Check

Your SQL database handles 1000 QPS. Traffic grows 10x to 10,000 QPS. What do you do first?

Answer: Add read replicas to scale read traffic and consider sharding to distribute write load, because the single DB server will become CPU and I/O bound.

Key Result

SQL databases excel at strong consistency and complex queries but face CPU and I/O bottlenecks at high scale; NoSQL databases scale horizontally more easily with flexible schemas but trade off some consistency.