PostgreSQL 表分区

介绍

在这个实验中，你将学习如何在 PostgreSQL 中实现表分区。目标是将一个大型表分割成更小、更易于管理的部分，以提高查询性能并简化数据管理。

你将从设置一个名为 sales 的范围分区表开始，该表按照 sale_date 列进行分区。这包括创建主 sales 表，然后为不同的日期范围定义分区。接下来，你将把数据插入到 sales 表中，这些数据将自动路由到正确的分区，并跨分区表进行查询。

Skills Graph

创建 sales 表和初始分区

在这一步中，你将创建主 sales 表和 2023 年的初始分区。分区将一个大型表分割成更小、更易于管理的部分，从而提高查询性能。

首先，以 postgres 用户身份连接到 PostgreSQL 数据库。打开终端并使用以下命令：

sudo -u postgres psql

现在你应该在 PostgreSQL 交互式终端中。

接下来，创建 sales 表。此表将存储销售数据，包括销售日期、产品 ID 和销售金额。

CREATE TABLE sales (
    sale_id SERIAL PRIMARY KEY,
    sale_date DATE NOT NULL,
    product_id INTEGER,
    sale_amount DECIMAL(10, 2)
) PARTITION BY RANGE (sale_date);

此命令创建 sales 表，并指定它将按 sale_date 列的范围进行分区。

现在，为 2023 年创建分区，将该年划分为四个季度：

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');

这些命令为 sales 表创建四个分区，每个分区覆盖 2023 年的一个季度。

要验证表和分区是否已成功创建，请列出所有表：

\dt

你应该在输出中看到 sales 表及其分区（sales_2023_q1、sales_2023_q2、sales_2023_q3、sales_2023_q4）。

定义 2024 年的分区

既然你已经创建了 sales 表和 2023 年的分区，让我们为 2024 年添加分区。这确保你可以存储全年的销售数据。

你应该仍然在前面步骤的 PostgreSQL 交互式终端中。如果不是，请使用以下命令重新连接：

sudo -u postgres psql

为 sales 表创建 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');

这些命令为 sales 表创建四个新的分区，覆盖 2024 年的每个季度。

要验证新的分区是否已成功创建，请再次列出所有表：

\dt

现在你应该在输出中看到 sales 表、2023 年的分区和新的 2024 年分区。

将数据插入到 sales 表中

在这一步中，你将把示例数据插入到 sales 表中。PostgreSQL 将根据 sale_date 自动将数据路由到正确的分区。

你应该仍然在 PostgreSQL 交互式终端中。如果不是，请使用以下命令重新连接：

sudo -u postgres psql

将以下示例数据插入到 sales 表中：

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 语句添加了 2023 年和 2024 年各种日期的销售记录。PostgreSQL 将根据 sale_date 自动将每个记录路由到适当的分区。

要验证数据是否已正确插入，请查询 sales 表以计算记录总数：

SELECT COUNT(*) FROM sales;

输出应显示总共 16 条记录。

查询分区后的 sales 表

在这一步中，你将查询分区后的 sales 表以检索数据。你可以像查询单个表一样查询整个表，并且 PostgreSQL 将优化查询以仅访问相关的分区。

你应该仍然在 PostgreSQL 交互式终端中。如果不是，请使用以下命令重新连接：

sudo -u postgres psql

要从整个 sales 表中检索所有销售记录，请使用以下 SELECT 语句：

SELECT * FROM sales;

此查询将返回 sales 表所有分区中的所有数据。

你还可以使用 WHERE 子句来过滤数据。例如，要检索 2023 年的所有销售记录：

SELECT * FROM sales WHERE sale_date >= '2023-01-01' AND sale_date < '2024-01-01';

PostgreSQL 将使用分区键（sale_date）来确定要访问的分区，从而提高查询性能。

让我们尝试另一个查询，以查找 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;

此查询计算 2024 年内每个 product_id 的 sale_amount 总和。

最后，退出 PostgreSQL 交互式终端：

\q

总结

在这个实验中，你已经学习了如何在 PostgreSQL 中实现表分区。你创建了一个 sales 表，按日期范围对其进行了分区，为 2023 年和 2024 年定义了分区，将数据插入到表中，并查询了分区表。对于大型表，分区可以显著提高查询性能并简化数据管理。