Why database choice impacts architecture in HLD - Scalability Evidence

Scalability Analysis - Why database choice impacts architecture

Growth Table: Impact of Database Choice at Different Scales

Users / Traffic	Database Behavior	Architecture Impact
100 users	Single instance DB handles requests easily	Simple architecture, direct DB connection
10,000 users	DB load increases, latency may rise	Introduce connection pooling, caching layers
1 million users	Single DB instance bottleneck; scaling limits reached	Need read replicas, sharding, or NoSQL options
100 million users	Massive data volume and traffic; complex queries slow	Distributed DB clusters, multi-region replication, polyglot persistence

First Bottleneck: Database

The database is usually the first component to break as user count and data grow. This happens because databases have limits on how many queries they can process per second and how much data they can store efficiently. When the DB is overwhelmed, response times increase and the whole system slows down.

Scaling Solutions for Database Bottlenecks

• Vertical Scaling: Upgrade to more powerful servers with faster CPUs, more RAM, and SSD storage.
• Read Replicas: Create copies of the database to handle read queries, reducing load on the main DB.
• Sharding: Split data horizontally across multiple database servers to distribute load.
• Caching: Use in-memory caches like Redis or Memcached to serve frequent queries quickly.
• NoSQL Databases: Use databases optimized for specific data types or access patterns (e.g., document stores, key-value stores) for better scalability.
• Polyglot Persistence: Combine multiple database types to handle different parts of data efficiently.

Back-of-Envelope Cost Analysis

• At 1 million users, assuming 10 requests per user per day, total requests = 10 million/day ≈ 115 requests/sec.
• A single PostgreSQL instance can handle ~5,000 QPS, so DB can handle this load but with little room for growth.
• Storage needs grow with data size; 100 million users may require terabytes of storage.
• Network bandwidth must support data transfer; 1 Gbps network can handle ~125 MB/s, enough for many applications but may need upgrades at large scale.

Interview Tip: Structuring Scalability Discussion

Start by identifying the database's role and limits in your system. Discuss expected traffic and data growth. Explain how the database choice affects read/write patterns, consistency, and latency. Then describe bottlenecks and propose scaling solutions like replication, sharding, or caching. Finally, mention trade-offs and cost implications.

Self-Check Question

Your database handles 1000 queries per second (QPS). Traffic grows 10x to 10,000 QPS. What do you do first and why?

Key Result

Database choice impacts architecture because databases have limits on query handling and storage. As users grow, the database becomes the first bottleneck, requiring solutions like replication, sharding, or caching to scale effectively.

Practice

1. Which of the following is a primary reason why the choice of database impacts system architecture?

easy

A. Database choice only matters for small systems

B. All databases use the same query language

C. Databases do not affect system performance

D. Different databases handle data consistency and scaling differently

Solution

1. Step 1: Understand database roles in architecture

   Databases store and manage data, affecting how data is accessed and maintained.

2. Step 2: Recognize differences in consistency and scaling

   Relational and NoSQL databases differ in consistency guarantees and how they scale horizontally or vertically.

3. Final Answer:

   Different databases handle data consistency and scaling differently -> Option D

4. Quick Check:

   Database choice affects consistency and scaling [OK]

Hint: Focus on how data handling varies by database type [OK]

Common Mistakes:

• Assuming all databases behave the same
• Ignoring scaling differences
• Thinking database choice doesn't affect performance

2. Which database type is best described by this syntax for creating a table: CREATE TABLE users (id INT PRIMARY KEY, name VARCHAR(100));?

easy

A. Document-based NoSQL database

B. Key-value store

C. Relational SQL database

D. Graph database

Solution

1. Step 1: Identify the syntax style

   The CREATE TABLE statement with columns and types is standard SQL syntax.

2. Step 2: Match syntax to database type

   Relational databases use SQL and tables with defined schemas.

3. Final Answer:

   Relational SQL database -> Option C

4. Quick Check:

   SQL table creation = Relational database [OK]

Hint: Look for SQL keywords like CREATE TABLE [OK]

Common Mistakes:

• Confusing NoSQL with SQL syntax
• Assuming all databases use SQL
• Ignoring schema definitions

3. Consider a system using a NoSQL document database storing user profiles as JSON. Which architectural impact is most likely when scaling this system?

medium

A. Easier horizontal scaling with eventual consistency

B. Strong ACID transactions across multiple documents

C. Rigid schema enforcement for all data

D. Limited ability to shard data across servers

Solution

1. Step 1: Understand NoSQL document database traits

   NoSQL document stores often allow flexible schemas and scale horizontally.

2. Step 2: Identify scaling and consistency trade-offs

   They usually provide eventual consistency and support sharding for horizontal scaling.

3. Final Answer:

   Easier horizontal scaling with eventual consistency -> Option A

4. Quick Check:

   NoSQL scaling = horizontal + eventual consistency [OK]

Hint: NoSQL means flexible schema and horizontal scaling [OK]

Common Mistakes:

• Expecting strong ACID transactions in NoSQL
• Assuming rigid schemas
• Thinking sharding is limited

4. A system architect chose a relational database but faces performance issues with high write loads. What is a likely architectural mistake?

medium

A. Using eventual consistency instead of strong consistency

B. Ignoring NoSQL options that scale writes better

C. Choosing a key-value store for complex joins

D. Not using SQL queries for data retrieval

Solution

1. Step 1: Analyze the problem context

   Relational databases can struggle with very high write loads due to ACID constraints.

2. Step 2: Identify architectural mistake

   Ignoring NoSQL databases that handle high write throughput with eventual consistency is a common error.

3. Final Answer:

   Ignoring NoSQL options that scale writes better -> Option B

4. Quick Check:

   High write load needs scalable DB choice [OK]

Hint: High writes? Consider NoSQL for better scaling [OK]

Common Mistakes:

• Assuming relational DB always best
• Confusing consistency models
• Misusing key-value stores for complex queries

5. You are designing a global social media platform requiring fast reads, flexible user data, and eventual consistency. Which database choice best fits this architecture and why?

hard

A. NoSQL document database for flexible schema and horizontal scaling

B. Graph database for user relationships but no horizontal scaling

C. In-memory key-value store only for all data storage

D. Relational database for strong consistency and complex joins

Solution

1. Step 1: Analyze requirements

   The system needs fast reads, flexible data, and can tolerate eventual consistency.

2. Step 2: Match database features to needs

   NoSQL document databases provide flexible schemas, scale horizontally worldwide, and support eventual consistency.

3. Step 3: Evaluate other options

   Relational DBs have strong consistency but less flexible scaling; in-memory stores lack persistence; graph DBs may not scale globally easily.

4. Final Answer:

   NoSQL document database for flexible schema and horizontal scaling -> Option A

5. Quick Check:

   Global scale + flexible data = NoSQL document DB [OK]

Hint: Flexible data + global scale? Pick NoSQL document DB [OK]

Common Mistakes:

• Choosing relational DB despite scaling limits
• Using in-memory store without persistence
• Ignoring horizontal scaling needs