pg_partman（分区管理器）

步骤一：安装插件并创建父表

首先，在您的数据库中安装pg_partman插件，然后创建一个用于分区的父表。

-- 1. 在当前数据库中启用 pg_partman 扩展
CREATE EXTENSION pg_partman;

-- 2. 创建一个父表，用于存储日志数据
-- PARTITION BY RANGE (created_at) 指定了该表将作为分区表，并使用 created_at 列进行范围分区
CREATE TABLE public.log_table (
    id bigint GENERATED BY DEFAULT AS IDENTITY,
    created_at timestamptz NOT NULL,
    message text
) PARTITION BY RANGE (created_at);

步骤二：初始化分区集

调用create_parent()函数，将log_table注册到pg_partman的管理体系中，并定义分区规则。

-- 调用 create_parent 函数初始化分区
-- public.log_table: 要管理的父表
-- created_at: 分区键列
-- '1 day': 分区间隔，表示每天创建一个新分区
SELECT create_parent(
    p_parent_table := 'public.log_table',
    p_control := 'created_at',
    p_interval := '1 day'
);

执行成功后，pg_partman会自动创建当前及未来几天的分区。您可以通过\d+ public.log_table命令查看已创建的分区。

步骤三：插入测试数据

向父表log_table中插入一些数据，数据会根据created_at的值自动路由到对应的分区中。

INSERT INTO public.log_table (created_at, message)
SELECT generate_series(
     now() - interval '4 days',
     now(),
     interval '1 hour'
 ),
 'This is a log message.';

步骤四：配置保留策略并验证清理

设置数据保留策略，让pg_partman自动清理过期数据。将配置log_table只保留最近2天的数据，并自动删除（DROP）过期的分区。

-- 1. 更新 part_config 表，为 log_table 设置保留策略
UPDATE part_config
SET
    retention = '2 days',             -- 保留周期为2天
    retention_keep_table = false      -- 不保留表结构，直接 DROP 过期的分区
WHERE parent_table = 'public.log_table';

-- 2. 手动执行维护任务，触发保留策略
CALL run_maintenance_proc();

-- 3. 验证：再次检查分区列表，确认2天前的分区已被删除
SELECT partition_schemaname, partition_tablename FROM show_partitions('public.log_table', 'DESC');

至此，您已成功配置了一个按天分区并自动清理的日志表。

场景一：按天分区的日志表（含自动清理）

此场景适用于日志、审计记录、物联网（IoT）时序数据等增长迅速且有明确生命周期的数据。

业务场景

按天对audit_logs表进行分区，数据保留30天，过期数据自动归档到另一个模式，而不是直接删除。

操作步骤

1. 创建父表和初始化分区集：

   -- 创建父表
   CREATE TABLE public.audit_logs (
       log_id uuid NOT NULL,
       event_time timestamptz NOT NULL,
       user_id text,
       details jsonb,
       PRIMARY KEY (event_time, log_id)
   ) PARTITION BY RANGE (event_time);
   
   -- 初始化分区集，按天分区
   SELECT create_parent(
       p_parent_table := 'public.audit_logs',
       p_control := 'event_time',
       p_interval := '1 day'
   );

2. 配置保留与归档策略：将超过30天的旧分区表移动到archive模式下，以便后续进行离线分析或备份。

   -- 创建用于归档的模式
   CREATE SCHEMA IF NOT EXISTS archive;
   
   -- 更新配置，设置保留策略
   UPDATE part_config
   SET
       retention = '30 days',          -- 保留30天
       retention_schema = 'archive'    -- 过期后移动到 archive 模式
   WHERE parent_table = 'public.audit_logs';

   当run_maintenance()运行时，任何完全早于30天前的分区表（例如audit_logs_p2023_10_01）将被从主分区集中分离，并将其模式从public更改为archive，变为archive.audit_logs_p2023_10_01。

场景二：按ID分区的业务表

此场景适用于用户表、商品表等以数字ID为主键，并希望通过分区将冷热数据分离的业务。

