使用原生分区和 pg_partman 管理大型 PostgreSQL 表

Source: Dev.to

问题

随着数据库规模的扩大，存储大量时间序列数据的表很快会变得难以管理。

常见的问题包括：

• 查询变慢
• 索引体积增大
• 像 VACUUM 之类的维护操作耗时更长
• 老旧数据的管理变得复杂

PostgreSQL 提供 native PostgreSQL partitioning 来帮助解决这些问题，但手动管理分区会增加运维复杂度。

在本文中我们将探讨：

• native PostgreSQL partitioning 的工作原理
• 手动管理分区的运维挑战
• pg_partman 如何实现分区管理自动化

原生 PostgreSQL 分区

PostgreSQL 支持表分区，允许将一个大的逻辑表拆分为多个较小的物理表，称为 分区。

分区方式

Range
List
Hash

对于基于时间的数据，范围分区（range partitioning） 是最常见的做法。

示例：创建分区表

CREATE TABLE events (
    id         BIGSERIAL,
    created_at TIMESTAMP,
    data       JSONB
) PARTITION BY RANGE (created_at);

events 成为 父表。实际数据存放在子表（分区）中。

示例：创建分区

CREATE TABLE events_2026_03_20
    PARTITION OF events
    FOR VALUES FROM ('2026-03-20') TO ('2026-03-21');

CREATE TABLE events_2026_03_21
    PARTITION OF events
    FOR VALUES FROM ('2026-03-21') TO ('2026-03-22');

每个分区都是 PostgreSQL 内部的 物理表。

数据如何插入分区

应用仍然向 父表 插入数据：

INSERT INTO events (created_at, data)
VALUES ('2026-03-21 10:15:00', '{"event":"login"}');

PostgreSQL 会根据分区键自动将行路由到正确的分区。在本例中，行会存入 events_2026_03_21。

查询仍然针对父表执行：

SELECT * FROM events
WHERE created_at >= now() - interval '7 days';

PostgreSQL 会进行 分区裁剪（partition pruning），只扫描相关的分区。

为什么分区能提升性能

分区可以减少需要扫描的数据量：

查询最近 7 天的数据
        ↓
PostgreSQL 只扫描最近的分区
        ↓
跳过旧的分区

它还简化了以下操作：

• 归档历史数据
• 快速删除旧数据
• 管理索引大小

示例： 删除整整一个月的数据几乎是瞬间完成的：

DROP TABLE events_2024_03_01;

这比在单个大表中删除数百万行要快得多。

Source:

本地分区的运维挑战

虽然功能强大，手动分区管理会带来多个运维难题：

• 必须 提前创建 新分区
• 必须 手动删除 旧分区
• 必须手动实现 保留策略
• 缺失的分区会导致插入失败

场景：

Application inserts event at midnight
        ↓
New partition does not exist
        ↓
Insert fails

随着系统规模的扩大，手动处理分区变得愈发困难，自动化变得必不可少。

介绍 pg_partman

pg_partman 是一个 PostgreSQL 扩展，用于自动化分区管理。它基于原生分区并处理：

• 自动创建未来的分区
• 保留和删除旧分区
• 例行分区维护

这降低了手动管理分区表的运维负担。

Source:

pg_partman 的工作原理

pg_partman 管理一个 父分区表，并自动维护其分区。

简化工作流程

父表
      ↓
pg_partman 配置
      ↓
自动创建未来的分区
      ↓
可选的旧分区保留策略

示例： 对于按天分区的表，pg_partman 可以自动创建即将到来的分区，例如：

events_p2026_03_22
events_p2026_03_23
events_p2026_03_24

确保新插入始终拥有有效的分区。

使用 pg_partman 的好处

与手动管理相比，pg_partman 提供：

• 自动分区创建 – 再也不会出现缺失分区的错误
• 降低运维开销 – 减少手动脚本和检查
• 一致的保留策略 – 自动删除旧分区
• 提升可靠性 – 需要时分区始终存在

当 pg_partman 有用时

pg_partman 在处理 大规模追加式数据集 的工作负载时特别有用，例如：

• 事件日志
• 分析数据
• 应用活动跟踪
• 时间序列数据

在这些场景中，新数据会持续到达，而旧数据最终变得不那么重要。分区自动化有助于高效管理这种生命周期。

最终思考

PostgreSQL 的原生分区提供了强大的大数据集管理能力。然而，手动操作分区表可能会带来额外的运维复杂性。

像 pg_partman 这样的扩展通过自动化分区创建和维护，简化了此过程。

通过将 PostgreSQL 的原生分区功能与 pg_partman 的自动化相结合，团队可以更可靠地管理大型基于时间的数据集，并减少手动干预。