Horizontal vs vertical partitioning in HLD - Practice Questions

Challenge - 5 Problems

🧠 Conceptual

intermediate

Understanding Horizontal Partitioning

Which of the following best describes horizontal partitioning in database design?

A Dividing a table into multiple tables each containing a subset of columns

B Merging multiple tables into one large table to improve query speed

C Creating a backup of the entire database on multiple servers

D Splitting a table into multiple tables each containing a subset of rows based on a key

💡 Hint

Think about dividing data by rows rather than columns.

Horizontal partitioning splits data by rows, distributing subsets of records across tables or servers.

🧠 Conceptual

intermediate

Understanding Vertical Partitioning

Which option correctly explains vertical partitioning in database systems?

A Combining multiple tables into one to reduce joins

B Splitting a table into smaller tables each containing a subset of columns

C Creating multiple copies of the same table for redundancy

D Distributing rows of a table across multiple servers

💡 Hint

Focus on dividing data by columns instead of rows.

Vertical partitioning divides a table by columns, storing different columns in separate tables.

❓ scaling

advanced

Choosing Partitioning for Large User Data

You have a user database with millions of records and many columns. You want to improve query speed for user profile info and reduce server load. Which partitioning strategy is best?

A Vertical partitioning by splitting frequently accessed profile columns from less used columns

B No partitioning, keep all data in one table for simplicity

C Horizontal partitioning by user region to distribute rows across servers

D Create multiple full copies of the user table on different servers

💡 Hint

Consider which data is accessed most often and how to reduce data scanned per query.

Vertical partitioning helps by separating frequently accessed columns, reducing data scanned and improving query speed.

❓ tradeoff

advanced

Tradeoffs Between Horizontal and Vertical Partitioning

Which statement correctly describes a tradeoff when choosing between horizontal and vertical partitioning?

A Vertical partitioning eliminates the need for database normalization

B Vertical partitioning reduces the number of rows but increases the number of columns in each table

C Horizontal partitioning improves write scalability but can increase complexity in queries joining multiple partitions

D Horizontal partitioning always reduces the need for indexes on tables

💡 Hint

Think about how data is split and how queries might be affected.

Horizontal partitioning splits rows, improving write scalability but may require complex joins across partitions.

❓ estimation

expert

Estimating Storage Savings with Vertical Partitioning

A table has 1 million rows and 50 columns. 10 columns are accessed frequently and 40 rarely. Each row is 1 KB total. If vertical partitioning splits the table into two tables (10 columns and 40 columns), approximately how much storage is saved when querying only the frequently accessed columns?

A About 80% storage saved per query by scanning only 10 columns

B No storage saved because all data is still stored together

C About 20% storage saved because 10 columns are smaller than 40

D About 50% storage saved because half the columns are separated

💡 Hint

Consider the proportion of columns accessed and data scanned per query.

Since 10 out of 50 columns are accessed, scanning only those saves about 80% of data scanned per query.