PostgreSQL pg_partman分区管理

核心概念

pg_partman是PostgreSQL的一个扩展，用于自动化管理分区表，支持时间分区和值分区。pg_partman分区管理主要涉及以下核心概念：

分区类型：时间分区（按天、周、月、年等）和值分区（按列表或范围）
分区策略：预设分区、自动创建和删除分区
分区维护：自动更新分区边界、清理旧分区
分区切换：将普通表转换为分区表
分区监控：监控分区状态和维护任务

安装与配置

1. 安装pg_partman扩展

sql

-- 安装扩展
CREATE EXTENSION pg_partman;

-- 查看扩展版本
SELECT partman_version();

2. 配置pg_partman

sql

-- 查看pg_partman配置参数
SELECT 
    name,
    setting,
    unit,
    short_desc
FROM 
    pg_settings
WHERE 
    name LIKE 'partman%';

-- 修改pg_partman配置
ALTER SYSTEM SET partman_bgw.interval = 3600; -- 后台工作进程运行间隔（秒）
ALTER SYSTEM SET partman_bgw.role = 'postgres'; -- 后台工作进程运行角色
ALTER SYSTEM SET partman_bgw.dbname = 'testdb'; -- 后台工作进程监控的数据库
-- 重新加载配置
SELECT pg_reload_conf();

3. 初始化pg_partman后台工作进程

sql

-- 启用后台工作进程
SELECT partman.create_parent(
    p_parent_table => 'public.test_table',
    p_control => 'created_at',
    p_type => 'native',
    p_interval => 'daily',
    p_start_partition => '2023-01-01',
    p_premake => 4
);

-- 检查后台工作进程状态
SELECT * FROM partman.partman_bgw;

创建分区表

1. 时间分区表

sql

-- 创建父表
CREATE TABLE public.test_table (
    id serial primary key,
    created_at timestamp not null,
    data text
);

-- 使用pg_partman创建时间分区表
SELECT partman.create_parent(
    p_parent_table => 'public.test_table',
    p_control => 'created_at',
    p_type => 'native',
    p_interval => 'daily',
    p_start_partition => '2023-01-01',
    p_premake => 4,
    p_automatic_maintenance => 'on'
);

-- 查看创建的分区
SELECT * FROM partman.show_partitions('public.test_table');

2. 值分区表

sql

-- 创建父表
CREATE TABLE public.customer (
    id serial primary key,
    customer_type text not null,
    name text not null,
    email text
);

-- 使用pg_partman创建值分区表
SELECT partman.create_parent(
    p_parent_table => 'public.customer',
    p_control => 'customer_type',
    p_type => 'native',
    p_interval => 'list',
    p_automatic_maintenance => 'on'
);

-- 添加值分区
SELECT partman.append_partition('public.customer', ARRAY['premium', 'standard', 'basic']);

-- 查看创建的分区
SELECT * FROM partman.show_partitions('public.customer');

3. 范围分区表

sql

-- 创建父表
CREATE TABLE public.order (
    id serial primary key,
    order_id bigint not null,
    order_date timestamp not null,
    amount numeric(10,2)
);

-- 使用pg_partman创建范围分区表
SELECT partman.create_parent(
    p_parent_table => 'public.order',
    p_control => 'order_id',
    p_type => 'native',
    p_interval => '1000000',
    p_start_partition => '1',
    p_premake => 2,
    p_automatic_maintenance => 'on'
);

-- 查看创建的分区
SELECT * FROM partman.show_partitions('public.order');

分区管理

1. 手动管理分区

sql

-- 手动创建新分区
SELECT partman.run_maintenance('public.test_table');

-- 手动添加分区
SELECT partman.append_partition('public.test_table', ARRAY['2023-02-01']);

-- 删除旧分区
SELECT partman.drop_partition('public.test_table', 'p_2023_01_01');

-- 合并分区
SELECT partman.merge_partitions('public.test_table', 'p_2023_01_01', 'p_2023_01_07');

-- 拆分分区
SELECT partman.split_partition('public.test_table', 'p_2023_01_01', '2023-01-02');

2. 自动管理分区

sql

-- 查看自动维护设置
SELECT * FROM partman.part_config WHERE parent_table = 'public.test_table';

