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

当每个查询、索引重建或数据保留任务都必须触及同一个庞大的关系时，大型PostgreSQL表会变得难以管理。声明式分区允许你将一个逻辑表拆分为多个较小的子表，这样PostgreSQL可以路由行，并为使用分区键的查询剪枝分区。

关键在于规划。当分区与你的查询过滤器和数据生命周期匹配时，它最有帮助；当分区键很少使用时，它可能会增加开销。

理解声明式分区

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

声明式分区的主要优势：

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

PostgreSQL中的分区策略

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

1. 范围分区

范围分区根据分区键中值的连续范围来划分数据。这非常适合时间序列数据、顺序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. 列表分区

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

何时使用：

• 地理区域（例如，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. 哈希分区

哈希分区根据分区键的哈希值来划分数据。当没有自然的范围或列表时，这有助于在分区之间均匀分布数据，从而平衡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. 选择适当的分区策略

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

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

3. 规划分区大小和数量

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

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

提示： 监控你的分区大小，并根据数据增长调整分区策略。如果需要，你可以分离并重新附加分区，甚至使用不同的策略重新创建分区。

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

在创建分区表时，你也可以定义默认分区或策略来处理不属于现有分区的数据。但是，通常建议显式创建分区，以避免意外的数据放置或错误。

示例： 使用 DEFAULT 分区来捕获范围分区中的意外值。

CREATE TABLE events (
    event_id BIGSERIAL,
    created_at DATE NOT NULL,
    payload JSONB
)
PARTITION BY RANGE (created_at);

CREATE TABLE events_2026_01 PARTITION OF events
    FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');

CREATE TABLE events_default PARTITION OF events DEFAULT;

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

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

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

• 分离分区： 你可以分离一个分区以归档其数据，或者完全删除它而不影响其他分区。

  -- 分离一个分区
  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;
  

• 删除分区： 对于不再需要查询的非常旧的数据。

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

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

6. 分区上的索引

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

• 父表上的分区索引： 在分区父表上声明的索引是虚拟的。PostgreSQL在分区上创建或附加匹配的索引；实际的索引数据存在于子索引中。
• 单个分区上的索引： 当一个分区需要不同的索引，或者当你将一个现有表作为分区附加时，你仍然可以按分区管理索引。

最佳实践： 在分区父表上创建通用索引，以便新分区继承预期的索引模式。对于异常情况和大型维护操作，使用按分区索引管理。

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

7. 一致使用声明式语法

在父表上使用 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 分区的迹象。如果查询计划显示它扫描了多个或所有分区（而它不应该这样做），则可能需要调整分区键或查询。

高级考虑

外键和唯一约束

• 外键： 现代PostgreSQL支持涉及分区表的外键，但锁行为和性能仍然需要在你使用的版本和模式上进行测试。
• 唯一约束： 分区表上的主键或唯一约束必须包含所有分区键列，并且分区键不能是表达式。这个限制允许PostgreSQL通过每个分区的索引来强制唯一性。

提示： 为了实现跨逻辑表的唯一性，将分区键包含在约束中。例如，对于按 country_code 的列表分区，使用 UNIQUE (country_code, customer_id)。

INSERT 性能

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

现有大型表的分区策略

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

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

这个过程应该仔细规划，在测试环境中进行测试，并在维护窗口期间执行，以最小化停机时间。

为查询和日历分区

当分区键出现在你最重要的过滤器中，并且与你保留或归档数据的方式匹配时，声明式分区效果最佳。从查询模式开始，据此选择范围、列表或哈希分区，并使用 EXPLAIN ANALYZE 验证剪枝。然后自动化分区的创建和退役，以便设计在第一个月的数据到达后仍然有效。