设置范围分区
学习实现范围分区，根据键的取值范围划分表，通常用于时间序列数据。
设置范围分区 是 CoddyKit 上的免费 Advanced PostgreSQL: Indexing, Partitioning, Replication 课时。 这是第 2 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 Advanced PostgreSQL: Indexing, Partitioning, Replication 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Advanced PostgreSQL: Indexing, Partitioning, Replication 课程共包含 4 节课。
本课时的部分内容尚未翻译，以英文显示。
Intro to Range Partitioning
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.
How Range Partitioning Works
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_attimestamp). - Ranges: Defined boundaries (e.g., 'January to March', 'IDs 1-1000').
Creating the Master Table
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:
CREATE TABLE sensor_data (
id SERIAL,
event_date DATE NOT NULL,
temperature NUMERIC,
humidity NUMERIC
) PARTITION BY RANGE (event_date);Defining Partition Bounds
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.
Creating the First Partition
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.
CREATE TABLE sensor_data_2023_01
PARTITION OF sensor_data
FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');Adding More Partitions
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:
CREATE TABLE sensor_data_2023_02
PARTITION OF sensor_data
FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');Inserting 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:
INSERT INTO sensor_data (event_date, temperature, humidity)
VALUES
('2023-01-15', 22.5, 60),
('2023-02-10', 20.1, 65),
('2023-01-28', 23.0, 58);Verifying Data Distribution
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:
SELECT * FROM sensor_data_2023_01;
SELECT * FROM sensor_data_2023_02;Adding Future Partitions
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:
CREATE TABLE sensor_data_2023_03
PARTITION OF sensor_data
FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');Quick Check
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?
Recap & Next Steps
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.
常见问题解答
「设置范围分区」课时是免费的吗？
是的 — 「设置范围分区」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 AI 导师）并解锁 Advanced PostgreSQL: Indexing, Partitioning, Replication 课程的其余内容，请升级到 CoddyKit PRO。 Advanced PostgreSQL: Indexing, Partitioning, Replication 课程共包含 4 节课。
「设置范围分区」这节课中我会学到什么？
学习实现范围分区，根据键的取值范围划分表，通常用于时间序列数据。 你通过在浏览器中直接运行的动手代码来练习 Advanced PostgreSQL: Indexing, Partitioning, Replication，全天候 AI 导师会在你学习这节课的过程中回答你的问题。
学习 Advanced PostgreSQL: Indexing, Partitioning, Replication 需要有经验吗？
无需任何先前经验。CoddyKit 上的 Advanced PostgreSQL: Indexing, Partitioning, Replication 课程适合初学者到高级学习者，你可以从这里开始或从头开始，按照自己的节奏学习。 这是第 2 节课，共 4 节。
「设置范围分区」课时需要多长时间？
大多数 CoddyKit 课程大约需要 5–10 分钟。每节课都很精短且互动，所以你能稳步进步，并在网页和应用中从离开的地方继续。
我能在这节 Advanced PostgreSQL: Indexing, Partitioning, Replication 课中编写并运行代码吗？
能。每节 Advanced PostgreSQL: Indexing, Partitioning, Replication 课都包含内置代码编辑器，你可以在浏览器中直接编写并运行真实代码，并获得即时 AI 反馈 — 无需本地设置。