PostgreSQL分区表实现——pg_pathman安装、配置

近日由于系统运行时间太长,数据库库表中的数据也是越来越多,为了缩短库表的操作时间,所以对数据库中的部分库表进行分区的操作。

通过研究,决定采用pg_pathman插件对库表进行分区操作。pg_pathman安装、配置特此记录。

Pg_pathman原理:

PostgreSQL传统的分区方法,使用约束来区分不同分区存放的数据(配置constraint_exclusion=partition),执行select/delete/update时执行计划根据约束和查询条件排除不需要查询的分区表。

调用COPY或插入数据时使用触发器或规则,将数据插入对应的分区表。

传统的做法,无论是查询还是插入,对性能的影响都较大。

pg_pathman与传统的继承分区表做法有一个不同的地方,分区的定义存放在一张元数据表中,表的信息会cache在内存中,同时使用HOOK来实现RELATION的替换,所以效率非常高。

目前支持两种分区模式,range和hash,其中range使用binary search查找对应的分区,hash使用hash search查找对应的分区。

Pg_pathman 特性:

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

2. 支持自动分区管理(通过函数接口创建分区,自动将主表数据迁移到分区表),或手工分区管理(通过函数实现,将已有的表绑定到分区表,或者从分区表剥离)。

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

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

5. 使用RuntimeAppend & RuntimeMergeAppend custom plan nodes实现了动态分区选择。

6. PartitionFilter HOOK,实现insert inplace, 代替传统的insert trigger或insert rule。

7. 支持自动新增分区。 目前仅支持range分区表。

8. 支持copy from/to 直接读取或写入分区表,提高效率。

9. 支持分区字段的更新,需要添加触发器,如果不需要更新分区字段,则不建议添加这个触发器,会产生一定的性能影响。

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

回调函数的规则如下

$part_init_callback$(args JSONB) RETURNS VOID

 

传入参数如下 

/* RANGE-partitioned table abc (for exp: child abc_4) */

{

    "parent":    "abc",

    "parttype":  "2",

    "partition": "abc_4",

    "range_max": "401",

    "range_min": "301"

}

 

/* HASH-partitioned table abc (for exp: child abc_0) */

{

    "parent":    "abc",

    "parttype":  "1",

    "partition": "abc_0"

}

11. 非堵塞式创建分区表,以及后台自动将主表数据迁移到分区表,非堵塞式。

12. 支持FDW,通过配置参数pg_pathman.insert_into_fdw=(disabled | postgres | any_fdw)支持postgres_fdw或任意fdw

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

以上内容摘自:https://yq.aliyun.com/articles/62314?spm=5176.8091938.0.0.tu4vhW

Pg_pathman安装、配置

Pg_pathman使用了custom scan provider api,所以只支持PostgreSQL 9.5以及以上的版本。

本次pg_pathman安装采用git的方式安装,首先安装git工具:

# yum install git   

切换到admin用户,并进入postgresql目录:

从git克隆pg_pathman:

$ git clone https://github.com/postgrespro/pg_pathman   

导入postgresql安装的环境变量:

$ export PATH=/home/digoal/pgsql9.6:$PATH   

 

$ cd pg_pathman   

$ make USE_PGXS=1   

$ make USE_PGXS=1 install   

 

更改postgresql的配置文件

$ cd $PGDATA   

$ vi postgresql.conf  

 

#将shared_preload_libraries注释取消,将下面变量赋值进去

shared_preload_libraries = 'pg_pathman,pg_stat_statements'  

 

修改完成后,重启数据库服务

$ pg_ctl restart -m fast   

启动数据库报错

这是由于未安装pg_stat_statements模块导致,pg_stat_statements模块位于contrib包中,因为contrib包为第三方工具包,虽然在postgresql数据库的安装包里面已经自带了contrib包,但是在安装数据库的时候并未主动安装,因此需要我们手动安装一下。

进去之前解压以后的postgresql数据库安装包的文件夹

$ cd /usr/local/postgresql-9.6.13  

进入contrib文件夹,一次执行以下命令:

make

make install

执行成功以后,再次启动数据库服务

启动成功

创建扩展

使用psql连接登录数据库

创建扩展

# create extension pg_pathman;  

# create extension pg_stat_statements;

 

查看

# \dx  

 

至此,pg_pathman安装配置完成

posted @ 2019-05-20 15:37  Aiden郭祥跃  阅读(1635)  评论(0编辑  收藏
";