Horizontal vs vertical partitioning in HLD - Design Approaches Compared

Design: Database Partitioning Strategies

Design and compare horizontal and vertical partitioning strategies for a relational database system. Out of scope: physical hardware setup, specific cloud provider configurations.

Functional Requirements

FR1: Store large datasets efficiently

FR2: Improve query performance by reducing data scanned

FR3: Support scalable data growth

FR4: Maintain data consistency and integrity

Non-Functional Requirements

NFR1: Handle up to 100 million records

NFR2: Query latency p99 under 200ms

NFR3: Availability target 99.9%

NFR4: Support read and write operations with minimal downtime

Think Before You Design

Questions to Ask

❓ Question 1

❓ Question 2

❓ Question 3

❓ Question 4

❓ Question 5

Key Components

Database tables and schemas

Partitioning keys or criteria

Query routing and optimization

Data replication and consistency mechanisms

Design Patterns

Horizontal partitioning (sharding)

Vertical partitioning (column-based splitting)

Composite partitioning (combining horizontal and vertical)

Caching layers to reduce database load

Reference Architecture

Client
  |
  v
Query Router
  |
  +---------------------+
  |                     |
Horizontal Partitions  Vertical Partitions
(Table shards by rows) (Tables split by columns)
  |                     |
Database Nodes         Database Nodes

Components

Query Router

Custom middleware or database proxy

Direct queries to appropriate partitions based on partitioning strategy

Horizontal Partitions

Relational database shards

Split data by rows using a partition key (e.g., user ID ranges) to distribute load

Vertical Partitions

Relational database tables

Split data by columns grouping related columns together to optimize column-specific queries

Database Nodes

SQL databases (e.g., PostgreSQL, MySQL)

Store partitioned data and handle queries

Request Flow

1. Client sends query to Query Router

2. Query Router analyzes query type and routing rules

3. For horizontal partitioning, router directs query to shard(s) containing relevant rows

4. For vertical partitioning, router directs query to tables containing requested columns

5. Database nodes execute query on their partitioned data

6. Results are aggregated if needed and returned to client

Database Schema

Entities: - User (id, name, email, address, phone, preferences) Horizontal Partitioning: - User table split into multiple shards by id ranges (e.g., User_1_1000000, User_1000001_2000000) Vertical Partitioning: - User_Basic (id, name, email) - User_Contact (id, address, phone) - User_Preferences (id, preferences) Relationships: - Each vertical partition shares the primary key 'id' to join data when needed

Scaling Discussion

Bottlenecks

Query Router becomes a single point of failure or bottleneck

Uneven data distribution causing hotspots in horizontal partitions

Complex joins across vertical partitions increasing query latency

Data consistency challenges when updating multiple partitions

Solutions

Implement load balancing and failover for Query Router

Use consistent hashing or range partitioning with monitoring to balance shards

Denormalize data or use caching to reduce cross-partition joins

Use distributed transactions or eventual consistency models with conflict resolution

Interview Tips

Time: Spend 10 minutes understanding requirements and clarifying data access patterns, 20 minutes designing partitioning strategies with diagrams, 10 minutes discussing scaling and trade-offs, 5 minutes summarizing key points.

Explain difference between horizontal and vertical partitioning clearly

Discuss when to use each based on query patterns and data shape

Highlight impact on performance, scalability, and complexity

Mention real-world examples like sharding in large web apps

Address consistency and operational challenges

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