A partitioning key is the column or expression used with a partitioning method and boundaries to assign rows to partitions. In PostgreSQL declarative partitioning, choose it around the access and maintenance work that must become smaller: a payments table keyed by timestamp can isolate monthly data for timestamp lookups and retention-driven removal. section 4.2
Use time when work follows time
A large payments table queried by tstamp required a sequential scan across 150,000,999 rejected rows and read 1,151,157 buffers. Defining the parent as PARTITION BY RANGE (tstamp) creates monthly partitions; the same lookup scans only the March 2022 partition. Old monthly data can then be removed by dropping its partition rather than deleting its rows. section 4.2
CREATE TABLE erp.payments_p (
id bigint GENERATED ALWAYS AS IDENTITY,
tstamp timestamp with time zone NOT NULL,
amount numeric NOT NULL,
invoice bigint NOT NULL
) PARTITION BY RANGE (tstamp);
CREATE TABLE
The parent-table definition makes tstamp the range partitioning key; monthly child boundaries supply the routing ranges. section 4.2
Do not mistake a multi-column key for multiple partitioning levels. RANGE (reading_time, branch_id) defines lexicographic bounds across the pair, so attempted branch ranges for the same month can overlap. For one monthly partition per branch group, partition first by reading_time, then partition each monthly partition by branch_id. section 4.3
CREATE TABLE erp.energy_usage_2024_01
PARTITION OF erp.energy_usage
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01')
PARTITION BY RANGE (branch_id);
CREATE TABLE
CREATE TABLE erp.energy_usage_2024_01_01to10
PARTITION OF erp.energy_usage_2024_01
FOR VALUES FROM (1) TO (10);
CREATE TABLE
CREATE TABLE erp.energy_usage_2024_01_11to20
PARTITION OF erp.energy_usage_2024_01
FOR VALUES FROM (11) TO (20);
CREATE TABLE
This DDL shows the safer hierarchy: a time-keyed monthly partition that is itself partitioned by branch_id. section 4.3
