Open In App

Vertical Partitioning vs Horizontal Partitioning

Last Updated : 20 Sep, 2023
Comments
Improve
Suggest changes
Like Article
Like
Report

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:

FeatureVertical PartitioningHorizontal Partitioning
DefinitionDividing a table into smaller tables based on columns.Dividing a table into smaller tables based on rows (usually ranges of rows).
PurposeReduce 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 distributionColumns 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 performanceImproves 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 indexingEasier to manage and index specific columns based on their characteristics and access patterns. Each partition can be indexed independently, making indexing more efficient.
JoinsMay 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 integrityEnsuring data consistency across partitions can be more challenging. Easier to maintain data integrity, as each partition contains a self-contained subset of data.
Use casesCommonly 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).
ExamplesSplitting 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.

Next Article
Partitioning in Distributed Systems

C
code_r
Improve
Article Tags :

Similar Reads

We use cookies to ensure you have the best browsing experience on our website. By using our site, you acknowledge that you have read and understood our Cookie Policy & Privacy Policy
Lightbox