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_%';

-- Creating an index on the parent creates a matching local index on every-- existing (and future) partition automaticallyCREATE INDEX idx_orders_customer ON orders (customer_id);-- Unique constraints must include the partition key -- Postgres cannot-- enforce global uniqueness across partitionsALTER TABLE orders ADD CONSTRAINT orders_pk PRIMARY KEY (id, order_date);-- Build an index on a single partition first (fast, no long lock on parent),-- then attach it to the parent's index definitionCREATE INDEX CONCURRENTLY idx_orders_2024_q3_customer  ON orders_2024_q3 (customer_id);ALTER INDEX idx_orders_customer ATTACH PARTITION idx_orders_2024_q3_customer;

CREATE TABLE events (    id BIGSERIAL,    tenant_id INT NOT NULL,    occurred_at TIMESTAMPTZ NOT NULL,    payload JSONB) PARTITION BY RANGE (occurred_at);CREATE TABLE events_2024_q3 PARTITION OF events    FOR VALUES FROM ('2024-07-01') TO ('2024-10-01')    PARTITION BY HASH (tenant_id);CREATE TABLE events_2024_q3_p0 PARTITION OF events_2024_q3    FOR VALUES WITH (MODULUS 4, REMAINDER 0);CREATE TABLE events_2024_q3_p1 PARTITION OF events_2024_q3    FOR VALUES WITH (MODULUS 4, REMAINDER 1);-- ... p2, p3-- A query filtering on both occurred_at and tenant_id prunes to exactly one leaf

CREATE EXTENSION IF NOT EXISTS pg_partman;-- Register the table for automatic monthly partition managementSELECT partman.create_parent(    p_parent_table => 'public.orders',    p_control      => 'order_date',    p_interval     => 'monthly',    p_premake      => 3          -- pre-create 3 future partitions);-- Configure retention: automatically detach (and optionally drop) old partitionsUPDATE partman.part_configSET retention = '12 months', retention_keep_table = falseWHERE parent_table = 'public.orders';-- Run via pg_cron or an external scheduler:SELECT partman.run_maintenance('public.orders');

-- Size of each partition, largest firstSELECT relname AS partition,       pg_size_pretty(pg_total_relation_size(relid)) AS sizeFROM pg_catalog.pg_statio_user_tablesWHERE relname LIKE 'orders_%'ORDER BY pg_total_relation_size(relid) DESC;-- Confirm the planner is pruning: only matching partitions should appearEXPLAIN (ANALYZE, BUFFERS)SELECT * FROM orders WHERE order_date = '2024-08-01';-- Look for "Subplans Removed" or a plan touching only orders_2024_q3-- Row counts per partition (useful for spotting skewed hash distribution)SELECT relname, n_live_tup FROM pg_stat_user_tablesWHERE relname LIKE 'orders_%' ORDER BY n_live_tup DESC;