PostgreSQL Partition Manager Extension（pg_partman）

作者：bytehouse

Oracle ACE、PostgreSQL ACE

10+年数据库架构与运维实战经验

公众号：bytehouse

墨天轮专栏：bytehouse

PostgreSQL Partition Manager Extension

PostgreSQL 分区管理器（pg_partman）是一款帮助简化基于时间、数值 / ID的表分区管理工作的扩展。它提供了丰富的配置选项，但核心常用的仅少数几个，因此实际使用远比初看简单，更比自行实现分区管理轻松得多。

从 5.0.1 版本开始，该扩展最低要求 PostgreSQL 14 版本，且不再支持基于触发器的分区方式，所有分区均通过 PostgreSQL 内置的声明式分区实现。支持基于时间和数值的范围分区；当分区间隔为 1 时，支持基于数值的列表分区。从 5.1 版本开始，实验性支持基于 numeric/decimal 数值类型的分区，但分区间隔仍必须为整数。欢迎针对数值分区功能提供反馈（无论正面或负面），以加速该特性退出实验阶段。

pg_partman 4.x 版本仍支持基于触发器的分区，目前已停止活跃开发，仅会在 PostgreSQL 14 结束支持前接收关键漏洞修复。如果分区是你基础设施中的核心环节，请尽快制定升级计划。

所有分区集都会自动创建默认分区，用于接收超出现有子分区边界的数据。check_default() 函数可用于监控默认分区中是否有数据写入，partition_data_* 系列函数可轻松将有效数据从默认分区迁移到对应子分区中。这比 “按需自动创建子分区” 更稳妥，避免产生成百上千个无用分区；也比 “插入超范围数据直接报错丢失数据” 更友好。

注意：未来子分区的创建仅基于分区集内的现有数据，默认情况下会忽略默认分区中的数据。建议将 premake（预创建）值设置得足够大，以覆盖预期的写入数据范围，详见下文配置值的详细说明。

Features

Child Table Property Inheritance

在本扩展中，子分区的绝大多数属性都继承自父表。但在声明式分区中，部分特性受 PostgreSQL 版本限制，无法从父表直接继承，因此 pg_partman 采用模板表来实现这一能力。下表展示了不同属性的继承管理方式，数字代表支持的最低 PostgreSQL 版本。未在表中列出的属性，均默认由父表继承。

如果属性由模板表管理，那么修改该属性后，不会自动回溯应用到已存在的子表，仅会对新创建的子表生效，已存在的子表需要手动修改。

权限与所有者默认不继承。如果通过 pg_partman 开启了继承，也仅在子表创建时生效，父表权限变更后不会自动同步（可通过 reapply_privileges() 手动同步）。除非你需要直接访问子表，否则无需开启该特性。如需开启，可设置 inherit_privileges 配置项。

如果你使用标识列（IDENTITY）实现序列功能，仅当通过父表插入数据时，序列的自动生成才会生效，直接向子表插入数据不会触发。

重要说明：

• 模板表特性只是为了加速声明式分区落地的临时方案。随着 PostgreSQL 内核对相关特性的支持完善，模板表的相关能力会快速从 pg_partman 中逐步淘汰。如果未来内核支持了原本由模板表管理的特性，pg_partman 会在后续版本中移除对应的模板表管理能力，因此大版本升级时请提前做好规划。
• 如果你需要为逻辑复制设置 REPLICA IDENTITY USING INDEX 属性，请注意：仅当索引创建在父表上（而非模板表）时，该特性才会被支持。因为无法限制 REPLICA IDENTITY 是在父表还是模板表上创建，无法确定哪个是 “正确” 的配置，因此仅选择父表作为配置来源，与其他标识继承方式保持一致。
• 非日志表（UNLOGGED）状态由 pg_partman 的模板表管理，因为从 PostgreSQL 18 开始，无法再为分区集的父表设置 unlogged 标记。在 18 版本之前，该特性的处理也并不一致。升级到 PG18 后，分区集父表的 unlogged 标记会被移除，但仍可在最底层的子表上设置该标记。

