精通 DevOps 工具与最佳实践 - DevOps 知识中心 精通 DevOps 工具与最佳实践 - DevOps 知识中心

理解并实现PostgreSQL 14+中的声明式表分区

探索PostgreSQL 14+版本中原生声明式分区功能。本指南详细介绍范围分区、列表分区和哈希分区类型，提供创建和管理分区表的实用SQL示例。学习如何通过利用分区剪枝和高效维护策略，优化查询性能并简化超大数据集的数据管理。

理解并实现PostgreSQL 14+中的声明式表分区

当一个表在查询、清理、归档或删除操作中变得难以处理时，PostgreSQL分区值得考虑。常见的例子是每天接收数百万行数据且几乎总是按时间范围查询的事件表。没有分区，即使有良好的索引，也可能导致表维护成本高昂且难以老化数据。

声明式分区允许一个逻辑表将行路由到称为分区的较小物理表中。PostgreSQL 10引入了原生语法，后续版本改进了规划、剪枝、索引和维护行为。PostgreSQL 14+已经足够成熟，许多团队可以在不依赖基于触发器的继承方案的情况下使用分区，但它仍然需要精心设计。错误的分区键可能使系统更复杂而不会更快。

什么是声明式表分区？

声明式分区是一种数据库特性，允许您根据一组定义的规则将单个逻辑表（父表或分区表）拆分为多个物理表（子表或分区表）。每个分区保存父表数据的一个子集。分区键决定一行属于哪个分区。

声明式分区的主要优点包括：

  • 提高查询性能：过滤分区键的查询可以更快，因为PostgreSQL可以剪枝不包含匹配行的分区。
  • 简化数据管理：删除旧数据或归档等操作可以通过分离或删除单个分区来更高效地执行，而不是在单个大表上执行大规模的DELETE操作。
  • 简化维护：索引和清理可以在每个分区的基础上进行管理，减少对整个表的影响。
  • 更小的维护单元：分区级索引、分离操作和有针对性的清理工作可以减少常规维护的影响范围。

声明式分区的类型

PostgreSQL支持多种声明式分区方法，每种方法适用于不同的数据分布模式：

1. 范围分区

范围分区根据特定列（例如，日期、数字）中的连续值范围划分数据。

用例：适用于时间序列数据，如日志、事件数据或销售记录，在这些数据中您经常查询特定日期或数值范围内的数据。

示例：按sale_date列对sales表进行分区。

创建范围分区表

首先，创建父表，指定分区方法和键：

CREATE TABLE sales (
    sale_id SERIAL,
    product_name VARCHAR(100),
    sale_amount NUMERIC(10, 2),
    sale_date DATE NOT NULL
)
PARTITION BY RANGE (sale_date);

接下来，创建各个分区。每个分区使用FOR VALUES子句定义其包含的范围。

-- 2023年1月的销售分区。
-- 上界是排他的，因此包括1月31日。
CREATE TABLE sales_2023_01
    PARTITION OF sales ()
    FOR VALUES FROM ('2023-01-01') TO ('2023-02-01');

-- 2023年2月的销售分区
CREATE TABLE sales_2023_02
    PARTITION OF sales ()
    FOR VALUES FROM ('2023-02-01') TO ('2023-03-01');

-- 2023年3月的销售分区
CREATE TABLE sales_2023_03
    PARTITION OF sales ()
    FOR VALUES FROM ('2023-03-01') TO ('2023-04-01');

提示：定义范围时，确保它们连续且覆盖所有可能的值。避免重叠范围。TO值是排他的。

2. 列表分区

列表分区根据列中的离散值列表划分数据。

用例：适用于具有固定、已知值集的列，如地理区域、状态代码或产品类别。

示例：按region列对orders表进行分区。

创建列表分区表

使用PARTITION BY LIST定义父表：

CREATE TABLE orders (
    order_id SERIAL,
    customer_name VARCHAR(100),
    order_total NUMERIC(10, 2),
    region VARCHAR(50) NOT NULL
)
PARTITION BY LIST (region);

为特定区域创建分区：

-- '北美'的订单分区
CREATE TABLE orders_north_america
    PARTITION OF orders ()
    FOR VALUES IN ('North America');

-- '欧洲'的订单分区
CREATE TABLE orders_europe
    PARTITION OF orders ()
    FOR VALUES IN ('Europe');

-- '亚洲'的订单分区
CREATE TABLE orders_asia
    PARTITION OF orders ()
    FOR VALUES IN ('Asia');

重要：如果您插入的region值与任何现有分区的IN列表不匹配，并且没有DEFAULT分区，插入将失败。您可以创建一个DEFAULT分区来捕获所有其他值。

创建默认分区

-- 未明确列出的任何区域的默认分区
CREATE TABLE orders_other
    PARTITION OF orders ()
    DEFAULT;

3. 哈希分区

哈希分区根据分区键的哈希值将数据分布到多个分区中。

用例：当您有大量数据并希望在没有明确范围或列表分布的情况下均匀分布数据时非常有用。它适用于负载均衡。

示例：按user_id对users表进行分区。

创建哈希分区表

使用PARTITION BY HASH定义父表并指定分区数量：

CREATE TABLE users (
    user_id BIGSERIAL,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100)
)
PARTITION BY HASH (user_id);

如果您不指定分区，PostgreSQL会自动为您创建分区，但通常建议显式创建它们，特别是当您希望控制分区的数量和命名时。

创建显式哈希分区

-- 创建4个哈希分区
CREATE TABLE users_p0
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 0);

CREATE TABLE users_p1
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 1);

CREATE TABLE users_p2
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 2);

