DEV Community

Pranav Bakare
Pranav Bakare

Posted on

Summary of Horizontal and Vertical Partitioning

Here's a consolidated overview of the different types of database partitioning, including horizontal partitioning (with its specific types) and vertical partitioning (including normalization, BLOB separation, and in-memory storage):

Partitioning Types

  1. Horizontal Partitioning

Horizontal partitioning involves dividing a table into smaller pieces based on rows. This approach improves query performance and manageability.

Types of Horizontal Partitioning:

Range Partitioning:

Divides data into partitions based on a range of values.

Example: A sales table can be partitioned by date, with one partition for sales from 2023 and another for sales from 2024.

List Partitioning:

Divides data into partitions based on a specified list of values.

Example: A customer table can be partitioned by region, with partitions for "North," "South," "East," and "West."

Hash Partitioning:

Distributes data evenly across a specified number of partitions based on a hash function applied to a column value.

Example: A user activity log can be partitioned based on user IDs to ensure an even distribution of data.

  1. Vertical Partitioning

Vertical partitioning involves dividing a table into smaller tables based on columns. This can enhance performance and optimize data management.

Types of Vertical Partitioning:

Normalization:

The process of organizing data to reduce redundancy and improve data integrity by splitting a larger table into smaller, related tables.

Example: A customer table might be split into CustomerInfo (containing basic user information) and CustomerContact (containing contact details).

BLOB Separation:

Storing large binary objects (BLOBs) in a separate table to optimize performance of the main table.

Example: A users table with basic information can have a separate UserImages table to store profile images as BLOBs.

In-Memory Storage:

Utilizing database features to store specific columns or tables in memory for faster access and improved performance.

Example: Enabling in-memory processing for a frequently accessed table to enhance analytical query performance.

Summary

Horizontal Partitioning: Divides tables based on rows, improving query performance and manageability through range, list, and hash partitioning.

Vertical Partitioning: Divides tables based on columns, enhancing performance and data integrity through normalization, BLOB separation, and in-memory storage.

This comprehensive overview provides a clear understanding of different partitioning techniques and their specific types, highlighting their roles in optimizing database performance and management.

Top comments (0)