PG的pathman分区表工具

太阳2年前技术文章528

一、概述

在PG<=10的版本中,都是通过表继承的方式进行分区的,必须使用CHECK CONSTRAINT将每个分区创建为子表 。

PostgreSQL 10提供了本机分区,它与经典方法没有什么不同,通过隐式约束条件来实现,并且其大多数限制仍然相关。

源生的两种分区表的实现在执行select/delete/update时执行计划根据约束和查询条件排除不需要查询的分区表。调用COPY或插入数据时使用触发器或规则,将数据插入对应的分区表。PLAN时需要对所有分区创建RangeTblEntry与RelOptInfo结果,同时需要加锁。无论是查询还是插入,对性能的影响都较大。

PG 12 版本以上,原生分区表再次做了一定的优化,其资源消耗和性能方面有了较大的提升。

pathman 是一块比较常用的分区表的管理插件,它将分区配置存储在pathman_config表中,表的信息会缓存在内存中,同时使用HOOK来实现RELATION的替换,所以效率非常高。目前该工具支持range和hash两种分区方式,range使用binary search查找对应的分区,hash使用hash search查找对应的分区。

pg_pathman 用到的hook如下:

  • pg_pathman uses ProcessUtility_hook hook to handle COPY queries for partitioned tables.

  • RuntimeAppend (overrides Append plan node)

  • RuntimeMergeAppend (overrides MergeAppend plan node)

  • PartitionFilter (drop-in replacement for INSERT triggers)

pg_pathman特性:

  • 目前支持HASH分区、RANGE分区。

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

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

  • 有效的分区表查询计划(JOINs、subselects 等)。

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

  • PartitionFilter:一种有效的插入触发器替换方法。

  • 支持自动新增分区(目前仅支持RANGE分区表)。

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

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

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

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

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


二、安装部署

1、下载源码安装包并编译安装

# git clone https://github.com/postgrespro/pg_pathman
# cd /usr/local/pg_pathman
# make USE_PGXS=1
# make USE_PGXS=1 install

2、数据库配置文件修改

$ vi postgres.conf
shared_preload_libraries = 'pg_pathman'

$ pg_ctl restart

该参数设置需要注意的一点是,若数据库存在一些使用相同 hook 函数的工具,可能会导致他们之间对 hook 函数的调用出现冲突。需要在配置文件中规范他们顺序。比较常见的一个场景就是若数据库中安装有pg_stat_statements,其配置顺序为 shared_preload_libraries = 'pg_stat_statements, pg_pathman'。


3、数据库加载扩展包

postgres=# CREATE EXTENSION pg_pathman;
CREATE EXTENSION
postgres=# \dx
                     List of installed extensions
    Name    | Version |   Schema   |           Description
------------+---------+------------+----------------------------------
 pg_pathman | 1.5     | public     | Partitioning tool for PostgreSQL
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)

4、扩展包升级

## 下载安装最新版本的源码包
## 重启数据库服务
## 执行以下命令
ALTER EXTENSION pg_pathman UPDATE;
SET pg_pathman.enable = t;

5、查看已安装插件

postgres=# \dx
                     List of installed extensions
    Name    | Version |   Schema   |           Description
------------+---------+------------+----------------------------------
 pg_pathman | 1.5     | public     | Partitioning tool for PostgreSQL
 plpgsql    | 1.0     | pg_catalog | PL/pgSQL procedural language
(2 rows)


三、基本命令

3.1 相关视图与表

1、分区表元数据信息 pathman_config
db1=# \d+ pathman_config
                                   Table "public.pathman_config"
     Column     |   Type   | Collation | Nullable | Default | Storage  | Stats target | Description
----------------+----------+-----------+----------+---------+----------+--------------+-------------
 partrel        | regclass |           | not null |         | plain    |              |主表oid
 expr           | text     |           | not null |         | extended |              |分区字段
 parttype       | integer  |           | not null |         | plain    |              |分区类型,hash或range
 range_interval | text     |           |          |         | extended |              |range分区的interval