Time Zones

运行 pg_partman 维护操作时，务必保证所有相关系统的时区一致，尤其是基于时间的分区场景。pg_partman 函数的调用会使用客户端在调用时设置的时区，这与 libpq 客户端的通用工作方式一致。

强烈建议数据库系统始终使用 UTC 时区。这能极大简化所有时间相关问题的处理，尤其是能解决夏令时（DST）变更带来的无法规避的问题。例如，按小时分区时，夏令时切换会导致分区创建异常或缺失。除此之外，还需确保创建分区集、运行维护任务的客户端也设置为 UTC 时区。

Subpartitioning

pg_partman 支持多级子分区，但在 PostgreSQL 中，子分区的使用场景非常有限，除非单分区集数据量达到数百 TB、PB 级别，否则几乎不会带来任何性能提升。如果追求性能收益，优先调整分区间隔，而非使用子分区。子分区的核心价值在于数据组织和保留策略管理。

支持的分区组合包括：时间 -> 时间、ID->ID、时间 ->ID、ID-> 时间。子分区的层级没有硬性限制，但请保持合理层级，注意单继承集内表数量过多带来的性能影响。此外，如果单分区集内表数量过多，可能需要调高 postgresql.conf 中的 max_locks_per_transaction 参数（默认 64），否则可能出现共享内存问题，甚至导致数据库集群崩溃。

如果调用 run_maintenance() 进行全量维护时出现锁竞争，可将 part_config 表中的 automatic_maintenance 列设为 false，这样通用维护调用不会管理该子分区集。但你必须通过 run_maintenance(parent_table) 直接、高频地调用维护函数，以保证未来分区的正常创建。推荐使用 run_maintenance_proc() 存储过程，它会在每个分区集维护完成后自动提交，大幅降低锁竞争问题。

逻辑复制的发布 / 订阅功能不支持子分区。

更多信息详见下文 create_sub_partition() 和 run_maintenance() 函数说明。

Retention

如果无需永久保留旧分区的数据，pg_partman 提供了自动清理无用子分区的保留系统。默认情况下，过期分区仅会被分离（detach）/ 取消继承，而非实际删除，可通过配置修改该行为。同时也提供了数据导出能力，满足 “无需在库中保留但需归档存储” 的需求。

如需设置保留策略，只需在 part_config 表的 retention 列填入对应值：

• 对于基于时间的分区：填入 interval 类型值，仅包含数据早于该间隔的分区会被清理（可妥善处理保留间隔不是分区大小整数倍的场景）。值必须符合 PostgreSQL interval 数据类型规范，注意：无单位的纯数字会被转换为秒，因此建议使用 1 day、1 month、2 years 等明确格式。 参考文档： https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-INTERVAL-INPUT
• 对于基于 ID 的分区：填入整数类型值，分区的 ID 上限小于「当前最大 ID 值 - 保留值」时，会被清理。例如当前最大 ID 为 100，保留值为 30，则 ID 上限小于 70 的分区会被清理。始终以函数运行时的当前最大 ID 值为计算基准。

注意：对于子分区集，当父表的子分区被删除时，会级联删除该子分区下所有层级的子表。同时，pg_partman 管理的分区集必须始终保留至少一个子表，因此保留策略永远不会删除分区集的最后一个子表。

Constraint Exclusion

分区的核心优势之一是约束排除（Constraint Exclusion）特性（详见官方文档：http://www.postgresql.org/docs/current/static/ddl-partitioning.html#DDL-PARTITIONING-CONSTRAINT-EXCLUSION）。但绝大多数分区方案中，约束排除仅对分区键列生效；如果在 WHERE 条件中使用其他列，数据库会扫描所有子表，除非这些列也设置了对应的约束。而提前预判列值并创建约束，往往非常困难甚至无法实现。

