Working with Table Partitioning

CREATE TABLE events (
   id         bigserial,
   tenant_id  int  NOT NULL,
   created_at timestamptz NOT NULL,
   payload    jsonb,
   PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);

CREATE TABLE events_2025_01 PARTITION OF events
   FOR VALUES FROM ('2025-01-01') TO ('2025-02-01');

CREATE TABLE orders (...) PARTITION BY LIST (region);
CREATE TABLE orders_us  PARTITION OF orders FOR VALUES IN ('US','CA');
CREATE TABLE orders_eu  PARTITION OF orders FOR VALUES IN ('DE','FR','GB');

CREATE TABLE users (...) 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);

CREATE TABLE events_2025_02 PARTITION OF events
   FOR VALUES FROM ('2025-02-01') TO ('2025-03-01')
   TABLESPACE fast_ssd;

FOR VALUES FROM (MINVALUE) TO ('2024-01-01');
FOR VALUES FROM ('2025-01-01') TO (MAXVALUE);

FOR VALUES IN ('a','b','c');

CREATE TABLE events_default PARTITION OF events DEFAULT;

ALTER TABLE events ATTACH PARTITION events_2024
   FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

ALTER TABLE events DETACH PARTITION events_2023 CONCURRENTLY;
-- PG 14+ avoids ACCESS EXCLUSIVE on the parent

DROP TABLE events_2022;     -- after DETACH (fast purge of old data)

CREATE INDEX events_tenant_created_idx
   ON events (tenant_id, created_at DESC);
-- Cascades to all current/future partitions as partitioned index

SET enable_partition_pruning = on;        -- default
EXPLAIN SELECT * FROM events
 WHERE created_at >= '2025-02-15' AND created_at < '2025-02-20';
-- Plan: Append scans only events_2025_02

SET enable_partitionwise_join = on;

SET enable_partitionwise_aggregate = on;