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

IT那活儿发布于2023-01-11 13:20 / 3473人阅读

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

[

前言

]

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

传统的做法，无论是查询还是插入，对性能的影响都较长。pg_pathman与传统的继承分区表做法有一个不同的地方，分区的定义放置在一张元数据表中，表的信息会缓存在内存中，同时使用HOOK来实现关系的替换，所以效率非常高。目前支持两种分区模式，范围和哈希，其中范围使用二进制搜索查找对应的分区，哈希使用哈希搜索查找对应的分区。

[

pg_pathman装备介绍

]

一、pg_pathman用到的hook如下

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

2. RuntimeAppend（重写Append计划节点）

3. RuntimeMergeAppend（重写MergeAppend计划节点）

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

二、pg_pathman特性

1.目前支持range，hash分区。

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

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

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

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

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

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

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

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

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

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

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

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

shared_preload_libraries =pg_stat_statements，pg_pathman

三、pg_pathman为什么高效

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

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

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

[

pg_pathman装备使用

]

1、安装部署

--下载安装包

https://github.com/postgrespro/pg_pathman

--pg_pathman安装

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_pathman需写在pg_stat_statements之后

--重启实例

$pg_ctl restart -m fast

--创建pg_pathman扩展

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)

二、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 | 2016-10-25 10:27:13.206713

2 | c7d7358e196a9180efb4d0a10269c889 | 2016-10-25 11:27:13.206893

3 | 005bdb063550579333264b895df5b75e | 2016-10-25 12:27:13.206904

4 | 6c900a0fc50c6e4da1ae95447c89dd55 | 2016-10-25 13:27:13.20691

5 | 857214d8999348ed3cb0469b520dc8e5 | 2016-10-25 14:27:13.206916

6 | 4495875013e96e625afbf2698124ef5b | 2016-10-25 15:27:13.206921

7 | 82488cf7e44f87d9b879c70a9ed407d4 | 2016-10-25 16:27:13.20693

8 | a0b92547c8f17f79814dfbb12b8694a0 | 2016-10-25 17:27:13.206936

9 | 2ca09e0b85042b476fc235e75326b41b | 2016-10-25 18:27:13.206942

10 | 7eb762e1ef7dca65faf413f236dff93d | 2016-10-25 19:27:13.206947

(10 rows)

注意:

1. 分区列必须有not null约束

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

创建分区，每个分区包含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

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

(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

-------

(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)

建议

1. 分区列必须有not null约束

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

3. 建议使用非堵塞式迁移接口

4. 建议数据迁移完成后，禁用主表

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_pathma

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

[

总结

]

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

setlocal default_tablespace=tablespacename;

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

3.不建议关闭pg_pathman.enable

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

5.推荐使用set_enable_parent禁用主表。

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

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

云服务器 GPU云服务器 linux不能挂载硬盘分区 linux下创建分区 linux 自动分区 hbase 自动分区

文章版权归作者所有，未经允许请勿转载,若此文章存在违规行为，您可以联系管理员删除。

转载请注明本文地址：https://www.ucloud.cn/yun/130058.html

postgresql分区表

摘要：创建自动分区采用两种方式采用视图分区方式采用直接分区方式创建表创建索引采用视图分区方式建立视图定义分表保证分区后的可以自增按照时间进行分区分表触发器定义更新更新触发器直接分区方式分表触发器两种方式比较视图分区所有操作都是对视图的操创建自动分区采用两种方式采用视图分区方式采用直接分区方式创建表 CREATE TABLE IF NOT EXISTS public.sales...

EdwardUp 2019-07-25 12:35 评论0 收藏0
PostgreSQL 自动分区分表维护管理插件 pathman 基础使用

摘要：使用数据库会自动的根据从某几个片中读取数据。更加详细的请参考德哥文章官方地址：https://github.com/postgrespr...关于pathman的原理和优化问题，请移步至https://yq.aliyun.com/article... 检查环境变量如果直接执行psql命令提示command not found则执行下面的命令设置环境变量 root@host# PA...

MASAILA 2019-07-25 12:37 评论0 收藏0