大型 PostgreSQL 表声明式分区的最佳实践

大型 PostgreSQL 表的声明式分区的最佳实践

大型 PostgreSQL 表会成为重大的性能瓶颈。随着数据集的增长，INSERT、UPDATE、DELETE 和 SELECT 查询等操作可能会显著变慢，从而影响应用程序的响应速度和用户体验。PostgreSQL 在 11 版本中引入的声明式分区提供了一种强大的解决方案，通过将大型表划分为更小、更易于管理的块（称为分区），来管理这些表。当正确实施时，这种方法可以带来显著的性能提升、减少维护开销以及更高效的数据管理。

本文将指导您完成在 PostgreSQL 中实施声明式分区的最佳实践。我们将探讨不同的分区策略（范围、列表和哈希），并提供实用的示例和建议，以帮助您利用此功能来优化大型数据集的性能和可管理性。

理解声明式分区

声明式分区允许您将一个表定义为已分区表，指定分区键和策略。然后，PostgreSQL 会根据分区键的值自动将数据路由到适当的分区。这消除了对复杂触发器或手动数据管理的需要，使其成为比旧方法更清晰、更高效的解决方案。

声明式分区的关键优势：

改进的查询性能： 按分区键过滤的查询只需扫描相关的分区，从而大大减少了需要处理的数据量。
更快的加载速度： 批量加载操作可以直接定向到特定分区，从而提高效率。
简化的维护： 归档、删除旧数据或重新建立索引等操作可以在单个分区上执行，而不会影响整个表。
减少开销： 消除了对手动分区逻辑和相关维护的需要。

PostgreSQL 中的分区策略

PostgreSQL 为声明式分区提供了三种主要策略，每种策略适用于不同的用例：

1. 范围分区 (Range Partitioning)

范围分区根据分区键中值的连续范围来划分数据。这对于时间序列数据、顺序 ID 或值落在定义区间内的任何数据非常理想。

何时使用：
* 时间序列数据（例如，按日期/时间戳划分的日志、事件）。
* 顺序生成的 ID。
* 具有有序、连续值的数据。

示例： 按 sale_date 分区 sales 表。

-- 创建父分区表
CREATE TABLE sales (
    sale_id SERIAL,
    product_id INT,
    amount DECIMAL(10, 2),
    sale_date DATE NOT NULL
)
PARTITION BY RANGE (sale_date);

-- 为特定日期范围创建分区
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');

-- 插入数据会自动进入正确的分区
INSERT INTO sales (product_id, amount, sale_date) VALUES (101, 150.50, '2023-02-15');

2. 列表分区 (List Partitioning)

列表分区根据分区键中值的离散列表来划分数据。当您有一组固定、已知的类别或标识符时，这很有用。

何时使用：
* 地理区域（例如，country、state）。
* 产品类别。
* 用户角色或状态。

示例： 按 country_code 分区 customers 表。

-- 创建父分区表
CREATE TABLE customers (
    customer_id SERIAL,
    name VARCHAR(100),
    country_code CHAR(2) NOT NULL
)
PARTITION BY LIST (country_code);

-- 为特定国家/地区代码创建分区
CREATE TABLE customers_us PARTITION OF customers
    FOR VALUES IN ('US');

CREATE TABLE customers_ca PARTITION OF customers
    FOR VALUES IN ('CA');

CREATE TABLE customers_uk PARTITION OF customers
    FOR VALUES IN ('GB');

-- 插入数据会自动进入正确的分区
INSERT INTO customers (name, country_code) VALUES ('John Doe', 'US');

3. 哈希分区 (Hash Partitioning)

哈希分区根据分区键的哈希值来划分数据。当不存在自然的范围或列表时，这对于将数据平均分布到各个分区中非常有用，有助于平衡 I/O 负载。

何时使用：
* 当其他策略不适用时，用于平均分配数据。
* 避免 I/O 中的热点。
* 对写入负载均衡要求高的事务表。