业务场景

对orders表按order_id进行分区，每个分区包含1000万个ID。

决策建议

p_interval的选择至关重要。应根据业务ID的增长速率和单个分区的合理大小来估算。如果每天新增100万订单，10000000的间隔意味着大约每10天创建一个新分区。

操作步骤

1. 创建父表和初始化分区集：

   -- 创建父表
   CREATE TABLE public.orders (
       order_id bigint NOT NULL,
       customer_id bigint,
       order_date timestamptz,
       amount numeric
   ) PARTITION BY RANGE (order_id);
   
   -- 初始化分区集，按ID范围分区
   -- 注意：p_interval 即使是数字，也必须以文本形式提供
   SELECT create_parent(
       p_parent_table := 'public.orders',
       p_control := 'order_id',
       p_interval := '10000000' -- 每个分区包含1000万个ID
   );

2. 配置保留策略（可选）：如果旧订单数据可以被归档或删除，可以配置基于ID的保留策略。

   -- 保留最近的5000万个订单数据
   -- 假设当前最大 order_id 是 100,000,000
   -- 该策略会删除 order_id < 50,000,000 的所有分区
   UPDATE part_config
   SET
       retention = '50000000',
       retention_keep_table = false
   WHERE parent_table = 'public.orders';

场景三：迁移现有大表到分区表

此场景适用于将一个已存在的、数据量巨大的单体表（如TB级别）平滑迁移到pg_partman管理的分区结构。

业务场景

将1 TB大小的sensor_data表迁移为按月分区的分区表，并最小化对在线业务的影响。

核心工具

partition_data_proc()过程。它能以小批量、独立事务的方式迁移数据，避免了长时间的表锁和事务积压。

操作步骤

1. 准备工作：创建父表并初始化分区集：

   -- 1. 将原表重命名，作为数据源
   ALTER TABLE public.sensor_data RENAME TO sensor_data_source;
   
   -- 2. 创建一个结构相同的新父表
   CREATE TABLE public.sensor_data (
       reading_id bigserial,
       device_id text,
       ts timestamptz NOT NULL,
       value double precision
   ) PARTITION BY RANGE (ts);
   
   -- 3. 初始化分区集
   SELECT create_parent('public.sensor_data','ts','1 month');

2. 执行数据迁移：使用partition_data_proc将sensor_data_source的数据分批迁移到新的分区表sensor_data中。

   -- 调用数据迁移过程
   -- p_source_table: 指定数据源表
   -- p_interval: 每个批次处理的数据时间范围，建议小于或等于分区间隔。此处设为'1 day'，表示每天的数据在一个事务中处理。
   -- p_wait: 每个批次之间的等待时间（秒），用于降低对I/O和CPU的持续压力。
   CALL partition_data_proc(
       p_parent_table := 'public.sensor_data',
       p_source_table := 'public.sensor_data_source',
       p_interval := '1 day',
       p_wait := 1
   );

   该过程会循环执行，直到sensor_data_source表中的所有数据都被迁移完毕。迁移过程中，pg_partman会按需自动创建所需的分区。

3. 收尾工作：迁移完成后，验证数据一致性，然后可以安全地删除原数据源表sensor_data_source。在迁移期间，应用可以继续向新的分区父表sensor_data写入新数据。

分区类型与间隔

分区类型

pg_partman基于PostgreSQL的声明式分区，主要支持以下分区方式：

• 范围分区（Range Partitioning）：适用于连续的数据，如时间戳或序列ID。这是最常用的分区类型。

• 列表分区（List Partitioning）：仅在基于数字的ID分区且间隔为1时支持。

分区间隔（p_interval）

此参数定义了每个子分区所覆盖的数据范围。

