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

Welcome to range partitioning! This technique divides a large table into smaller, more manageable pieces called partitions.

Range partitioning is especially useful for data that has a natural order, like time-series data (e.g., logs, sensor readings) or data with sequential IDs.

With range partitioning, rows are distributed into partitions based on a 'partition key' column's value falling within a specified range.

• Partition Key: The column used to determine which partition a row belongs to (e.g., created_at timestamp).
• Ranges: Defined boundaries (e.g., 'January to March', 'IDs 1-1000').

First, you create a master (or parent) table. This table defines the schema for all its partitions and specifies the partitioning strategy.

Notice the PARTITION BY RANGE clause and the partition key (event_date) in the example:

After the master table, you create individual child tables, which are the actual partitions. Each child table is linked to the master table and defines its specific range.

The FOR VALUES FROM (...) TO (...) clause sets the lower (inclusive) and upper (exclusive) bounds for the event_date column.

Let's create a partition for data from January 2023. The TO value is exclusive, so '2023-02-01' means up to, but not including, February 1st.

You can create as many partitions as needed, covering different time periods or ranges. It's common to create partitions for months, quarters, or years.

Here's a partition for February 2023 data:

When you insert data into the master sensor_data table, PostgreSQL automatically directs each row to the correct child partition based on its event_date.

You insert data into the parent table, not directly into the child partitions:

You can query the master table as usual, and PostgreSQL will scan only the relevant partitions (a process called 'partition pruning').

Or, you can directly query a child partition to see its contents:

As new data arrives, you'll need to create new partitions. It's a good practice to pre-create future partitions to ensure seamless data ingestion.

For example, to add a partition for March 2023:

You've set up a range-partitioned table for sales_data based on sale_date. The master table is sales_data, and you've created a partition sales_q1_2023 for '2023-01-01' to '2023-04-01'.

Which of the following INSERT statements into the master sales_data table would correctly route data into the sales_q1_2023 partition?

You've learned how to implement range partitioning in PostgreSQL!

• Range partitioning divides tables by a key's value range.
• You define a master table with PARTITION BY RANGE.
• Child tables are created using PARTITION OF ... FOR VALUES FROM ... TO ....
• Data is inserted into the master table and automatically routed.

This method is excellent for managing large, time-ordered datasets, improving both query performance and data maintenance.