Indexes:
    "pathman_config_pkey" PRIMARY KEY, btree (partrel)
Check constraints:
    "pathman_config_interval_check" CHECK (validate_interval_value(partrel, expr, parttype, range_interval))
    "pathman_config_parttype_check" CHECK (parttype = ANY (ARRAY[1, 2]))
Policies:
    POLICY "allow_select" FOR SELECT
      USING (true)
    POLICY "deny_modification"
      USING (check_security_policy(partrel))
Triggers:
    pathman_config_trigger AFTER INSERT OR DELETE OR UPDATE ON pathman_config FOR EACH ROW EXECUTE FUNCTION pathman_config_params_trigger_func()
Access method: heap
db1=# select * from  pathman_config;
    partrel     |   expr   | parttype | range_interval
----------------+----------+----------+----------------
 part_test      | crt_time |        2 | 1 mon               //主表为part_test、分区字段为crt_time,分区类型2表示range分区,range的interval为1个月
 part_hash_test | crt_time |        1 |                     //主表为part_hash_test,分区字段为crt_time,分区类型1表是hash分区
(2 rows)

2、分区表元数据信息 pathman_config_params

db1=# \d+ pathman_config_params
                                Table "public.pathman_config_params"
     Column      |   Type   | Collation | Nullable | Default | Storage  | Stats target | Description
-----------------+----------+-----------+----------+---------+----------+--------------+-------------
 partrel         | regclass |           | not null |         | plain    |              |主表oid
 enable_parent   | boolean  |           | not null | false   | plain    |              |是否在优化器中过滤主表
 auto            | boolean  |           | not null | true    | plain    |              |insert时是否自动扩展不存在的分区
 init_callback   | text     |           |          |         | extended |              |create partition时的回调函数oid
 spawn_using_bgw | boolean  |           | not null | false   | plain    |              |
Indexes:
    "pathman_config_params_pkey" PRIMARY KEY, btree (partrel)
Check constraints:
    "pathman_config_params_init_callback_check" CHECK (validate_part_callback(
CASE
    WHEN init_callback IS NULL THEN 0::regprocedure
    ELSE init_callback::regprocedure
END))
Policies:
    POLICY "allow_select" FOR SELECT
      USING (true)
    POLICY "deny_modification"
      USING (check_security_policy(partrel))
Triggers:
    pathman_config_params_trigger AFTER INSERT OR DELETE OR UPDATE ON pathman_config_params FOR EACH ROW EXECUTE FUNCTION pathman_config_params_trigger_func()
Access method: heap
db1=# select * from pathman_config_params;
    partrel     | enable_parent | auto | init_callback | spawn_using_bgw
----------------+---------------+------+---------------+-----------------
 part_test      | f             | t    |               | f                  //主表为part_test,数据无法写入到主表,自动扩展分区
 part_hash_test | t             | t    |               | f                  //主表为part_hash_test,允许数据写入到主表,自动扩展分区
(2 rows)

3、分区表后台数据迁移任务信息 pathman_concurrent_part_tasks

db1=# \d+ pathman_concurrent_part_tasks
                  View "public.pathman_concurrent_part_tasks"
  Column   |   Type   | Collation | Nullable | Default | Storage  | Description
-----------+----------+-----------+----------+---------+----------+-------------
 userid    | regrole  |           |          |         | plain    |
 pid       | integer  |           |          |         | plain    |
 dbid      | oid      |           |          |         | plain    |
 relid     | regclass |           |          |         | plain    |
 processed | bigint   |           |          |         | plain    |
 status    | text     |           |          |         | extended |
View definition:
 SELECT show_concurrent_part_tasks.userid,
    show_concurrent_part_tasks.pid,
    show_concurrent_part_tasks.dbid,
    show_concurrent_part_tasks.relid,
    show_concurrent_part_tasks.processed,
    show_concurrent_part_tasks.status
   FROM show_concurrent_part_tasks() show_concurrent_part_tasks(userid, pid, dbid, relid, processed, status);
