开发者社区> 德哥> 正文

PostgreSQL 批量、单步 写入 - row, statement 触发器(中间表)、CTE 几种用法性能对比

简介:
+关注继续查看

标签

PostgreSQL , 触发器 , row , statement , statement 触发器中间表 , CTE , 性能对比


背景

数据库写入方式包括:

1、单条insert, autocommit

insert into xx values ();  

2、批量(单QUERY)

insert into xx values (),(),...();  

3、分组提交

begin;  
insert into xx values ();  
...  
insert into xx values ();  
end;  

INSERT协议包括extended, prepared, simple等。

当需要对每行写入的数据进行逻辑处理时,可以使用以下三种方法:

postgres=# \h create trigger  
Command:     CREATE TRIGGER  
Description: define a new trigger  
Syntax:  
CREATE [ CONSTRAINT ] TRIGGER name { BEFORE | AFTER | INSTEAD OF } { event [ OR ... ] }  
    ON table_name  
    [ FROM referenced_table_name ]  
    [ NOT DEFERRABLE | [ DEFERRABLE ] [ INITIALLY IMMEDIATE | INITIALLY DEFERRED ] ]  
    [ REFERENCING { { OLD | NEW } TABLE [ AS ] transition_relation_name } [ ... ] ]  
    [ FOR [ EACH ] { ROW | STATEMENT } ]  
    [ WHEN ( condition ) ]  
    EXECUTE PROCEDURE function_name ( arguments )  
  
where event can be one of:  
  
    INSERT  
    UPDATE [ OF column_name [, ... ] ]  
    DELETE  
    TRUNCATE  
  
postgres=# \h create rule  
Command:     CREATE RULE  
Description: define a new rewrite rule  
Syntax:  
CREATE [ OR REPLACE ] RULE name AS ON event  
    TO table_name [ WHERE condition ]  
    DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }  
  
where event can be one of:  
  
    SELECT | INSERT | UPDATE | DELETE  

1、行级触发器

2、语句级触发器

https://www.postgresql.org/docs/devel/static/plpgsql-trigger.html

《PostgreSQL 10.0 preview 功能增强 - 触发器函数内置中间表》

3、CTE写法

with tmp as (insert into xxx values xxx returning *),  
t1 as (逻辑处理1 from tmp where ...),  
...,  
tn as (逻辑处理n from tmp where ...)  
逻辑处理n+1 from tmp where ...;  

下面针对以上三种方法,分别介绍单步、批量插入时的性能差异,以便业务上针对性的做出选择。

项目背景参考

《PostgreSQL 流式处理应用实践 - 二手商品实时归类(异步消息notify/listen、阅后即焚)》

DEMO

1、建表

create table test(id int, info text, c1 int , c2 int, crt_time timestamp);  

2、逻辑处理的结果写入下面的表

create table t_result (like test);  

无触发器时,批量写入性能

postgres=# insert into test select 1,'test',random()*100,random()*100,now() from generate_series(1,1000000);  
INSERT 0 1000000  
Time: 1118.051 ms (00:01.118)  

行级触发器 - 多个触发器的批量写入性能

1、触发器函数

create or replace function tg1() returns trigger as $$  
declare  
begin  
  if NEW.c1<=1 and NEW.c2<=1 then   
    insert into t_result values (NEW.*);  
  end if;  
  return null;  
end;  
$$ language plpgsql strict;  

2、2个触发器的性能

create trigger tg1 after insert on test for each row execute procedure tg1();  
create trigger tg2 after insert on test for each row execute procedure tg1();  
  
  
postgres=# insert into test select 1,'test',random()*100,random()*100,now() from generate_series(1,1000000);  
INSERT 0 1000000  
Time: 4879.985 ms (00:04.880)  

3、4个触发器的性能

create trigger tg3 after insert on test for each row execute procedure tg1();  
create trigger tg4 after insert on test for each row execute procedure tg1();  
  
postgres=# insert into test select 1,'test',random()*100,random()*100,now() from generate_series(1,1000000);  
INSERT 0 1000000  
Time: 8776.416 ms (00:08.776)  

4、6个触发器的性能

create trigger tg5 after insert on test for each row execute procedure tg1();  
create trigger tg6 after insert on test for each row execute procedure tg1();  
  
postgres=# insert into test select 1,'test',random()*100,random()*100,now() from generate_series(1,1000000);  
INSERT 0 1000000  
Time: 12648.707 ms (00:12.649)  

5、将触发器合并成1个,所有逻辑放到一个触发器中,批量写入性能

