Fundamentals and Core Concepts

When to Use Range Partitioning

• When data has a natural range-based organization (dates, IDs, etc.)
• When you need to efficiently query ranges of data
• For time-series data where recent data is accessed more frequently

Challenges

• Risk of hot spots if data is not evenly distributed across ranges
• Requires careful planning to ensure balanced partitions
• May require rebalancing as data grows

2. Hash Partitioning

Hash partitioning applies a hash function to the partition key to determine the partition.

How It Works

1. A hash function is applied to the partition key
2. The hash value is used to determine which partition the data belongs to
3. Data is evenly distributed across partitions

Partition = hash(key) % number_of_partitions

Implementation Example (MySQL)

CREATE TABLE users (
    id INT NOT NULL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL,
    PRIMARY KEY (id)
) ENGINE=INNODB
PARTITION BY HASH(id)
PARTITIONS 4;

When to Use Hash Partitioning

• When you need even data distribution
• When most queries access data by primary key
• When range queries are not a primary access pattern

Challenges

• Poor performance for range queries
• Difficult to add or remove partitions without rehashing
• Cannot easily co-locate related data

3. List Partitioning

List partitioning assigns data to partitions based on discrete values of a column.

How It Works

Data is partitioned based on specific values of a column, such as:

• All US users in partition 1
• All EU users in partition 2
• All APAC users in partition 3

Implementation Example (Oracle)

CREATE TABLE users (
    id NUMBER,
    username VARCHAR2(50),
    country VARCHAR2(2),
    email VARCHAR2(100)
)
PARTITION BY LIST (country) (
    PARTITION p_americas VALUES ('US', 'CA', 'MX', 'BR'),
    PARTITION p_europe VALUES ('UK', 'DE', 'FR', 'IT'),
    PARTITION p_asia VALUES ('JP', 'CN', 'IN', 'SG')
);

When to Use List Partitioning

• When data naturally falls into discrete categories
• When queries frequently filter by those categories
• For geographic partitioning of data

Challenges

• Uneven distribution if categories have vastly different sizes
• Need to handle new category values that weren’t initially defined
• May require frequent maintenance as categories evolve

4. Composite Partitioning

Composite partitioning combines multiple partitioning strategies, such as range-hash or list-hash.