db1=#
db1=#
db1=# select * from pathman_concurrent_part_tasks;
 userid | pid | dbid | relid | processed | status
--------+-----+------+-------+-----------+--------
(0 rows)

4、分区表的分区字段信息 pathman_partition_list

db1=# \d+ pathman_partition_list
                      View "public.pathman_partition_list"
  Column   |   Type   | Collation | Nullable | Default | Storage  | Description
-----------+----------+-----------+----------+---------+----------+-------------
 parent    | regclass |           |          |         | plain    |主表oid
 partition | regclass |           |          |         | plain    |子分区表
 parttype  | integer  |           |          |         | plain    |分区表类型,1表示range,2表示hash
 expr      | text     |           |          |         | extended |分区字段
 range_min | text     |           |          |         | extended |range的左边界
 range_max | text     |           |          |         | extended |range的右边界
View definition:
 SELECT show_partition_list.parent,
    show_partition_list.partition,
    show_partition_list.parttype,
    show_partition_list.expr,
    show_partition_list.range_min,
    show_partition_list.range_max
   FROM show_partition_list() show_partition_list(parent, partition, parttype, expr, range_min, range_max);
   
db1=# select * from pathman_partition_list;
     parent     |    partition     | parttype |   expr   |      range_min      |      range_max
----------------+------------------+----------+----------+---------------------+---------------------
 part_test      | part_test_1      |        2 | crt_time | 2020-09-01 00:00:00 | 2020-10-01 00:00:00
 part_test      | part_test_2      |        2 | crt_time | 2020-10-01 00:00:00 | 2020-11-01 00:00:00
 part_test      | part_test_3      |        2 | crt_time | 2020-11-01 00:00:00 | 2020-12-01 00:00:00
 part_test      | part_test_4      |        2 | crt_time | 2020-12-01 00:00:00 | 2021-01-01 00:00:00
 part_test      | part_test_5      |        2 | crt_time | 2021-01-01 00:00:00 | 2021-02-01 00:00:00
 part_test      | part_test_6      |        2 | crt_time | 2021-02-01 00:00:00 | 2021-03-01 00:00:00
 part_test      | part_test_7      |        2 | crt_time | 2021-03-01 00:00:00 | 2021-04-01 00:00:00
 part_test      | part_test_8      |        2 | crt_time | 2021-04-01 00:00:00 | 2021-05-01 00:00:00
 part_test      | part_test_9      |        2 | crt_time | 2021-05-01 00:00:00 | 2021-06-01 00:00:00
 part_test      | part_test_10     |        2 | crt_time | 2021-06-01 00:00:00 | 2021-07-01 00:00:00
 part_test      | part_test_11     |        2 | crt_time | 2021-07-01 00:00:00 | 2021-08-01 00:00:00
 part_test      | part_test_12     |        2 | crt_time | 2021-08-01 00:00:00 | 2021-09-01 00:00:00
 part_test      | part_test_13     |        2 | crt_time | 2021-09-01 00:00:00 | 2021-10-01 00:00:00
 part_test      | part_test_14     |        2 | crt_time | 2021-10-01 00:00:00 | 2021-11-01 00:00:00
 part_test      | part_test_15     |        2 | crt_time | 2021-11-01 00:00:00 | 2021-12-01 00:00:00
 part_test      | part_test_16     |        2 | crt_time | 2021-12-01 00:00:00 | 2022-01-01 00:00:00
 part_test      | part_test_17     |        2 | crt_time | 2022-01-01 00:00:00 | 2022-02-01 00:00:00
 part_test      | part_test_18     |        2 | crt_time | 2022-02-01 00:00:00 | 2022-03-01 00:00:00
 part_test      | part_test_19     |        2 | crt_time | 2022-03-01 00:00:00 | 2022-04-01 00:00:00
 part_test      | part_test_20     |        2 | crt_time | 2022-04-01 00:00:00 | 2022-05-01 00:00:00
 part_test      | part_test_21     |        2 | crt_time | 2022-05-01 00:00:00 | 2022-06-01 00:00:00
 part_test      | part_test_22     |        2 | crt_time | 2022-06-01 00:00:00 | 2022-07-01 00:00:00
 part_test      | part_test_23     |        2 | crt_time | 2022-07-01 00:00:00 | 2022-08-01 00:00:00
 part_test      | part_test_24     |        2 | crt_time | 2022-08-01 00:00:00 | 2022-09-01 00:00:00
 part_test      | part_test_25     |        2 | crt_time | 2022-09-01 00:00:00 | 2022-10-01 00:00:00
 part_hash_test | part_hash_test_0 |        1 | crt_time |                     |
 part_hash_test | part_hash_test_1 |        1 | crt_time |                     |
 part_hash_test | part_hash_test_2 |        1 | crt_time |                     |
 part_hash_test | part_hash_test_3 |        1 | crt_time |                     |
