Declarative Partitioning

Declarative Partitioning by Range, List and Hash

A partitioned table is an empty parent that routes each row to a child table, a partition, by its partition key (declarative partitioning since PostgreSQL 10 1,289 , hash and default partitions since 11). Each partition is an ordinary heap with its own indexes, statistics and vacuum, so an old month is dropped, not deleted row by row.

Three ways to split BookNest's sales: by month, by channel and by order number

In 453-partition.sql, \gexec runs each row a query returns as a statement, creating partitions in a loop:

453-partition.sql: range, list and hash partitioningSQL

CREATE TABLE mart.sales_m (LIKE mart.sales) PARTITION BY RANGE (order_date);
SELECT format('CREATE TABLE mart.sales_%s PARTITION OF mart.sales_m '
              'FOR VALUES FROM (%L) TO (%L)', to_char(m, 'YYYY_MM'), m::date,
              (m + interval '1 month')::date)
FROM generate_series(date '2025-01-01', date '2026-06-01', interval '1 month') AS m \gexec
INSERT INTO mart.sales_m SELECT * FROM mart.sales ORDER BY order_date;
CREATE TABLE mart.sales_ch (LIKE mart.sales) PARTITION BY LIST (channel);
CREATE TABLE mart.sales_ch_apps PARTITION OF mart.sales_ch FOR VALUES IN ('ios', 'android');
CREATE TABLE mart.sales_ch_web PARTITION OF mart.sales_ch FOR VALUES IN ('web');
INSERT INTO mart.sales_ch SELECT * FROM mart.sales;
CREATE TABLE p_orders (LIKE orders) PARTITION BY HASH (order_id);
CREATE TABLE p_items (LIKE order_items) PARTITION BY HASH (order_id);
SELECT format('CREATE TABLE %s_%s PARTITION OF %s FOR VALUES WITH (MODULUS 4, REMAINDER %s)',
              t, r, t, r) FROM unnest(array['p_orders', 'p_items']) AS t,
              generate_series(0, 3) AS r \gexec
INSERT INTO p_orders SELECT * FROM orders;
INSERT INTO p_items SELECT * FROM order_items;
VACUUM ANALYZE mart.sales_m, mart.sales_ch, p_orders, p_items;
INSERT INTO mart.sales_m (order_id, order_date) VALUES (100001, '2026-07-01');

Output

ERROR:  no partition of relation "sales_m" found for row
DETAIL:  Partition key of the failing row contains (order_date) = (2026-07-01).

Bounds include FROM and exclude TO. A July order has nowhere to go: create partitions ahead of the data (Partitioning the Sales Mart), or add a DEFAULT partition, which catches strays but must be scanned whenever a new partition is attached. Range suits time, list a few known values, and hash spreads IDs evenly but cannot prune a range predicate. Partition facts by time at a grain that gives millions of rows per partition, not thousands: each partition costs planning time and memory.

MENU