drop trigger tg1 ON test;  
drop trigger tg2 ON test;  
drop trigger tg3 ON test;  
drop trigger tg4 ON test;  
drop trigger tg5 ON test;  
drop trigger tg6 ON test;  
  
  
create or replace function tg1() returns trigger as $$  
declare  
begin  
  if NEW.c1<=1 and NEW.c2<=1 then   
    insert into t_result values (NEW.*);  
  end if;  
  if NEW.c1<=1 and NEW.c2<=1 then   
    insert into t_result values (NEW.*);  
  end if;  
  if NEW.c1<=1 and NEW.c2<=1 then   
    insert into t_result values (NEW.*);  
  end if;  
  if NEW.c1<=1 and NEW.c2<=1 then   
    insert into t_result values (NEW.*);  
  end if;  
  if NEW.c1<=1 and NEW.c2<=1 then   
    insert into t_result values (NEW.*);  
  end if;  
  if NEW.c1<=1 and NEW.c2<=1 then   
    insert into t_result values (NEW.*);  
  end if;  
  return null;  
end;  
$$ language plpgsql strict;  
  
  
create trigger tg1 after insert on test for each row execute procedure tg1();  
  
  
postgres=# insert into test select 1,'test',random()*100,random()*100,now() from generate_series(1,1000000);  
INSERT 0 1000000  
Time: 5042.071 ms (00:05.042)  

语句级触发器 - 1个触发器,批量写入,6个规则的批量写入性能

https://www.postgresql.org/docs/devel/static/plpgsql-trigger.html

1、创建触发器函数,使用中间表,逻辑处理放到中间表中

create or replace function tg1() returns trigger as $$  
declare  
begin  
  insert into t_result select * from new_table where c1<=1 and c2<=1;  
  insert into t_result select * from new_table where c1<=1 and c2<=1;  
  insert into t_result select * from new_table where c1<=1 and c2<=1;  
  insert into t_result select * from new_table where c1<=1 and c2<=1;  
  insert into t_result select * from new_table where c1<=1 and c2<=1;  
  insert into t_result select * from new_table where c1<=1 and c2<=1;  
  return null;  
end;  
$$ language plpgsql strict;  
  
drop trigger tg1 ON test;  
  
create trigger tg1 after insert on test REFERENCING NEW TABLE AS new_table for each STATEMENT execute procedure tg1();  

2、6个规则的性能

postgres=# insert into test select 1,'test',random()*100,random()*100,now() from generate_series(1,1000000);  
INSERT 0 1000000  
Time: 1847.532 ms (00:01.848)  

CTE - 批量写入,6个规则性能

with new_table as (insert into test select 1,'test',random()*100,random()*100,now() from generate_series(1,1000000) returning *),  
t1 as (insert into t_result select * from new_table where c1<=1 and c2<=1),  
t2 as (insert into t_result select * from new_table where c1<=1 and c2<=1),  
t3 as (insert into t_result select * from new_table where c1<=1 and c2<=1),  
t4 as (insert into t_result select * from new_table where c1<=1 and c2<=1),  
t5 as (insert into t_result select * from new_table where c1<=1 and c2<=1)  
insert into t_result select * from new_table where c1<=1 and c2<=1;  
  
INSERT 0 222  
Time: 2833.217 ms (00:02.833)  

单条INSERT,“行级、语句级触发器、CTE用法”6个规则,性能对比

1、测试脚本

vi test.sql  
  
insert into test values (1,'test',random()*100,random()*100,now());  
  
  
pgbench -M prepared -n -r -P 1 -f ./test.sql -c 28 -j 28 -T 120  

无触发器

transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 28  
number of threads: 28  
duration: 120 s  
number of transactions actually processed: 29783861  
latency average = 0.113 ms  
latency stddev = 0.329 ms  
tps = 248197.480858 (including connections establishing)  
tps = 248213.036221 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.113  insert into test values (1,'test',random()*100,random()*100,now());  

2、ROW触发器

create or replace function tg1() returns trigger as $$  
declare  
begin  
  if NEW.c1<=1 and NEW.c2<=1 then   
    insert into t_result values (NEW.*);  
  end if;  
  if NEW.c1<=1 and NEW.c2<=1 then   
    insert into t_result values (NEW.*);  
  end if;  
  if NEW.c1<=1 and NEW.c2<=1 then   
    insert into t_result values (NEW.*);  
  end if;  
  if NEW.c1<=1 and NEW.c2<=1 then   
    insert into t_result values (NEW.*);  
  end if;  
  if NEW.c1<=1 and NEW.c2<=1 then   
    insert into t_result values (NEW.*);  
  end if;  
  if NEW.c1<=1 and NEW.c2<=1 then   
    insert into t_result values (NEW.*);  
  end if;  
  return null;  
end;  
$$ language plpgsql strict;  
  
  
create trigger tg1 after insert on test for each row execute procedure tg1();  
  
  
  
  
transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 28  
number of threads: 28  
duration: 120 s  
number of transactions actually processed: 18398837  
latency average = 0.183 ms  
latency stddev = 0.335 ms  
tps = 153322.919798 (including connections establishing)  
tps = 153332.364931 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.183  insert into test values (1,'test',random()*100,random()*100,now());  

