Table Partitioning

Splitting huge tables by range, list or hash, partition pruning, and dropping old data instantly.

Time-series and log tables grow without end. Declarative partitioning splits one logical table into partitions — for example one per month. Queries on a date range only touch the relevant partitions, and old data is removed by dropping a partition instead of a slow DELETE.

Monthly partitions

PARTITION BY RANGE (taken_at) declares the partitioning; each PARTITION OF table holds one month.

Inserts go into the parent table and are routed automatically; tableoid::regclass shows where each row went.

The EXPLAIN plan scans only readings_2025_03: partition pruning skipped January and February.

DROP TABLE readings_2025_01 removed a month of data instantly.

Notes

WHERE date_trunc('month', taken_at) = '2025-03-01' WHERE taken_at >= '2025-03-01' AND taken_at < '2025-04-01'

Pruning works on conditions directly on the partition key. Wrapping the key in a function can prevent the planner from pruning, so every partition is scanned.

Why is DROP TABLE on a partition better than DELETE for removing old data?

Dropping a partition is a quick metadata change. DELETE must find and mark every row, generates WAL for each, and leaves dead rows for VACUUM to clean up.