Faster queries, easier maintenance of large tables.
Range Partitioning¶
CREATE TABLE orders (
id SERIAL,
created_at TIMESTAMPTZ NOT NULL,
total DECIMAL
) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2024_q1 PARTITION OF orders
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
List Partitioning¶
CREATE TABLE logs (id SERIAL, level TEXT, message TEXT)
PARTITION BY LIST (level);
CREATE TABLE logs_error PARTITION OF logs
FOR VALUES IN ('error','fatal');
pg_partman¶
CREATE EXTENSION pg_partman;
SELECT partman.create_parent('public.orders','created_at','native','monthly');
- 100M+ rows
- Time-series data
- DROP PARTITION instead of DELETE
Partitioning for Large Tables¶
Speeds up queries and simplifies maintenance.
postgresqlpartitioningperformance