[译文] PostgreSQL 内置触发函数加速更新

原创 Luca Ferrari 2021-08-03

472

PostgreSQL 附带一个内部触发器函数，命名为suppress_redundant_updates_trigger可用于避免对表进行幂等更新。

该在线文档关于如何使用它解释得非常好，包括一个事实，即触发应该就如触发链上的导火索一样，所以触发器的名称应该是按字母顺序自然排序的最后一个。

但是值得使用这样的功能吗？

让我们在众所周知的pgbench数据库上找出一个非常简单的例子。

首先，我们进行初始设置：

pgbench=> SELECT count(*), 
          pg_size_pretty( pg_relation_size( 'pgbench_accounts' ) ) 
          FROM pgbench_accounts;
  count   | pg_size_pretty 
----------|----------------
 10000000 | 1281 MB
(1 row)

现在，执行一个 idempotet UPDATE，它不会改变任何东西，并监控时间：

pgbench=> \timing
Timing is on.
pgbench=> UPDATE pgbench_accounts SET filler = filler;
UPDATE 10000000
Time: 307939,763 ms (05:07,940)

pgbench=> SELECT pg_size_pretty( pg_relation_size( 'pgbench_accounts' ) );
 pg_size_pretty 
----------------
 2561 MB
(1 row)

Time: 180,732 ms

请注意该表的大小是如何增加一倍的：这是因为每一行都被它的精确副本替换而导致膨胀。

接着，使用该suppress_redundant_updates_trigger函数创建触发器，并再次运行相同的更新，但在服务器重新启动以清理内存后。

pgbench=> CREATE TRIGGER tr_avoid_idempotent_updates
BEFORE UPDATE ON pgbench_accounts
FOR EACH ROW
EXECUTE FUNCTION suppress_redundant_updates_trigger();

-- restart the server

pgbench=> \timing
Timing is on.
pgbench=> UPDATE pgbench_accounts SET filler = filler;
UPDATE 0
Time: 287588,607 ms (04:47,589)

pgbench=> SELECT pg_size_pretty( pg_relation_size( 'pgbench_accounts' ) );
 pg_size_pretty 
----------------
 2561 MB
(1 row)

总增益大约是20 secs，这是大约的加速7%，这根本不是太多。
但是，请注意UPDATE报告零元组是如何被触及的，因此虽然加速增益并不是很令人兴奋，但表格的膨胀仍然与之前相同UPDATE。
在完全真空之后，加速结果会更多，但这可能是内存中已有一些页面的反作用：

pgbench=> VACUUM FULL pgbench_accounts ;
VACUUM
Time: 222455,150 ms (03:42,455)
pgbench=> UPDATE pgbench_accounts SET filler = filler;
UPDATE 0
Time: 198104,981 ms (03:18,105)

但是，即使在重新启动服务器后，时间仍然较短：