-- 更新自动维护配置
UPDATE partman.part_config 
SET 
    automatic_maintenance = 'on',
    premake = 5,
    retain = 30,
    infinite_time_partitions = true
WHERE 
    parent_table = 'public.test_table';

-- 检查分区维护状态
SELECT * FROM partman.run_maintenance_proc();

3. 分区切换

sql

-- 将普通表转换为分区表
-- 1. 创建普通表
CREATE TABLE public.legacy_table (
    id serial primary key,
    created_at timestamp not null,
    data text
);

-- 2. 插入测试数据
INSERT INTO public.legacy_table (created_at, data) 
SELECT 
    '2023-01-01'::timestamp + (random() * 365 * interval '1 day'),
    'test data ' || generate_series(1, 10000)
FROM 
    generate_series(1, 10000);

-- 3. 使用pg_partman切换为分区表
SELECT partman.create_parent(
    p_parent_table => 'public.legacy_table',
    p_control => 'created_at',
    p_type => 'native',
    p_interval => 'monthly',
    p_start_partition => '2023-01-01',
    p_premake => 2,
    p_automatic_maintenance => 'on',
    p_use_run_maintenance => true
);

分区监控

1. 监控分区状态

sql

-- 查看分区配置
SELECT * FROM partman.part_config;

-- 查看分区维护日志
SELECT * FROM partman.part_config_log ORDER BY run_start_time DESC LIMIT 10;

-- 查看分区信息
SELECT * FROM partman.show_partitions('public.test_table');

-- 查看分区大小
SELECT 
    partition_name,
    table_size,
    index_size,
    total_size
FROM 
    partman.show_partition_sizes('public.test_table');

2. 监控后台工作进程

sql

-- 查看后台工作进程状态
SELECT * FROM partman.partman_bgw;

-- 查看后台工作进程日志
SELECT * FROM partman.partman_bgw_log ORDER BY run_time DESC LIMIT 10;

-- 手动启动后台工作进程
SELECT partman.start_partman_bgw();

-- 停止后台工作进程
SELECT partman.stop_partman_bgw();

性能优化

1. 分区表性能优化

sql

-- 优化分区表查询
-- 1. 确保查询条件包含分区键
SELECT * FROM public.test_table WHERE created_at >= '2023-01-01' AND created_at < '2023-02-01';

-- 2. 收集分区表统计信息
ANALYZE public.test_table;

-- 3. 优化分区表真空
VACUUM ANALYZE public.test_table;

-- 4. 配置分区表自动 vacuum
ALTER TABLE public.test_table SET (autovacuum_enabled = true, toast.autovacuum_enabled = true);

2. pg_partman性能优化

sql

-- 优化pg_partman配置
-- 1. 调整premake参数（预创建分区数量）
UPDATE partman.part_config SET premake = 3 WHERE parent_table = 'public.test_table';

-- 2. 调整retain参数（保留旧分区数量）
UPDATE partman.part_config SET retain = 60 WHERE parent_table = 'public.test_table';

-- 3. 调整后台工作进程间隔
ALTER SYSTEM SET partman_bgw.interval = 7200;
SELECT pg_reload_conf();

-- 4. 优化分区表索引
-- 确保每个分区都有合适的索引
CREATE INDEX idx_test_table_created_at ON public.test_table(created_at);

最佳实践

1. 设计最佳实践

选择合适的分区类型：时间序列数据使用时间分区，分类数据使用列表分区，连续数值数据使用范围分区
合理设置分区间隔：根据数据量和查询模式选择合适的分区间隔（如按天、周、月等）
预创建足够的分区：根据数据增长速度设置合适的premake值
设置合理的分区保留时间：根据业务需求设置retain值，自动清理旧分区
使用原生分区：优先使用PostgreSQL原生分区（p_type => 'native'）

2. 维护最佳实践

定期监控分区状态：使用partman.show_partitions和partman.show_partition_sizes监控分区
定期运行维护任务：确保后台工作进程正常运行，或手动定期运行maintenance任务
监控分区大小：避免单个分区过大，影响查询性能
定期收集统计信息：使用ANALYZE或VACUUM ANALYZE维护分区表统计信息
测试分区维护：在测试环境测试分区维护操作，避免生产环境出现问题