(29 rows)

3.2 分区管理函数

1、range分区

1)创建range分区 create_range_partitions

create_range_partitions(relation       REGCLASS,  -- 主表OID
                        attribute      TEXT,      -- 分区列名
                        start_value    ANYELEMENT,  -- 开始值
                        p_interval     ANYELEMENT,  -- 间隔;任意类型,适合任意类型的分区表
                        p_count        INTEGER DEFAULT NULL,   --  分多少个区
                        partition_data BOOLEAN DEFAULT TRUE)   --  是否立即将数据从主表迁移到分区, 不建议这么使用

2)创建range分区 create_partitions_from_range

create_partitions_from_range(relation       REGCLASS,  -- 主表OID
                             attribute      TEXT,      -- 分区列名
                             start_value    ANYELEMENT,  -- 开始值
                             end_value      ANYELEMENT,  -- 结束值
                             p_interval     ANYELEMENT,  -- 间隔;任意类型,适合任意类型的分区表
                             partition_data BOOLEAN DEFAULT TRUE)   --  是否立即将数据从主表迁移到分区, 不建议这么使用

RANGE分区表使用建议:

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

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

  • 创建分区表时建议partition_data设置为false,使用非堵塞式迁移接口,在数据迁移完成后,建议禁用主表。

2、hash分区

1)创建hash分区 create_hash_partitions

create_hash_partitions(relation         REGCLASS,  -- 主表OID
                       attribute        TEXT,      -- 分区列名
                       partitions_count INTEGER,   -- 打算创建多少个分区
                       partition_data   BOOLEAN DEFAULT TRUE)   --  是否立即将数据从主表迁移到分区, 不建议这么使用

在HASH分区表使用建议:

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

  • 创建分区表时建议partition_data设置为false,使用非堵塞式迁移接口迁移,在数据迁移完成后,建议禁用主表。

  • pg_pathman不会受制于表达式的写法,所以select * from part_test where crt_time = '2016-10-25 00:00:00'::timestamp;这样的写法也能用于HASH分区的。

  • HASH分区列不局限于int类型的列,会使用HASH函数自动转换。

3、分区迁移

如果创建分区表时,未将主表数据迁移到分区,那么可以使用非堵塞式的迁移接口,将数据迁移到对应分区。

1)迁移主表数据到子分区表 partition_table_concurrently

partition_table_concurrently(relation   REGCLASS,              -- 主表OID
                             batch_size INTEGER DEFAULT 1000,  -- 一个事务批量迁移多少记录
                             sleep_time FLOAT8 DEFAULT 1.0)    -- 获得行锁失败时,休眠多久再次获取,重试60次退出任务。

2)停止迁移任务

stop_concurrent_part_task(relation REGCLASS)    -- 主表OID

4、分区的分裂与合并

1)range分区分裂

如果某个分区太大,想分裂为两个分区,可以指定该分区分裂为2个子分区表(目前仅支持RANGE分区表),数据会自动迁移到另一个分区。

split_range_partition(partition      REGCLASS,            -- 分区oid
                      split_value    ANYELEMENT,          -- 分裂值
                      partition_name TEXT DEFAULT NULL)   -- 分裂后新增的分区表名

2)多个分区的合并

