作者
digoal
日期
2019-11-13
标签
PostgreSQL , pg_pathman , 分区表 , 转换
背景
pg 12分区表的性能有了质的飞跃，pg_pathman的分区可以转换为pg 12原生分区，不需要迁移数据。只需要改一下分区的继承关系。
pg_pathman的hash分区表转换为pg 原生的list分区。 range分区直接转换。
例子
部署pg_pathman
```
yum install -y pg_pathman11*
vi $PGDATA/postgresql.conf
shared_preload_libraries = 'pg_pathman'
pg_pathman.enable=on
pg_ctl restart -m fast
```
创建插件
CREATE EXTENSION pg_pathman;
pg_pathman的几个常用创建分区表的函数
```
create_hash_partitions(parent_relid REGCLASS,
expression TEXT,
partitions_count INTEGER,
partition_data BOOLEAN DEFAULT TRUE,
partition_names TEXT[] DEFAULT NULL,
tablespaces TEXT[] DEFAULT NULL)
create_range_partitions(parent_relid REGCLASS,
expression TEXT,
start_value ANYELEMENT,
p_interval ANYELEMENT,
p_count INTEGER DEFAULT NULL
partition_data BOOLEAN DEFAULT TRUE)
create_range_partitions(parent_relid REGCLASS,
expression TEXT,
start_value ANYELEMENT,
p_interval INTERVAL,
p_count INTEGER DEFAULT NULL,
partition_data BOOLEAN DEFAULT TRUE)
create_range_partitions(parent_relid REGCLASS,
expression TEXT,
bounds ANYARRAY,
partition_names TEXT[] DEFAULT NULL,
tablespaces TEXT[] DEFAULT NULL,
partition_data BOOLEAN DEFAULT TRUE)
generate_range_bounds(p_start ANYELEMENT,
p_interval INTERVAL,
p_count INTEGER)
generate_range_bounds(p_start ANYELEMENT,
p_interval ANYELEMENT,
p_count INTEGER)
Builds bounds array for create_range_partitions().
```
创建pg_pathman分区表
```
CREATE TABLE items (
id SERIAL PRIMARY KEY,
name TEXT,
code BIGINT);
INSERT INTO items (id, name, code)
SELECT g, md5(g::text), random() * 100000
FROM generate_series(1, 100000) as g;
```
Now run the create_hash_partitions() function with appropriate arguments:
SELECT create_hash_partitions('items', 'id', 4);
查看items分区定义
postgres=# \d+ items
Table "public.items"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+-----------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('items_id_seq'::regclass) | plain | |
name | text | | | | extended | |
code | bigint | | | | plain | |
Indexes:
"items_pkey" PRIMARY KEY, btree (id)
Child tables: items_0,
items_1,
items_2,
items_3
采用了get_hash_part_idx(hashint4(id), 4)分区定义，对应的是原生list分区，所以转换为原生分区需要使用list分区
postgres=# \d items_0
Table "public.items_0"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+-----------------------------------
id | integer | | not null | nextval('items_id_seq'::regclass)
name | text | | |
code | bigint | | |
Indexes:
"items_0_pkey" PRIMARY KEY, btree (id)
Check constraints:
"pathman_items_0_check" CHECK (get_hash_part_idx(hashint4(id), 4) = 0)
Inherits: items
作为list分区，必须使用immutable函数，可以修改一下函数属性
postgres=# \df+ get_hash_part_idx
List of functions
Schema | Name | Result data type | Argument data types | Type | Volatility | Parallel | Owner | Security | Access privileges | Language | Source code | Description
--------+-------------------+------------------+---------------------+------+------------+----------+----------+----------+-------------------+----------+-------------------+-------------
public | get_hash_part_idx | integer | integer, integer | func | volatile | unsafe | postgres | invoker | | c | get_hash_part_idx |
(1 row)
修改为immutable 函数
postgres=# alter function get_hash_part_idx(int,int) immutable;
ALTER FUNCTION
转换例子
创建4096个分区的pg_pathman分区表，写入100万数据。
```
postgres=# drop table items cascade;
postgres=# CREATE TABLE items (
id SERIAL PRIMARY KEY,
name TEXT,
code BIGINT);
CREATE TABLE
Time: 5.738 ms
postgres=# SELECT create_hash_partitions('items', 'id', 4096);
create_hash_partitions
4096
(1 row)
Time: 12132.819 ms (00:12.133)
postgres=# insert into items select generate_series(1,1000000);
INSERT 0 1000000
Time: 6701.793 ms (00:06.702)
```
转换方法如下
no inherit
attach
do language plpgsql $$
declare
p int := 4096;
begin
execute format('create table pgitems (like items) partition by list (get_hash_part_idx(hashint4(id), %s))', p);
for i in 0..p-1 loop
execute format('alter table items_%s no inherit items;', i);
execute format('alter table pgitems ATTACH PARTITION items_%s FOR VALUES in (%s)', i, i);
end loop;
end;
$$;
查询转换后的数据
```
postgres=# select count(*) from pgitems ;
count
1000000
(1 row)
```
转换后，原生写入速度和pg_pathman一样
```
truncate pgitems;
postgres=# insert into pgitems select generate_series(1,1000000);
INSERT 0 1000000
Time: 6545.695 ms (00:06.546)
```
参考
https://github.com/postgrespro/pg_pathman/blob/master/README.md
PostgreSQL 许愿链接
您的愿望将传达给PG kernel hacker、数据库厂商等, 帮助提高数据库产品质量和功能, 说不定下一个PG版本就有您提出的功能点. 针对非常好的提议，奖励限量版PG文化衫、纪念品、贴纸、PG热门书籍等，奖品丰富，快来许愿。开不开森.
9.9元购买3个月阿里云RDS PostgreSQL实例
PostgreSQL 解决方案集合
德哥 / digoal's github - 公益是一辈子的事.