pg_partman 提供了针对性能力：可对分区集中不再有数据修改的旧表（“旧表” 定义为早于 optimize_constraint 配置值的分区），基于列的当前最小 / 最大值自动创建 CHECK 约束。这能让约束排除特性在非分区键的查询中生效，大幅减少对旧子表的扫描，在超大分区集中能带来显著的查询性能提升。

注意：该特性会限制对应列的数据修改，因此仅适用于 “仅新数据插入、旧数据无变更” 的场景；如果数据会在全分区集内频繁插入 / 更新，该特性价值有限。同时提供了约束重建函数，当旧分区数据需要修改时，可快速重建约束。请勿重命名 pg_partman 管理的约束，否则会导致扩展无法正常管理。

更详细的示例可参考博客：http://www.keithf4.com/managing-constraint-exclusion-in-table-partitioning

添加约束可能会与表中数据产生锁竞争，也会导致 pg_partman 维护耗时变长。part_config(_sub) 表中的 constraint_valid 列可设置约束创建时是否为 NOT VALID 状态。设置为 NOT VALID 时，约束创建几乎瞬间完成，但约束排除特性仅在约束验证通过后才会生效。因此默认情况下，约束会以 VALID 状态创建。

注意：该特性可能无法在子分区中正常工作。仅能保证在一级分区中生效，多级子分区中，受间隔组合和 optimize_constraint 值影响，无法保证正常工作。例如：周分区 -> 日分区，日分区的 optimize_constraint 设为 7，就无法按预期工作，周分区约束会正常创建，但日分区子表大概率不会。

Time Interval Considerations

支持的最小时间间隔为 1 秒，上限由 PostgreSQL 支持的 timestamp 最小 / 最大值决定（详见：http://www.postgresql.org/docs/current/static/datatype-datetime.html）。

首次执行 create_partition() 创建分区集时：

• 小于 1 天的间隔，会向下取整确定首个分区的起始时间；
• 大于 1 分钟、小于 24 小时的间隔，会向下取整到最近的整点；
• 小于 1 分钟的间隔，会向下取整到最近的整分钟。

但无论如何，都会创建足够的分区以覆盖当前真实时间。这意味着 create_partition() 执行时，可能会创建比预期更多的历史分区，而未来分区可能未完全创建，首次执行 run_maintenance() 会补全缺失的未来分区。这是为了支持自定义时间间隔带来的特性。所有大于等于 24 小时的间隔，都会按预期正常创建分区。

注意：对于大于等于 100 年的间隔，扩展会使用世纪 / 千年的真实起始时间来确定分区名称和约束规则。例如，21 世纪和第 3 个千年始于 2001 年 1 月 1 日（而非 2000 年），同时不存在 “0 年”。

对于周分区，默认一周的起始时间为执行 create_partition() 的当天。例如，在周二或周五执行，那么所有子分区的时间边界都会从对应周二 / 周五开始，而非预期的周一或周日。最简单的解决方式是使用 date_trunc() 函数，通过 create_partition() 的 p_start_partition 参数指定周起始为周一，示例如下：

SELECT partman.create_partition('public.time_table', 'col3', '1 week', p_start_partition := to_char(date_trunc('week',CURRENT_TIMESTAMP), 'YYYY-MM-DD HH24:MI:SS'));

Naming Length Limits

PostgreSQL 的对象命名长度限制为 63 字节（注意：不是字符数）。如果创建的对象名超过该长度，会自动截断末尾字符以适配，这会导致依赖特定后缀的分区名出现异常。pg_partman 会自动处理所有子表名的长度适配，会截断父表名以保证后缀完整。

注意：如果父表名过长且高度相似，不同分区集的子表可能出现命名冲突。对于数值型分区，随着时间推移，分区后缀会越来越长，表名也会被更多截断。因此，尽管扩展会自动处理该边界情况，仍建议分区表的表名尽量简短。

Unique Constraints

