pg_pathman RANGE分区实战教程5分钟构建按天归档的日志表附完整代码【免费下载链接】pg_pathmanPartitioning tool for PostgreSQL项目地址: https://gitcode.com/gh_mirrors/pg/pg_pathmanpg_pathman 是 PostgreSQL 生态中一款强大的分区管理工具本教程带你用5 分钟掌握 pg_pathman 的 RANGE范围分区把一张千万级的日志表自动拆分成一天一个分区实现查询秒级响应、归档秒级删除。全文附带完整可运行的 SQL 代码新手也能直接照做。为什么日志表需要按天归档想象一下这样的场景一张journal日志表每天增长几十万行半年后轻松突破5000 万行业务查询WHERE dt 2026-08-20却要全表扫描慢得像蜗牛运维想清理 90 天前的数据DELETE执行几小时还不带锁索引越来越大VACUUM越来越慢整个库性能雪崩。RANGE 分区就是为了解决这些问题而生的把一张大表按时间范围拆成许多小表查询只扫相关分区删除历史数据只需DROP TABLE一个分区毫秒级完成。pg_pathman 是什么和普通继承有什么区别pg_pathman 是一个 PostgreSQL 扩展模块支持 PostgreSQL 11~15它提供两大利器分区管理函数一行 SQL 就能创建、拆分、合并、追加、删除分区还能自动创建新分区优化的查询规划器通过共享内存缓存分区边界用二分查找快速定位分区避免传统表继承方式下逐个检查约束的规划开销分区数量再多也不怕。安装后它的分区配置统一存放在pathman_config表中每个被分区管理的表只占一行记录管理起来非常清爽。 小贴士PostgreSQL 10 以上已内置声明式分区但 pg_pathman 的亮点在于自动分区创建、跨分区 UPDATE 路由、后台并发数据迁移等高级能力适合对分区管理要求更高的场景。一键安装 pg_pathman最快配置方法第 1 步编译安装git clone https://gitcode.com/gh_mirrors/pg/pg_pathman cd pg_pathman make install USE_PGXS1第 2 步修改配置并重启在postgresql.conf中加入shared_preload_libraries pg_pathman然后重启 PostgreSQL 实例必须重启这是共享库的硬性要求。第 3 步创建扩展CREATE SCHEMA pathman; GRANT USAGE ON SCHEMA pathman TO PUBLIC; CREATE EXTENSION pg_pathman WITH SCHEMA pathman;三步完成总共不到 5 分钟。核心实战构建按天归档的日志表下面用一个真实感十足的journal日志表走一遍完整流程。1️⃣ 创建日志表CREATE TABLE journal ( id SERIAL, dt TIMESTAMP NOT NULL, -- 分区键日志时间 level INTEGER, msg TEXT ); -- 在父表上建的索引会自动应用到每个分区 CREATE INDEX ON journal(dt);2️⃣ 一行命令创建 RANGE 分区-- 从 2026-08-01 开始每 1 天一个分区先建 30 个 SELECT create_range_partitions( journal, -- 父表名 dt, -- 分区键 2026-08-01::date, -- 起始值 1 day::interval, -- 分区间隔 30 -- 预建分区数 );执行后pg_pathman 会自动生成journal_1~journal_30三十个分区每个分区覆盖一天的时间范围并把父表中已有的数据自动搬迁到对应分区。如果想按月份归档只要把1 day换成1 month即可按数值范围归档如用户 ID 分段则传入数字类型的区间值。这些函数签名可以在仓库根目录的range.sql和sql/pathman_interval.sql示例文件中找到完整定义。3️⃣ 开启自动创建分区日志写入永不落空这是 pg_pathman 最实用的功能之一当你插入的数据超出了最后一个分区的范围时它会自动追加一个新分区业务代码完全无感知。只要创建分区时保留了p_interval本例为1 day自动创建默认就是开启的。你可以随时调整间隔或关闭该功能-- 把默认间隔改为 1 个月 SELECT set_interval(journal, 1 month::interval); -- 显式开启/关闭自动分区 SELECT set_auto(journal, true);⚠️ 注意如果当初把p_interval设为NULL自动创建功能将不可用只能手动管理分区。4️⃣ 验证分区裁剪查询快在哪EXPLAIN SELECT * FROM journal WHERE dt 2026-08-15 AND dt 2026-08-16;你会惊喜地发现执行计划里Append节点只包含一个子分区如journal_15而不是 30 个Append (cost...) - Seq Scan on journal_15这就是 pg_pathman 的威力查询 1 天数据只碰 1 个分区。仓库中sql/pathman_interval.sql提供了各类类型INT、DATE、FLOAT、NUMERIC下间隔设置的完整测试用例可供延伸阅读。日常运维分区管理函数速查表日志表跑起来之后日常就靠这几个函数了场景函数示例追加一个默认间隔的分区append_range_partitionSELECT append_range_partition(journal);指定范围补建分区add_range_partitionSELECT add_range_partition(journal, 2026-09-01::date, 2026-09-07::date);删除旧分区数据一起删drop_range_partitionSELECT drop_range_partition(journal_1);合并相邻分区merge_range_partitionsSELECT merge_range_partitions(journal_1, journal_2);拆分大分区split_range_partitionSELECT split_range_partition(journal_30, 2026-08-20::date);挂载已有表为分区attach_range_partition可挂载本地表甚至FDW 外部表作为冷数据归档归档老数据的经典操作把 3 个月前的分区 detach 下来或挂载一个只含历史数据的冷存储表作为分区热表始终保持轻快。删除分区用drop_range_partition比DELETE快几个数量级——因为它是 DDL不是 DML。生产环境避坑指南存量数据迁移别锁表create_range_partitions默认会把父表数据一次性搬到分区期间表会被锁住直到事务提交。生产大表请改用后台并发迁移SELECT partition_table_concurrently(journal); -- 分批小事务几乎不干扰业务它由后台 worker 按小批量每批最多 1 万行复制数据不会长时间持有锁。执行中可随时用show_concurrent_part_tasks()查看进度用stop_concurrent_part_task(journal)停止任务。回调函数分区创建时自动做点事通过set_init_callback()可以为每个新创建的分区注册初始化回调比如自动建索引、设置存储参数回调函数签名为part_init_callback(args JSONB) RETURNS VOIDJSONB 参数中带有分区名、范围边界等信息非常适合自动化运维。常用开关GUC 参数pg_pathman.enable整体启用/禁用 pg_pathmanpg_pathman.enable_auto_partition按会话控制自动建分区pg_pathman.enable_partitionfilter控制 INSERT 路由节点。想对某张表彻底停用它执行SELECT disable_pathman_for(journal);即可数据原封不动。写在最后5 分钟回顾一下今天学到的 pg_pathman RANGE 分区三件套建表 一行create_range_partitions按天/按月自动切分日志表自动分区创建让写入永不失败运维彻底解放EXPLAIN可见的分区裁剪查询和归档性能立竿见影。从日志表起步pg_pathman 同样适用于订单表、监控指标表、IoT 时序数据等一切按时间无限增长的场景。动手试试吧【免费下载链接】pg_pathmanPartitioning tool for PostgreSQL项目地址: https://gitcode.com/gh_mirrors/pg/pg_pathman创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考
pg_pathman RANGE分区实战教程：5分钟构建按天归档的日志表（附完整代码）
pg_pathman RANGE分区实战教程5分钟构建按天归档的日志表附完整代码【免费下载链接】pg_pathmanPartitioning tool for PostgreSQL项目地址: https://gitcode.com/gh_mirrors/pg/pg_pathmanpg_pathman 是 PostgreSQL 生态中一款强大的分区管理工具本教程带你用5 分钟掌握 pg_pathman 的 RANGE范围分区把一张千万级的日志表自动拆分成一天一个分区实现查询秒级响应、归档秒级删除。全文附带完整可运行的 SQL 代码新手也能直接照做。为什么日志表需要按天归档想象一下这样的场景一张journal日志表每天增长几十万行半年后轻松突破5000 万行业务查询WHERE dt 2026-08-20却要全表扫描慢得像蜗牛运维想清理 90 天前的数据DELETE执行几小时还不带锁索引越来越大VACUUM越来越慢整个库性能雪崩。RANGE 分区就是为了解决这些问题而生的把一张大表按时间范围拆成许多小表查询只扫相关分区删除历史数据只需DROP TABLE一个分区毫秒级完成。pg_pathman 是什么和普通继承有什么区别pg_pathman 是一个 PostgreSQL 扩展模块支持 PostgreSQL 11~15它提供两大利器分区管理函数一行 SQL 就能创建、拆分、合并、追加、删除分区还能自动创建新分区优化的查询规划器通过共享内存缓存分区边界用二分查找快速定位分区避免传统表继承方式下逐个检查约束的规划开销分区数量再多也不怕。安装后它的分区配置统一存放在pathman_config表中每个被分区管理的表只占一行记录管理起来非常清爽。 小贴士PostgreSQL 10 以上已内置声明式分区但 pg_pathman 的亮点在于自动分区创建、跨分区 UPDATE 路由、后台并发数据迁移等高级能力适合对分区管理要求更高的场景。一键安装 pg_pathman最快配置方法第 1 步编译安装git clone https://gitcode.com/gh_mirrors/pg/pg_pathman cd pg_pathman make install USE_PGXS1第 2 步修改配置并重启在postgresql.conf中加入shared_preload_libraries pg_pathman然后重启 PostgreSQL 实例必须重启这是共享库的硬性要求。第 3 步创建扩展CREATE SCHEMA pathman; GRANT USAGE ON SCHEMA pathman TO PUBLIC; CREATE EXTENSION pg_pathman WITH SCHEMA pathman;三步完成总共不到 5 分钟。核心实战构建按天归档的日志表下面用一个真实感十足的journal日志表走一遍完整流程。1️⃣ 创建日志表CREATE TABLE journal ( id SERIAL, dt TIMESTAMP NOT NULL, -- 分区键日志时间 level INTEGER, msg TEXT ); -- 在父表上建的索引会自动应用到每个分区 CREATE INDEX ON journal(dt);2️⃣ 一行命令创建 RANGE 分区-- 从 2026-08-01 开始每 1 天一个分区先建 30 个 SELECT create_range_partitions( journal, -- 父表名 dt, -- 分区键 2026-08-01::date, -- 起始值 1 day::interval, -- 分区间隔 30 -- 预建分区数 );执行后pg_pathman 会自动生成journal_1~journal_30三十个分区每个分区覆盖一天的时间范围并把父表中已有的数据自动搬迁到对应分区。如果想按月份归档只要把1 day换成1 month即可按数值范围归档如用户 ID 分段则传入数字类型的区间值。这些函数签名可以在仓库根目录的range.sql和sql/pathman_interval.sql示例文件中找到完整定义。3️⃣ 开启自动创建分区日志写入永不落空这是 pg_pathman 最实用的功能之一当你插入的数据超出了最后一个分区的范围时它会自动追加一个新分区业务代码完全无感知。只要创建分区时保留了p_interval本例为1 day自动创建默认就是开启的。你可以随时调整间隔或关闭该功能-- 把默认间隔改为 1 个月 SELECT set_interval(journal, 1 month::interval); -- 显式开启/关闭自动分区 SELECT set_auto(journal, true);⚠️ 注意如果当初把p_interval设为NULL自动创建功能将不可用只能手动管理分区。4️⃣ 验证分区裁剪查询快在哪EXPLAIN SELECT * FROM journal WHERE dt 2026-08-15 AND dt 2026-08-16;你会惊喜地发现执行计划里Append节点只包含一个子分区如journal_15而不是 30 个Append (cost...) - Seq Scan on journal_15这就是 pg_pathman 的威力查询 1 天数据只碰 1 个分区。仓库中sql/pathman_interval.sql提供了各类类型INT、DATE、FLOAT、NUMERIC下间隔设置的完整测试用例可供延伸阅读。日常运维分区管理函数速查表日志表跑起来之后日常就靠这几个函数了场景函数示例追加一个默认间隔的分区append_range_partitionSELECT append_range_partition(journal);指定范围补建分区add_range_partitionSELECT add_range_partition(journal, 2026-09-01::date, 2026-09-07::date);删除旧分区数据一起删drop_range_partitionSELECT drop_range_partition(journal_1);合并相邻分区merge_range_partitionsSELECT merge_range_partitions(journal_1, journal_2);拆分大分区split_range_partitionSELECT split_range_partition(journal_30, 2026-08-20::date);挂载已有表为分区attach_range_partition可挂载本地表甚至FDW 外部表作为冷数据归档归档老数据的经典操作把 3 个月前的分区 detach 下来或挂载一个只含历史数据的冷存储表作为分区热表始终保持轻快。删除分区用drop_range_partition比DELETE快几个数量级——因为它是 DDL不是 DML。生产环境避坑指南存量数据迁移别锁表create_range_partitions默认会把父表数据一次性搬到分区期间表会被锁住直到事务提交。生产大表请改用后台并发迁移SELECT partition_table_concurrently(journal); -- 分批小事务几乎不干扰业务它由后台 worker 按小批量每批最多 1 万行复制数据不会长时间持有锁。执行中可随时用show_concurrent_part_tasks()查看进度用stop_concurrent_part_task(journal)停止任务。回调函数分区创建时自动做点事通过set_init_callback()可以为每个新创建的分区注册初始化回调比如自动建索引、设置存储参数回调函数签名为part_init_callback(args JSONB) RETURNS VOIDJSONB 参数中带有分区名、范围边界等信息非常适合自动化运维。常用开关GUC 参数pg_pathman.enable整体启用/禁用 pg_pathmanpg_pathman.enable_auto_partition按会话控制自动建分区pg_pathman.enable_partitionfilter控制 INSERT 路由节点。想对某张表彻底停用它执行SELECT disable_pathman_for(journal);即可数据原封不动。写在最后5 分钟回顾一下今天学到的 pg_pathman RANGE 分区三件套建表 一行create_range_partitions按天/按月自动切分日志表自动分区创建让写入永不失败运维彻底解放EXPLAIN可见的分区裁剪查询和归档性能立竿见影。从日志表起步pg_pathman 同样适用于订单表、监控指标表、IoT 时序数据等一切按时间无限增长的场景。动手试试吧【免费下载链接】pg_pathmanPartitioning tool for PostgreSQL项目地址: https://gitcode.com/gh_mirrors/pg/pg_pathman创作声明：本文部分内容由AI辅助生成（AIGC），仅供参考