PG不能自动创建分区，那是因为装备不够

PostgreSQL传统的分区方法，使用约束来区分不同的分区存储数据（配置constraint_exclusion = partition），执行选择/删除/更新时执行计划根据约束和查询条件排除不需要的查询的分区表。调用COPY或插入数据时使用插入或规则，将数据插入对应的分区表。

• pg_pathman使用ProcessUtility_hook钩子来处理分区表的COPY查询。

• RuntimeAppend（重写Append计划节点）

• RuntimeMergeAppend（重写MergeAppend计划节点）

• PartitionFilter（INSERT触发器的直接替代）

• 目前支持range，hash分区。

• 支持自动分区管理（通过函数接口创建分区，自动将主表数据迁移到分区表），或手工分区管理（通过函数实现，将现有的表绑定到分区表，或者从分区表分割） 。

• 支持的分区分区类型包括int，float，date，以及其他常用类型，包括自定义的域。

• 通过CUSTOM SCAN实现了有效的分区表JOIN，子查询过滤分区。

• 使用RuntimeAppend和RuntimeMergeAppend自定义计划节点实现了动态分区选择。

• PartitionFilter HOOK，实现就地插入，代替传统的插入触发器或插入规则。

• 支持自动添加分区。目前仅支持范围分区表。

• 支持从/到直接读取或写入分区表的复制，提高效率。

• 支持分区分区的更新，需要添加替换，如果不需要更新分区分区，则不建议添加此转换器，会产生一定的性能影响。

• 允许用户自定义定义函数，在创建分区时会自动触发。

• 非插入式创建分区表，以及后台自动将主表数据迁移到分区表，非插入式。

• 支持FDW，通过配置参数pg_pathman.insert_into_fdw =（禁用| postgres | any_fdw）支持postgres_fdw或任意fdw(外部分区)

• 支持GUC参数配置，注意通过使用了HOOK，如果其他插件也使用了相同的HOOK，需要将pg_pathman放在后面注册，如pg_stat_statements。

shared_preload_libraries =pg_stat_statements，pg_pathman

• 插入优化，使用PartitionFilter替换关系，替换初始化的方式。效率提高非常明显。

• 查询优化，分区定义加载在内存中，使用二进制搜索和哈希搜索对应范围与哈希分区表，使用RuntimeAppend和RuntimeMerge附加自定义计划节点以在运行时选择分区；

• 同时运行时过滤，支持子查询。传统的约束法不支持子查询过滤。

1. 安装部署

unzip pg_pathman-master.zip

cd pg_pathman-master

make USE_PGXS=1

make USE_PGXS=1 install

alter system set shared_preload_libraries=pg_stat_statements,pg_pathman,telepg_monitor;

$pg_ctl restart -m fast

c ksl

postgres=# create extension pg_pathman;

CREATE EXTENSION



ksl=# dx

                                                    List of installed extensions

    Name | Version |   Schema | Description 

-------------+---------+------------+-------------------------------------------------------

 pageinspect | 1.7 | public | inspect the contents of database pages at a low level

 pg_pathman | 1.5 | public | Partitioning tool for PostgreSQL

   plpgsql | 1.0  | pg_catalog | PL/pgSQL procedural language

(3 rows)

2. RANGE分区实战举例

创建需要分区的主表：

postgres=# create table part_test(id int, info text, 
crt_time timestamp not null); -- 分区列必须有not null约束 

CREATE TABLE

插入一批测试数据，模拟已经有数据了的主表：

postgres=# insert into part_test select id,md5(random()::text),clock_timestamp() + (id|| hour)::interval from generate_series(1,10000) t(id); 

INSERT 0 10000

postgres=# select * from part_test limit 10; 

 id | info |          crt_time 

----+----------------------------------+----------------------------

  1 | 36fe1adedaa5b848caec4941f87d443a | 20XX-10-25 10:27:13.206713

  2 | c7d7358e196a9180efb4d0a10269c889 | 20XX-10-25 11:27:13.206893

  3 | 005bdb063550579333264b895df5b75e | 20XX-10-25 12:27:13.206904

  4 | 6c900a0fc50c6e4da1ae95447c89dd55 | 20XX-10-25 13:27:13.20691

  5 | 857214d8999348ed3cb0469b520dc8e5 | 20XX-10-25 14:27:13.206916

  6 | 4495875013e96e625afbf2698124ef5b | 20XX-10-25 15:27:13.206921

  7 | 82488cf7e44f87d9b879c70a9ed407d4 | 20XX-10-25 16:27:13.20693

  8 | a0b92547c8f17f79814dfbb12b8694a0 | 20XX-10-25 17:27:13.206936

  9 | 2ca09e0b85042b476fc235e75326b41b | 20XX-10-25 18:27:13.206942

 10 | 7eb762e1ef7dca65faf413f236dff93d | 20XX-10-25 19:27:13.206947

