Table Partitioning

Table partitioning splits a large table into smaller partitions. TiDB supports RANGE, HASH, LIST, and KEY partitioning.

RANGE Partitioning

CREATE TABLE orders (
    id INT PRIMARY KEY AUTO_RANDOM,
    order_date DATE NOT NULL,
    amount DECIMAL(12, 2)
) PARTITION BY RANGE (YEAR(order_date)) (
    PARTITION p2022 VALUES LESS THAN (2023),
    PARTITION p2023 VALUES LESS THAN (2024),
    PARTITION p2024 VALUES LESS THAN (2025),
    PARTITION p2025 VALUES LESS THAN (2026),
    PARTITION pmax VALUES LESS THAN MAXVALUE
);

RANGE COLUMNS

CREATE TABLE logs (
    id INT PRIMARY KEY AUTO_RANDOM,
    log_date DATE NOT NULL,
    message TEXT
) PARTITION BY RANGE COLUMNS(log_date) (
    PARTITION p202401 VALUES LESS THAN ('2024-02-01'),
    PARTITION p202402 VALUES LESS THAN ('2024-03-01'),
    PARTITION p202403 VALUES LESS THAN ('2024-04-01')
);

HASH Partitioning

CREATE TABLE users (
    id INT PRIMARY KEY AUTO_RANDOM,
    name VARCHAR(100),
    email VARCHAR(100)
) PARTITION BY HASH(id) PARTITIONS 4;

LIST Partitioning

CREATE TABLE sales (
    id INT PRIMARY KEY AUTO_RANDOM,
    region VARCHAR(20),
    amount DECIMAL(12, 2)
) PARTITION BY LIST COLUMNS(region) (
    PARTITION p_jawa VALUES IN ('jawa_barat', 'jawa_tengah', 'jawa_timur'),
    PARTITION p_sumatera VALUES IN ('sumatera_utara', 'sumatera_barat'),
    PARTITION p_bali VALUES IN ('bali')
);

KEY Partitioning

CREATE TABLE sessions (
    id INT PRIMARY KEY AUTO_RANDOM,
    user_id INT NOT NULL,
    data JSON
) PARTITION BY KEY(user_id) PARTITIONS 8;

Partition Operations

Adding a Partition

ALTER TABLE orders ADD PARTITION (
    PARTITION p2026 VALUES LESS THAN (2027)
);

Dropping a Partition

ALTER TABLE orders DROP PARTITION p2022;

Truncate Partition

ALTER TABLE orders TRUNCATE PARTITION p2023;

Querying Partitions

-- Query with partition pruning (automatic)
SELECT * FROM orders WHERE order_date = '2024-06-15';
-- TiDB only reads partition p2024

-- Show partition information
SELECT PARTITION_NAME, TABLE_ROWS
FROM information_schema.PARTITIONS
WHERE TABLE_NAME = 'orders';

Limitations

• SUBPARTITION is not supported
• {CHECK|OPTIMIZE|REPAIR} PARTITION is not supported
• PARTITION BY LINEAR KEY is not supported
• Foreign key constraints are not supported on partitioned tables

In the next chapter, we will look at views in TiDB.