• 基于时间：接受任何有效的interval值，如'1 hour'、'1 day'、'1 week'或'1 month'。

  说明

  • 支持的最小时间间隔为1秒，上限则受限于PostgreSQL所支持的最小和最大时间戳值。

  • 首次运行create_parent()创建分区集时，在确定要创建的第一个分区时，小于一天的间隔会向下取整。

    • 小于24小时但大于1分钟的间隔将向下取整到最近的小时。

    • 小于1分钟的间隔则向下取整到最近的分钟。

    • 系统会创建足够多的分区以支持到当前真实时间。这意味着在运行create_parent()时，可能会创建比预期更多的先前分区，而未来分区可能不会全部创建。首次运行run_maintenance()时会修复缺失的未来分区。这是由于需要支持自定义时间间隔所导致的。对于大于或等于24小时的间隔，设置将会如预期一样进行。

  • 对于等于或大于100年的间隔，插件将使用世纪或千年的实际起始点来确定分区名称和约束规则。例如，21世纪和第3个千年是从2001年01月01日开始的（不是2000年），这也意味着没有0年。

• 基于数字/ID：接受一个表示范围大小的整数，但需以文本形式提供，如'1000000'。

子分区（Sub-partitioning）

子分区允许在已有的分区上创建更细粒度的分区，形成多级分区结构（如按年分区，每年再按月子分区）。其主要用于数据组织和生命周期管理，例如对一个巨大的年分区进行按月归档。对于性能提升，子分区通常效果有限，优先考虑调整顶层分区的间隔。

数据保留策略（Retention）

pg_partman通过run_maintenance()自动执行数据保留策略，管理旧分区的生命周期。核心配置参数如下：

约束排除（Constraint Exclusion）

约束排除是分区表提升查询性能的关键特性。当查询的WHERE条件可以明确排除某些分区时，查询优化器会跳过对这些分区的扫描。

pg_partman提供了在非分区键列上添加CHECK约束的功能，以增强约束排除的效果。例如，一个按created_at分区的表，如果经常有WHERE device_id = 'A'的查询，可以为旧分区（数据不再变化）的device_id列添加约束。

核心配置参数如下：

• constraint_cols：一个文本数组，指定要在哪些列上创建CHECK约束。例如'{device_id, user_id}'。

• optimize_constraint：一个整数，定义对多老的分区应用约束。默认值30表示对30个分区间隔之前的旧分区应用约束。

• constraint_valid：在表上添加约束可能会与表中已有的数据产生冲突，也可能导致pg_partman的维护操作耗时较长。可以设置这些约束在创建时是否为不生效。虽然这样可以让约束的创建几乎瞬间完成，但在验证之前无法使用约束排除。因此，默认情况下约束会被创建为有效的（valid）。

模板表与属性继承

由于PostgreSQL声明式分区对某些对象属性（如非分区键上的主键/唯一索引、autovacuum设置等）的继承支持不完善，pg_partman使用模板表机制来确保新创建的分区能正确继承这些属性。下表展示了pg_partman是如何管理特定属性继承的。如果某个属性未在表中列出，则表示它通过父表进行管理。

• 属性：当您调用create_parent()时，pg_partman会自动创建一个名为parent_table_template的表。您对此模板表所做的任何ALTER TABLE修改（如添加索引、更改autovacuum参数）都会在未来创建新分区时被应用。

  说明

  对模板表的修改不会追溯应用于已存在的分区。如需修改，需要手动对旧分区执行 ALTER 操作。

• 权限和所有权：默认情况下，权限和所有权不会被继承。如果通过pg_partman启用了该功能，请注意这种继承仅发生在分区创建时，更改后不会自动应用（可通过reapply_privileges()重新应用）。除非你需要直接访问分区，否则通常不需要启用此功能。如果你需要，可以设置inherit_privileges选项。

  说明

  如果您使用IDENTITY特性来管理序列，则只有在通过父表插入数据时，该特性才支持自动生成新的序列值；而直接向分区插入数据时，该特性不予支持。

自动维护

pg_partman的核心维护任务（创建新分区和执行保留策略）由run_maintenance()函数或run_maintenance_proc()过程执行。在PolarDB PostgreSQL版中，推荐使用内置的后台工作进程（Background Worker）来自动化此过程，它会根据您设置的间隔自动调用run_maintenance_proc()。