3. 性能最佳实践

查询条件包含分区键：确保查询条件包含分区键，避免全表扫描
避免跨分区查询：尽量避免跨越大量分区的查询
使用并行查询：对于跨分区查询，考虑启用并行查询
优化分区表索引：为每个分区创建合适的索引
考虑分区裁剪：确保PostgreSQL能够正确进行分区裁剪

常见问题处理

1. 分区创建失败

问题症状：

ERROR:  could not create partition "p_2023_02_01" for relation "test_table"

解决方法：

sql

-- 检查分区键数据类型
SELECT column_name, data_type FROM information_schema.columns WHERE table_name = 'test_table' AND column_name = 'created_at';

-- 检查分区起始值
SELECT * FROM partman.part_config WHERE parent_table = 'public.test_table';

-- 手动创建分区
SELECT partman.append_partition('public.test_table', ARRAY['2023-02-01']);

2. 后台工作进程不运行

问题症状：

SELECT * FROM partman.partman_bgw; -- 显示未运行

解决方法：

sql

-- 检查pg_partman配置
SELECT 
    name,
    setting
FROM 
    pg_settings
WHERE 
    name LIKE 'partman%';

-- 启动后台工作进程
SELECT partman.start_partman_bgw();

-- 检查后台工作进程日志
SELECT * FROM partman.partman_bgw_log ORDER BY run_time DESC LIMIT 5;

3. 分区维护任务运行缓慢

问题症状：

SELECT * FROM partman.part_config_log WHERE run_status = 'failed' ORDER BY run_start_time DESC LIMIT 5;

解决方法：

sql

-- 优化分区维护
UPDATE partman.part_config SET premake = 2 WHERE parent_table = 'public.test_table';

-- 调整维护任务运行时间
ALTER SYSTEM SET partman_bgw.interval = 7200;
SELECT pg_reload_conf();

-- 手动运行维护任务
SELECT partman.run_maintenance('public.test_table');

常见问题（FAQ）

Q1：pg_partman支持哪些分区类型？

A1：pg_partman支持以下分区类型：

时间分区：按天、周、月、年等时间间隔分区
值分区：按列表（list）或范围（range）进行值分区
原生分区：使用PostgreSQL原生分区功能（推荐）
继承分区：使用PostgreSQL表继承实现分区（传统方式）

Q2：如何将普通表转换为分区表？

A2：可以使用pg_partman的create_parent函数将普通表转换为分区表，步骤如下：

确保表有合适的分区键
调用create_parent函数，设置p_use_run_maintenance为true
系统会自动将现有数据迁移到相应分区
可以选择保留原表或删除原表

Q3：pg_partman后台工作进程的作用是什么？

A3：pg_partman后台工作进程的主要作用是：

自动创建新分区
自动删除旧分区
更新分区边界
维护分区表统计信息
记录维护日志

Q4：如何监控pg_partman的性能？

A4：可以使用以下方法监控pg_partman的性能：

监控分区表大小和增长趋势
监控分区维护任务的运行时间
监控后台工作进程的CPU和内存使用
分析包含分区键的查询性能
监控分区裁剪的效果

Q5：pg_partman与PostgreSQL原生分区有什么区别？

A5：pg_partman与PostgreSQL原生分区的主要区别：

自动化管理：pg_partman提供自动创建和删除分区的功能
更灵活的分区策略：支持更多的分区间隔和类型
更好的分区维护工具：提供丰富的分区管理函数
支持分区切换：可以将普通表转换为分区表
后台工作进程：提供自动维护的后台工作进程

Q6：如何升级pg_partman？

A6：升级pg_partman的步骤如下：

备份数据库
安装新版本的pg_partman
运行升级脚本：
sql
```
ALTER EXTENSION pg_partman UPDATE;
```
检查升级后的版本：
sql
```
SELECT partman_version();
```
验证分区功能正常

迁移与升级

1. 从其他分区解决方案迁移

sql

-- 从传统继承分区迁移到pg_partman
-- 1. 创建新的分区表
CREATE TABLE public.new_table (
    id serial primary key,
    created_at timestamp not null,
    data text
);

