Automating Partition Creation and Retention
Build maintenance jobs with pg_partman or custom DDL to roll new partitions in and detach old ones cheaply.
Why Partition Maintenance Must Be Automated
Range partitioning by time (daily, weekly, monthly) only pays off if a future partition always exists before data arrives. If a row's partition key falls outside every defined partition, the INSERT fails with no partition of relation found for row.
- Roll-in: create the next partition(s) ahead of time.
- Roll-out (retention): detach or drop old partitions once they pass your retention window.
Doing this by hand is error-prone, so you either automate it with a maintenance function on a schedule, or use the pg_partman extension. This lesson covers both.
The Parent Table
Everything starts from a declaratively partitioned parent. Here we partition events by month on created_at. The parent holds no rows itself; it only routes inserts to child partitions.
Note that the partition key column must be part of the primary key, which is why the PK is (id, created_at).
CREATE TABLE events (
id bigint GENERATED ALWAYS AS IDENTITY,
created_at timestamptz NOT NULL DEFAULT now(),
user_id bigint NOT NULL,
payload jsonb,
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);All lessons in this course
- Choosing a Partition Key and Strategy
- Partition Pruning at Plan and Execution Time
- Automating Partition Creation and Retention
- Migrating a Huge Table to Partitions Online