手动维护

在某些情况下，您可能需要手动触发维护，例如在调试或需要对特定表进行精细控制时。

• CALL run_maintenance_proc(); 推荐使用此过程。它会为part_config中所有automatic_maintenance为on的分区集执行维护，并在每个分区集维护完成后自动提交事务，减少锁争用。

• SELECT run_maintenance('public.my_table');如果只想对单个分区集执行维护，可以调用此函数并传入父表名。

监控与告警

在生产环境中，需监控分区维护是否正常运行，以防因分区未及时创建而导致数据写入失败。part_config表中的maintenance_last_run字段记录了每个分区集最后一次成功运行维护的时间戳。这是一个关键的监控指标。

-- 查询所有自动维护的分区集中，超过2倍维护间隔仍未成功运行的
SELECT parent_table
FROM part_config
WHERE
    automatic_maintenance = 'on'
    AND maintenance_last_run < (now() - (SELECT setting::interval * 2 FROM pg_settings WHERE name = 'pg_partman_bgw.interval'));

如果此查询返回任何行，意味着对应的分区集维护可能已中断，需要立即介入排查。

创建分区

此类函数用于初始化分区集和定义子分区。

create_parent(
    p_parent_table text
    , p_control text
    , p_interval text
    , p_type text DEFAULT 'range'
    , p_epoch text DEFAULT 'none'
    , p_premake int DEFAULT 4
    , p_start_partition text DEFAULT NULL
    , p_default_table boolean DEFAULT true
    , p_automatic_maintenance text DEFAULT 'on'
    , p_constraint_cols text[] DEFAULT NULL
    , p_template_table text DEFAULT NULL
    , p_jobmon boolean DEFAULT true
    , p_date_trunc_interval text DEFAULT NULL
    , p_control_not_null boolean DEFAULT true
    , p_time_encoder text DEFAULT NULL
    , p_time_decoder text DEFAULT NULL
)
RETURNS boolean

create_sub_parent(
    p_top_parent text
    , p_control text
    , p_interval text
    , p_type text DEFAULT 'range'
    , p_default_table boolean DEFAULT true
    , p_declarative_check text DEFAULT NULL
    , p_constraint_cols text[] DEFAULT NULL
    , p_premake int DEFAULT 4
    , p_start_partition text DEFAULT NULL
    , p_epoch text DEFAULT 'none'
    , p_jobmon boolean DEFAULT true
    , p_date_trunc_interval text DEFAULT NULL
    , p_control_not_null boolean DEFAULT true
    , p_time_encoder text DEFAULT NULL
    , p_time_decoder text DEFAULT NULL
)
RETURNS boolean

create_partition_time(
    p_parent_table text
    , p_partition_times timestamptz[]
    , p_start_partition text DEFAULT NULL
)
RETURNS boolean

create_partition_id(
    p_parent_table text
    , p_partition_ids bigint[]
    , p_start_partition text DEFAULT NULL
)
RETURNS boolean

迁移数据

此类函数用于将存量数据或误入默认分区的数据迁移到正确的分区中。

partition_data_time(
    p_parent_table text
    , p_batch_count int DEFAULT 1
    , p_batch_interval interval DEFAULT NULL
    , p_lock_wait numeric DEFAULT 0
    , p_order text DEFAULT 'ASC'
    , p_analyze boolean DEFAULT true
    , p_source_table text DEFAULT NULL
    , p_ignored_columns text[] DEFAULT NULL
)
RETURNS bigint

partition_data_id(
    p_parent_table text
    , p_batch_count int DEFAULT 1
    , p_batch_interval bigint DEFAULT NULL
    , p_lock_wait numeric DEFAULT 0
    , p_order text DEFAULT 'ASC'
    , p_analyze boolean DEFAULT true
    , p_source_table text DEFAULT NULL
    , p_ignored_columns text[] DEFAULT NULL
)
RETURNS bigint

