Skip to main content

Partitioning

Partitioning = dividing a large table (or index) into smaller, manageable pieces (partitions), while still treating it as one logical table in SQL.

  • The database engine automatically decides which partition to read/write based on rules.
  • Helps with query performance, parallelism, and maintenance.

Types of Partitioning​

Horizontal Partitioning (Row-based)​

  • Rows are divided into subsets based on a condition.
  • Each partition contains different rows of the same schema.
  • Example: Partitioning Orders table by year → Orders_2023, Orders_2024.

Vertical Partitioning (Column-based)​

  • Table is split by columns.
  • Frequently used columns stored in one partition, rarely used in another.
  • Example: Split Employee into:
    • EmployeeBasic(EmpID, Name, DeptID)
    • EmployeeDetails(EmpID, Address, Photo, ResumeBlob).

Range Partitioning​

  • Rows divided based on ranges of values.
  • Example: Partition Sales by SaleDate:
    • Partition1 → Jan–Mar
    • Partition2 → Apr–Jun, etc.

List Partitioning​

  • Rows divided based on discrete values.
  • Example: Partition employees by Region: Asia, Europe, America.

Hash Partitioning​

  • Rows assigned to partitions based on a hash function of a column.
  • Example: MOD(CustomerID, 4) distributes rows into 4 partitions.

Example of Partitioning​

Table: Transaction(TxnID, AccountNo, TxnDate, Amount) Partition by year:

  • Transactions_2023
  • Transactions_2024
  • Transactions_2025

Benefits:

  • Query like WHERE TxnDate BETWEEN '2025-01-01' AND '2025-08-27' only scans relevant partitions.
  • Improves performance + allows old partitions to be archived.