PostgreSQL 批量SQL before/after for each row trigger的触发时机、性能差异分析、建议 - 含9.4 , 10版本

6 minute read

背景

数据库触发器的触发时机，性能，高并发批量导入时，触发器的性能如何？

批量导入时，before, after触发器在for each row模式下，触发机制如何，什么时候开始条到触发器指定的function中进行运算？

1、before for each row，在数据落目标表前，被触发，同时返回的内容（TUPLE）被REPLACE到对应的数据文件存储。触发器必须明确返回NEW。

以insert为例    
    
insert request to HEAP table -> 每一row立即generate NEW -> before trigger(s) -> return NEW -> write tuple to HEAP table    

2、after for each row，在数据落到目标表之后，再被触发(如果是批量写入，那么会等批量写入结束后，才开始触发after trigger procedure)。after tirgger procedure返回什么值都无所谓，因为用不上。after for each row建议触发器返回null。

以insert为例    
    
insert request to HEAP table -> write tuple to HEAP table -> 所有row一次性generate NEW -> after trigger(s) -> return NULL    

到底哪个性能好？

测试

测试场景参考

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

《PostgreSQL 批量SQL before/after for each row trigger的触发时机、性能差异》

1、建表

create table a (          
  id int8 primary key,   -- 商品ID          
  att jsonb   -- 商品属性          
);    

2、建结果表

create table t_result(id serial8 primary key, class text, content text);

3、建merge json函数