PostgreSQL 的表继承机制中，父表的主键 / 唯一索引必须包含分区键列，否则无法创建。pg_partman 通过模板表，支持为非分区键列创建主键 / 唯一约束，但需注意：约束仅在单个子表内生效，无法跨整个分区集全局强制唯一。例如，应用层操作不当仍可能导致主键值在分区集内重复。

为此，pg_partman 附带了一个 python 脚本 check_unique_constraint.py，可用于监控并保证全局唯一性，详见下文【辅助脚本】章节。

Logging/Monitoring

pg_jobmon 扩展（https://github.com/omniti-labs/pg_jobmon）是可选依赖，用于分区维护的审计和监控。如果已安装并正确配置，pg_partman 会自动启用，无需额外设置。也可通过 part_config 表的 jobmon 列，或创建分区时的 create_partition() 参数，为每个分区集单独开启 / 关闭 jobmon。

注意：如果要对 pg_jobmon 自身的表做分区，必须将 create_partition() 的 jobmon 选项设为 false，否则会导致永久锁等待，因为 pg_jobmon 会尝试向正在分区的表写入数据。

默认情况下，函数连续 3 次运行失败，jobmon 会触发告警。这也是预创建（premake）默认值设为 4 的原因，无需额外配置 jobmon，就能在分区创建失败时及时收到告警并介入处理。如果在生产环境使用 pg_partman，强烈建议安装 jobmon，并配合第三方监控系统（Nagios、Circonus 等）配置告警，及时感知分区创建失败的情况。

Background Worker

pg_partman 的后台工作器（BGW）本质上是一个调度器，可自动执行 run_maintenance_proc() 存储过程，无需依赖外部调度器（如 cron 等）。目前其行为与直接调用 run_maintenance_proc() 完全一致，未来可能会扩展更多能力。

安装详见 README.md 文件。如果需要为特定分区集单独调用 run_maintenance()，仍需通过外部调度器手动执行。BGW 仅维护 part_config 表中 automatic_maintenance 设为 true 的分区集。BGW 的运行日志会输出到 PostgreSQL 常规日志文件中；如果将 log_min_messages 设为 "DEBUG1"，会输出更多详细日志。

重要：必须将 pg_partman_bgw 加入 shared_preload_libraries 配置中（需要重启数据库生效）。

postgresql.conf 中可配置的 BGW 参数如下：

• pg_partman_bgw.dbname 必填项。需要执行维护任务的数据库，多个数据库用逗号分隔。未设置时，BGW 不会执行任何操作。
• pg_partman_bgw.interval 两次维护调用的间隔秒数，默认 3600 秒（1 小时）。详见下文关于不同分区类型 / 间隔的建议值。
• pg_partman_bgw.role 执行维护任务的数据库角色，默认 "postgres"，仅支持单个角色名。
• pg_partman_bgw.analyze 作用与 run_maintenance() 的 p_analyze 参数一致，详见下文。设为 'on' 为开启，'off' 为关闭（默认关闭）。
• pg_partman_bgw.jobmon 作用与 run_maintenance() 的 p_jobmon 参数一致，详见下文。设为 'on' 为开启，'off' 为关闭（默认开启）。

如果主后台工作器进程意外崩溃，会设置为每 10 分钟尝试重启一次。如果 BGW 无法启动，请查看 PostgreSQL 日志排查问题。

Extension Objects

pg_partman 不强制要求超级用户权限。如需以非超级用户运行，执行 pg_partman 函数和维护任务的角色，必须拥有其管理的所有分区集的所有权，以及在分区集所在模式中创建对象的权限。为便于权限管理，建议创建一个专用的分区管理角色。

权限配置详见主 README.md 文件。

补充说明：PostgreSQL 支持在函数调用中为参数命名，可大幅简化调用、避免参数混淆。有默认值的参数无需传值。示例：

SELECT create_partition('schema.table', 'col1',  '1 day', p_start_partition := '2023-03-20');