示例： 按 order_id 分区 orders 表。

-- 创建父分区表
CREATE TABLE orders (
    order_id BIGSERIAL,
    user_id INT,
    order_total DECIMAL(10, 2)
)
PARTITION BY HASH (order_id);

-- 创建指定数量的分区（例如，4 个）
CREATE TABLE orders_part_1 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 0);
CREATE TABLE orders_part_2 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 1);
CREATE TABLE orders_part_3 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 2);
CREATE TABLE orders_part_4 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 3);

-- 插入数据会自动进入正确的分区
INSERT INTO orders (user_id, order_total) VALUES (500, 250.75);

实施声明式分区的最佳实践

有效实施分区需要仔细的规划和遵循最佳实践，以最大限度地发挥其优势。

1. 选择正确的分区键

分区键是最关键的决定。它直接影响查询性能和维护。选择一个在您最常用查询的 WHERE 子句中频繁使用的键。

对于时间序列数据： DATE、TIMESTAMP 列是范围分区的绝佳选择。
对于分类数据： country_code、status、region 等列适用于列表分区。
用于均匀分布： 一个基数高且经常在查询中使用的列，适用于哈希分区。

提示： 避免在很少在 WHERE 子句中使用的列或在分区之间没有不同值的列上进行分区，因为这可能导致查询扫描所有分区。

2. 选择合适的分区策略

如前所述，选择最符合您的数据和查询模式的策略（范围、列表、哈希）。

范围 (Range)： 用于有序、连续的数据。
列表 (List)： 用于离散、已知的类别。
哈希 (Hash)： 用于均匀的数据分布和负载平衡。

3. 规划分区的大小和数量

分区大小没有万能的答案。但是，请考虑以下几点：

分区过多过小： 会增加规划器和系统的开销。每个分区都有其元数据。
分区过少过大： 会抵消分区的性能优势。
理想大小： 目标是使分区足够大以提供性能优势，但又易于进行维护操作。一个常见的起点是将分区与逻辑时间单位（例如，时间序列数据的每日、每周、每月）或可管理的逻辑数据量对齐。

提示： 监控分区大小，并随着数据增长调整分区策略。如果需要，您可以分离（detach）和重新附加（reattach）分区，甚至用不同的策略重新创建分区。

4. 为未来数据定义分区策略

创建分区表时，您还可以定义默认分区或策略来处理未落入现有分区中的数据。然而，通常建议明确创建分区，以避免意外的数据放置或错误。

示例： 对哈希分区使用 DEFAULT 分区（需谨慎使用并考虑其对数据管理的影响）。

-- 这是 PostgreSQL 14+ 默认分区的示例
-- CREATE TABLE orders (
--     order_id BIGSERIAL,
--     user_id INT,
--     order_total DECIMAL(10, 2)
-- )
-- PARTITION BY HASH (order_id);
-- CREATE TABLE orders_part_1 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 0);
-- CREATE TABLE orders_part_2 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 1);
-- CREATE TABLE orders_part_3 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 2);
-- CREATE TABLE orders_part_4 PARTITION OF orders FOR VALUES WITH (MODULUS 4, REMAINDER 3);
-- CREATE TABLE orders_default PARTITION OF orders DEFAULT;

最佳实践： 为了清晰和控制，请手动为预期的数据范围/列表创建分区。请谨慎考虑 DEFAULT 分区，尤其是在列表或范围分区中，因为它们可能会积累意外的数据。

5. 管理分区的生命周期（归档/删除数据）

分区最大的优势之一是简化的数据生命周期管理。对于时间序列数据，归档或删除旧数据很常见。

分离分区 (Detaching Partitions)： 您可以分离分区以归档其数据或将其完全删除，而不会影响其他分区。

