背景

目前PG的native partition分区性能还有优化空间,一种解决方法是使用pg_pathman插件,另一种方法是业务上直接插分区,还有一种方法是使用UDF函数接口(函数内部使用prepared statement来降低PARSE CPU开销)。

本文提供的是UDF的例子,以及性能比对。

例子

1、创建分区表

  1. create table p (id int , info text, crt_time timestamp) partition by list (abs(mod(id,128)));

2、创建128个分区

  1. do language plpgsql $$
  2. declare
  3. begin
  4. for i in 0..127 loop
  5. execute format('create table p%s partition of p for values in (%s)', i, i);
  6. end loop;
  7. end;
  8. $$;

直接插分区主表

  1. vi test.sql
  2. \set id random(1,2000000000)
  3. insert into p values (:id, 'test', now());

性能

  1. pgbench -M prepared -n -r -P 1 -f ./test.sql -c 64 -j 64 -T 120
  2. pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 120
  3. progress: 1.0 s, 26287.2 tps, lat 1.178 ms stddev 0.418
  4. progress: 2.0 s, 27441.8 tps, lat 1.166 ms stddev 0.393
  5. progress: 3.0 s, 27526.0 tps, lat 1.163 ms stddev 0.398

批量插性能

  1. vi test.sql
  2. insert into p values (1,'test',now()),(2,'test',now()),(3,'test',now()),(4,'test',now()),(5,'test',now()),(6,'test',now()),(7,'test',now()),(8,'test',now()),(9,'test',now()),(10,'test',now());
  1. pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 120
  2. progress: 1.0 s, 26240.5 tps, lat 1.179 ms stddev 0.462
  3. progress: 2.0 s, 28285.8 tps, lat 1.131 ms stddev 0.393
  4. progress: 3.0 s, 28185.1 tps, lat 1.135 ms stddev 0.423
  5. progress: 4.0 s, 28266.1 tps, lat 1.132 ms stddev 0.395
  6. progress: 5.0 s, 28248.9 tps, lat 1.133 ms stddev 0.438
  7. progress: 6.0 s, 26739.0 tps, lat 1.197 ms stddev 1.154
  8. progress: 7.0 s, 28075.1 tps, lat 1.140 ms stddev 0.426
  9. progress: 8.0 s, 28297.8 tps, lat 1.131 ms stddev 0.384

使用UDF+绑定变量插分区

1、绑定变量的语法

  1. postgres=# \h prepare
  2. Command: PREPARE
  3. Description: prepare a statement for execution
  4. Syntax:
  5. PREPARE name [ ( data_type [, ...] ) ] AS statement
  6. postgres=# \h execute
  7. Command: EXECUTE
  8. Description: execute a prepared statement
  9. Syntax:
  10. EXECUTE name [ ( parameter [, ...] ) ]

2、写一个UDF,使用绑定变量插入

  1. create or replace function ins_p(int, text, timestamp) returns void as $$
  2. declare
  3. suffix text := abs(mod($1,128));
  4. begin
  5. execute format('execute ps%s(%s, %L, %L)', suffix, $1, $2, $3);
  6. exception when others then
  7. execute format('prepare ps%s(int,text,timestamp) as insert into p%s (id,info,crt_time) values ($1,$2,$3)', suffix, suffix);
  8. execute format('execute ps%s(%s, %L, %L)', suffix, $1, $2, $3);
  9. end;
  10. $$ language plpgsql strict;

3、性能

  1. vi test.sql
  2. \set id random(1,2000000000)
  3. select ins_p(:id, 'test', now()::timestamp);
  1. pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 120
  2. progress: 1.0 s, 192814.1 tps, lat 0.161 ms stddev 0.092
  3. progress: 2.0 s, 205480.6 tps, lat 0.156 ms stddev 0.061
  4. progress: 3.0 s, 209206.4 tps, lat 0.153 ms stddev 0.058
  5. progress: 4.0 s, 206333.8 tps, lat 0.155 ms stddev 0.061

如果是BATCH写入,可以改一下这个UDF如下

  1. create or replace function ins_p(int, text, timestamp) returns void as $$
  2. declare
  3. suffix text := abs(mod($1,128));
  4. begin
  5. execute format('execute ps%s(%s, %L, %L)', suffix, $1, $2, $3);
  6. exception when others then
  7. execute format('prepare ps%s(int,text,timestamp) as insert into p%s (id,info,crt_time) values ($1,$2,$3)', suffix, suffix);
  8. execute format('execute ps%s(%s, %L, %L)', suffix, $1, $2, $3);
  9. end;
  10. $$ language plpgsql strict;
  1. create or replace function ins_p_batch(p[]) returns void as $$
  2. declare
  3. i p;
  4. begin
  5. foreach i in array $1 loop
  6. perform ins_p(i.id, i.info, i.crt_time);
  7. end loop;
  8. end;
  9. $$ language plpgsql strict;