partition_data_proc (
    p_parent_table text
    , p_loop_count int DEFAULT NULL
    , p_interval text DEFAULT NULL
    , p_lock_wait int DEFAULT 0
    , p_lock_wait_tries int DEFAULT 10
    , p_wait int DEFAULT 1
    , p_order text DEFAULT 'ASC'
    , p_source_table text DEFAULT NULL
    , p_ignored_columns text[] DEFAULT NULL
    , p_quiet boolean DEFAULT false
)

维护分区

此类函数用于日常的分区管理、监控和信息查询。

run_maintenance(
    p_parent_table text DEFAULT NULL
    , p_analyze boolean DEFAULT false
)
RETURNS void

run_maintenance_proc(
    p_wait int DEFAULT 0
    , p_analyze boolean DEFAULT NULL
)

check_default(
    p_exact_count boolean DEFAULT true
)

show_partitions(
    p_parent_table text
    , p_order text DEFAULT 'ASC'
    , p_include_default boolean DEFAULT false
)
RETURNS TABLE (
    partition_schemaname text
    , partition_tablename text
)

show_partition_name(
    p_parent_table text
    , p_value text
    , OUT partition_schema text
    , OUT partition_table text
    , OUT suffix_timestamp timestamptz
    , OUT suffix_id bigint
    , OUT table_exists boolean
)
RETURNS record

show_partition_info(
    p_child_table text
    , p_partition_interval text DEFAULT NULL
    , p_parent_table text DEFAULT NULL
    , OUT child_start_time timestamptz
    , OUT child_end_time timestamptz
    , OUT child_start_id bigint
    , OUT child_end_id bigint
    , OUT suffix text
)
RETURNS record

销毁分区

此类函数用于撤销分区结构或根据保留策略删除旧分区。

undo_partition(
    p_parent_table text
    , p_target_table text
    , p_loop_count int DEFAULT 1
    , p_batch_interval text DEFAULT NULL
    , p_keep_table boolean DEFAULT true
    , p_lock_wait numeric DEFAULT 0
    , p_ignored_columns text[] DEFAULT NULL
    , p_drop_cascade boolean DEFAULT false
    , OUT partitions_undone int
    , OUT rows_undone bigint)
RETURNS record

undo_partition_proc(
    p_parent_table text
    , p_target_table text DEFAULT NULL
    , p_loop_count int DEFAULT NULL
    , p_interval text DEFAULT NULL
    , p_keep_table boolean DEFAULT true
    , p_lock_wait int DEFAULT 0
    , p_lock_wait_tries int DEFAULT 10
    , p_wait int DEFAULT 1
    , p_ignored_columns text[] DEFAULT NULL
    , p_drop_cascade boolean DEFAULT false
    , p_quiet boolean DEFAULT false
)

drop_partition_time(
    p_parent_table text
    , p_retention interval DEFAULT NULL
    , p_keep_table boolean DEFAULT NULL
    , p_keep_index boolean DEFAULT NULL
    , p_retention_schema text DEFAULT NULL
    , p_reference_timestamp timestamptz DEFAULT CURRENT_TIMESTAMP
)
RETURNS int

drop_partition_id(
    p_parent_table text
    , p_retention bigint DEFAULT NULL
    , p_keep_table boolean DEFAULT NULL
    , p_keep_index boolean DEFAULT NULL
    , p_retention_schema text DEFAULT NULL
)
RETURNS int

part_config

此表是pg_partman的主配置中心，存储所有顶级父表的分区策略和维护设置。

part_config_sub

此表用于存储子分区的配置。其结构与part_config基本一致，但有一个关键区别：它使用sub_parent列来标识其所属的上一级父表。

• sub_parent（text）：子分区集的父表名，该表本身是顶级父表的一个分区。

• 其他列：表中所有其他的列（如partition_interval、retention、premake等）的含义与part_config中完全相同，但其作用域是当前子分区集。

您无需在此重复查看所有列的定义。只需理解，当pg_partman维护一个子分区集时，它会从part_config_sub表中查找以sub_parent为键的配置行，并应用该行中定义的策略。