Horje
Vertical Partitioning vs Horizontal Partitioning

Vertical partitioning and horizontal partitioning are two techniques used in database design to optimize the organization and management of large tables. They involve dividing a table into smaller, more manageable pieces to improve query performance and maintenance.

vertical-partitioning-vs-horizontal-partitioning

Vertical Partitioning vs Horizontal Partitioning

Vertical Partitioning

Involves dividing a table based on columns. It is useful when you have tables with many columns, and not all columns are frequently accessed together. This can improve query performance by reducing I/O and allowing for more efficient indexing of relevant columns.

Horizontal Partitioning

Involves dividing a table based on rows, often using a range or a condition. It is useful when dealing with tables containing a large number of rows, and data can be logically grouped based on certain criteria. This can improve query performance by minimizing the amount of data that needs to be scanned for specific queries and allows for easier data management.

Difference between Vertical and Horizontal Partitioning

Here’s a comparison of the two partitioning methods:

Feature Vertical Partitioning Horizontal Partitioning
Definition Dividing a table into smaller tables based on columns. Dividing a table into smaller tables based on rows (usually ranges of rows).
Purpose Reduce the number of columns in a table to improve query performance and reduce I/O. Divide a table into smaller tables to manage large volumes of data efficiently.
Data distribution Columns with related data are placed together in the same table. Rows with related data (typically based on a range or a condition) are placed together in the same table.
Query performance Improves query performance when queries only involve specific columns that are part of a partition. Improves query performance when queries primarily access a subset of rows in a large table.
Maintenance and indexing Easier to manage and index specific columns based on their characteristics and access patterns. Each partition can be indexed independently, making indexing more efficient.
Joins May require joins to combine data from multiple partitions when querying. Joins between partitions are typically not needed, as they contain disjoint sets of data.
Data integrity Ensuring data consistency across partitions can be more challenging. Easier to maintain data integrity, as each partition contains a self-contained subset of data.
Use cases Commonly used for tables with a wide range of columns, where not all columns are frequently accessed together. Commonly used for tables with a large number of rows, where data can be grouped based on some criteria (e.g., date ranges).
Examples Splitting a customer table into one table for personal details and another for transaction history. Partitioning a large sales order table by date, with each partition containing orders from a specific month or year. 



Reffered: https://www.geeksforgeeks.org

System Design

Related
Complete Tutorial of Configuration in Redis Complete Tutorial of Configuration in Redis
Reliability in System Design Reliability in System Design
Designing Facebook Messenger | System Design Interview Designing Facebook Messenger | System Design Interview
System Design of Splitwise Backend (Expense Sharing Apps) System Design of Splitwise Backend (Expense Sharing Apps)
Design Notification Services | System Design Design Notification Services | System Design

Type:
 Geek
Category:
 Coding
Sub Category:
 Tutorial
Uploaded by:
 Admin
Views:
 13



 

Horje