子分区技术
通过实现子分区来组合分区方法，以更细粒度地组织数据。
子分区技术 是 CoddyKit 上的免费 Advanced PostgreSQL: Indexing, Partitioning, Replication 课时。 这是第 2 节课，共 4 节。 你可以在下方免费阅读本课时的完整内容 — 然后在浏览器中使用内置代码编辑器和全天候 AI 导师进行实践。 这是 Advanced PostgreSQL: Indexing, Partitioning, Replication 学习路径的一部分，你的进度在网页和 CoddyKit 应用中同步。 Advanced PostgreSQL: Indexing, Partitioning, Replication 课程共包含 4 节课。
本课时的部分内容尚未翻译，以英文显示。
Deeper Data Organization
Welcome! In this lesson, we'll explore sub-partitioning, an advanced technique to combine different partitioning methods in PostgreSQL.
It allows you to organize your data with even finer granularity, creating a powerful hierarchical structure for very large tables.
Why Use Sub-Partitioning?
Sub-partitioning offers several key advantages for managing and querying massive datasets:
- Finer Granularity: Break down large partitions into smaller, more manageable units.
- Targeted Management: Easier to perform operations (e.g., attach, detach, archive) on specific data subsets.
- Improved Query Performance: The database can prune even more irrelevant data blocks, significantly speeding up queries on specific sub-sections.
How Nested Partitions Work
With sub-partitioning, you define a primary partitioning strategy for your main table. Then, for each individual partition of that main table, you define a secondary partitioning strategy.
Think of it as partitioning a table by year, and then partitioning each year's data further by region. It's a 'partition of a partition' concept.
Strategy: Range by Date, List by Region
A common and effective sub-partitioning pattern is to first partition a table by a date range (e.g., year or quarter), and then sub-partition each date range by a list of discrete values (e.g., region, department, status).
This is ideal for time-series data that also has important categorical attributes, allowing you to quickly filter by both.
Code: Main Table (Range)
Let's create an orders table. This will be our top-level parent, partitioned by order_date using RANGE partitioning.
CREATE TABLE orders (
order_id INT,
order_date DATE,
region TEXT,
amount DECIMAL
) PARTITION BY RANGE (order_date);Code: Level 1 Partition (Range & List Parent)
Now, we create a partition for the year 2023. Crucially, we add PARTITION BY LIST (region) to this partition definition.
This makes orders_2023 itself a parent table, ready for its own sub-partitions.
CREATE TABLE orders_2023
PARTITION OF orders
FOR VALUES FROM ('2023-01-01') TO ('2024-01-01')
PARTITION BY LIST (region);Code: Level 2 Sub-Partitions & Insert
Finally, we create the actual sub-partitions for specific regions within the orders_2023 partition. Data for 'North' goes into orders_2023_north, etc. We'll also insert some data to see it in action.
CREATE TABLE orders_2023_north
PARTITION OF orders_2023
FOR VALUES IN ('North');
CREATE TABLE orders_2023_south
PARTITION OF orders_2023
FOR VALUES IN ('South');
INSERT INTO orders VALUES
(1, '2023-03-15', 'North', 150.00),
(2, '2023-07-22', 'South', 200.50),
(3, '2023-11-01', 'North', 75.25);
SELECT tableoid::regclass, * FROM orders ORDER BY order_id;Strategy: List by Category, Range by Year
You can also reverse the strategy: partition first by a list of categories (e.g., 'Electronics', 'Books'), and then sub-partition each category by a date range (e.g., release year).
This is useful when your primary access pattern is by category, and then you need to filter within categories by time.
Quick Check: Sub-Partitioning
Sub-partitioning offers powerful ways to organize data. Which of the following statements correctly describe its characteristics or benefits?
Recap & Next Steps
You've now learned about PostgreSQL sub-partitioning!
- We saw how to combine RANGE and LIST partitioning to create deeply organized tables.
- This technique provides finer data granularity and can significantly boost query performance by enabling more precise partition pruning.
- Understanding sub-partitioning is crucial for managing extremely large and complex datasets effectively.
Next, we'll dive into managing partitioned tables, including adding, dropping, and altering partitions efficiently.
常见问题解答
「子分区技术」课时是免费的吗？
是的 — 「子分区技术」的完整文本可在网页上免费阅读。要进行交互式练习（内置代码编辑器和全天候 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 反馈 — 无需本地设置。