介绍
在本实验中，你将学习如何在 PostgreSQL 中实现表分区。目标是将一个大型表划分为更小、更易于管理的部分，这可以显著提高查询性能，并简化数据管理任务，如备份或归档。
你将首先创建一个用于分区的“父”表。然后，你将定义几个“子”表，即分区，每个分区存储特定日期范围内的数据。最后，你将向父表插入数据，并观察 PostgreSQL 如何自动将其路由到正确的分区。你还将学习如何查询分区表，并了解 PostgreSQL 如何通过仅访问相关分区来优化这些查询。
创建父分区表
在此步骤中，你将创建 sales 主表，它将作为我们分区的父表。此表定义了其所有分区的结构，但本身不存储任何数据。
首先，你需要连接到 PostgreSQL 数据库。打开终端，使用以下命令以 postgres 用户身份启动 psql 交互式 shell：
sudo -u postgres psql
现在你应该看到 PostgreSQL 提示符，它看起来像 postgres=#。本实验中后续的所有 SQL 命令都将在此提示符下运行。
接下来，创建 sales 表。此表将按 sale_date 列进行范围分区。
CREATE TABLE sales (
sale_id SERIAL,
sale_date DATE NOT NULL,
product_id INTEGER,
sale_amount DECIMAL(10, 2),
PRIMARY KEY (sale_id, sale_date)
) PARTITION BY RANGE (sale_date);
让我们分解一下这个命令：
CREATE TABLE sales (...): 定义了我们销售数据的列。PRIMARY KEY (sale_id, sale_date): 在分区表中，主键必须包含分区列（sale_date）。PARTITION BY RANGE (sale_date): 这是关键部分。它声明该表使用RANGE方法按sale_date列进行分区。
运行命令后，你应该会看到一个 CREATE TABLE 的确认消息。
要验证表是否已创建，你可以在 psql 中使用 \d 命令来描述表结构。
\d sales
输出将显示表的列，并在底部确认它是一个“Partitioned table”（分区表），并列出“Partition key”（分区键）。
Table "public.sales"
Column | Type | Collation | Nullable | Default
-------------+---------------+-----------+----------+------------------------------------------------
sale_id | integer | | not null | nextval('sales_sale_id_seq'::regclass)
sale_date | date | | not null |
product_id | integer | | |
sale_amount | numeric(10,2) | | |
Partition key: RANGE (sale_date)
Indexes:
"sales_pkey" PRIMARY KEY, btree (sale_id, sale_date)
Number of partitions: 0
请注意，“Number of partitions”（分区数量）为 0。你将在下一步创建实际的分区。
定义日期范围分区
现在你已经有了父表 sales，你需要创建实际存储数据的分区。每个分区将存储特定日期范围内的数据。在此步骤中，你将为 2023 年和 2024 年创建季度分区。
你应该仍然在 psql 交互式终端中。
首先，为 2023 年创建四个分区。每个命令都将一个新表定义为 sales 的分区，并指定它将覆盖的日期范围。
CREATE TABLE sales_2023_q1 PARTITION OF sales
FOR VALUES FROM ('2023-01-01') TO ('2023-04-01');
CREATE TABLE sales_2023_q2 PARTITION OF sales
FOR VALUES FROM ('2023-04-01') TO ('2023-07-01');
CREATE TABLE sales_2023_q3 PARTITION OF sales
FOR VALUES FROM ('2023-07-01') TO ('2023-10-01');
CREATE TABLE sales_2023_q4 PARTITION OF sales
FOR VALUES FROM ('2023-10-01') TO ('2024-01-01');
FOR VALUES FROM ... TO ... 子句定义了每个分区的范围。下限是包含的，上限是不包含的。例如，sales_2023_q1 将存储 sale_date 从 2023-01-01 到但不包括 2023-04-01 的记录。
接下来，使用相同的季度方案为 2024 年创建分区：
CREATE TABLE sales_2024_q1 PARTITION OF sales
FOR VALUES FROM ('2024-01-01') TO ('2024-04-01');
CREATE TABLE sales_2024_q2 PARTITION OF sales
FOR VALUES FROM ('2024-04-01') TO ('2024-07-01');
CREATE TABLE sales_2024_q3 PARTITION OF sales
FOR VALUES FROM ('2024-07-01') TO ('2024-10-01');
CREATE TABLE sales_2024_q4 PARTITION OF sales
FOR VALUES FROM ('2024-10-01') TO ('2025-01-01');
在运行每个 CREATE TABLE 命令后，你将看到一个确认消息。
要验证所有分区是否已创建，你可以再次列出数据库中的表。
\dt
现在，你应该在输出中看到父表 sales 以及你刚刚创建的所有八个分区（sales_2023_q1、sales_2023_q2 等）。
插入和路由数据
在此步骤中，你将插入示例数据。分区的一个关键特性是，你直接将数据插入父表（sales），PostgreSQL 会根据分区键（sale_date）的值自动将每一行路由到正确的分区。
你应该仍然在 psql 交互式终端中。
执行以下 INSERT 语句，添加 16 条跨越 2023 年和 2024 年的示例销售记录：
INSERT INTO sales (sale_date, product_id, sale_amount) VALUES
('2023-01-15', 101, 50.00),
('2023-02-20', 102, 75.50),
('2023-04-10', 103, 100.00),
('2023-05-25', 104, 60.25),
('2023-07-01', 105, 120.00),
('2023-08-12', 106, 80.75),
('2023-10-05', 107, 90.00),
('2023-11-18', 108, 110.50),
('2024-01-22', 109, 55.00),
('2024-03-01', 110, 70.00),
('2024-04-15', 111, 95.50),
('2024-06-10', 112, 65.00),
('2024-07-08', 113, 125.00),
('2024-09-20', 114, 85.25),
('2024-10-12', 115, 95.00),
('2024-12-01', 116, 115.75);
命令完成后，你将看到输出 INSERT 0 16，这表示成功插入了 16 行。
为了验证数据是否被正确路由，你可以查询各个分区。例如，让我们检查 2023 年第一季度的记录数：
SELECT COUNT(*) FROM sales_2023_q1;
输出应为：
count
-------
2
(1 row)
现在，检查 2024 年第四季度的记录数：
SELECT COUNT(*) FROM sales_2024_q4;
输出也应为 2。这证实了 PostgreSQL 已将数据放入了正确的基础分区表中。
查询数据并分析性能
在最后一步，你将查询分区表 sales。分区的主要优势在于“分区裁剪”（partition pruning），即 PostgreSQL 的查询规划器足够智能，能够只扫描必要的那些分区，从而避免对整个数据集进行全表扫描。
你应该仍然在 psql 交互式终端中。
首先，运行一个查询来检索 2023 年第一季度的所有销售记录。
SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-04-01';
你将看到落入此日期范围内的两条记录。要查看 PostgreSQL 如何优化此查询，你可以使用 EXPLAIN 命令，它会显示查询的执行计划。
EXPLAIN SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2023-04-01';
输出将类似于：
QUERY PLAN
------------------------------------------------------------------------------------
Seq Scan on sales_2023_q1 sales (cost=0.00..31.75 rows=7 width=28)
Filter: ((sale_date >= '2023-01-01'::date) AND (sale_date < '2023-04-01'::date))
(2 rows)
注意 Seq Scan on sales_2023_q1 这一行。这证明了 PostgreSQL 只扫描了 sales_2023_q1 分区，而忽略了其他七个分区，这在大数据集上可以大大加快查询速度。
现在，让我们运行一个更复杂的查询，以查找 2024 年每个产品的总销售额。
SELECT product_id, SUM(sale_amount) AS total_sales
FROM sales
WHERE sale_date >= '2024-01-01' AND sale_date < '2025-01-01'
GROUP BY product_id
ORDER BY product_id;
此查询将有效地只扫描 2024 年的四个分区来计算结果。输出将显示从 109 到 116 的每个产品的总销售额。
最后，你可以通过输入以下命令退出 PostgreSQL 交互式终端：
\q
你将返回到常规的 shell 提示符。
总结
在本实验中，你学习了 PostgreSQL 表分区的基本知识。你成功创建了一个按日期范围分区的父表，为不同的时间段定义了具体的分区，并插入了自动路由到正确分区的数据。最重要的是，你使用了 EXPLAIN 命令来观察分区裁剪（partition pruning）的实际效果，展示了分区如何通过允许数据库只扫描部分数据来显著提高查询性能。这是一种高效管理大规模数据集（large-scale datasets）的强大技术。
