Horizontal vs vertical partitioning in HLD - Scaling Approaches Compared

Scalability Analysis - Horizontal vs vertical partitioning

Growth Table: Horizontal vs Vertical Partitioning

Users/Data Size	Horizontal Partitioning	Vertical Partitioning
100 users / small data	Single database instance; simple queries	Single database; all columns together
10,000 users / medium data	Split data rows across shards by user ID; reduces row size per shard	Split tables by columns (e.g., user info separate from logs); reduces column size per query
1 million users / large data	Many shards; each handles subset of users; easier to scale writes	Tables split by feature sets; some queries need joins across vertical partitions
100 million users / huge data	Hundreds of shards; complex routing; cross-shard queries costly	Multiple vertical partitions across clusters; increased join complexity; possible duplication

First Bottleneck

At small scale, the database handles all data easily. As users grow, the first bottleneck is usually the database storage and query performance.

Horizontal partitioning bottleneck: Single shard storage and write throughput limits.

Vertical partitioning bottleneck: Complex joins across partitions slow queries.

Scaling Solutions

• Horizontal Partitioning: Add more shards to distribute rows; use consistent hashing or range-based sharding; implement routing layer to direct queries.
• Vertical Partitioning: Separate tables by feature or column groups; optimize queries to avoid unnecessary joins; replicate frequently joined partitions.
• Combine both: Use horizontal partitioning within vertical partitions for very large scale.
• Use caching layers to reduce database load.
• Use read replicas to scale read-heavy workloads.

Back-of-Envelope Cost Analysis

Assuming 1 million users, each generating 10 requests per second:

• Total requests: 10 million QPS (queries per second).
• Single DB instance handles ~5,000 QPS → Need ~2,000 shards for horizontal partitioning.
• Storage per user: 1 MB → Total 1 TB data; split across shards or vertical tables.
• Network bandwidth: 1 Gbps = 125 MB/s; large data transfers require multiple network interfaces or data centers.

Interview Tip

Start by explaining the difference: horizontal splits rows, vertical splits columns.

Discuss pros and cons of each with examples.

Identify bottlenecks based on data size and query patterns.

Suggest scaling steps logically: start with vertical if columns are large, horizontal if rows grow fast.

Mention combining both for complex systems.

Self Check

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

Answer: Implement horizontal partitioning (sharding) to distribute load across multiple database instances, because a single instance cannot handle 10x the queries.

Key Result

Horizontal partitioning scales by splitting data rows across multiple databases to handle more users and writes, while vertical partitioning splits data columns to optimize query performance and storage. The first bottleneck is usually database throughput or join complexity, fixed by sharding or splitting tables respectively.

Practice

1. Which of the following best describes horizontal partitioning in system design?

easy

A. Combining multiple tables into one large table

B. Splitting a database table into multiple tables by columns

C. Splitting a database table into multiple tables by rows

D. Duplicating the entire database for backup

Solution

1. Step 1: Understand horizontal partitioning

   Horizontal partitioning means dividing data by rows, so each partition holds a subset of rows.

2. Step 2: Compare with other options

   Vertical partitioning splits by columns, combining tables merges data, and duplication is for backup, not partitioning.

3. Final Answer:

   Splitting a database table into multiple tables by rows -> Option C

4. Quick Check:

   Horizontal partitioning = splitting by rows [OK]

Hint: Rows split = horizontal, columns split = vertical [OK]

Common Mistakes:

• Confusing horizontal with vertical partitioning
• Thinking partitioning means duplicating data
• Mixing partitioning with database backup

2. Which syntax correctly describes vertical partitioning?

easy

A. Splitting data by rows into multiple tables

B. Splitting data by columns into multiple tables

C. Merging rows from different tables

D. Backing up the entire database

Solution

1. Step 1: Define vertical partitioning

   Vertical partitioning splits a table by columns, separating attributes into different tables.

2. Step 2: Eliminate incorrect options

   Options about rows, merging, or backup do not describe vertical partitioning.

3. Final Answer:

   Splitting data by columns into multiple tables -> Option B

4. Quick Check:

   Vertical partitioning = splitting by columns [OK]

Hint: Columns split = vertical partitioning [OK]

Common Mistakes:

• Confusing vertical with horizontal partitioning
• Assuming partitioning means merging tables
• Mixing partitioning with backup processes

3. Consider a user database table with columns: id, name, email, password, address. If we apply vertical partitioning by splitting sensitive data into a separate table, which of the following is a valid partition?

medium

A. Table 1: id, password; Table 2: name, email, address

B. Table 1: id, name; Table 2: email, password, address

C. Table 1: id, name, password; Table 2: email, address

D. Table 1: id, name, email; Table 2: id, password, address

Solution

1. Step 1: Identify sensitive data columns

   Password and address are sensitive, so they should be separated from general info.

2. Step 2: Check partitioning by columns

   Table 1: id, name, email; Table 2: id, password, address separates password and address into one table, and id, name, email into another, matching vertical partitioning.

3. Final Answer:

   Table 1: id, name, email; Table 2: id, password, address -> Option D

4. Quick Check:

   Vertical partitioning splits columns, sensitive data separated [OK]

Hint: Sensitive columns go to separate table in vertical partitioning [OK]

Common Mistakes:

• Mixing rows and columns in partitioning
• Not separating sensitive data properly
• Splitting columns unevenly without reason

4. A system uses horizontal partitioning by user region but experiences slow queries on user emails. What is the likely cause and fix?

medium

A. Cause: Partitioning by rows only; Fix: Add vertical partitioning to separate email column

B. Cause: Partitioning by columns only; Fix: Add horizontal partitioning by region

C. Cause: No partitioning; Fix: Add horizontal partitioning by email

D. Cause: Too many partitions; Fix: Merge all partitions into one

Solution

1. Step 1: Analyze the problem

   Horizontal partitioning splits by rows (region), but email queries are slow because email column is not separated.

2. Step 2: Suggest fix

   Adding vertical partitioning to separate email column improves query speed on emails.

3. Final Answer:

   Cause: Partitioning by rows only; Fix: Add vertical partitioning to separate email column -> Option A

4. Quick Check:

   Slow email queries fixed by vertical partitioning [OK]

Hint: Slow column queries? Add vertical partitioning [OK]

Common Mistakes:

• Confusing cause with partition type
• Suggesting wrong partitioning fix
• Ignoring query patterns in design

5. You design a large e-commerce database with millions of orders. You want to improve performance by splitting data. Which approach is best and why?

hard

A. Use horizontal partitioning by customer ID and vertical partitioning by payment info for balanced load

B. Use vertical partitioning by splitting order details and customer info into separate tables to reduce row size

C. Use no partitioning and rely on caching for performance

D. Use horizontal partitioning by order date to distribute rows across servers, improving query speed on recent orders

Solution

1. Step 1: Understand data and query patterns

   Orders have many rows and columns; splitting by customer ID (horizontal) balances load; splitting payment info (vertical) reduces row size.

2. Step 2: Evaluate options

   Use horizontal partitioning by customer ID and vertical partitioning by payment info for balanced load combines horizontal and vertical partitioning for scalability and performance, better than single approach or no partitioning.

3. Final Answer:

   Use horizontal partitioning by customer ID and vertical partitioning by payment info for balanced load -> Option A

4. Quick Check:

   Combine horizontal and vertical partitioning for large data [OK]

Hint: Combine horizontal and vertical for big, complex data [OK]

Common Mistakes:

• Using only one partitioning type for complex data
• Ignoring query patterns and data distribution
• Relying solely on caching without partitioning