将两个子分区表进行合并,自动将子分区表2的数据迁移到子分区表1,然后删除子分区表2。(目前仅支持RANGE分区表)

指定两个需要合并分区,必须为相邻分区  
merge_range_partitions(partition1 REGCLASS,               -- 子分区表1
                        partition2 REGCLASS)              -- 相邻的子分区表2

5、新增分区

每个分区表默认是可以自动创建分区的,

1)向后添加范围分区

append_range_partition(parent         REGCLASS,            -- 主表OID
                       partition_name TEXT DEFAULT NULL,   -- 新增的分区表名, 默认不需要输入
                       tablespace     TEXT DEFAULT NULL)   -- 新增的分区表放到哪个表空间, 默认不需要输入

2)向前添加范围分区

prepend_range_partition(parent         REGCLASS,
                        partition_name TEXT DEFAULT NULL,
                        tablespace     TEXT DEFAULT NULL)

3)添加分区

add_range_partition(relation       REGCLASS,    -- 主表OID
                    start_value    ANYELEMENT,  -- 起始值
                    end_value      ANYELEMENT,  -- 结束值
                    partition_name TEXT DEFAULT NULL,  -- 分区名
                    tablespace     TEXT DEFAULT NULL)  -- 分区创建在哪个表空间下

6、删除分区

## 删除指定子分区
drop_range_partition(partition TEXT,   -- 子分区名称
                    delete_data BOOLEAN DEFAULT TRUE)  -- 是否删除分区数据,如果false,表示分区数据迁移到主表。  
## 批量删除全部子分区
drop_partitions(parent      REGCLASS,       -- 主表oid
                delete_data BOOLEAN DEFAULT FALSE) -- 是否删除分区数据,如果false,表示分区数据迁移到主表。


7、绑定/解绑分区

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

将已有的表,绑定到已有的某个分区主表。已有的表与主表要保持一致的结构,包括dropped columns (查看pg_attribute的一致性)

attach_range_partition(relation    REGCLASS,    -- 主表OID
                       partition   REGCLASS,    -- 分区表OID
                       start_value ANYELEMENT,  -- 起始值
                       end_value   ANYELEMENT)  -- 结束值


2)解绑分区(将分区变成普通表)

将分区从主表的继承关系中删除,不删数据,删除继承关系,删除约束。接口如下:

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

8、禁用主表

当主表的数据全部迁移到分区后,可以禁用主表。接口函数如下:

set_enable_parent(relation REGCLASS,      -- 主表名称
                    value BOOLEAN)        -- true/false

9、自动扩展分区

范围分区表,允许自动扩展分区。如果新插入的数据不在已有的分区范围内,会自动创建分区。

set_auto(relation REGCLASS,         -- 主表名称
            value BOOLEAN)          -- true/false


标签: PostgreSQL

相关文章

PG的统计信息(二)

1.2.4 pg_statio_user_tables 指标含义通过对pg_statio_user_tables的查询,如果heap_blks_read,idx_blks_read很高说明shared...

PG的执行计划

一、Explain基本使用1.1 命令解释explain [ ( option [,...] ) ] statement explain [ analyze ] [ verbose ] statem...

PG查询性能Top SQL

一、查询当前正在运行的Top SQL    查询当前正在运行的会话中耗时最长的Top SQL,where条件可按需修改SELECT pgsa.datname AS database_name    ...

pg_dump

逻辑备份    PG提供了pg_dump、pg_dumpall两种方式进行逻辑备份,其区别就是pg_dumpall只能将数据库全部数据集dump到一个脚本文件中,而pg_dump可以选择指定数据库进行...

PG体系结构(三)

PG体系结构(三)

四、物理结构4.1 软件安装目录bin             //二进制可执行文件 include         //头文件目录 lib             //动态库文件 share ...

pg_probackup

一、pg_probackup概述pg_probackup 是一款免费的postgres数据库集群备份工具,与其他备份工具相比,它主要有如下一些优势:提供增量备份,增量备份一定程度上可以节省磁盘空间的使...

发表评论    

◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。