Database Partitioning Cheat Sheet

-- Declarative range partitioning by date (PostgreSQL 10+)CREATE TABLE orders (    id SERIAL,    order_date DATE NOT NULL,    customer_id INT,    amount NUMERIC) PARTITION BY RANGE (order_date);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');-- The planner automatically routes this to orders_2024_q1SELECT * FROM orders WHERE order_date = '2024-02-15';

-- Hash partitioning: evenly distributes rows with no natural rangeCREATE TABLE users (    id INT NOT NULL,    email TEXT) PARTITION BY HASH (id);CREATE TABLE users_p0 PARTITION OF users    FOR VALUES WITH (MODULUS 4, REMAINDER 0);CREATE TABLE users_p1 PARTITION OF users    FOR VALUES WITH (MODULUS 4, REMAINDER 1);-- List partitioning: split by known discrete valuesCREATE TABLE sales (    id SERIAL,    region TEXT NOT NULL,    amount NUMERIC) PARTITION BY LIST (region);CREATE TABLE sales_us PARTITION OF sales FOR VALUES IN ('US', 'CA');CREATE TABLE sales_eu PARTITION OF sales FOR VALUES IN ('DE', 'FR', 'UK');

-- Detach a partition without a long-lived table lockALTER TABLE orders DETACH PARTITION orders_2024_q1;-- Attach a new partition for the next quarterALTER TABLE orders ATTACH PARTITION orders_2024_q3    FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');-- Instantly drop old data (no row-by-row DELETE, minimal WAL)DROP TABLE orders_2023_q4;-- Catch-all partition for values that don't match any defined range/listCREATE TABLE orders_default PARTITION OF orders DEFAULT;-- Inspect existing partitions of a tableSELECT relname FROM pg_class  WHERE relispartition AND relname LIKE 'orders_%';