Range_list_partitioning

Chapter 39: Range & List Partitioning

Advanced Partitioning Strategies

---

39.1 Range Partitioning

Range partitioning divides data into ranges based on column values.

-- More complex range partitioning
CREATE TABLE logs (
    log_id BIGSERIAL,
    log_time TIMESTAMP NOT NULL,
    level VARCHAR(20),
    message TEXT,
    user_id INTEGER
) PARTITION BY RANGE (log_time);

-- Create monthly partitions for 2024
CREATE TABLE logs_2024_01 PARTITION OF logs
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE logs_2024_02 PARTITION OF logs
    FOR VALUES FROM ('2024-02-01') TO ('2024-03-01');

-- Continue for other months...

-- Create partition for older data
CREATE TABLE logs_archive PARTITION OF logs
    FOR VALUES FROM (MINVALUE) TO ('2024-01-01');

-- Multi-column range partitioning
CREATE TABLE sales (
    sale_id BIGSERIAL,
    sale_date DATE NOT NULL,
    region VARCHAR(50),
    amount DECIMAL(12,2)
) PARTITION BY RANGE (sale_date, region);

-- Partition by date ranges within regions
CREATE TABLE sales_north_2024 PARTITION OF sales
    FOR VALUES FROM ('2024-01-01', 'North') TO ('2025-01-01', 'North');

---

39.2 List Partitioning

List partitioning divides data based on specific values.

-- List partitioning by region
CREATE TABLE customers (
    customer_id SERIAL,
    name VARCHAR(100),
    region VARCHAR(50),
    signup_date DATE
) PARTITION BY LIST (region);

CREATE TABLE customers_north PARTITION OF customers
    FOR VALUES IN ('North', 'Northeast', 'Northwest');

CREATE TABLE customers_south PARTITION OF customers
    FOR VALUES IN ('South', 'Southeast', 'Southwest');

CREATE TABLE customers_midwest PARTITION OF customers
    FOR VALUES IN ('Midwest');

CREATE TABLE customers_west PARTITION OF customers
    FOR VALUES IN ('West');

-- Default partition for other regions
CREATE TABLE customers_other PARTITION OF customers DEFAULT;

-- Subpartitioning (partition by region, then by date)
CREATE TABLE customers PARTITION BY LIST (region) SUBPARTITION BY RANGE (signup_date);

CREATE TABLE customers_north PARTITION OF customers
    FOR VALUES IN ('North', 'Northeast', 'Northwest')
    PARTITION BY RANGE (signup_date);

CREATE TABLE customers_north_2024 PARTITION OF customers_north
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

---

39.3 Partition Maintenance

-- Create new partition for future data
CREATE TABLE orders_2025_q1 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

-- Split a partition
-- Detach, create new, reattach
ALTER TABLE orders DETACH PARTITION orders_2024_q1;

CREATE TABLE orders_2024_jan PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

CREATE TABLE orders_2024_q1_new PARTITION OF orders
    FOR VALUES FROM ('2024-02-01') TO ('2024-04-01');

ALTER TABLE orders ATTACH PARTITION orders_2024_jan
    FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

ALTER TABLE orders ATTACH PARTITION orders_2024_q1_new
    FOR VALUES FROM ('2024-02-01') TO ('2024-04-01');

-- Rename partition
ALTER TABLE orders_2024_01 RENAME TO orders_2024_jan;

---

Summary

Strategy	Use Case
Range	Dates, numbers
List	Categories
Subpartition	Complex requirements

---

Next: Chapter 40: Partition Management