pgbench=> UPDATE pgbench_accounts SET filler = filler;
UPDATE 0
Time: 184217,260 ms (03:04,217

所以在一个不臃肿的桌子上的收益67%是更有趣的！

定时触发器执行

对每一行执行触发函数需要多长时间？可以通过以下方式获取此信息EXPLAIN ANALYZE：

pgbench=> EXPLAIN (FORMAT yaml, ANALYZE, VERBOSE, TIMING ) 
          UPDATE pgbench_accounts SET filler = filler;               
                    QUERY PLAN                     
---------------------------------------------------
 - Plan:                                          +
     Node Type: "ModifyTable"                     +
     Operation: "Update"                          +
     Parallel Aware: false                        +
     Relation Name: "pgbench_accounts"            +
     Schema: "public"                             +
     Alias: "pgbench_accounts"                    +
     Startup Cost: 0.00                           +
     Total Cost: 263935.00                        +
     Plan Rows: 10000000                          +
     Plan Width: 103                              +
     Actual Startup Time: 153053.980              +
     Actual Total Time: 153377.845                +
     Actual Rows: 0                               +
     Actual Loops: 1                              +
     Plans:                                       +
       - Node Type: "Seq Scan"                    +
         Parent Relationship: "Member"            +
         Parallel Aware: false                    +
         Relation Name: "pgbench_accounts"        +
         Schema: "public"                         +
         Alias: "pgbench_accounts"                +
         Startup Cost: 0.00                       +
         Total Cost: 263935.00                    +
         Plan Rows: 10000000                      +
         Plan Width: 103                          +
         Actual Startup Time: 8.968               +
         Actual Total Time: 44542.939             +
         Actual Rows: 10000000                    +
         Actual Loops: 1                          +
         Output:                                  +
           - "aid"                                +
           - "bid"                                +
           - "abalance"                           +
           - "filler"                             +
           - "ctid"                               +
   Planning Time: 24.475                          +
   Triggers:                                      +
     - Trigger Name: "tr_avoid_idempotent_updates"+
       Relation: "pgbench_accounts"               +
       Time: 1510.272                             +
       Calls: 10000000                            +
   Execution Time: 159552.624
(1 row)

运行触发器大致1.5 secs需要10 million元组。
假设计时足够准确和稳定，这意味着0.00015 msecs对于每个元组，毕竟开销并不大。

可以提供另一个表进行实验，以查看触发器执行的时间是否取决于数据类型及其内容：

pgbench=> create table stuff( pk serial, t text );

pgbench=> INSERT INTO stuff( t ) SELECT repeat( 'abc', 1000 ) 
          from generate_series( 1, 2000000 );
          
          
pgbench=> CREATE TRIGGER tr_avoid_idempotent_updates
BEFORE UPDATE ON stuff 
FOR EACH ROW
EXECUTE FUNCTION suppress_redundant_updates_trigger();


pgbench=> EXPLAIN ( FORMAT yaml, ANALYZE, VERBOSE, TIMING ) 
          UPDATE stuff SET t = t;
          
...
  Triggers:                                      +
     - Trigger Name: "tr_avoid_idempotent_updates"+
       Relation: "stuff"                          +
       Time: 223.227                              +
       Calls: 2000000                             +

同样，触发器的平均执行时间是0.00011 msecs，并且可以通过pk列获得非常相似（如果不相等）的结果，所以我会说触发器的执行时间不涉及列的特定类型正在更新。

黑底触发功能

suppress_redundant_updates_trigger是在文件中定义utils/adt/trigfuncs.c，和魔在下面的代码段发生的情况：

/* if the tuple payload is the same ... */
	if (newtuple->t_len == oldtuple->t_len &&
		newheader->t_hoff == oldheader->t_hoff &&
		(HeapTupleHeaderGetNatts(newheader) ==
		 HeapTupleHeaderGetNatts(oldheader)) &&
		((newheader->t_infomask & ~HEAP_XACT_MASK) ==
		 (oldheader->t_infomask & ~HEAP_XACT_MASK)) &&
		memcmp(((char *) newheader) + SizeofHeapTupleHeader,
			   ((char *) oldheader) + SizeofHeapTupleHeader,
			   newtuple->t_len - SizeofHeapTupleHeader) == 0)
	{
		/* ... then suppress the update */
		rettuple = NULL;
	}

本质上是比较旧元组和新元组，看它们是否具有相同的标头、相同数量的属性，当然还有相同的内存表示内容（通过memcpm(3)）。

在 plpgsql 中

可以plpgsql通过IS DISTINCT FROM操作符实现一个基本功能：

CREATE OR REPLACE FUNCTION
  f_avoid_idempotent_updates()
  RETURNS TRIGGER
AS $CODE$
BEGIN
  IF NEW.* IS DISTINCT FROM OLD.* THEN
    RETURN NEW;
  ELSE
    RETURN NULL;
  END IF;
END
  $CODE$
  LANGUAGE plpgsql;

使用此触发器执行会导致：

pgbench=> drop trigger tr_avoid_idempotent_updates on pgbench_accounts;
DROP TRIGGER
                                                     
pgbench=> create trigger tr_avoid_idempotent_updates 
before update on pgbench_accounts              
for each row
execute function f_avoid_idempotent_updates();
CREATE TRIGGER


pgbench=> update pgbench_accounts set filler = filler;
UPDATE 0
Time: 167400,098 ms (02:47,400)

如果您跟踪函数执行：

pgbench=> select * from pg_stat_user_functions ;
-[ RECORD 1 ]--------------------------
funcid     | 36672
schemaname | public
funcname   | f_avoid_idempotent_updates
calls      | 10000000
total_time | 21276.741
self_time  | 21276.741

这表明21 secs花费在进行触发器分析上，因此大致0,0021 msecs花费在每个元组上。到目前为止，这比 C 默认函数（大致为0.00015 msecs）要贵得多。输出强调了类似的结果EXPLAIN ANALYZE：

pgbench=> EXPLAIN (FORMAT yaml, ANALYZE, TIMING )
          UPDATE pgbench SET filler = filler;
...
  |   Triggers:                                      +
  |     - Trigger Name: "tr_avoid_idempotent_updates"+
  |       Relation: "pgbench_accounts"               +
  |       Time: 23002.383                            +
  |       Calls: 10000000                            +
  |   Execution Time: 163343.183

这Time是关于23000 msecsC 本机函数的内容1500 msecs。

结论

内部suppress_redundant_updates_trigger函数对于减少大批量s 的时间和膨胀很有用UPDATE。

该函数是用 C 语言实现的，并检查元组的内存内容是否相同，这使得这种方法非常强大，并且不像用户定义自定义触发器函数那样容易出错。

原文链接：https://fluca1978.github.io/2021/06/03/PostgreSQLUpdateTrigger.html

postgresql

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

[译文] PostgreSQL 内置触发函数加速更新

定时触发器执行

黑底触发功能

在 plpgsql 中

结论

评论