What is partitioning?

Partitioning a large database or table into smaller, independent parts called partitions to improve performance, manageability, and availability.

Types of partitioning:

• Horizontal (Sharding): Dividing table rows based on a specified criterion. Each partition contains a subset of rows.
• Vertical: Dividing table columns into multiple tables. Each partition contains a subset of columns for all rows.

Horizontal partitioning methods:

• Range: Dividing based on value ranges in a column.

  -- Example of range partitioning by date
  CREATE TABLE sales (
      sale_id INT,
      sale_date DATE,
      amount DECIMAL(10, 2)
  ) PARTITION BY RANGE (sale_date) (
      PARTITION p_2022_q1 VALUES LESS THAN ('2022-04-01'),
      PARTITION p_2022_q2 VALUES LESS THAN ('2022-07-01'),
      PARTITION p_2022_q3 VALUES LESS THAN ('2022-10-01'),
      PARTITION p_2022_q4 VALUES LESS THAN ('2023-01-01')
  );
  

• List: Dividing based on an explicit list of values in a column.

  -- Example of list partitioning by regions
  CREATE TABLE customers (
      customer_id INT,
      name VARCHAR(100),
      region VARCHAR(50)
  ) PARTITION BY LIST (region) (
      PARTITION p_north VALUES IN ('North'),
      PARTITION p_south VALUES IN ('South'),
      PARTITION p_east VALUES IN ('East'),
      PARTITION p_west VALUES IN ('West')
  );
  

• Hash: Dividing based on a hash value of a column. Distributes data evenly.

  -- Example of hash partitioning by user ID
  CREATE TABLE users (
      user_id INT,
      username VARCHAR(50),
      email VARCHAR(100)
  ) PARTITION BY HASH (user_id)
  PARTITIONS 10; -- Create 10 partitions
  

Advantages:

• Improved query performance (only the needed partition is scanned)
• Increased availability (failure of one partition does not affect others)
• Simplified management (archiving or deleting data by partitions)

Disadvantages:

• Complexity in design and implementation
• Queries spanning multiple partitions may be less efficient