3、STATEMENT触发器

create or replace function tg1() returns trigger as $$  
declare  
begin  
  insert into t_result select * from new_table where c1<=1 and c2<=1;  
  insert into t_result select * from new_table where c1<=1 and c2<=1;  
  insert into t_result select * from new_table where c1<=1 and c2<=1;  
  insert into t_result select * from new_table where c1<=1 and c2<=1;  
  insert into t_result select * from new_table where c1<=1 and c2<=1;  
  insert into t_result select * from new_table where c1<=1 and c2<=1;  
  return null;  
end;  
$$ language plpgsql strict;  
  
drop trigger tg1 ON test;  
  
create trigger tg1 after insert on test REFERENCING NEW TABLE AS new_table for each STATEMENT execute procedure tg1();  
  
  
transaction type: ./test.sql  
scaling factor: 1  
query mode: prepared  
number of clients: 28  
number of threads: 28  
duration: 120 s  
number of transactions actually processed: 16868185  
latency average = 0.199 ms  
latency stddev = 0.214 ms  
tps = 140567.379172 (including connections establishing)  
tps = 140576.126770 (excluding connections establishing)  
script statistics:  
 - statement latencies in milliseconds:  
         0.199  insert into test values (1,'test',random()*100,random()*100,now());  

性能总结

模式 规则个数 语句触发器 处理吞吐 行触发器 处理吞吐 无规则 处理吞吐
单条写入 单触发器6个规则 14万行/s 15.3万行/s 24.8万行/s
批量写入100万行 单触发器6个规则 54.1万行/s 19.8万行/s 89.4万行/s
批量写入100万行 2个触发器2个规则 - 20.5万行/s -
批量写入100万行 4个触发器4个规则 - 11.4万行/s -
批量写入100万行 6个触发器6个规则 - 7.9万行/s -
CTE语法批量写入100万行 6个规则 - - 35.3万行/s (含6规则)

小结

对于需要逻辑处理的数据表,使用语句级触发器,同时使用中间表的模式来进行逻辑处理,性能是比较均衡的。相比行级触发器,性能好很多。

另外,每增加一个触发器,性能会下降比较厉害。建议把处理逻辑放到一个触发器里面,而不要使用多个触发器。

参考

《PostgreSQL 流式处理应用实践 - 二手商品实时归类(异步消息notify/listen、阅后即焚)》

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
Linux性能测试 netstat命令
功能说明:Netstat用于显示与IP、TCP、UDP和ICMP协议相关的统计数据,一般用于检验本机各端口的网络连接情况。语  法:netstat [-acCeFghilMnNoprstuvVwx][-A][--ip]补充说明:利用netstat指令可让你得知整个Linux系统的网络情况。
879 0
PostgreSQL prepared statement和simple query的profile及性能差异
prepared statement是非常重要的高并发SQL优化手段之一,效果也显而易见。下面是测试,同时观察绑定和不绑定的情况下的profile。在未使用绑定变量的时候,新增或上升了一些硬解析相关的CODE。 测试数据 postgres=# create table test(id i
2552 0
ORACLE PL/SQL编程之八:把触发器说透
原文:ORACLE PL/SQL编程之八:把触发器说透   ORACLE PL/SQL编程之八:  把触发器说透    大家一定要评论呀,感谢!光发表就花了我将近一个下午。 本篇主要内容如下: 8.
800 0
Qt .pro文件之defineReplace函数的用法,实现lib文件名自动添加后缀“d“
Qt .pro文件之defineReplace函数的用法,实现lib文件名自动添加后缀“d“
91 0
+关注
德哥
公益是一辈子的事, I&#39;m digoal, just do it.
2153
文章
245
问答
来源圈子
更多
阿里云数据库:帮用户承担一切数据库风险,给您何止是安心!支持关系型数据库:MySQL、SQL Server、PostgreSQL、PPAS(完美兼容Oracle)、自研PB级数据存储的分布式数据库Petadata、自研金融级云数据库OceanBase支持NoSQL数据库:MongoDB、Redis、Memcache更有褚霸、丁奇、德哥、彭立勋、玄惭、叶翔等顶尖数据库专家服务。
+ 订阅
相关文档: 云数据库 OceanBase 版 可信账本数据库 云原生关系型数据库 PolarDB PostgreSQL引擎
文章排行榜
最热
最新
相关电子书
更多
OceanBase 入门到实战教程
立即下载
阿里云图数据库GDB,加速开启“图智”未来.ppt
立即下载
实时数仓Hologres技术实战一本通2.0版(下)
立即下载