PostgreSQL 分区表为什么要带 pg_pathman 过时了？

PostgreSQL 如果使用较早的“大仙”们，在做分区的时候会提pg_pathman,为什么一个数据库使用分区表还要一个插件，可能习惯商业数据库的“人儿们”，不大理解。这点要从PG的分区表的来源来说， PG的分区表其实是PG的表继承概念的延伸。表继承允许planner只包含那些与查询兼容的子表(分区)。同时，用户在分区管理方面还有很多工作要做:创建继承的表，编写触发器来选择合适的分区进行行插入等。为了自动化这项工作，编写了pg_partman扩展。

其实实际上现在的postgresql 新的版本已经慢慢可以不再使用pathman,尤其新版本12. 但目前还有大量的PG 9.x 在服役，新版本的更换还没有那么快，所以pathman 还有很多使用的空间。当然这里也有当你从一个分区表中提取的数据比较少的情况下，由于早期的pg的版本问题，造成编译查询的速度可能比查询速度还要慢。由于多种早期版本的问题所以引入了pathman.

这点并不值得吐槽，比起有些产品而言，要等待补丁，无尽的补丁，开源的数据库一直在前进，有问题马上会有，“先驱者” 来给你解决这个问题。

目前pg_pathman支持到目前最新的postgresql 12，支持分区的模式有

Hash 和 Range 两种模式

安装很简单，在有PG的环境变量下执行

make install USE_PGXS=1

修改postgresql.conf 文件

shared_preload_libraries = 'pg_pathman'

然后重启动数据库，再在你需要的数据库中执行 create extension pg_pathman 就可以使用大名鼎鼎的 pg_pathman

下面就来校验一下pg_pathman 的使用

1 通过简单的hash 例子来实践pg_pathman

通过下面的脚本，在test数据库中插入partition_table表

#!/usr/bin/python3
# -*- coding: utf-8 -*-
import psycopg2
from psycopg2 import Error

try:  #捕捉连接中的错误
    #连接数据库
    connection = psycopg2.connect(user = "admin",password="admin",host="192.168.198.80",port="5432",database = "test")
    #获得连接产生cursor
    cursor = connection.cursor()
    #执行API中的方法
    print (connection.get_dsn_parameters(),"\n")

    #通过游标来执行SQL语句并传到数据库
    cursor.execute("SELECT version();")
    #取第一行记录
    record = cursor.fetchone()
    print("You are connected to - ", record,"\n")

    num = 1
    while num < 2:
        table_name = "partition_table"
        create_table_query = '''create table if not exists ''' + table_name + ''' (id serial primary key,name varchar(200),insert_date timestamp); '''
        cursor.execute(create_table_query)
        connection.commit()
        print("The table created successfully. ")
        num = num + 1
    times = 1
    num = 1
    while times < 1000000:

        table_name = "partition_table"
        insert_table_query = '''insert into ''' + table_name + '''(name,insert_date) values (''' + str(num) + ''',current_timestamp)'''
        cursor.execute(insert_table_query)
        connection.commit()
        print("insert data ok. ")
        num = num + 1
        times = times + 1
except (Exception, psycopg2.DatabaseError) as error:
    print("Error while creating PostgreSQL table", error)
#如果有错误，则捕捉然后打印错误，这里是无法连接时报错
except (Exception, psycopg2.Error) as error :
    print ("Error while connecting to PostgreSQL", error)
     #在使用完毕后，关闭连接
finally:
    #closing database connection.
        if(connection):
            cursor.close()
            connection.close()
            print("PostgreSQL connection is closed")

2 建立分区表有两种情况，1 分区表在建立前没有数据 2 分区表在建立时已经有数据了，这两种情况是不一样的，主要是数据的迁移的成本需要考虑进去，并且执行的命令也不一样。

1 我们的分区的数据表已经存在了，名字是 partition_table,我们需要根据，1主键的方式来进行hash 分区，分区表 固定10个。

select create_hash_partitions('partition_table','id',10,true);

创建hash 分区的命令很简单 create_hash_partitions ，后面的参数依次是

需要分区的表名， 分区键，想分几个分区表 ，如果分区表存在的情况下，会将数据迁移到分区表，在迁移数据的时候，会进行锁表。

我们看看基本上数据已经很均匀的到了各个分区表。这样的操作方式其实比有些商业数据库的操作还要简单方便，快捷。

可以通过下面的数据来验证，数据拷贝后，hash 分布还是比较的散和匀

那刚才也讲了，我们在需要分区表时，可能数据已经有了几千万了，这时要进行分区，那刚才的命令就不OK了。那我们操作分区表的步骤就会变成下面的样子。

随便的验证了一下，分区表里面的数据，的确是已经有数据进入了

通过上的命令迁移数据的好处在于，他不会阻塞你的数据库系统，并且很多时候建立分区表的时候，正是因为由于初期的设计问题，造成的问题的堆积，很可能已经有了大量的数据，并且需要在线业务工作的过程中，迁移数据，所以下面的命令是很有用的。

最后我们将原表禁用即可，整个的流程HASH 的初级分区表就建立OK了。

实际上建立range 分区也和HASH 大致一样。

总结一下：