batch使用举例

  1. postgres=# select count(*) from p;
  2. count
  3. ----------
  4. 28741670
  5. (1 row)
  6. Time: 390.775 ms
  7. postgres=# select ins_p_batch((select array_agg(p) from (select p from p limit 10000) t));
  8. ins_p_batch
  9. -------------
  10. (1 row)
  11. Time: 247.861 ms
  12. postgres=# select count(*) from p;
  13. count
  14. ----------
  15. 28751670
  16. (1 row)
  17. Time: 383.485 ms
  1. postgres=# select array_agg(p) from (select p from p limit 10) t;
  2. -[ RECORD 1 ]-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  3. array_agg | {"(1269675648,test,\"2019-01-09 17:08:35.432933\")","(1515917568,test,\"2019-01-09 17:08:35.435001\")","(137413760,test,\"2019-01-09 17:08:35.438484\")","(1750920192,test,\"2019-01-09 17:08:35.443544\")","(849316096,test,\"2019-01-09 17:08:35.448552\")","(891638016,test,\"2019-01-09 17:08:35.449074\")","(320902144,test,\"2019-01-09 17:08:35.449142\")","(95829120,test,\"2019-01-09 17:08:35.453658\")","(358048256,test,\"2019-01-09 17:08:35.454924\")","(1009512320,test,\"2019-01-09 17:08:35.457164\")"}
  4. Time: 1.771 ms
  5. postgres=# select ins_p_batch('{"(1269675648,test,\"2019-01-09 17:08:35.432933\")","(1515917568,test,\"2019-01-09 17:08:35.435001\")","(137413760,test,\"2019-01-09 17:08:35.438484\")","(1750920192,test,\"2019-01-09 17:08:35.443544\")","(849316096,test,\"2019-01-09 17:08:35.448552\")","(891638016,test,\"2019-01-09 17:08:35.449074\")","(320902144,test,\"2019-01-09 17:08:35.449142\")","(95829120,test,\"2019-01-09 17:08:35.453658\")","(358048256,test,\"2019-01-09 17:08:35.454924\")","(1009512320,test,\"2019-01-09 17:08:35.457164\")"}');
  6. ins_p_batch
  7. -------------
  8. (1 row)
  9. Time: 0.841 ms

性能

  1. vi test.sql
  2. select ins_p_batch('{"(1269675648,test,\"2019-01-09\")","(1515917568,test,\"2019-01-09\")","(137413760,test,\"2019-01-09\")","(1750920192,test,\"2019-01-09\")","(849316096,test,\"2019-01-09\")","(891638016,test,\"2019-01-09\")","(320902144,test,\"2019-01-09\")","(95829120,test,\"2019-01-09\")","(358048256,test,\"2019-01-09\")","(1009512320,test,\"2019-01-09\")"}');

一次插10行

  1. pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 120
  2. progress: 1.0 s, 41637.4 tps, lat 0.745 ms stddev 0.742
  3. progress: 2.0 s, 42862.5 tps, lat 0.746 ms stddev 0.614
  4. progress: 3.0 s, 42417.1 tps, lat 0.754 ms stddev 0.689
  5. progress: 4.0 s, 42389.5 tps, lat 0.755 ms stddev 0.691

应用程序直接写分区

性能

  1. vi test.sql
  2. \set id random(1,2000000000)
  3. insert into p2 values (2,'test',now());
  1. pgbench -M prepared -n -r -P 1 -f ./test.sql -c 32 -j 32 -T 120
  2. progress: 1.0 s, 364350.5 tps, lat 0.085 ms stddev 0.208
  3. progress: 2.0 s, 379071.4 tps, lat 0.084 ms stddev 0.215
  4. progress: 3.0 s, 384452.1 tps, lat 0.083 ms stddev 0.188

性能对比

方法 | 每秒插入多少行
—|—
插分区主表(单条) | 2.7万
插分区主表(10条) | 28万
应用直接插分区(单条) | 38万
使用UDF+动态绑定变量插分区(单条) | 20万
使用UDF+动态绑定变量批量查(10条) | 42万

另外需要注意,并发越高,直接插主表的性能越差,例如使用64个并发插入时,只有2.1万行/s。

参考

《PostgreSQL 9.x, 10, 11 hash分区表 用法举例》

《分区表锁粒度差异 - pg_pathman VS native partition table》

《PostgreSQL 商用版本EPAS(阿里云ppas(Oracle 兼容版)) - 分区表性能优化 (堪比pg_pathman)》

《PostgreSQL 10 内置分区 vs pg_pathman perf profiling》

《PostgreSQL 9.6 sharding based on FDW & pg_pathman》

《PostgreSQL 9.5+ 高效分区表实现 - pg_pathman》

《PostgreSQL 查询涉及分区表过多导致的性能问题 - 性能诊断与优化(大量BIND, spin lock, SLEEP进程)》

digoal’s 大量PostgreSQL文章入口

免费领取阿里云RDS PostgreSQL实例、ECS虚拟机