-- 2. 使用pg_partman创建分区
SELECT partman.create_parent(
    p_parent_table => 'public.new_table',
    p_control => 'created_at',
    p_type => 'native',
    p_interval => 'daily',
    p_start_partition => '2023-01-01',
    p_premake => 2
);

-- 3. 迁移数据
INSERT INTO public.new_table SELECT * FROM public.old_table;

-- 4. 切换表名
ALTER TABLE public.old_table RENAME TO old_table_backup;
ALTER TABLE public.new_table RENAME TO old_table;

2. 升级pg_partman

sql

-- 升级pg_partman扩展
ALTER EXTENSION pg_partman UPDATE;

-- 验证升级
SELECT partman_version();

-- 检查分区配置
SELECT * FROM partman.part_config;

-- 运行维护任务，确保升级后功能正常
SELECT partman.run_maintenance('public.test_table');

监控与告警

1. 监控指标

分区数量：监控分区数量的增长趋势
分区大小：监控单个分区和整体分区表的大小
维护任务运行时间：监控分区维护任务的运行时间
后台工作进程状态：监控后台工作进程是否正常运行
分区裁剪效率：监控查询是否正确进行分区裁剪

2. 告警设置

当分区数量超过阈值时告警
当单个分区大小超过阈值时告警
当分区维护任务运行失败时告警
当后台工作进程停止运行时告警
当分区表统计信息过期时告警

通过遵循上述pg_partman分区管理规范，可以有效地管理PostgreSQL分区表，提高数据库性能和可维护性，减轻DBA的运维负担。

PostgreSQL pg_partman分区管理 ​

核心概念 ​

安装与配置 ​

1. 安装pg_partman扩展 ​

2. 配置pg_partman ​

3. 初始化pg_partman后台工作进程 ​

创建分区表 ​

1. 时间分区表 ​

2. 值分区表 ​

3. 范围分区表 ​

分区管理 ​

1. 手动管理分区 ​

2. 自动管理分区 ​

3. 分区切换 ​

分区监控 ​

1. 监控分区状态 ​

2. 监控后台工作进程 ​

性能优化 ​

1. 分区表性能优化 ​

2. pg_partman性能优化 ​

最佳实践 ​

1. 设计最佳实践 ​

2. 维护最佳实践 ​

3. 性能最佳实践 ​

常见问题处理 ​

1. 分区创建失败 ​

2. 后台工作进程不运行 ​

3. 分区维护任务运行缓慢 ​

常见问题（FAQ） ​

Q1：pg_partman支持哪些分区类型？ ​

Q2：如何将普通表转换为分区表？ ​

Q3：pg_partman后台工作进程的作用是什么？ ​

Q4：如何监控pg_partman的性能？ ​

Q5：pg_partman与PostgreSQL原生分区有什么区别？ ​

Q6：如何升级pg_partman？ ​

迁移与升级 ​

1. 从其他分区解决方案迁移 ​

2. 升级pg_partman ​

监控与告警 ​

1. 监控指标 ​

2. 告警设置 ​

PostgreSQL pg_partman分区管理

核心概念

安装与配置

1. 安装pg_partman扩展

2. 配置pg_partman

3. 初始化pg_partman后台工作进程

创建分区表

1. 时间分区表

2. 值分区表

3. 范围分区表

分区管理

1. 手动管理分区

2. 自动管理分区

3. 分区切换

分区监控

1. 监控分区状态

2. 监控后台工作进程

性能优化

1. 分区表性能优化

2. pg_partman性能优化

最佳实践

1. 设计最佳实践

2. 维护最佳实践

3. 性能最佳实践

常见问题处理

1. 分区创建失败

2. 后台工作进程不运行

3. 分区维护任务运行缓慢

常见问题（FAQ）

Q1：pg_partman支持哪些分区类型？

Q2：如何将普通表转换为分区表？

Q3：pg_partman后台工作进程的作用是什么？

Q4：如何监控pg_partman的性能？

Q5：pg_partman与PostgreSQL原生分区有什么区别？

Q6：如何升级pg_partman？

迁移与升级

1. 从其他分区解决方案迁移

2. 升级pg_partman

监控与告警

1. 监控指标

2. 告警设置