Partitioned Tables

Code

-- Range partition
CREATE TABLE orders (
    id SERIAL,
    order_date DATE NOT NULL,
    total DECIMAL(10,2),
    user_id INT
) PARTITION BY RANGE (order_date);

-- Create partition
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-04-01') TO ('2024-07-01');

-- List partition
CREATE TABLE users_by_region (
    id SERIAL,
    name TEXT,
    region TEXT
) PARTITION BY LIST (region);

CREATE TABLE users_east PARTITION OF users_by_region
    FOR VALUES IN ('east', 'northeast');

-- Hash partition
CREATE TABLE events (id INT, data TEXT)
    PARTITION BY HASH (id);
CREATE TABLE events_0 PARTITION OF events
    FOR VALUES WITH (modulus 4, remainder 0);

Explanation

Partitioning splits large tables into smaller ones, improving query performance and data management efficiency.

More SQL Snippets