(10 rows)

• 分区列必须有not null约束;

• 分区个数必须能覆盖已有的所有记录.

创建分区，每个分区包含1个月的跨度数据：

postgres=#select create_range_partitions(part_test::regclass, -- 主表OID

 crt_time, -- 分区列名

 2020-11-05 00:00:00::timestamp, -- 开始值

 interval 1 month, -- 间隔；interval 类型，用于时间分区表

 24, -- 分多少个区

 false) ; -- 不迁移数据

NOTICE:  sequence "part_test_seq" does not exist, skipping

 create_range_partitions 

-------------------------

     24

(1 row)

由于不迁移数据，所以数据还在主表：

postgres=# select count(*) from only part_test;

 count 

-------

 10000

(1 row)

使用非堵塞式的迁移接口：

postgres=# select partition_table_concurrently(part_test::regclass,

                             10000,

                             1.0);

NOTICE: worker started, you can stop it with the following command: select stop_concurrent_part_task(part_test);

 partition_table_concurrently 

------------------------------



(1 row)

--查看后台的数据迁移任务：

select * from pathman_concurrent_part_tasks;

迁移结束后，主表数据已经没有了，全部在分区中：

postgres=# select count(*) from only part_test;

 count 

-------

     0

(1 row)

数据迁移完成后，建议禁用主表，这样执行计划就不会出现主表了。

postgres=# select set_enable_parent(part_test::regclass, false);

 set_enable_parent 

-------------------

(1 row)

postgres=# explain select * from part_test where crt_time = 2020-11-05 00:00:00::timestamp;

              QUERY PLAN 

-------------------------------------------------------------------------------

 Append (cost=0.00..16.18 rows=1 width=45)

   -> Seq Scan on part_test_1 (cost=0.00..16.18 rows=1 width=45)

         Filter: (crt_time = 2020-11-05 00:00:00::timestamp without time zone)

(3 rows)

• 分区列必须有not null约束；

• 分区个数必须能覆盖已有的所有记录；

• 建议使用非堵塞式迁移接口；

• 建议数据迁移完成后，禁用主表。

1700w数据大概迁移了一个多小时，如果表有索引可以先删除索引，数据迁移完成后再建索引，因为在创建分区的时候，所有的分区表都会多带带创建索引，这也是不能保证全局唯一的原因。

--禁止自动扩展分区：

select set_auto(part_test::regclass, false);

insert into ksl.part_test values (1,test,2023-01-01::timestamp);

--向后添加分区：

select append_range_partition(part_test::regclass);

--向前添加分区：

select prepend_range_partition(part_test::regclass);

--停止迁移任务：

select top_concurrent_part_task(part_test::regclass);

-- 查看后台的数据迁移任务：

select * from pathman_concurrent_part_tasks;

-- 分裂范围分区,数据会自动迁移到另一个分区：

select split_range_partition(

part_test_1::regclass, -- 分区oid

      2020-11-25 00:00:00::timestamp, -- 分裂值

      part_test_1_2); -- 分区表名

--合并范围分区,目前仅支持范围分区。

--指定两个需要合并分区，必须为相邻分区：

select merge_range_partitions(part_test_1::regclass, part_test_1_2::regclass) ;

--不相邻的分区合并会报错

select merge_range_partitions(part_test_2::regclass, part_test_12::regclass) ;

ERROR:  partitions "part_test_2" and "part_test_12" are not adjacent

--合并后，会删掉其中一个分区表。

--删除单个范围分区：

drop_range_partition(

partition TEXT, -- 分区名称

        delete_data BOOLEAN DEFAULT TRUE) -- 是否删除分区数据，如果false，表示分区数据迁移到主表。  

--删除分区， 数据迁移到主表：

select drop_range_partition(part_test_1,false);  

--删除分区，分区数据也删除，不迁移到主表：

select drop_range_partition(part_test_3,true);

drop_partitions(parent REGCLASS,delete_data BOOLEAN DEFAULT FALSE)

-- 删除所有分区表，并将数据迁移到主表

select drop_partitions(part_test::regclass, false);

--绑定分区(已有的表加入分区表)。

--将已有的表，绑定到已有的某个分区主表。

--已有的表与主表要保持一致的结构，包括dropped columns。 (查看pg_attribute的一致性)

