Automate partition creation and retention with pg_partman

Command

CREATE SCHEMA partman;
CREATE EXTENSION pg_partman SCHEMA partman;

CREATE TABLE public.measurements (
  id    bigint GENERATED ALWAYS AS IDENTITY,
  ts    timestamptz NOT NULL,
  value numeric,
  PRIMARY KEY (id, ts)
) PARTITION BY RANGE (ts);

-- Daily partitions, pre-create 7 days ahead (pg_partman 5.x syntax)
SELECT partman.create_parent(
  p_parent_table := 'public.measurements',
  p_control      := 'ts',
  p_interval     := '1 day',
  p_premake      := 7
);

-- Drop partitions older than 30 days
UPDATE partman.part_config
   SET retention = '30 days',
       retention_keep_table = false
 WHERE parent_table = 'public.measurements';

-- Maintenance: run periodically (schedule with pg_cron or pg_partman_bgw)
CALL partman.run_maintenance_proc();

-- Any rows in the default partition?
SELECT * FROM partman.check_default(p_exact_count := true);

What it does

Registers a parent table with pg_partman; pre-creates future partitions, drops those past the retention period and reports rows that landed in the default partition.

When to use it

When you need standard, observable automation instead of managing daily/monthly partitions by hand or with custom scripts (log, metric, event tables).

Caution

The API changed between pg_partman 4.x and 5.x: in 5.x p_type accepts only 'range'/'list' (default 'range'), and the 4.x p_type := 'native' is invalid; check the installed version with SELECT extversion FROM pg_extension WHERE extname = 'pg_partman';. create_parent also creates a default partition by default. retention_keep_table = false permanently DROPs partitions; if you need an archive, keep it true and use retention_schema. If run_maintenance_proc() is not scheduled, no new partitions are created and data lands in the default. pg_partman_bgw requires shared_preload_libraries plus a restart.