13. 永久禁止分区表pg_pathman插件
你可以针对单个分区主表禁用pg_pathman。
接口函数如下
disable_pathman_for(relation TEXT)
Permanently disable pg_pathman partitioning mechanism for the specified parent table and remove the insert trigger if it exists.
All partitions and data remain unchanged.
postgres=# \sf disable_pathman_for
CREATE OR REPLACE FUNCTION public.disable_pathman_for(parent_relid regclass)
RETURNS void
LANGUAGE plpgsql
STRICT
AS $function$
BEGIN
PERFORM public.validate_relname(parent_relid);
DELETE FROM public.pathman_config WHERE partrel = parent_relid;
PERFORM public.drop_triggers(parent_relid);
/* Notify backend about changes */
PERFORM public.on_remove_partitions(parent_relid);
END
$function$
例子
postgres=# select disable_pathman_for('part_test');
NOTICE: drop cascades to 23 other objects
DETAIL: drop cascades to trigger part_test_upd_trig on table part_test_3
drop cascades to trigger part_test_upd_trig on table part_test_4
drop cascades to trigger part_test_upd_trig on table part_test_5
drop cascades to trigger part_test_upd_trig on table part_test_6
drop cascades to trigger part_test_upd_trig on table part_test_7
drop cascades to trigger part_test_upd_trig on table part_test_8
drop cascades to trigger part_test_upd_trig on table part_test_9
drop cascades to trigger part_test_upd_trig on table part_test_10
drop cascades to trigger part_test_upd_trig on table part_test_11
drop cascades to trigger part_test_upd_trig on table part_test_12
drop cascades to trigger part_test_upd_trig on table part_test_13
drop cascades to trigger part_test_upd_trig on table part_test_14
drop cascades to trigger part_test_upd_trig on table part_test_15
drop cascades to trigger part_test_upd_trig on table part_test_16
drop cascades to trigger part_test_upd_trig on table part_test_17
drop cascades to trigger part_test_upd_trig on table part_test_18
drop cascades to trigger part_test_upd_trig on table part_test_19
drop cascades to trigger part_test_upd_trig on table part_test_20
drop cascades to trigger part_test_upd_trig on table part_test_21
drop cascades to trigger part_test_upd_trig on table part_test_22
drop cascades to trigger part_test_upd_trig on table part_test_23
drop cascades to trigger part_test_upd_trig on table part_test_24
drop cascades to trigger part_test_upd_trig on table part_test_25
disable_pathman_for
---------------------
(1 row)
postgres=# \d+ part_test
Table "public.part_test"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | not null | plain | |
Child tables: part_test_10,
part_test_11,
part_test_12,
part_test_13,
part_test_14,
part_test_15,
part_test_16,
part_test_17,
part_test_18,
part_test_19,
part_test_20,
part_test_21,
part_test_22,
part_test_23,
part_test_24,
part_test_25,
part_test_26,
part_test_27,
part_test_28,
part_test_29,
part_test_3,
part_test_30,
part_test_31,
part_test_32,
part_test_33,
part_test_34,
part_test_35,
part_test_4,
part_test_5,
part_test_6,
part_test_7,
part_test_8,
part_test_9
postgres=# \d+ part_test_10
Table "public.part_test_10"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | not null | plain | |
Check constraints:
"pathman_part_test_10_3_check" CHECK (crt_time >= '2017-06-25 00:00:00'::timestamp without time zone AND crt_time < '2017-07-25 00:00:00'::timestamp without time zone)
Inherits: part_test
禁用pg_pathman后，继承关系和约束不会变化，只是pg_pathman不介入custom scan 执行计划。
禁用pg_pathman后的执行计划
postgres=# explain select * from part_test where crt_time='2017-06-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 = '2017-06-25 00:00:00'::timestamp without time zone)
-> Seq Scan on part_test_10 (cost=0.00..16.00 rows=1 width=45)
Filter: (crt_time = '2017-06-25 00:00:00'::timestamp without time zone)
(5 rows)
disable_pathman_for没有可逆操作，请慎用。
14. 全局禁止pg_pathman
与禁用单个分区主表不同，全局禁止只需要调整参数即可，不需要修改pg_pathman的元数据，同时它是可逆操作。
pg_pathman.enable
例子
$ vi $PGDATA/postgresql.conf
pg_pathman.enable = off
$ pg_ctl reload
四.5 分区表高级管理
1. 禁用主表
当主表的数据全部迁移到分区后，可以禁用主表。
接口函数如下
set_enable_parent(relation REGCLASS, value BOOLEAN)
Include/exclude parent table into/from query plan.
In original PostgreSQL planner parent table is always included into query plan even if it's empty which can lead to additional overhead.
You can use disable_parent() if you are never going to use parent table as a storage.
Default value depends on the partition_data parameter that was specified during initial partitioning in create_range_partitions() or create_partitions_from_range() functions.
If the partition_data parameter was true then all data have already been migrated to partitions and parent table disabled.
Otherwise it is enabled.
例子
select set_enable_parent('part_test', false);
2. 自动扩展分区
范围分区表，允许自动扩展分区。
如果新插入的数据不在已有的分区范围内，会自动创建分区。
set_auto(relation REGCLASS, value BOOLEAN)
Enable/disable auto partition propagation (only for RANGE partitioning).
It is enabled by default.
例子
postgres=# \d+ part_test
Table "public.part_test"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | not null | plain | |
Child tables: part_test_10,
part_test_11,
part_test_12,
part_test_13,
part_test_14,
part_test_15,
part_test_16,
part_test_17,
part_test_18,
part_test_19,
part_test_20,
part_test_21,
part_test_22,
part_test_23,
part_test_24,
part_test_25,
part_test_26,
part_test_3,
part_test_4,
part_test_5,
part_test_6,
part_test_7,
part_test_8,
part_test_9
postgres=# \d+ part_test_26
Table "public.part_test_26"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | not null | plain | |
Check constraints:
"pathman_part_test_26_3_check" CHECK (crt_time >= '2018-09-25 00:00:00'::timestamp without time zone AND crt_time < '2018-10-25 00:00:00'::timestamp without time zone)
Inherits: part_test
postgres=# \d+ part_test_25
Table "public.part_test_25"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | not null | plain | |
Check constraints:
"pathman_part_test_25_3_check" CHECK (crt_time >= '2018-08-25 00:00:00'::timestamp without time zone AND crt_time < '2018-09-25 00:00:00'::timestamp without time zone)
Inherits: part_test
插入一个不在已有分区范围的值，会根据创建分区时的interval自动扩展若干个分区，这个操作可能很久很久。
postgres=# insert into part_test values (1,'test','2222-01-01'::timestamp);
等了很久
21298 digoal 20 0 93.1g 184m 127m R 98.7 0.1 0:33.34 postgres: bgworker: SpawnPartitionsWorker
插入结束后，扩展了好多好多分区，原因是插入的值跨度范围太大了。
postgres=# \d+ part_test
Table "public.part_test"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | not null | plain | |
Child tables: part_test_10,
part_test_100,
part_test_1000,
part_test_1001,
.....................................
很多
不建议开启自动扩展范围分区
3. 回调函数 (创建每个分区时都会触发)
回调函数是在每创建一个分区时会自动触发调用的函数。
例如可以用在ddl逻辑复制中，将DDL语句记录下来，存放到表中。
回调函数如下
set_init_callback(relation REGCLASS, callback REGPROC DEFAULT 0)
Set partition creation callback to be invoked for each attached or created partition (both HASH and RANGE).
The callback must have the following signature:
part_init_callback(args JSONB) RETURNS VOID.
Parameter arg consists of several fields whose presence depends on partitioning type:
/* RANGE-partitioned table abc (child abc_4) */
{
"parent": "abc",
"parttype": "2",
"partition": "abc_4",
"range_max": "401",
"range_min": "301"
}
/* HASH-partitioned table abc (child abc_0) */
{
"parent": "abc",
"parttype": "1",
"partition": "abc_0"
}
例子
回调函数
postgres=# create or replace function f_callback_test(jsonb) returns void as
$$
declare
begin
create table if not exists rec_part_ddl(id serial primary key, parent name, parttype int, partition name, range_max text, range_min text);
if ($1->>'parttype')::int = 1 then
raise notice 'parent: %, parttype: %, partition: %', $1->>'parent', $1->>'parttype', $1->>'partition';
insert into rec_part_ddl(parent, parttype, partition) values (($1->>'parent')::name, ($1->>'parttype')::int, ($1->>'partition')::name);
elsif ($1->>'parttype')::int = 2 then
raise notice 'parent: %, parttype: %, partition: %, range_max: %, range_min: %', $1->>'parent', $1->>'parttype', $1->>'partition', $1->>'range_max', $1->>'range_min';
insert into rec_part_ddl(parent, parttype, partition, range_max, range_min) values (($1->>'parent')::name, ($1->>'parttype')::int, ($1->>'partition')::name, $1->>'range_max', $1->>'range_min');
end if;
end;
$$ language plpgsql strict;
测试表
postgres=# create table tt(id int, info text, crt_time timestamp not null);
CREATE TABLE
设置测试表的回调函数
select set_init_callback('tt'::regclass, 'f_callback_test'::regproc);
创建分区
postgres=# select
create_range_partitions('tt'::regclass, -- 主表OID
'crt_time', -- 分区列名
'2016-10-25 00:00:00'::timestamp, -- 开始值
interval '1 month', -- 间隔；interval 类型，用于时间分区表
24, -- 分多少个区
false) ;
create_range_partitions
-------------------------
24
(1 row)
检查回调函数是否已调用
postgres=# select * from rec_part_ddl;
id | parent | parttype | partition | range_max | range_min
----+--------+----------+-----------+---------------------+---------------------
1 | tt | 2 | tt_1 | 2016-11-25 00:00:00 | 2016-10-25 00:00:00
2 | tt | 2 | tt_2 | 2016-12-25 00:00:00 | 2016-11-25 00:00:00
3 | tt | 2 | tt_3 | 2017-01-25 00:00:00 | 2016-12-25 00:00:00
4 | tt | 2 | tt_4 | 2017-02-25 00:00:00 | 2017-01-25 00:00:00
5 | tt | 2 | tt_5 | 2017-03-25 00:00:00 | 2017-02-25 00:00:00
6 | tt | 2 | tt_6 | 2017-04-25 00:00:00 | 2017-03-25 00:00:00
7 | tt | 2 | tt_7 | 2017-05-25 00:00:00 | 2017-04-25 00:00:00
8 | tt | 2 | tt_8 | 2017-06-25 00:00:00 | 2017-05-25 00:00:00
9 | tt | 2 | tt_9 | 2017-07-25 00:00:00 | 2017-06-25 00:00:00
10 | tt | 2 | tt_10 | 2017-08-25 00:00:00 | 2017-07-25 00:00:00
11 | tt | 2 | tt_11 | 2017-09-25 00:00:00 | 2017-08-25 00:00:00
12 | tt | 2 | tt_12 | 2017-10-25 00:00:00 | 2017-09-25 00:00:00
13 | tt | 2 | tt_13 | 2017-11-25 00:00:00 | 2017-10-25 00:00:00
14 | tt | 2 | tt_14 | 2017-12-25 00:00:00 | 2017-11-25 00:00:00
15 | tt | 2 | tt_15 | 2018-01-25 00:00:00 | 2017-12-25 00:00:00
16 | tt | 2 | tt_16 | 2018-02-25 00:00:00 | 2018-01-25 00:00:00
17 | tt | 2 | tt_17 | 2018-03-25 00:00:00 | 2018-02-25 00:00:00
18 | tt | 2 | tt_18 | 2018-04-25 00:00:00 | 2018-03-25 00:00:00
19 | tt | 2 | tt_19 | 2018-05-25 00:00:00 | 2018-04-25 00:00:00
20 | tt | 2 | tt_20 | 2018-06-25 00:00:00 | 2018-05-25 00:00:00
21 | tt | 2 | tt_21 | 2018-07-25 00:00:00 | 2018-06-25 00:00:00
22 | tt | 2 | tt_22 | 2018-08-25 00:00:00 | 2018-07-25 00:00:00
23 | tt | 2 | tt_23 | 2018-09-25 00:00:00 | 2018-08-25 00:00:00
24 | tt | 2 | tt_24 | 2018-10-25 00:00:00 | 2018-09-25 00:00:00
(24 rows)
五、性能测试
前面介绍了pg_pathman的用法，以及它为什么高效。
接下来对比一下pg_pathman与传统分区表的效率。
1. pg_pathman vs 传统分区表
传统分区表使用触发器完成对写操作的分区选择。
传统分区表
create table test_pg_part_orig(id int primary key, info text, crt_time timestamp);
create table test_pg_part_orig_1(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_2(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_3(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_4(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_5(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_6(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_7(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_8(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_9(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_10(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_11(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_12(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_13(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_14(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_15(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_16(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_17(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_18(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_19(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_20(like test_pg_part_orig including all) inherits(test_pg_part_orig);
alter table test_pg_part_orig_1 add constraint ck_test_pg_part_orig_1 check(id >=1 and id<1000001);
alter table test_pg_part_orig_2 add constraint ck_test_pg_part_orig_2 check(id >=1000000 and id<2000001);
alter table test_pg_part_orig_3 add constraint ck_test_pg_part_orig_3 check(id >=2000000 and id<3000001);
alter table test_pg_part_orig_4 add constraint ck_test_pg_part_orig_4 check(id >=3000000 and id<4000001);
alter table test_pg_part_orig_5 add constraint ck_test_pg_part_orig_5 check(id >=4000000 and id<5000001);
alter table test_pg_part_orig_6 add constraint ck_test_pg_part_orig_6 check(id >=5000000 and id<6000001);
alter table test_pg_part_orig_7 add constraint ck_test_pg_part_orig_7 check(id >=6000000 and id<7000001);
alter table test_pg_part_orig_8 add constraint ck_test_pg_part_orig_8 check(id >=7000000 and id<8000001);
alter table test_pg_part_orig_9 add constraint ck_test_pg_part_orig_9 check(id >=8000000 and id<9000001);
alter table test_pg_part_orig_10 add constraint ck_test_pg_part_orig_10 check(id >=9000000 and id<10000001);
alter table test_pg_part_orig_11 add constraint ck_test_pg_part_orig_11 check(id >=10000000 and id<11000001);
alter table test_pg_part_orig_12 add constraint ck_test_pg_part_orig_12 check(id >=11000000 and id<12000001);
alter table test_pg_part_orig_13 add constraint ck_test_pg_part_orig_13 check(id >=12000000 and id<13000001);
alter table test_pg_part_orig_14 add constraint ck_test_pg_part_orig_14 check(id >=13000000 and id<14000001);
alter table test_pg_part_orig_15 add constraint ck_test_pg_part_orig_15 check(id >=14000000 and id<15000001);
alter table test_pg_part_orig_16 add constraint ck_test_pg_part_orig_16 check(id >=15000000 and id<16000001);
alter table test_pg_part_orig_17 add constraint ck_test_pg_part_orig_17 check(id >=16000000 and id<17000001);
alter table test_pg_part_orig_18 add constraint ck_test_pg_part_orig_18 check(id >=17000000 and id<18000001);
alter table test_pg_part_orig_19 add constraint ck_test_pg_part_orig_19 check(id >=18000000 and id<19000001);
alter table test_pg_part_orig_20 add constraint ck_test_pg_part_orig_20 check(id >=19000000 and id<20000001);
create or replace function tg_ins() returns trigger as $$
declare
id int := NEW.id;
begin
if NEW.id >=1 and NEW.id<1000001 then
insert into test_pg_part_orig_1 values (NEW.*);
elsif NEW.id >=1000000 and NEW.id<2000001 then
insert into test_pg_part_orig_2 values (NEW.*);
elsif NEW.id >=2000000 and NEW.id<3000001 then
insert into test_pg_part_orig_3 values (NEW.*);
elsif NEW.id >=3000000 and NEW.id<4000001 then
insert into test_pg_part_orig_4 values (NEW.*);
elsif NEW.id >=4000000 and NEW.id<5000001 then
insert into test_pg_part_orig_5 values (NEW.*);
elsif NEW.id >=5000000 and NEW.id<6000001 then
insert into test_pg_part_orig_6 values (NEW.*);
elsif NEW.id >=6000000 and NEW.id<7000001 then
insert into test_pg_part_orig_7 values (NEW.*);
elsif NEW.id >=7000000 and NEW.id<8000001 then
insert into test_pg_part_orig_8 values (NEW.*);
elsif NEW.id >=8000000 and NEW.id<9000001 then
insert into test_pg_part_orig_9 values (NEW.*);
elsif NEW.id >=9000000 and NEW.id<10000001 then
insert into test_pg_part_orig_10 values (NEW.*);
elsif NEW.id >=10000000 and NEW.id<11000001 then
insert into test_pg_part_orig_11 values (NEW.*);
elsif NEW.id >=11000000 and NEW.id<12000001 then
insert into test_pg_part_orig_12 values (NEW.*);
elsif NEW.id >=12000000 and NEW.id<13000001 then
insert into test_pg_part_orig_13 values (NEW.*);
elsif NEW.id >=13000000 and NEW.id<14000001 then
insert into test_pg_part_orig_14 values (NEW.*);
elsif NEW.id >=14000000 and NEW.id<15000001 then
insert into test_pg_part_orig_15 values (NEW.*);
elsif NEW.id >=15000000 and NEW.id<16000001 then
insert into test_pg_part_orig_16 values (NEW.*);
elsif NEW.id >=16000000 and NEW.id<17000001 then
insert into test_pg_part_orig_17 values (NEW.*);
elsif NEW.id >=17000000 and NEW.id<18000001 then
insert into test_pg_part_orig_18 values (NEW.*);
elsif NEW.id >=18000000 and NEW.id<19000001 then
insert into test_pg_part_orig_19 values (NEW.*);
elsif NEW.id >=19000000 and NEW.id<20000001 then
insert into test_pg_part_orig_20 values (NEW.*);
else
-- 超出范围抛出异常
raise exception 'id: % out of range', NEW.id;
end if;
return null;
end;
$$ language plpgsql;
postgres=# create trigger tg_ins before insert on test_pg_part_orig for each row execute procedure tg_ins();
CREATE TRIGGER
postgres=# insert into test_pg_part_orig values (0);
ERROR: id: 0 out of range
CONTEXT: PL/pgSQL function tg_ins() line 27 at RAISE
postgres=# insert into test_pg_part_orig values (1);
INSERT 0 0
postgres=# select * from test_pg_part_orig;
id | info | crt_time
----+------+----------
1 | |
(1 row)
postgres=# select * from test_pg_part_orig where id=1;
id | info | crt_time
----+------+----------
1 | |
(1 row)
postgres=# explain select * from test_pg_part_orig where id=1;
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Append (cost=0.00..2.17 rows=2 width=44)
-> Seq Scan on test_pg_part_orig (cost=0.00..0.00 rows=1 width=44)
Filter: (id = 1)
-> Index Scan using test_pg_part_orig_1_pkey on test_pg_part_orig_1 (cost=0.15..2.17 rows=1 width=44)
Index Cond: (id = 1)
(5 rows)
pg_pathman分区表
create table test_pg_part_pathman(id int primary key, info text, crt_time timestamp);
select
create_range_partitions('test_pg_part_pathman'::regclass, -- 主表OID
'id', -- 分区列名
1, -- 开始值
1000000, -- 间隔
20, -- 分多少个区
true) ; -- 迁移数据
postgres=# select set_enable_parent('test_pg_part_pathman'::regclass, false);
postgres=# \d+ test_pg_part_pathman
Table "public.test_pg_part_pathman"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | not null | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | | plain | |
Indexes:
"test_pg_part_pathman_pkey" PRIMARY KEY, btree (id)
Child tables: test_pg_part_pathman_1,
test_pg_part_pathman_10,
test_pg_part_pathman_11,
test_pg_part_pathman_12,
test_pg_part_pathman_13,
test_pg_part_pathman_14,
test_pg_part_pathman_15,
test_pg_part_pathman_16,
test_pg_part_pathman_17,
test_pg_part_pathman_18,
test_pg_part_pathman_19,
test_pg_part_pathman_2,
test_pg_part_pathman_20,
test_pg_part_pathman_3,
test_pg_part_pathman_4,
test_pg_part_pathman_5,
test_pg_part_pathman_6,
test_pg_part_pathman_7,
test_pg_part_pathman_8,
test_pg_part_pathman_9
postgres=# \d+ test_pg_part_pathman_1
Table "public.test_pg_part_pathman_1"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | not null | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | | plain | |
Indexes:
"test_pg_part_pathman_1_pkey" PRIMARY KEY, btree (id)
Check constraints:
"pathman_test_pg_part_pathman_1_1_check" CHECK (id >= 1 AND id < 1000001)
Inherits: test_pg_part_pathman
postgres=# \d+ test_pg_part_pathman_10
Table "public.test_pg_part_pathman_10"
Column | Type | Modifiers | Storage | Stats target | Description
----------+-----------------------------+-----------+----------+--------------+-------------
id | integer | not null | plain | |
info | text | | extended | |
crt_time | timestamp without time zone | | plain | |
Indexes:
"test_pg_part_pathman_10_pkey" PRIMARY KEY, btree (id)
Check constraints:
"pathman_test_pg_part_pathman_10_1_check" CHECK (id >= 9000001 AND id < 10000001)
Inherits: test_pg_part_pathman
性能对比
1. 插入
传统分区表
postgres=# \timing
Timing is on.
postgres=# truncate test_pg_part_orig;
postgres=# insert into test_pg_part_orig select generate_series(1,20000000);
INSERT 0 0
Time: 647028.838 ms
postgres=# select count(*) from test_pg_part_orig;
count
----------
20000000
(1 row)
Time: 1879.631 ms
pg_pathman分区表
postgres=# insert into test_pg_part_pathman select generate_series(1,20000000);
INSERT 0 20000000
Time: 61634.401 ms
postgres=# select count(*) from test_pg_part_pathman;
count
----------
20000000
(1 row)
Time: 1879.867 ms
2. 查询
传统分区表
$ vi test1.sql
\set id random(1,20000000)
select * from test_pg_part_orig where id=:id;
$ pgbench -M prepared -n -r -P 1 -f ./test1.sql -c 64 -j 64 -T 120
tps = 75102.033587 (including connections establishing)
tps = 75104.843437 (excluding connections establishing)
pg_pathman分区表
$ vi test2.sql
\set id random(1,20000000)
select * from test_pg_part_pathman where id=:id;
$ pgbench -M simple -n -r -P 1 -f ./test2.sql -c 64 -j 64 -T 120
tps = 420535.120243 (including connections establishing)
tps = 420549.323880 (excluding connections establishing)
目前pg_pathman使用prepared statement会导致大量的LWLOCK，需要优化，所以这里先使用了simple query
已提issue
3. 更新
传统分区表
$ vi test1.sql
\set id random(1,20000000)
update test_pg_part_orig set info='test' where id=:id;
$ pgbench -M prepared -n -r -P 1 -f ./test1.sql -c 64 -j 64 -T 120
tps = 56540.152159 (including connections establishing)
tps = 56542.070077 (excluding connections establishing)
pg_pathman分区表
$ vi test2.sql
\set id random(1,20000000)
update test_pg_part_pathman set info='test' where id=:id;
$ pgbench -M simple -n -r -P 1 -f ./test2.sql -c 64 -j 64 -T 120
tps = 224089.981643 (including connections establishing)
tps = 224098.969105 (excluding connections establishing)
2. pg_pathman vs 单表
postgres=# create table test_pg_part_single(id int primary key, info text, crt_time timestamp);
postgres=# insert into test_pg_part_single select generate_series(1,20000000);
INSERT 0 20000000
Time: 46749.048 ms
$ vi test3.sql
\set id random(1,20000000)
select * from test_pg_part_single where id=:id;
$ pgbench -M prepared -n -r -P 1 -f ./test3.sql -c 64 -j 64 -T 120
tps = 1071941.086992 (including connections establishing)
tps = 1071986.078786 (excluding connections establishing)
$ vi test3.sql
\set id random(1,20000000)
update test_pg_part_single set info='test' where id=:id;
$ pgbench -M prepared -n -r -P 1 -f ./test3.sql -c 64 -j 64 -T 120
tps = 262356.355517 (including connections establishing)
tps = 262365.373182 (excluding connections establishing)
性能测试数据对比如图
pic2
3. pg_pathman hash分区表性能对比传统分区表
传统分区表
create table test_pg_part_orig(id int primary key, info text, crt_time timestamp);
create table test_pg_part_orig_1(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_2(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_3(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_4(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_5(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_6(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_7(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_8(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_9(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_10(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_11(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_12(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_13(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_14(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_15(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_16(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_17(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_18(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_19(like test_pg_part_orig including all) inherits(test_pg_part_orig);
create table test_pg_part_orig_20(like test_pg_part_orig including all) inherits(test_pg_part_orig);
alter table test_pg_part_orig_1 add constraint ck_test_pg_part_orig_1 check(mod(id,20)=0);
alter table test_pg_part_orig_2 add constraint ck_test_pg_part_orig_2 check(mod(id,20)=1);
alter table test_pg_part_orig_3 add constraint ck_test_pg_part_orig_3 check(mod(id,20)=2);
alter table test_pg_part_orig_4 add constraint ck_test_pg_part_orig_4 check(mod(id,20)=3);
alter table test_pg_part_orig_5 add constraint ck_test_pg_part_orig_5 check(mod(id,20)=4);
alter table test_pg_part_orig_6 add constraint ck_test_pg_part_orig_6 check(mod(id,20)=5);
alter table test_pg_part_orig_7 add constraint ck_test_pg_part_orig_7 check(mod(id,20)=6);
alter table test_pg_part_orig_8 add constraint ck_test_pg_part_orig_8 check(mod(id,20)=7);
alter table test_pg_part_orig_9 add constraint ck_test_pg_part_orig_9 check(mod(id,20)=8);
alter table test_pg_part_orig_10 add constraint ck_test_pg_part_orig_10 check(mod(id,20)=9);
alter table test_pg_part_orig_11 add constraint ck_test_pg_part_orig_11 check(mod(id,20)=10);
alter table test_pg_part_orig_12 add constraint ck_test_pg_part_orig_12 check(mod(id,20)=11);
alter table test_pg_part_orig_13 add constraint ck_test_pg_part_orig_13 check(mod(id,20)=12);
alter table test_pg_part_orig_14 add constraint ck_test_pg_part_orig_14 check(mod(id,20)=13);
alter table test_pg_part_orig_15 add constraint ck_test_pg_part_orig_15 check(mod(id,20)=14);
alter table test_pg_part_orig_16 add constraint ck_test_pg_part_orig_16 check(mod(id,20)=15);
alter table test_pg_part_orig_17 add constraint ck_test_pg_part_orig_17 check(mod(id,20)=16);
alter table test_pg_part_orig_18 add constraint ck_test_pg_part_orig_18 check(mod(id,20)=17);
alter table test_pg_part_orig_19 add constraint ck_test_pg_part_orig_19 check(mod(id,20)=18);
alter table test_pg_part_orig_20 add constraint ck_test_pg_part_orig_20 check(mod(id,20)=19);
create or replace function tg_ins() returns trigger as $$
declare
id int := NEW.id;
begin
if mod(id,20)=0 then
insert into test_pg_part_orig_1 values (NEW.*);
elsif mod(id,20)=1 then
insert into test_pg_part_orig_2 values (NEW.*);
elsif mod(id,20)=2 then
insert into test_pg_part_orig_3 values (NEW.*);
elsif mod(id,20)=3 then
insert into test_pg_part_orig_4 values (NEW.*);
elsif mod(id,20)=4 then
insert into test_pg_part_orig_5 values (NEW.*);
elsif mod(id,20)=5 then
insert into test_pg_part_orig_6 values (NEW.*);
elsif mod(id,20)=6 then
insert into test_pg_part_orig_7 values (NEW.*);
elsif mod(id,20)=7 then
insert into test_pg_part_orig_8 values (NEW.*);
elsif mod(id,20)=8 then
insert into test_pg_part_orig_9 values (NEW.*);
elsif mod(id,20)=9 then
insert into test_pg_part_orig_10 values (NEW.*);
elsif mod(id,20)=10 then
insert into test_pg_part_orig_11 values (NEW.*);
elsif mod(id,20)=11 then
insert into test_pg_part_orig_12 values (NEW.*);
elsif mod(id,20)=12 then
insert into test_pg_part_orig_13 values (NEW.*);
elsif mod(id,20)=13 then
insert into test_pg_part_orig_14 values (NEW.*);
elsif mod(id,20)=14 then
insert into test_pg_part_orig_15 values (NEW.*);
elsif mod(id,20)=15 then
insert into test_pg_part_orig_16 values (NEW.*);
elsif mod(id,20)=16 then
insert into test_pg_part_orig_17 values (NEW.*);
elsif mod(id,20)=17 then
insert into test_pg_part_orig_18 values (NEW.*);
elsif mod(id,20)=18 then
insert into test_pg_part_orig_19 values (NEW.*);
elsif mod(id,20)=19 then
insert into test_pg_part_orig_20 values (NEW.*);
else
-- 超出范围抛出异常
raise exception 'id: % out of range', NEW.id;
end if;
return null;
end;
$$ language plpgsql;
postgres=# create trigger tg_ins before insert on test_pg_part_orig for each row execute procedure tg_ins();
CREATE TRIGGER
postgres=# insert into test_pg_part_orig values (0);
ERROR: id: 0 out of range
CONTEXT: PL/pgSQL function tg_ins() line 27 at RAISE
postgres=# insert into test_pg_part_orig values (1);
INSERT 0 0
postgres=# select * from test_pg_part_orig;
id | info | crt_time
----+------+----------
1 | |
(1 row)
postgres=# select * from test_pg_part_orig where id=1;
id | info | crt_time
----+------+----------
1 | |
(1 row)
使用这种写法才能正确的选择分区表
postgres=# explain select * from test_pg_part_orig where id=1 and mod(id,20)=mod(1,20);
QUERY PLAN
-----------------------------------------------------------------------------------------------------------
Append (cost=0.00..2.18 rows=2 width=44)
-> Seq Scan on test_pg_part_orig (cost=0.00..0.00 rows=1 width=44)
Filter: ((id = 1) AND (mod(id, 20) = 1))
-> Index Scan using test_pg_part_orig_2_pkey on test_pg_part_orig_2 (cost=0.15..2.18 rows=1 width=44)
Index Cond: (id = 1)
Filter: (mod(id, 20) = 1)
(6 rows)
pg_pathman分区表
创建20个分区
create table test_hash_pathman(id int primary key, info text, crt_time timestamp);
select
create_hash_partitions('test_hash_pathman'::regclass, -- 主表OID
'id', -- 分区列名
20, -- 打算创建多少个分区
false) ; -- 不迁移数据
select set_enable_parent('test_hash_pathman'::regclass, false);
性能对比
1. 插入
传统分区表
postgres=# \timing
Timing is on.
postgres=# truncate test_pg_part_orig;
postgres=# insert into test_pg_part_orig select generate_series(1,20000000);
INSERT 0 0
Time: 537338.575 ms
postgres=# \dt+ test_pg_part_orig*
List of relations
Schema | Name | Type | Owner | Size | Description
--------+----------------------+-------+----------+------------+-------------
public | test_pg_part_orig | table | postgres | 8192 bytes |
public | test_pg_part_orig_1 | table | postgres | 35 MB |
public | test_pg_part_orig_10 | table | postgres | 35 MB |
public | test_pg_part_orig_11 | table | postgres | 35 MB |
public | test_pg_part_orig_12 | table | postgres | 35 MB |
public | test_pg_part_orig_13 | table | postgres | 35 MB |
public | test_pg_part_orig_14 | table | postgres | 35 MB |
public | test_pg_part_orig_15 | table | postgres | 35 MB |
public | test_pg_part_orig_16 | table | postgres | 35 MB |
public | test_pg_part_orig_17 | table | postgres | 35 MB |
public | test_pg_part_orig_18 | table | postgres | 35 MB |
public | test_pg_part_orig_19 | table | postgres | 35 MB |
public | test_pg_part_orig_2 | table | postgres | 35 MB |
public | test_pg_part_orig_20 | table | postgres | 35 MB |
public | test_pg_part_orig_3 | table | postgres | 35 MB |
public | test_pg_part_orig_4 | table | postgres | 35 MB |
public | test_pg_part_orig_5 | table | postgres | 35 MB |
public | test_pg_part_orig_6 | table | postgres | 35 MB |
public | test_pg_part_orig_7 | table | postgres | 35 MB |
public | test_pg_part_orig_8 | table | postgres | 35 MB |
public | test_pg_part_orig_9 | table | postgres | 35 MB |
(21 rows)
postgres=# select count(*) from test_pg_part_orig;
count
----------
20000000
(1 row)
Time: 1907.163 ms
只需要输入分区键值即可选择正确的分区表
pg_pathman分区表
postgres=# insert into test_hash_pathman select generate_series(1,20000000);
INSERT 0 20000000
Time: 64193.880 ms
postgres=# select count(*) from test_hash_pathman;
count
----------
20000000
(1 row)
Time: 1913.448 ms
2. 查询
传统分区表
$ vi test1.sql
\set id random(1,20000000)
select * from test_pg_part_orig where id=:id and mod(id,20)=mod(:id,20);
$ pgbench -M prepared -n -r -P 1 -f ./test1.sql -c 64 -j 64 -T 120
tps = 72817.370007 (including connections establishing)
tps = 72820.055756 (excluding connections establishing)
pg_pathman分区表
$ vi test2.sql
\set id random(1,20000000)
select * from test_hash_pathman where id=:id;
$ pgbench -M simple -n -r -P 1 -f ./test2.sql -c 64 -j 64 -T 120
tps = 436483.383155 (including connections establishing)
tps = 436500.556155 (excluding connections establishing)
目前pg_pathman使用prepared statement会导致大量的LWLOCK，需要优化，所以这里先使用了simple query
已提issue
3. 更新
传统分区表
$ vi test1.sql
\set id random(1,20000000)
update test_pg_part_orig set info='test' where id=:id and mod(id,20)=mod(:id,20);
$ pgbench -M prepared -n -r -P 1 -f ./test1.sql -c 64 -j 64 -T 120
tps = 54515.549230 (including connections establishing)
tps = 54517.624457 (excluding connections establishing)
pg_pathman分区表
$ vi test2.sql
\set id random(1,20000000)
update test_pg_part_pathman set info='test' where id=:id;
$ pgbench -M simple -n -r -P 1 -f ./test2.sql -c 64 -j 64 -T 120
tps = 223477.184306 (including connections establishing)
tps = 223484.907151 (excluding connections establishing)
性能测试数据对比如图
pic3
4. pg_pathman哈希分区表，对子查询的功效
对于哈希分区，pg_pathman支持对子查询的分区过滤。
例子
/* create table we're going to partition */
CREATE TABLE partitioned_table(id INT NOT NULL, payload REAL);
/* insert some data */
INSERT INTO partitioned_table
SELECT generate_series(1, 1000), random();
/* perform partitioning */
SELECT create_hash_partitions('partitioned_table', 'id', 100);
/* create ordinary table */
CREATE TABLE some_table AS SELECT generate_series(1, 100) AS VAL;
select set_enable_parent('partitioned_table', false);
RuntimeAppend节点介入，只扫描需要扫描的分区
postgres=# EXPLAIN (COSTS OFF, ANALYZE) SELECT * FROM partitioned_table
WHERE id = (SELECT * FROM some_table LIMIT 1);
QUERY PLAN
----------------------------------------------------------------------------------
Custom Scan (RuntimeAppend) (actual time=0.051..0.053 rows=1 loops=1)
InitPlan 1 (returns $0)
-> Limit (actual time=0.017..0.017 rows=1 loops=1)
-> Seq Scan on some_table (actual time=0.015..0.015 rows=1 loops=1)
-> Seq Scan on partitioned_table_70 (actual time=0.013..0.014 rows=1 loops=1)
Filter: (id = $0)
Rows Removed by Filter: 9
Planning time: 5.336 ms
Execution time: 0.128 ms
(9 rows)
postgres=# EXPLAIN (COSTS OFF, ANALYZE) SELECT * FROM partitioned_table
WHERE id = any (SELECT * FROM some_table limit 10);
QUERY PLAN
-----------------------------------------------------------------------------------------
Nested Loop (actual time=0.053..0.199 rows=10 loops=1)
-> HashAggregate (actual time=0.024..0.028 rows=10 loops=1)
Group Key: some_table.val
-> Limit (actual time=0.012..0.014 rows=10 loops=1)
-> Seq Scan on some_table (actual time=0.010..0.011 rows=10 loops=1)
-> Custom Scan (RuntimeAppend) (actual time=0.007..0.010 rows=1 loops=10)
-> Seq Scan on partitioned_table_88 (actual time=0.006..0.009 rows=16 loops=2)
-> Seq Scan on partitioned_table_72 (actual time=0.008..0.010 rows=13 loops=1)
-> Seq Scan on partitioned_table_70 (actual time=0.002..0.003 rows=10 loops=1)
-> Seq Scan on partitioned_table_26 (actual time=0.006..0.007 rows=9 loops=1)
-> Seq Scan on partitioned_table_27 (actual time=0.007..0.009 rows=20 loops=1)
-> Seq Scan on partitioned_table_83 (actual time=0.008..0.010 rows=9 loops=1)
-> Seq Scan on partitioned_table_63 (actual time=0.005..0.006 rows=9 loops=1)
-> Seq Scan on partitioned_table_34 (actual time=0.006..0.006 rows=9 loops=1)
-> Seq Scan on partitioned_table_23 (actual time=0.005..0.006 rows=9 loops=1)
Planning time: 1.068 ms
Execution time: 0.264 ms
(17 rows)
六、sharding based on pg_pathman
未完待续
小结
1. 如果在建初始分区时，需要设置分区表的表空间，可以设置会话或事务的参数
set local default_tablespace='tbs1';
2. disable_pathman_for函数没有可逆操作，请慎用。
3. 不建议关闭pg_pathman.enable
4. 不建议开启自动扩展范围分区，一个错误的分区值可能导致创建很多分区。
5. 推荐使用set_enable_parent禁用主表。
6. 由于pg_pathman使用了custom scan接口，所以只支持9.5以及以上版本。
7. 传统哈希分区需要输入分区键值的约束条件，才能正确选择分区。pg_pathman只要输入键值即可。
8. 目前使用prepared statement会造成性能下降，跟踪到与LWLOCK有关，并不是不支持过滤分区造成的，已发ISSUE，将来可能会解决。
when use simple query:
50236.00 5.2% AllocSetAlloc /home/digoal/pgsql9.6/bin/postgres
38826.00 4.0% SearchCatCache /home/digoal/pgsql9.6/bin/postgres
24134.00 2.5% hash_search_with_hash_value /home/digoal/pgsql9.6/bin/postgres
22720.00 2.3% base_yyparse /home/digoal/pgsql9.6/bin/postgres
13425.00 1.4% palloc /home/digoal/pgsql9.6/bin/postgres
12965.00 1.3% expression_tree_walker /home/digoal/pgsql9.6/bin/postgres
11487.00 1.2% _bt_compare /home/digoal/pgsql9.6/bin/postgres
11087.00 1.1% MemoryContextAllocZeroAligned /home/digoal/pgsql9.6/bin/postgres
10703.00 1.1% GetSnapshotData /home/digoal/pgsql9.6/bin/postgres
9870.00 1.0% _int_malloc /lib64/libc-2.12.so
9842.00 1.0% LWLockAcquire /home/digoal/pgsql9.6/bin/postgres
when use prepared query:
26913.00 6.4% hash_search_with_hash_value /home/digoal/pgsql9.6/bin/postgres
21941.00 5.2% _spin_lock [kernel.kallsyms]
12531.00 3.0% LWLockAcquire /home/digoal/pgsql9.6/bin/postgres
8658.00 2.0% schedule [kernel.kallsyms]
8232.00 1.9% LWLockRelease /home/digoal/pgsql9.6/bin/postgres
8072.00 1.9% AllocSetAlloc /home/digoal/pgsql9.6/bin/postgres
7650.00 1.8% LockReleaseAll /home/digoal/pgsql9.6/bin/postgres
7222.00 1.7% SearchCatCache /home/digoal/pgsql9.6/bin/postgres
6795.00 1.6% reschedule_interrupt [kernel.kallsyms]
5482.00 1.3% LWLockWaitListLock /home/digoal/pgsql9.6/bin/postgres
5413.00 1.3% _bt_compare /home/digoal/pgsql9.6/bin/postgres
5085.00 1.2% GetSnapshotData /home/digoal/pgsql9.6/bin/postgres
4519.00 1.1% LockAcquireExtended /home/digoal/pgsql9.6/bin/postgres
4509.00 1.1% try_atomic_semop [kernel.kallsyms]
4420.00 1.0% tick_nohz_stop_sched_tick [kernel.kallsyms]
4222.00 1.0% ktime_get [kernel.kallsyms]
explain 不能显示是否过滤分区，使用explain analyze
postgres=# prepare p1 (int) as select * from test_hash_pathman where id=$1;
PREPARE
postgres=# explain execute p1(1);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------
Append (cost=0.42..2.44 rows=1 width=44)
-> Index Scan using test_hash_pathman_10_pkey on test_hash_pathman_10 (cost=0.42..2.44 rows=1 width=44)
Index Cond: (id = 1)
(3 rows)
第六次
postgres=# explain execute p1(1);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
Custom Scan (RuntimeAppend) (cost=0.42..2.44 rows=1 width=44)
-> Index Scan using test_hash_pathman_0_pkey on test_hash_pathman_0 test_hash_pathman (cost=0.42..2.44 rows=1 width=44)
Index Cond: (id = $1)
-> Index Scan using test_hash_pathman_1_pkey on test_hash_pathman_1 test_hash_pathman (cost=0.42..2.44 rows=1 width=44)
Index Cond: (id = $1)
-> Index Scan using test_hash_pathman_2_pkey on test_hash_pathman_2 test_hash_pathman (cost=0.42..2.44 rows=1 width=44)
Index Cond: (id = $1)
.....
-> Index Scan using test_hash_pathman_18_pkey on test_hash_pathman_18 test_hash_pathman (cost=0.42..2.44 rows=1 width=44)
Index Cond: (id = $1)
-> Index Scan using test_hash_pathman_19_pkey on test_hash_pathman_19 test_hash_pathman (cost=0.42..2.44 rows=1 width=44)
Index Cond: (id = $1)
(41 rows)
但是实际上选择了分区，使用explain analyze可察觉
postgres=# explain analyze execute p1(1);
QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------------------------
Custom Scan (RuntimeAppend) (cost=0.42..2.44 rows=1 width=44) (actual time=0.061..0.062 rows=1 loops=1)
-> Index Scan using test_hash_pathman_10_pkey on test_hash_pathman_10 (cost=0.42..2.44 rows=1 width=44) (actual time=0.034..0.034 rows=1 loops=1)
Index Cond: (id = $1)
Execution time: 0.077 ms
(4 rows)
595
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