• 分区列必须有not null。
• 如果有数据则使用命令将数据迁移，不建议在建立分区的时候就直接迁移数据，特殊情况除外。
• 数据迁移后，禁用主表。
• pg_pathman不会受制于表达式的写法，操作HASH分区简便。
• HASH分区列不局限于int类型，HASH函数自动转换。

Range 分区

select create_range_partitions('partition_table','id',1,10000,100,'true');

通过截图我们可以看到建立一个range 分区也是很简单的事情，分表给出分区键，初始值，间隔值（一个表能承载的数据量或者间接值，生成的表的数量，是否在建立分区的过程中就开始从原表拷贝数据

如果对range 分区中的某个分区不满意可以使用

select plit_range_partition('partition_table_1',40,'partition_table_super');

命令，针对某个分区，输入分区键截断值，以及新的分区的名字，截断值以及下面的值会分割到新的分区中。

同时可以分割range 分区，也可以对range 分区进行合并，合并仅仅能在两个相邻的分区进行，并且合并后，后面的分区会进行清理。

最后就是扩展分区，实际上手工扩展空间是比较容易的，

select append_range_partition('partition_table'::regclass);

执行上面的命令，直接可以在当前分区后进行扩展。

另外还有一些不是特别常用的命令

1 添加分区 add_range_partition

2 删除分区 drop_range_partition

3 drop_partition 删除分区是否将数据在返回到主表

4 添加分区 attach_range_partition

5 解绑分区 detach_range_partition

6 disable_pathman_for

具体的操作可以去查一下官方文档

基于性能的问题上，看PostgreSQL的要使用pathman的原因可以归结为性能与易用性,pathman将分区配置存储在pathman_config表中;每行包含一个分区表的单个条目(关系名、分区列及其类型)

通过上面的截图，可以看到pathman_config 里面存储的数据，我们目前是有一个分区表并且是range分区的。其中包含的信息会有添加的需要分区的表名，分区的主键，分区类型，和分区的中例如如果是range 分区，其中包含range_interval.

另外使用PATHMAN的原因是就是pathman将分区的信息保存在cache中，系统在去对分区的操作会很快获取相关的数据，表的信息会缓存在内存中，同时使用HOOK来实现关系的替换，所以效率高，不会在和不使用pathman的老版本的PG一样陷入性能上的问题。

使用pathman_concurrent_part 可以快速的查看当前是否有正在运行的分区业务，到底是哪个用户在操作，状态是什么

基于性能的问题上，看PostgreSQL的要使用pathman的原因可以归结为pathman,pathman将分区配置存储在pathman_config表中;每行包含一个分区表的单个条目(关系名、分区列及其类型)

通过上面的截图，可以看到pathman_config 里面存储的数据，我们目前是有一个分区表并且是range分区的。其中包含的信息会有添加的需要分区的表名，分区的主键，分区类型，和分区的中例如如果是range 分区，其中包含range_interval.

另外使用PATHMAN的原因是就是pathman将分区的信息保存在cache中，系统在去对分区的操作会很快获取相关的数据，表的信息会缓存在内存中，同时使用HOOK来实现关系的替换，所以效率高，不会在和不使用pathman的老版本的PG一样陷入性能上的问题。

使用pathman_concurrent_part 可以快速的查看当前是否有正在运行的分区业务，到底是哪个用户在操作，状态是什么

基于性能的问题上，看PostgreSQL的要使用pathman的原因可以归结为pathman,pathman将分区配置存储在pathman_config表中;每行包含一个分区表的单个条目(关系名、分区列及其类型)

通过上面的截图，可以看到pathman_config 里面存储的数据，我们目前是有一个分区表并且是range分区的。其中包含的信息会有添加的需要分区的表名，分区的主键，分区类型，和分区的中例如如果是range 分区，其中包含range_interval.

另外使用PATHMAN的原因是就是pathman将分区的信息保存在cache中，系统在去对分区的操作会很快获取相关的数据，表的信息会缓存在内存中，同时使用HOOK来实现关系的替换，所以效率高，不会在和不使用pathman的老版本的PG一样陷入性能上的问题。

使用pathman_concurrent_part 可以快速的查看当前是否有正在运行的分区业务，到底是哪个用户在操作，状态是什么

同时通过pathman_config_params 来查看当前分区表的一些特性，

1 是否打开了父表，是否能插入数据

2 是否在插入数据超过了原有的设置后，会自动触发新建新的分区表

3 当插入超出分区范围的新数据时，使用SpawnPartitionsWorker在单独的事务中创建新分区。

这里注意不建议打开auto ，在插入数据的时候如果发现数据没有落在range分区内会自动建立分区，但实际上如果横跨度很高的情况下，会大量建立分区表。导致某些无法预料的问题

SELECT disable_pathman_for('partition_table');

当决定不再使用pathman的情况下，可以使用上面的命令，将pathman从分区表中分离出来,

整体的分区表就回归postgresql 管理了。

当然PG12 已经使用了新的方法，来操作分区表了，可以不在使用这个插件，但实际上使用了也挺方便。这也说明一点，PG的本身的数据库的程序是越来越完善了。如同MYSQL 里面的很多工具，在MYSQL 8上面开始不在被提起。