Partitioning (Range / Hash / List)

Splitting a large table into smaller, manageable pieces called partitions, while still treating it as a single logical table.

1. Range Partitioning

Data is divided based on a range of values in a column.

CREATE TABLE orders (id INT, order_date DATE, amount DECIMAL)
PARTITION BY RANGE (YEAR(order_date)) (
  PARTITION p2021 VALUES LESS THAN (2022),
  PARTITION p2022 VALUES LESS THAN (2023),
  PARTITION p2023 VALUES LESS THAN (2024),
  PARTITION p2024 VALUES LESS THAN MAXVALUE
);

• Best For: Date/time based data (logs, orders, events), time-series, archiving old data (just drop old partition)
• Weakness: Uneven distribution if data is skewed. Hot partition problem — latest partition gets all writes.

2. Hash Partitioning

Data is divided by applying a hash function on a column.

Partition Number = HASH(column_value) % num_partitions

CREATE TABLE customers (id INT, name VARCHAR(100))
PARTITION BY HASH(id) PARTITIONS 4;

• Best For: Evenly distributing data when no natural range exists, avoiding hot partitions.
• Weakness: No partition pruning for range queries. Adding/removing partitions requires reshuffling all data.

3. List Partitioning

Data is divided based on a predefined list of discrete values in a column.

CREATE TABLE sales (id INT, region VARCHAR(50), amount DECIMAL)
PARTITION BY LIST (region) (
  PARTITION p_north VALUES IN ('Delhi', 'Punjab', 'UP'),
  PARTITION p_south VALUES IN ('Kerala', 'TamilNadu', 'Karnataka'),
  PARTITION p_west  VALUES IN ('Mumbai', 'Gujarat', 'Rajasthan')
);

• Best For: Categorical data (region, country, status). Queries filtering on specific category values.
• Weakness: Must manually update partitions when new values are added. Unlisted values are rejected.

Comparison Table

	Range	Hash	List
Based on	Value ranges	Hash function	Discrete value sets
Data distribution	Uneven (skew possible)	Very even	Depends on categories
Partition pruning	Excellent	Poor for ranges	Good for exact matches
Best for	Dates, time-series	IDs, even distribution	Regions, categories
Adding partitions	Easy	Hard (reshuffle)	Easy
Hot partition risk	Yes	No	Depends

Partition Pruning

The biggest performance benefit — the query optimizer skips irrelevant partitions:

-- Only scans p2023 partition, ignores all others
SELECT * FROM orders WHERE order_date BETWEEN '2023-01-01' AND '2023-12-31';

Partitioning vs Sharding

	Partitioning	Sharding
Location	Same machine	Different machines
Managed by	DB engine	Application/middleware
Scalability	Vertical	Horizontal