Relational database strengths in HLD - Scalability & System Analysis

Scalability Analysis - Relational database strengths

Growth Table: Relational Database Scaling

Users / Traffic	Data Size	Query Load	System Changes
100 users	Small (MBs)	Low (few QPS)	Single DB instance, simple indexing
10,000 users	Medium (GBs)	Moderate (hundreds QPS)	Read replicas, connection pooling, optimized queries
1,000,000 users	Large (TBs)	High (thousands QPS)	Sharding, caching layers, partitioning, load balancing
100,000,000 users	Very Large (PBs)	Very High (tens of thousands QPS)	Multi-region clusters, advanced sharding, distributed transactions, strong caching/CDN

First Bottleneck

At small to medium scale, the database CPU and disk I/O become the first bottleneck because relational databases rely on complex joins and ACID transactions that consume CPU and disk resources.

As traffic grows, connection limits and query latency increase, causing slow responses.

Scaling Solutions

• Vertical scaling: Upgrade server CPU, RAM, and faster disks to handle more queries.
• Read replicas: Offload read queries to replicas to reduce load on primary DB.
• Connection pooling: Reuse DB connections to reduce overhead.
• Caching: Use in-memory caches (e.g., Redis) for frequent queries to reduce DB hits.
• Sharding: Split data horizontally across multiple DB instances by key ranges or user IDs.
• Partitioning: Divide large tables into smaller parts to improve query performance.
• Load balancing: Distribute traffic evenly across app servers and DB replicas.
• Multi-region deployment: Place DBs closer to users to reduce latency.

Back-of-Envelope Cost Analysis

Assuming 1 million users with 1 request per second each:

• Requests per second: ~1,000,000 QPS (too high for single DB)
• Single PostgreSQL instance handles ~5,000 QPS → need ~200 DB instances or heavy caching
• Storage: 1 TB data growing at 10 GB/day requires scalable storage solutions
• Network bandwidth: 1 Gbps = 125 MB/s, so multiple network interfaces or cloud bandwidth needed

Interview Tip

Start by explaining the strengths of relational databases: strong consistency, ACID transactions, and complex queries.

Then discuss how these strengths impact scaling: joins and transactions add CPU and I/O load.

Outline bottlenecks clearly and propose solutions step-by-step: vertical scaling, read replicas, caching, sharding.

Use real numbers to show understanding of limits and trade-offs.

Self Check

Question: Your database handles 1000 QPS. Traffic grows 10x to 10,000 QPS. What do you do first and why?

Answer: Add read replicas to offload read queries and implement connection pooling to reduce overhead. This addresses the immediate CPU and connection bottlenecks without major redesign.

Key Result

Relational databases excel at consistency and complex queries but face CPU and I/O bottlenecks as traffic grows; scaling requires read replicas, caching, and sharding to maintain performance.

Practice

1. Which of the following is a key strength of relational databases?

easy

A. They are best suited for storing large binary files.

B. They ensure strong data consistency across multiple users.

C. They store data in unstructured formats like documents.

D. They do not support complex queries.

Solution

1. Step 1: Understand relational database data organization

   Relational databases organize data in tables with relationships, which helps maintain consistency.

2. Step 2: Identify the strength related to multi-user access

   They support multiple users accessing and modifying data while keeping it accurate and consistent.

3. Final Answer:

   They ensure strong data consistency across multiple users. -> Option B

4. Quick Check:

   Data consistency = C [OK]

Hint: Relational = tables + strong consistency [OK]

Common Mistakes:

• Confusing relational with document databases
• Thinking relational databases store unstructured data
• Assuming relational databases don't support complex queries

2. Which SQL keyword is used to combine rows from two tables based on a related column?

easy

A. UPDATE

B. SELECT

C. INSERT

D. JOIN

Solution

1. Step 1: Recall SQL keywords for combining tables

   The JOIN keyword is used to combine rows from two or more tables based on a related column.

2. Step 2: Differentiate from other SQL commands

   SELECT retrieves data, INSERT adds data, UPDATE modifies data, but JOIN combines tables.

3. Final Answer:

   JOIN -> Option D

4. Quick Check:

   Combine tables = JOIN [OK]

Hint: JOIN combines tables on related columns [OK]

Common Mistakes:

• Confusing SELECT with JOIN
• Using INSERT or UPDATE to combine tables
• Not knowing JOIN syntax

3. Given two tables Users and Orders, what will the following SQL query return?

SELECT Users.name, Orders.id FROM Users JOIN Orders ON Users.id = Orders.user_id;

medium

A. All users with their order IDs where user IDs match

B. All users regardless of orders

C. All orders regardless of users

D. An error due to missing WHERE clause

Solution

1. Step 1: Analyze the JOIN condition

   The query joins Users and Orders where Users.id equals Orders.user_id, linking users to their orders.

2. Step 2: Understand the SELECT output

   It selects user names and order IDs only for matching pairs, so users without orders are excluded.

3. Final Answer:

   All users with their order IDs where user IDs match -> Option A

4. Quick Check:

   JOIN with ON = matched user-orders [OK]

Hint: JOIN ON filters matching rows only [OK]

Common Mistakes:

• Thinking JOIN returns all users or all orders
• Assuming WHERE clause is mandatory for JOIN
• Confusing JOIN with UNION

4. Identify the error in this SQL query that tries to fetch all customers with their orders:

SELECT Customers.name, Orders.id FROM Customers JOIN Orders;

medium

A. No error, query is correct

B. Using SELECT instead of INSERT

C. Missing ON clause to specify join condition

D. Orders table does not exist

Solution

1. Step 1: Check JOIN syntax

   JOIN requires an ON clause to specify how tables relate; missing ON causes error or unintended Cartesian product.

2. Step 2: Confirm other parts are correct

   SELECT is correct for fetching data; assuming Orders table exists; so error is missing ON clause.

3. Final Answer:

   Missing ON clause to specify join condition -> Option C

4. Quick Check:

   JOIN needs ON clause [OK]

Hint: JOIN without ON causes errors [OK]

Common Mistakes:

• Assuming JOIN works without ON
• Confusing SELECT with INSERT
• Ignoring table existence

5. A company needs a system that supports multiple users updating customer data simultaneously without losing accuracy. Which relational database feature best supports this requirement?

hard

A. ACID transactions ensuring data consistency

B. Storing data as JSON documents

C. Using key-value pairs for fast access

D. NoSQL horizontal scaling

Solution

1. Step 1: Understand multi-user data update needs

   Multiple users updating data require mechanisms to avoid conflicts and keep data accurate.

2. Step 2: Identify relational database feature for consistency

   ACID transactions ensure atomicity, consistency, isolation, and durability, preventing data loss or corruption.

3. Final Answer:

   ACID transactions ensuring data consistency -> Option A

4. Quick Check:

   Multi-user accuracy = ACID transactions [OK]

Hint: ACID = safe multi-user updates [OK]

Common Mistakes:

• Confusing NoSQL features with relational strengths
• Thinking JSON storage ensures consistency
• Ignoring transaction properties