CREATE TABLE users_p3
    PARTITION OF users
    FOR VALUES WITH (modulus 4, remainder 3);

注意：使用哈希分区时，您需要指定modulus（分区总数）和remainder（此分区是哪一个）。

实现声明式分区：最佳实践

  • 选择正确的分区键：分区键应与您最频繁的查询过滤器和数据管理操作对齐。一个好的键可以显著提高性能。
  • 考虑分区数量：分区太少可能无法提供足够的收益，而太多则会增加开销。目标是找到一个平衡可管理性和性能的数量。对于范围分区，请考虑您的数据增长率和保留策略。
  • 使用pg_partman进行自动化：对于范围分区，特别是时间序列数据，考虑使用像pg_partman这样的扩展。它可以自动创建新分区以及归档/删除旧分区，大大减少手动工作。
  • 战略性索引：索引按分区物理存储。在父表上创建索引会创建匹配的分区索引，但您仍应检查每个分区是否需要相同的索引模式。
  • 分区剪枝：确保您的查询通过将分区键包含在WHERE子句中利用分区剪枝。EXPLAIN命令可以显示是否发生了剪枝。
  • DEFAULT分区：对于列表分区，DEFAULT分区对于避免在意外出现新值时插入错误至关重要。
  • 唯一约束：分区表上的唯一约束或主键通常必须包含所有分区键列。这常常让初次设计者感到意外。
  • 数据类型：确保分区键的数据类型适当，并且在父表和子表之间保持一致。

管理分区

附加和分离分区

虽然分区直接通过CREATE TABLE ... PARTITION OF ...创建，但您也可以将现有表分离和附加为分区。这对于迁移数据或管理大型数据集非常有用。

分离分区：分离将分区转换为常规表，同时保留其数据。

-- 分离 sales_2023_01 分区
ALTER TABLE sales DETACH PARTITION sales_2023_01;

将表附加为分区：您可以附加一个符合父表模式且数据符合分区边界的常规表。

-- 假设 sales_2022_12 是一个与 sales 具有相同列的常规表
-- 并且只有2022年12月的行。
ALTER TABLE sales ATTACH PARTITION sales_2022_12
    FOR VALUES FROM ('2022-12-01') TO ('2023-01-01');

在附加大表之前，先添加一个匹配的CHECK约束。PostgreSQL可以使用该约束来避免扫描整个表以证明行符合分区边界。

删除分区

删除分区是一个快速操作，因为它只删除分区表，而不删除其中的数据（除非明确指定）。这比DELETE快得多。

-- 要删除分区，您可以直接删除子表
DROP TABLE sales_2023_01;

示例：通过分区剪枝提高查询性能

考虑前面按sale_date分区的sales表。

没有分区剪枝的查询（假设在非分区表上）：

SELECT SUM(sale_amount)
FROM sales
WHERE sale_date >= '2023-01-15' AND sale_date < '2023-01-20';

如果sales是一个巨大的未分区表，此查询将扫描整个表。然而，使用声明式分区：

-- 此查询将仅扫描 sales_2023_01 分区
SELECT SUM(sale_amount)
FROM sales
WHERE sale_date >= '2023-01-15' AND sale_date < '2023-01-20';

PostgreSQL的查询规划器识别出sale_date是分区键，并且指定的范围完全落在sales_2023_01分区内。因此，它将仅扫描该分区，大大减少I/O并提高性能。

要验证这一点，请使用EXPLAIN：

EXPLAIN SELECT SUM(sale_amount) FROM sales WHERE sale_date >= '2023-01-15' AND sale_date < '2023-01-20';

输出应仅显示相关分区，或者根据PostgreSQL版本和计划形状显示已移除的子计划。重要的标志是不相关的分区未被扫描。

实用设计清单

仅在您能明确命名操作收益时才进行分区。“表很大”本身并不足够。一个具有良好索引点查找的大表可能没问题。当大多数查询包含分区键、旧数据定期归档或删除，或者对单个大表的维护已经造成问题时，分区更有意义。

对于时间序列表，选择与您的查询和保留模式匹配的分区大小。每日分区对于非常高的数据摄入和短保留期很有用。对于中等事件量，每月分区通常更易于管理。太多的小分区可能会减慢规划并使维护变得嘈杂；太少的大分区可能无法解决原始问题。

在发布之前规划插入。如果行可能延迟到达，请保持旧分区足够长时间可用以接收它们。如果分区键可能包含意外值，请创建一个DEFAULT分区并监控它。默认分区应该是一个安全网，而不是被遗忘的数据悄悄积累数月的地方。

最后，使用真实的查询形状进行测试。当WHERE子句清晰地暴露分区键时，分区剪枝效果最好，例如sale_date >= '2023-01-01' AND sale_date < '2023-02-01'。将键包装在函数中会使剪枝更困难：

-- 对剪枝不太友好
WHERE date_trunc('month', sale_date) = DATE '2023-01-01';

-- 规划器更容易处理
WHERE sale_date >= DATE '2023-01-01'
  AND sale_date <  DATE '2023-02-01';

声明式分区既是查询工具，也是维护工具。使用得当，它使旧数据易于删除，热数据更易于扫描。随意使用，它会增加更多的表、更多的索引和更多的边缘情况。从访问模式开始，根据该模式选择分区键，并在完成设计之前验证计划。

对于现有的大表，不要在高峰流量期间进行有风险的一次性转换。常见的迁移路径是创建一个新的分区表，分块复制数据，通过应用程序逻辑或经过仔细测试的触发器保持新写入，然后在短暂的维护窗口内交换名称。具体方法取决于写入量和停机容忍度，但原则是相同的：证明复制，证明约束，并在接触生产之前演练切换。