pg_partman

pig build pkg pg_partman;		# build rpm/deb

pig repo add pgdg -u    # add pgdg repo and update cache

pig install pg_partman;		# install via package name, for the active PG version

pig install pg_partman -v 18;   # install for PG 18
pig install pg_partman -v 17;   # install for PG 17
pig install pg_partman -v 16;   # install for PG 16
pig install pg_partman -v 15;   # install for PG 15
pig install pg_partman -v 14;   # install for PG 14

CREATE EXTENSION pg_partman CASCADE; -- requires plpgsql

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;

CREATE TABLE public.measurements (
    id          bigserial,
    created_at  timestamptz NOT NULL DEFAULT now(),
    value       numeric
) PARTITION BY RANGE (created_at);

SELECT partman.create_parent(
    p_parent_table  := 'public.measurements',
    p_control       := 'created_at',
    p_interval      := '1 day'
);

CREATE TABLE public.events (
    id      bigserial,
    data    text
) PARTITION BY RANGE (id);

SELECT partman.create_parent(
    p_parent_table  := 'public.events',
    p_control       := 'id',
    p_interval      := '100000'
);

SELECT partman.run_maintenance();

SELECT partman.run_maintenance(p_parent_table := 'public.measurements');

UPDATE partman.part_config
SET    retention = '30 days',
       retention_keep_table = false
WHERE  parent_table = 'public.measurements';

CALL partman.partition_data_proc('public.measurements');

SELECT * FROM partman.show_partitions('public.measurements');

CALL partman.undo_partition_proc('public.measurements');