```sql
-- 分离一个分区
ALTER TABLE sales DETACH PARTITION sales_2023_q1;

-- 可选地，在删除之前归档分离的分区
-- CREATE TABLE sales_archive_2023_q1 (LIKE sales INCLUDING ALL);
-- INSERT INTO sales_archive_2023_q1 SELECT * FROM sales_2023_q1;

-- 删除分离的分区
DROP TABLE sales_2023_q1;
```
删除分区 (Dropping Partitions)： 针对不再需要查询的非常旧的数据。

sql -- 直接删除一个分区（如果未先分离，父表需要知道） DROP TABLE sales_2023_q1;

提示： 使用 cron 作业或其他调度工具（通常与脚本结合使用）来自动化新分区的创建以及旧分区的分离/删除。

6. 在分区上建立索引

分区表的索引可以在父表级别或单个分区级别上进行管理。

全局索引 (Global Indexes)： 在父表上定义。这些索引在所有分区中维护。它们可能很方便，但在插入期间可能开销较高，并且可能比本地索引慢。
本地索引 (Local Indexes)： 在各个分区上定义。它们通常对 INSERT 操作更快，并且对于针对特定分区的查询更有效。每个分区都将拥有自己的索引。

最佳实践： 对于大多数用例，建议使用本地索引以获得更好的性能和可管理性。它们允许独立管理，并且可能更有效率。在与非分区表上会使用的索引策略相匹配的分区上创建索引。

-- 示例：在分区上创建本地索引
CREATE INDEX ON sales_2023_q2 (product_id);

7. 考虑 `PARTITION BY` 与 `PARTITION OF` 语法演变

PostgreSQL 演化了创建分区表的语法。请确保您使用的是适用于您的 PostgreSQL 版本的语法。从 11 版本开始，在父表上使用 PARTITION BY，在子表上使用 PARTITION OF ... FOR VALUES 是标准的声明式方法。

8. 监控和分析查询计划

实施分区后，监控查询性能至关重要。使用 EXPLAIN ANALYZE 来验证查询是否正确地进行了分区修剪（即只扫描相关的分区）。

EXPLAIN ANALYZE SELECT * FROM sales WHERE sale_date BETWEEN '2023-02-01' AND '2023-02-28';

在 EXPLAIN 输出中查找查询规划器只考虑 sales_2023_q1 分区的迹象。如果查询计划显示它扫描了多个或所有分区（本不应该时），则可能需要调整您的分区键或查询。

高级注意事项

外键和唯一约束

外键： 外键约束只能定义在叶子分区上，而不能定义在父分区表上。这意味着您需要在每个相关的分区上定义外键。
唯一约束： 与外键类似，唯一约束也只能定义在叶子分区上。要在整个表上强制执行唯一性，您需要在每个分区上在分区键本身上定义一个唯一约束，并可能在父表上使用包含分区键的 UNIQUE INDEX。

提示： 对于跨整个表的唯一性，请考虑在叶子分区的唯一约束中添加分区键。例如，对于按 country_code 进行的列表分区，使用 UNIQUE (country_code, customer_id)。

`INSERT` 性能

虽然分区通常会提高 SELECT 性能，但 INSERT 性能可能会受到影响。如果分区键分布不均匀或分区逻辑复杂，插入可能会产生一些开销，因为 PostgreSQL 需要确定正确的分区。哈希分区通常有利于分散写入负载。

现有大型表的 I/O 策略

对现有的大型表进行分区可能是一个复杂的操作。它通常涉及：

创建新的分区表结构。
为历史数据创建分区。
将数据从旧表复制到新的分区表（可能分批进行）。
将应用程序的读/写切换到新的分区表。
删除旧表。

此过程应经过仔细规划，在暂存环境中进行测试，并在维护窗口期间执行，以最大限度地减少停机时间。

结论

PostgreSQL 中的声明式分区是管理大型数据集和提高查询性能的强大功能。通过仔细选择分区键、策略并有效管理分区，您可以释放出显著的优势。请记住规划您的分区方案、监控性能，并随着数据的演变调整您的策略。遵循这些最佳实践将确保即使在扩展时，您的 PostgreSQL 数据库也能保持高性能和易于管理。