Table partitioning
On This Lesson 4 topics
This lesson is planned. The outline below lists the topics it will cover. The full lesson has not been published yet.
In this lesson
split a large table by range or list and use partition pruning.
Planned topics
- Declarative partitioning:
PARTITION BY RANGE (created_at)with child tables per month. - Partition pruning: when the query filters on the partition key, the planner reads only the matching partitions.
- Benefits: faster maintenance (drop or detach an old partition instead of a huge
DELETE), smaller indexes, easier archiving. - Rules: the primary key and unique constraints must include the partition key; create partitions ahead of time (or automate).
- Do not partition small tables; the overhead is not worth it (a typical threshold is tens of millions of rows or hundreds of GB; measure).
Draft outline details
Demonstration
CREATE TABLE orders (
id bigint, created_at timestamptz NOT NULL, customer_id bigint, total_cents bigint,
PRIMARY KEY (id, created_at)
) PARTITION BY RANGE (created_at);
CREATE TABLE orders_2026_01 PARTITION OF orders
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');Check your understanding
Why must the primary key include created_at?
Show answer
Unique constraints must contain the partition key.
Practice
Apply this lesson with the module's lab, exercises and quiz.
Marking a lesson complete is optional and you can undo it any time. Your progress stays in this browser.