一、背景
随着业务数据的不断增长，单表数据量可能达到数亿甚至数十亿行，查询性能会显著下降。PostgreSQL 原生支持声明式分区（Declarative Partitioning），但不具备自动创建和维护分区的能力，需要你手动管理分区的创建、过期删除等操作。
pg_partman（PostgreSQL Partition Manager）是 PostgreSQL 社区最流行的分区管理扩展，它可以自动管理基于时间或 ID 的分区表，并配合 pg_partman_bgw（Background Worker）实现完全自动化的分区维护。
二、适用场景
| 场景 | 说明 |
| 日志类数据 | 按天/按周/按月自动创建分区，定期清理历史数据 |
| 订单/交易数据 | 按月分区，保留指定月份的数据 |
| 时序数据 | IoT 设备数据、监控指标按时间分区 |
| 审计数据 | 按季度/年度分区，满足合规保留要求 |
三、环境信息
| 项目 | 版本 |
| 云数据库 PostgreSQL | 15.x |
| pg_partman | 4.7.4 |
| pg_partman_bgw | 随 pg_partman 安装 |
五、pg_partman 核心组件
·create_parent()：为已有的分区父表注册自动管理，创建初始分区。
·run_maintenance()：手动触发维护（创建新分区、清理过期分区）。
·part_config：存储每个受管理父表的配置信息。
·show_partitions()：查看指定父表的所有分区。
六、pg_partman_bgw 后台工作进程
pg_partman_bgw 是一个 Background Worker 进程，自动定期执行 run_maintenance()，无需人工干预或外部定时任务。
八、开启 pg_partman_bgw 插件（控制台操作）
注意：说明：
1. 登录 云数据库 PostgreSQL 控制台。
2. 在实例列表单击目标实例 ID，进入实例管理页，选择参数设置页面。
3. 找到 shared_preload_libraries 参数。
4. 单击编辑，在可选值中选中 pg_partman_bgw。
5. 单击立即重启。
九、设置 pg_partman_bgw 关键参数（控制台操作）
注意：说明：
在控制台参数设置页面，设置以下三个参数：
| 参数名 | 是否必须 | 说明 | 示例值 |
| pg_partman_bgw.dbname | 必须 | BGW 进程要连接的目标数据库名。可以设置多个数据库，用逗号分隔 | mydb |
| pg_partman_bgw.role | 必须 | BGW 进程连接数据库使用的角色名。必须设置为非 postgres 的用户，否则内核会禁止连接 | myuser |
| pg_partman_bgw.interval | 推荐设置 | 自动维护的执行间隔（秒），默认3600（1小时） | 3600 |
十、安装 pg_partman 扩展（SQL 操作）
连接到目标数据库，执行以下 SQL：
— 创建 partman schema（推荐将 pg_partman 对象放在独立 schema）CREATE SCHEMA IF NOT EXISTS partman;– 安装 pg_partman 扩展CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;– 验证安装SELECT extname, extversion FROM pg_extension WHERE extname = 'pg_partman';
预期输出：
extname | extversion————+———— pg_partman | 4.7.4
十三、1. 创建分区父表
— 创建原生分区表（PARTITION BY RANGE）CREATE TABLE public.orders ( id BIGSERIAL, order_date TIMESTAMPTZ NOT NULL DEFAULT now(), customer_id INTEGER NOT NULL, amount NUMERIC(12,2) NOT NULL, status VARCHAR(20) DEFAULT 'pending', PRIMARY KEY (id, order_date)) PARTITION BY RANGE (order_date);– 创建业务索引CREATE INDEX idx_orders_customer ON public.orders(customer_id);CREATE INDEX idx_orders_status ON public.orders(status);
注意：说明：
注意：分区键必须包含在主键中。
十四、2. 注册 pg_partman 管理
— 使用 create_parent 注册分区管理– pg_partman v4.7.4 签名：create_parent(p_parent_table, p_control, p_type, p_interval, …)SELECT partman.create_parent( p_parent_table := 'public.orders', p_control := 'order_date', p_type := 'native', — 使用 PostgreSQL 原生分区 p_interval := '1 month', — 按月分区 p_premake := 4 — 预创建未来4个分区);
执行后，pg_partman 会自动创建：
·当前月份的分区。
·过去若干月的分区（基于当前时间）。
·未来4个月的分区（premake=4）。
·一个 DEFAULT 分区（接收不匹配任何分区的数据）。
十五、3. 验证分区创建
— 查看已创建的分区SELECT child.relname AS partition_name, pg_get_expr(child.relpartbound, child.oid) AS partition_boundFROM pg_inherits iJOIN pg_class parent ON i.inhparent = parent.oidJOIN pg_class child ON i.inhrelid = child.oidJOIN pg_namespace n ON parent.relnamespace = n.oidWHERE parent.relname = 'orders' AND n.nspname = 'public'ORDER BY child.relname;
输出示例：
partition_name | partition_bound—————————–+——————————————————————- orders_default | DEFAULT orders_p2025_12 | FOR VALUES FROM ('2025-12-01') TO ('2026-01-01') orders_p2026_01 | FOR VALUES FROM ('2026-01-01') TO ('2026-02-01') orders_p2026_02 | FOR VALUES FROM ('2026-02-01') TO ('2026-03-01') orders_p2026_03 | FOR VALUES FROM ('2026-03-01') TO ('2026-04-01') orders_p2026_04 | FOR VALUES FROM ('2026-04-01') TO ('2026-05-01')
十六、4. 使用 show_partitions 查看分区
SELECT * FROM partman.show_partitions('public.orders');
十七、按天分区 – 日志表
— 创建日志分区表CREATE TABLE public.app_logs ( id BIGSERIAL, log_time TIMESTAMPTZ NOT NULL DEFAULT now(), log_level VARCHAR(10) NOT NULL DEFAULT 'INFO', message TEXT, PRIMARY KEY (id, log_time)) PARTITION BY RANGE (log_time);– 注册 pg_partman 按天分区SELECT partman.create_parent( p_parent_table := 'public.app_logs', p_control := 'log_time', p_type := 'native', p_interval := '1 day', p_premake := 7 — 预创建未来7天的分区);
十八、设置数据保留策略
— 设置保留策略：只保留最近 3 个月的数据UPDATE partman.part_configSET retention = '3 months', retention_keep_table = true, — 过期分区解绑但保留表（可后续手动删除） retention_keep_index = true — 保留过期分区的索引WHERE parent_table = 'public.orders';
| retention 策略 | 说明 |
| retention_keep_table = true | 过期分区从父表解绑（DETACH），但不删除物理表 |
| retention_keep_table = false | 过期分区从父表解绑后直接 DROP |
注意：说明：
注意：生产环境建议：设置 retention_keep_table = true，先解绑再手动确认删除，避免数据误删。
十九、手动触发维护
— 对指定表执行维护SELECT partman.run_maintenance('public.orders');– 对所有受管理的表执行维护SELECT partman.run_maintenance();
20、part_config 配置参考
— 查看所有受管理表的配置SELECT parent_table, control, partition_interval, premake, automatic_maintenance, retention, infinite_time_partitionsFROM partman.part_config;
| 字段 | 说明 |
| parent_table | 受管理的分区父表名 |
| control | 分区键列名 |
| partition_interval | 分区间隔（如1 mon、1 day） |
| premake | 预创建的未来分区数 |
| automatic_maintenance | 自动维护开关（on/off） |
| retention | 保留策略（如3 months） |
| infinite_time_partitions | 是否允许无限分区 |
22、工作原理
pg_partman_bgw 是 PostgreSQL 的 Background Worker 进程，会按照 interval 间隔自动执行 partman.run_maintenance()，使用 role 角色连接 dbname 数据库。它会根据 partman.part_config 中的配置自动创建新分区（premake）和自动清理过期分区（retention）。
23、参数详解
| 参数 | 类型 | 默认值 | 说明 |
| pg_partman_bgw.dbname | string | (空) | 目标数据库名，多个库用逗号分隔 |
| pg_partman_bgw.role | string | postgres | 连接使用的角色。必须设置为非 postgres 用户 |
| pg_partman_bgw.interval | integer | 3600 | 维护间隔（秒） |
| pg_partman_bgw.analyze | boolean | on | 维护后是否自动 ANALYZE |
| pg_partman_bgw.jobmon | boolean | on | 是否集成 pg_jobmon 日志 |
24、关于 pg_partman_bgw.role 的特别说明
最容易被忽略但最关键的参数：
·社区默认行为：pg_partman_bgw 默认以 postgres 超级用户身份连接数据库。
·腾讯云限制：云数据库 PostgreSQL 内核禁用了 postgres 用户的直接连接（安全策略）。
·后果：如果不设置 pg_partman_bgw.role，BGW 进程会使用默认的 postgres 尝试连接，但被内核拒绝，导致自动维护完全无法工作。
·解决方案：必须在控制台将 pg_partman_bgw.role 设置为你的业务数据库用户（如创建扩展时使用的用户名）。
正确配置示例（在控制台参数设置中配置）：
pg_partman_bgw.dbname = 'mydb' — 目标数据库pg_partman_bgw.role = 'myuser' — 不能为 postgres！pg_partman_bgw.interval = 3600 — 每小时执行一次维护
25、验证 BGW 是否正常工作
— 检查 shared_preload_libraries 是否包含 pg_partman_bgwSHOW shared_preload_libraries;– 检查 BGW 参数SELECT name, setting, sourceFROM pg_settingsWHERE name LIKE 'pg_partman_bgw%'ORDER BY name;– 检查后台进程SELECT pid, backend_type, state, queryFROM pg_stat_activityWHERE backend_type LIKE '%partman%';
27、第一步：控制台配置（需重启实例）
1. 登录 云数据库 PostgreSQL 控制台。
2. 在实例列表单击目标实例 ID，进入实例管理页，选择参数设置页面。
3. 找到 shared_preload_libraries 参数。
4. 单击编辑，在可选值中选中 pg_partman_bgw。
5. 设置以下参数：
·pg_partman_bgw.dbname = <数据库名>。
·pg_partman_bgw.role = <业务用户名>（不能是 postgres）。
·pg_partman_bgw.interval = 3600。
6. 保存并单击立即重启，等待实例重启完成。
28、第二步：安装扩展
CREATE SCHEMA IF NOT EXISTS partman;CREATE EXTENSION IF NOT EXISTS pg_partman SCHEMA partman;
29、第三步：创建分区表
CREATE TABLE public.my_table ( id BIGSERIAL, created_at TIMESTAMPTZ NOT NULL DEFAULT now(), data JSONB, PRIMARY KEY (id, created_at)) PARTITION BY RANGE (created_at);
30、第四步：注册 pg_partman 管理
SELECT partman.create_parent( p_parent_table := 'public.my_table', p_control := 'created_at', p_type := 'native', p_interval := '1 month', p_premake := 4);
31、第五步：设置保留策略（可选）
UPDATE partman.part_config SET retention = '6 months', retention_keep_table = trueWHERE parent_table = 'public.my_table';
32、第六步：验证
— 查看分区SELECT * FROM partman.show_partitions('public.my_table');– 查看配置SELECT * FROM partman.part_config WHERE parent_table = 'public.my_table';– 手动测试维护SELECT partman.run_maintenance('public.my_table');
34、Q1: pg_partman_bgw 不工作，自动维护没有执行
检查清单：
1. shared_preload_libraries 是否包含 pg_partman_bgw？
2. pg_partman_bgw.dbname 是否设置为正确的数据库名？
3. pg_partman_bgw.role 是否设置为非 postgres 的用户？
4. 设置参数后是否重启了实例？
35、Q2: create_parent 报函数不存在
pg_partman v4.7.4 的 create_parent 需要4个必要参数：
— 正确写法（包含 p_type 参数）SELECT partman.create_parent( p_parent_table := 'public.my_table', p_control := 'created_at', p_type := 'native', — 不能省略！ p_interval := '1 month');– 错误写法（缺少 p_type）会导致函数签名不匹配
36、Q3: 数据插入报 no partition found for row
说明目标时间范围的分区不存在。解决方案：
1. 检查 premake 设置是否足够。
2. 手动执行 SELECT partman.run_maintenance(); 。
3. 确认分区范围覆盖插入数据的时间。
37、Q4: 如何查看 pg_partman_bgw 是否在运行
SELECT pid, backend_type, stateFROM pg_stat_activityWHERE backend_type LIKE '%partman%';
如果查询无结果，说明 BGW 进程未启动，请检查控制台参数配置。
38、注意事项
1. 分区键必须包含在主键中，PostgreSQL 原生分区的限制。
2. pg_partman_bgw.role 不能是 postgres，腾讯云内核安全限制。
3. 修改 shared_preload_libraries 需要重启，请在维护窗口操作。
4. 建议 retention_keep_table = true，先解绑再手动删除，防止误删。
5. premake 建议设置为4 ~ 7，确保有足够的未来分区。
6. 生产环境先手动测试 run_maintenance()，确认行为符合预期后再依赖 BGW 自动执行。