Partitioning: Range, Hash, List & Composite

-- RANGE Partitioning: split by value ranges
-- Best for: time-series data (logs, orders, events)

CREATE TABLE orders (
    id BIGSERIAL,
    customer_id INT NOT NULL,
    order_date DATE NOT NULL,
    total DECIMAL(12,2),
    status VARCHAR(20)
) PARTITION BY RANGE (order_date);

-- Create partitions for each quarter
CREATE TABLE orders_2024_q1 PARTITION OF orders
    FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');

CREATE TABLE orders_2024_q2 PARTITION OF orders
    FOR VALUES FROM ('2024-0
...

-- LIST Partitioning: split by specific values
-- Best for: categorical data (regions, status, type)

CREATE TABLE customers (
    id SERIAL,
    name VARCHAR(100),
    email VARCHAR(200),
    region VARCHAR(20) NOT NULL,
    created_at TIMESTAMP DEFAULT NOW()
) PARTITION BY LIST (region);

CREATE TABLE customers_americas PARTITION OF customers
    FOR VALUES IN ('us', 'ca', 'mx', 'br');

CREATE TABLE customers_europe PARTITION OF customers
    FOR VALUES IN ('uk', 'de', 'fr', 'es', 'it');

CREA
...

-- HASH Partitioning: even distribution by hash
-- Best for: spreading load when no natural range/list exists

CREATE TABLE sessions (
    id UUID DEFAULT gen_random_uuid(),
    user_id INT NOT NULL,
    data JSONB,
    created_at TIMESTAMP DEFAULT NOW()
) PARTITION BY HASH (user_id);

-- Create 4 hash partitions (use power of 2 for even distribution)
CREATE TABLE sessions_p0 PARTITION OF sessions
    FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE sessions_p1 PARTITION OF sessions
    FO
...

-- Partition Maintenance Operations

-- 1. Add a new partition (e.g., new quarter)
CREATE TABLE orders_2025_q1 PARTITION OF orders
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

-- 2. Detach a partition (keep data, remove from parent)
ALTER TABLE orders DETACH PARTITION orders_2024_q1;
-- Now orders_2024_q1 is a standalone table
-- Queries on "orders" no longer include it

-- 3. Archive old data by detaching + moving
ALTER TABLE orders DETACH PARTITION orders_2024_q1;
ALTER TABLE orders_
...

-- Composite (Sub-) Partitioning
-- Split by range, then by list within each range

CREATE TABLE events (
    id BIGSERIAL,
    event_type VARCHAR(50) NOT NULL,
    event_date DATE NOT NULL,
    payload JSONB
) PARTITION BY RANGE (event_date);

-- Year-level partitions, sub-partitioned by type
CREATE TABLE events_2024 PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01')
    PARTITION BY LIST (event_type);

CREATE TABLE events_2024_clicks PARTITION OF events_2024
    FOR VALUE
...