Planned lessonMID-019-L06

Demo: partitioning the order history by month

On This Lesson 3 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

convert the order history to monthly partitions and observe pruning.

Draft outline details

Show the unpartitioned 10 M-row orders and the plan for a one-month query (large index or sequential scan).
Create the partitioned table orders_p with a composite primary key and monthly partitions plus a default partition.
Copy data in batches and add indexes; swap names in a short transaction.
Run the one-month query: EXPLAIN shows only one partition scanned (pruning).
Planned mistake: filter with a function on created_at, so pruning does not happen. Fix: use a direct range.
DETACH PARTITION orders_2025_01 and archive; compare with the old bulk DELETE.

Prepares MID-019-LAB1.

What shows pruning in the plan?

Show answer

Only the matching partitions appear as scan nodes.

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.