--如果设置了回调函数，会触发。

--绑定分区时，自动创建继承关系，自动创建约束。

attach_range_partition(

relation REGCLASS, -- 主表OID

          partition REGCLASS, -- 分区表OID

          start_value ANYELEMENT, -- 起始值

          end_value ANYELEMENT) -- 结束值

create table part_test_1 (like part_test including all);

select attach_range_partition(part_test::regclass, part_test_1::regclass, 2020-11-05 00:00:00::timestamp, 2020-12-05 00:00:00::timestamp);

--将分区从主表的继承关系中删除, 不删数据，删除继承关系，删除约束。

detach_range_partition(partition REGCLASS) -- 指定分区名，转换为普通表 

select detach_range_partition(part_test_2);

-- 更新触发器。

--如果分区字段要被更新，需要创建更新触发器，否则不需要。

create_range_update_trigger(parent REGCLASS)

ksl=> select * from part_test_3 limit 10;

  id | info |          crt_time 

------+----------------------------------+----------------------------

 1450 | d16ae9fa14aabb821df6692beef610e6 | 2021-01-05 00:33:46.657077

 1451 | b88247d2cb9acb9e98ba472f575f180c | 2021-01-05 01:33:46.657081

 1452 | 344c48262f105e8622099b24d9ed7d8a | 2021-01-05 02:33:46.657086

 1453 | bd6e36744447ab70a1624134de9dbde0 | 2021-01-05 03:33:46.65709

 1454 | 3d8c3470df5dcbb1e5ad68974fabf11a | 2021-01-05 04:33:46.657094

 1455 | 71664d8dcdad66ef2ccd0464cc61279b | 2021-01-05 05:33:46.657098

 1456 | 7f0da1bec230ad34741081a5da79b995 | 2021-01-05 06:33:46.657102

 1457 | 34045bcda2117d5643a54c29febd51b6 | 2021-01-05 07:33:46.657107

 1458 | 103a593f0be11898153cf58d5ca576be | 2021-01-05 08:33:46.657111

 1459 | 16e4d2340014ddfeb195c141c0395474 | 2021-01-05 09:33:46.657117

(10 rows)

--创建更新触发器前，如果更新分区字段后的值跨分区了，会报约束错误。

ksl=> update part_test set crt_time=2021-11-05 00:33:46.657077 where id=1450;

ERROR: new row for relation "part_test_3" violates check constraint "pathman_part_test_3_check"

DETAIL: Failing row contains (1450, d16ae9fa14aabb821df6692beef610e6, 2021-11-05 00:33:46.657077).

--创建更新触发器后，正常。

--永久禁止分区表pg_pathman插件。

--可以针对单个分区主表禁用pg_pathman：

select disable_pathman_for(part_test);

禁用pg_pathman后，继承关系和约束不会变化，只是pg_pathman不介入custom scan 执行计划。

禁用pg_pathman后的执行计划：

postgres=# explain select * from part_test where crt_time=2020-11-25 00:00:00::timestamp;

                QUERY PLAN 

-------------------------------------------------------------------------------

 Append (cost=0.00..16.00 rows=2 width=45)

   -> Seq Scan on part_test (cost=0.00..0.00 rows=1 width=45)

         Filter: (crt_time = 2020-11-25 00:00:00::timestamp without time zone)

   ->  Seq Scan on part_test_1 (cost=0.00..16.00 rows=1 width=45)

         Filter: (crt_time = 2020-11-25 00:00:00::timestamp without time zone)

(5 rows)

disable_pathman_for没有可逆操作，请慎用。

--全局禁止pg_pathman

与禁用单个分区主表不同，全局禁止只需要调整参数即可，不需要修改pg_pathman的元数据，同时它是可逆操作。

pg_pathman.enable

$ vi $PGDATA/postgresql.conf

pg_pathman.enable = off


$ pg_ctl reload

• 如果在建初始分区时，需要设置分区表的表空间，可以设置会话或事务的参数

  setlocal default_tablespace=tablespacename;

• disable_pathman_for函数没有可逆操作，请慎用。

• 不建议关闭pg_pathman.enable

• 不建议开启自动扩展范围分区，一个错误的分区值可能导致创建很多分区。

• 推荐使用set_enable_parent禁用主表。

• 由于pg_pathman使用了customscan接口，所以只支持9.5以及以上版本。

• 传统哈希分区需要输入分区键值的约束条件，才能正确选择分区。pg_pathman只要输入键值即可。

文章首发于2020年12月19日

本文作者：柯诗亮（上海新炬王翦团队）

本文来源：“IT那活儿”公众号