การแบ่งพาร์ทิชันตามช่วงเวลา

Range partitioning splits a table into chunks based on a continuous value, most often a date or timestamp.

It is the most common strategy for time-series data such as logs, events, and orders, because old data can be dropped or archived as a whole partition.

You declare partitioning with PARTITION BY RANGE on the parent table.

Each child partition covers a half-open interval: the lower bound is inclusive and the upper bound is exclusive.

The exclusive upper bound prevents gaps and overlaps. A row at exactly 2024-02-01 00:00:00 lands in events_2024_02, never in January.

This makes consecutive partitions perfectly contiguous.

You insert into the parent table. PostgreSQL routes each row to the correct partition based on the range key.

A DEFAULT partition catches any row that does not match a defined range, avoiding insert errors.

An index created on the parent is automatically propagated to all current and future partitions.

The killer feature: deleting old data is just DROP TABLE on a partition. No row-by-row DELETE, no bloat, no vacuum pressure.

When the planner sees a range predicate on the partition key it can skip entire partitions. This is called partition pruning.

Tools like pg_partman create future partitions ahead of time on a schedule, so you never insert into the default partition by accident.

• Define a retention window
• Pre-create N future partitions
• Detach or drop expired ones

Pick a width that keeps each partition in the tens of millions of rows. Too many tiny partitions hurt planning time; too few huge ones lose pruning benefits.

You learned range partitioning by time: declare with PARTITION BY RANGE, create half-open child intervals, rely on automatic routing and pruning, and archive by dropping whole partitions. Automation tools like pg_partman keep the partition set healthy.