create or replace function merge_json(jsonb, jsonb) returns jsonb as $$        
  -- select json_object_agg(key,value)::jsonb from (     -- 9.4   
  select jsonb_object_agg(key,value) from (        
  select         
    coalesce(a.key, b.key) as key,         
    case         
    when         
    coalesce(jsonb_array_element(a.value,1)::text::timestamp, '1970-01-01'::timestamp)         
    >         
    coalesce(jsonb_array_element(b.value,1)::text::timestamp, '1970-01-01'::timestamp)         
    then a.value        
    else b.value        
    end        
  from jsonb_each($1) a full outer join jsonb_each($2) b using (key)        
  ) t;          
$$ language sql strict ;        

批量，并发数据写入性能对比(before, after, no trigger)

1、创建dblink插件

create extension dblink;

2、建立断开连接的函数，目的是不抛异常。

create or replace function dis_conn(name) returns void as $$    
declare    
begin    
  perform dblink_disconnect($1);    
  return;    
exception when others then    
  return;    
end;    
$$ language plpgsql strict;    

3、创建连接函数接口

CREATE OR REPLACE FUNCTION public.conn(name, text)    
 RETURNS void    
 LANGUAGE plpgsql    
 STRICT    
AS $function$                
declare                
begin                
  perform dis_conn($1);      
  perform dblink_connect($1, $2);               
  return;                
exception when others then                
  return;                
end;                
$function$;    

4、创建并行，批量加载函数。 56个并行，每一批写入200万条数据。总共写入1.12亿行。

CREATE OR REPLACE FUNCTION public.get_res()    
 RETURNS SETOF record    
 LANGUAGE plpgsql    
 STRICT    
AS $function$          
declare          
  start_time timestamptz := clock_timestamp();    
  loops int := 55;    
  batchs int := 2000000;    
  -- 总数据量1.12亿    
begin          
  for i in 0..loops loop                 
    perform conn('link'||i,  'hostaddr=127.0.0.1 port='||current_setting('port')||' user=postgres dbname=postgres application_name=digoal_loader');               
    perform '1' from dblink_get_result('link'||i) as t(res text);                
    perform dblink_send_query('link'||i, format($_$    
    insert into a select         
    id, '{"price":[10000, "2018-01-01 10:10:11"]}'    
    from generate_series(%s,%s) t(id)    
    on conflict (id)        -- 9.4 注释掉 这行  
    do update set           -- 9.4 注释掉 这行  
    att = merge_json(a.att, excluded.att)      -- 9.4 注释掉 这行  
    $_$, i*batchs, (i+1)*batchs-1));                
  end loop;             
  for i in 0..loops loop          
    return query select extract(epoch from clock_timestamp()-start_time)::text from dblink_get_result('link'||i) as t(res text);    
  end loop;          
end;          
$function$;    

after trigger for each row

当一条SQL写入a完成后，触发after触发器，开始处理每行。

1、建触发器函数，用于处理每一行原始数据，包括50个处理逻辑.

CREATE OR REPLACE FUNCTION notify1() returns trigger          
AS $function$          
declare          
begin          
  if jsonb_array_element(NEW.att->'price', 0)::text::float8 > 100 then   -- 规则1， 价格大于100，写入结果表          
     insert into t_result(class,content) values (        
       'a',    -- 归类        
       format('CLASS:high price, ID:%s, ATT:%s', NEW.id, NEW.att)   -- 消息内容          
     );          
  end if;         
  -- 模拟多轮判断    
  for i in 1..49 loop    
    if jsonb_array_element(NEW.att->'price', 0)::text::float8 > 100 then   -- 规则xx        
      null;         
    end if;        
  end loop;    
  return null;    -- aster 触发器    
  -- return NEW;  -- BEFORE 触发器    
end;          
$function$ language plpgsql strict;          

2、创建after insert or update触发器

create trigger tg1 after insert or update on a for each row execute procedure notify1();     

3、写入单条，测试

insert into a values           
  (1, '{"price":[10000, "2018-01-01 10:10:11"]}')           
  on conflict (id)           
  do update set           
  att = merge_json(a.att, excluded.att)     -- 合并新属性，保留老属性，需要使用一个UDF来合并          
;      

4、调用并行接口，批量并发写入

select * from get_res() as t(id text);

5、你会发现，数据是在写入完成后，才开始逐行处理触发器内部逻辑。

目标表在写入，但是trigger并没有处理，因此结果表还没有看到任何记录

以insert为例    
    
insert request to HEAP table -> write tuple to HEAP table -> 所有row一次性generate NEW -> after trigger(s) -> return NULL    

postgres=# \dt+ a|t_result    
                    List of relations    
 Schema | Name | Type  |  Owner   |  Size   | Description     
--------+------+-------+----------+---------+-------------    
 public | a    | table | postgres | 3560 MB |     
 public | t_result | table | postgres | 8192 bytes |     
    
postgres=# \dt+ a    
                    List of relations    
 Schema | Name | Type  |  Owner   |  Size   | Description     
--------+------+-------+----------+---------+-------------    
 public | a    | table | postgres | 3603 MB |     
 public | t_result | table | postgres | 8192 bytes |     

6、数据量：1.12亿条

总耗时：
(主要慢在trigger内部的逻辑处理)

1367 秒。

before trigger for each row

before触发器，在数据落盘前，触发before trigger function

1、建触发器函数，用于处理每一行原始数据，包括50个处理逻辑.

CREATE OR REPLACE FUNCTION notify1() returns trigger          
AS $function$          
declare          
begin          
  if jsonb_array_element(NEW.att->'price', 0)::text::float8 > 100 then   -- 规则1， 价格大于100，写入结果表          
     insert into t_result(class,content) values (        
       'a',    -- 归类        
       format('CLASS:high price, ID:%s, ATT:%s', NEW.id, NEW.att)   -- 消息内容          
     );          
  end if;         
  -- 模拟多轮判断    
  for i in 1..49 loop    
    if jsonb_array_element(NEW.att->'price', 0)::text::float8 > 100 then   -- 规则xx        
      null;         
    end if;        
  end loop;    
  -- return null;    -- aster 触发器    
  return NEW;  -- BEFORE 触发器    
end;          
$function$ language plpgsql strict;          

2、创建before insert or update触发器

drop trigger tg1 on a;    
    
create trigger tg1 before insert or update on a for each row execute procedure notify1();     

3、调用并行接口，批量并发写入

truncate a;  
truncate t_result;  
select * from get_res() as t(id text);      

4、写入过程中查看

你会发现，目标表和结果表同时在增长，因为

以insert为例    
    
insert request to HEAP table -> 每一row立即generate NEW -> before trigger(s) -> return NEW -> write tuple to HEAP table    

postgres=# \dt+ a|t_res*    
                      List of relations    
 Schema |   Name   | Type  |  Owner   |  Size  | Description     
--------+----------+-------+----------+--------+-------------    
 public | a        | table | postgres | 335 MB |     
 public | t_result | table | postgres | 387 MB |     
(2 rows)    

6、数据量：1.12亿条

总耗时：
(主要慢在trigger内部的逻辑处理)

1207 秒。

无trigger导入速度：

1、删除触发器

postgres=# drop trigger tg1 on a;    
DROP TRIGGER    

2、调用并行接口，批量并发写入

truncate a;  
truncate t_result;  
select * from get_res() as t(id text);      

3、数据量：1.12亿条

总耗时：
(主要慢在trigger内部的逻辑处理)

706 秒。

性能对比

PostgreSQL 10 on CentOS 7.x

PostgreSQL 10 logged table 测试结果

case	并发数	写入量	耗时
无触发器	56	1.12亿	103 秒
before for each row触发器	56	1.12亿	1165 秒
after for each row触发器	56	1.12亿	1247 秒

性能瓶颈，在写wal日志上面，如果使用unlogged table，就可以发挥出CPU所有能力了。

postgres=# select wait_event_type,wait_event,count(*) from pg_stat_activity group by 1,2 order by count(*) desc;  
 wait_event_type |     wait_event      | count   
-----------------+---------------------+-------  
 LWLock          | wal_insert          |    40  
                 |                     |    19  
 Activity        | BgWriterMain        |     1  
 Activity        | AutoVacuumMain      |     1  
 IO              | DataFileWrite       |     1  
 Activity        | LogicalApplyMain    |     1  
 Activity        | LogicalLauncherMain |     1  
(7 rows)  

PostgreSQL 10 unlogged table 测试结果

truncate a;  
truncate t_result;  
alter table a set unlogged;  
alter table t_result set unlogged;  

case	并发数	写入量	耗时
无触发器	56	1.12亿	61 秒
before for each row触发器	56	1.12亿	1113 秒
after for each row触发器	56	1.12亿	1158 秒

现在“无触发器”模式的瓶颈变成了EXTEND BLOCK，也就是扩展数据文件。触发器的情况下，CPU计算为瓶颈，没有其他瓶颈，所以unlogged与logged table性能差异不大）。

postgres=# select wait_event_type,wait_event,count(*) from pg_stat_activity group by 1,2 order by count(*) desc;  
 wait_event_type |     wait_event      | count   
-----------------+---------------------+-------  
 Lock            | extend              |    41  

《HTAP数据库 PostgreSQL 场景与性能测试之 43 - (OLTP+OLAP) unlogged table 含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 42 - (OLTP+OLAP) unlogged table 不含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 41 - (OLTP+OLAP) 含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 40 - (OLTP+OLAP) 不含索引多表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 39 - (OLTP+OLAP) 含索引多表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 38 - (OLTP+OLAP) 不含索引多表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 37 - (OLTP+OLAP) 含索引单表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 36 - (OLTP+OLAP) 不含索引单表批量写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 35 - (OLTP+OLAP) 含索引单表单点写入》

《HTAP数据库 PostgreSQL 场景与性能测试之 34 - (OLTP+OLAP) 不含索引单表单点写入》

PostgreSQL 9.4 on CentOS 7.x

PostgreSQL 9.4 logged table 测试结果

PostgreSQL 9.4，当批量导入的TABLE加了trigger，并且trigger function里面有query处理时，很卡很卡，数据库几乎不可用。

卡在哪里？

Samples: 655K of event 'cpu-clock', Event count (approx.): 143038981880  
Overhead  Shared Object          Symbol                                  
93%  postgres               [.] s_lock                              
60%  postgres               [.] LWLockAcquire                       
34%  postgres               [.] LWLockRelease                       
55%  [kernel]               [k] run_timer_softirq                   
84%  postgres               [.] GetSnapshotData                     
73%  postgres               [.] AllocSetAlloc                       
64%  postgres               [.] PushActiveSnapshot                  
59%  [kernel]               [k] __do_softirq                        
54%  [kernel]               [k] _raw_spin_unlock_irqrestore         
40%  [kernel]               [k] finish_task_switch                  
35%  libc-2.17.so           [.] __GI_____strtod_l_internal          
32%  [kernel]               [k] rcu_process_callbacks               
26%  postgres               [.] ExecMakeFunctionResultNoSets        
25%  libc-2.17.so           [.] __memcpy_ssse3_back                 
24%  postgres               [.] palloc                              
21%  plpgsql.so             [.] exec_eval_expr                      
21%  [kernel]               [k] tick_nohz_idle_exit  

lwlockacquire到release的过程可能过长。

PostgreSQL 10在各方面都有优化，比如典型的GIN索引场景，9.4在高并发更新下面也是存在性能问题。

《PostgreSQL 10 GIN索引锁优化》

建议本文提到的场景，不要使用9.4的版本。(并发控制到8以下，s_lock问题才不是那么明显)，以下是并发8的测试结果

下面测试只写入1400万，耗时乘以7，可以对比postgresql 10

case	并发数	写入量	耗时
无触发器	8	1400万	21 秒 , 147 秒
before for each row触发器	8	1400万	210 秒 , 1470 秒
after for each row触发器	8	1400万	206 秒 , 1442 秒

其他

可以随时杀掉导入进程

select pg_terminate_backend(pid) from pg_stat_activity where application_name='digoal_loader';    

参考

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

《PostgreSQL 自动创建分区实践 - 写入触发器》

[《PostgreSQL Oracle 兼容性之 - ALTER TRIGGER ENABLE

DISABLE》](../201804/20180408_02.md)

《PostgreSQL rotate table 自动清理调度 - 约束，触发器》

《PostgreSQL 事件触发器应用 - DDL审计记录 + 异步通知(notify)》

《数据入库实时转换 - trigger , rule》

《(流式、lambda、触发器)实时处理大比拼 - 物联网(IoT)\金融,时序处理最佳实践》

《快速入门PostgreSQL应用开发与管理 - 7 函数、存储过程和触发器》

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

《PostgreSQL 安全陷阱 - 利用触发器或规则，结合security invoker函数制造反噬陷阱》

《use PostgreSQL trigger manage stock & offer infomation》

[《PostgreSQL trigger/rule based replication configure, DISABLE/ENABLE [ REPLICA

ALWAYS ] TRIGGER

RULE》](../201506/20150615_01.md)

《PostgreSQL Oracle 兼容性之 - 事件触发器实现类似Oracle的回收站功能》

《PostgreSQL 触发器应用 - use trigger audit record which column modified, insert, delete.》

《use event trigger function record user who alter table’s SQL》

《PostgreSQL 事件触发器 - DDL审计 , DDL逻辑复制 , 打造DDL统一管理入》

《PostgreSQL 触发器应用 - (触发器WHEN)前置条件过滤跟踪目标记录》

《PostgreSQL 闪回 - flash back query emulate by trigger》

《PostgreSQL 事件触发器 - PostgreSQL 9.3 Event Trigger》

《表级复制(base on trigger) – PostgreSQL general sync and async multi-master replication trigger function》

《PostgreSQL 触发器用法详解 2》

《PostgreSQL 触发器用法详解 1》

《递归优化CASE - performance tuning case :use cursor\trigger\recursive replace (group by and order by) REDUCE needed blockes scan》

《PostgreSQL general public partition table trigger》

《表级复制(base on trigger) – multi master replication & performance tuning》

[《表级复制(base on trigger) – one(rw) to many(ro

rw)》](../201208/20120831_01.md)

《PostgreSQL 跟踪DDL时间 - cann’t use pg_class’s trigger trace user_table’s create,modify,delete Time》

digoal’s 大量PostgreSQL文章入口

Twitter Facebook Google+ LinkedIn

Digoal.zhou

PostgreSQL 批量SQL before/after for each row trigger的触发时机、性能差异分析、建议 - 含9.4 , 10版本

背景

测试

批量，并发数据写入性能对比(before, after, no trigger)

after trigger for each row

before trigger for each row

无trigger导入速度：

性能对比

PostgreSQL 10 on CentOS 7.x

PostgreSQL 10 logged table 测试结果

PostgreSQL 10 unlogged table 测试结果

PostgreSQL 9.4 on CentOS 7.x

PostgreSQL 9.4 logged table 测试结果

其他

参考

digoal’s 大量PostgreSQL文章入口

You May Also Enjoy

PostgreSQL(PPAS 兼容Oracle) 从零开始入门手册 - 珍藏版

PostgreSQL pipelinedb 流计算插件 - IoT应用 - 实时轨迹聚合

PostgreSQL plpgsql 存储过程、函数 - 状态、异常变量打印、异常捕获… - GET [STACKED] DIAGNOSTICS

PostgreSQL datediff 日期间隔（单位转换）兼容SQL用法