为什么我的 PostgreSQL 更新变慢了？

最近，一位客户问我为什么随着时间的推移，批量加载到 PostgreSQL 表中的速度会越来越慢。他们看到这是表上的一个触发器，耗时越来越长，并且在那个触发器中，更新一直在变慢。现在缓慢的更新是一个常见的问题，所以我认为详细解释这里发生的事情会很有趣。

一个演示 PostgreSQL 中更新变慢的示例

对象定义

我们的示例场景是一个接收批量加载的表。因为我们经常需要查询表中值的总和，所以我们保留了一个由触发器维护的汇总表：

CREATE TABLE item (
   id bigint GENERATED ALWAYS AS IDENTITY,
   value bigint NOT NULL
);
 
CREATE TABLE sum (
   total bigint NOT NULL
);
 
INSERT INTO sum VALUES (0);
 
CREATE FUNCTION add_to_sum() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   UPDATE sum SET total = total + NEW.value;
   RETURN NEW;
END;$$;
 
CREATE CONSTRAINT TRIGGER add_to_sum
   AFTER INSERT ON item
   DEFERRABLE INITIALLY DEFERRED
   FOR EACH ROW
   EXECUTE FUNCTION add_to_sum();

我们在这里使用延迟约束触发器，以便在事务的最后进行更新。然后，sum表中的行不会被锁定超过绝对必要的时间，并且我们获得了最好的并发性。此外，我们注意不要在上面放置任何索引sum（这很容易强制只有一行），以便我们可以获得快速的HOT 更新。

我们如何执行批量加载

我们将使用如下脚本批量加载表：

\timing on
 
COPY item (value) FROM STDIN;
11638
422
17165
[a total of 100000 random numbers]
\.

由于此脚本将语句与数据混合在一起COPY，因此我们需要使用以下命令执行它psql：

psql -d dbname -f script.sql

在我的机器上，上面的脚本需要 60 秒，这是加载 100000 行的非常长的时间。如果我将触发器放在 上item，相同的脚本会在不到 70 毫秒的时间内运行。

如何证明问题是更新变慢？

问题显然是触发器，而触发器函数只包含一条UPDATE语句。为什么这个声明需要这么长时间？我们可以使用auto_explain扩展来查看各个UPDATE语句的执行细节。为此，我们将以下行添加到postgresql.conf：

shared_preload_libraries = 'auto_explain'
auto_explain.log_min_duration = 0
auto_explain.log_analyze = on
auto_explain.log_buffers = on
auto_explain.log_wal = on
auto_explain.log_nested_statements = on

我们需要重新启动 PostgreSQL 以使更改的shared_preload_libraries设置生效。请注意，这些不是我想在繁忙的服务器上使用的设置。一方面，auto_explain.log_analyze = on将记录所有语句，这可能超出您的磁盘可以处理的范围。此外，auto_explain.log_analyze = on将大大减慢所有语句，因为 PostgreSQL 然后详细测量语句执行。

但是，如果我们重新开始实验，我们会UPDATE记录来自触发器的 100000 条语句中的每一条，结果很有启发性：

第一个UPDATE：

Query Text: UPDATE sum SET total = total + NEW.value
Update on sum  (cost=0.00..38.25 rows=0 width=0) (actual time=0.016..0.017 rows=0 loops=1)
  Buffers: shared hit=3
  WAL: records=1 bytes=74
  ->  Seq Scan on sum  (cost=0.00..38.25 rows=2260 width=14) (actual time=0.005..0.005 rows=1 loops=1)
        Buffers: shared hit=1

然后执行变得越来越慢，直到第 100000 个UPDATE读取：

Query Text: UPDATE sum SET total = total + NEW.value
Update on sum  (cost=0.00..38.25 rows=0 width=0) (actual time=1.641..1.641 rows=0 loops=1)
  Buffers: shared hit=445
  WAL: records=1 bytes=74
  ->  Seq Scan on sum  (cost=0.00..38.25 rows=2260 width=14) (actual time=1.637..1.637 rows=1 loops=1)
        Buffers: shared hit=443

解释更新变慢的原因

行版本不断累积

时间显然花在了 的顺序扫描上sum，它必须增长到 443 个 8kB 页面的大小。要理解这一点，请记住UPDATEPostgreSQL 中的 an 不会覆盖现有行，而是将新的行版本添加到表中。这与a后跟 an类似（但不完全相同） 。所以这 100000 个实际上向表中添加了 100000 个新行版本。DELETEINSERTUPDATE

VACUUM无法清理

删除语句VACUUM产生的这些“死行版本”是其工作。UPDATE通常，autovacuum 进程会自动处理此问题。那么为什么它在我们的案例中不起作用呢？原因是VACUUM只能删除比任何当前活动事务更旧的行版本。但是我们的COPY语句是单个事务，这通常是一件好事，因为它使批量加载速度更快。所以autovacuum只能在语句完成sum后清理死行版本，到时候表已经膨胀变形了。COPYsum

热修剪也无济于事

但是等一下：我们仔细设计了我们的设置来获得 HOT 更新，这样 PostgreSQL 就不必运行VACUUM来清理死行版本。每个 SQL 语句都可以执行HOT 修剪以廉价地去除死行版本。但是，HOT 修剪受到相同的限制，VACUUM并且也无法从当前打开的事务中清理行版本。

索引无济于事

乍一看执行计划很慢，你可能会认为执行计划变慢的原因是 PostgreSQL 执行顺序扫描，索引会加快处理速度。这不适sum用于当前定义中的表，但我们当然可以添加一个主键列。但是，这会损害性能而不是提高性能：PostgreSQL 仍然必须遵循每个块内的 HOT 链到最后。此外，每一个UPDATE不是 HOT 的（因为当前页面中没有更多空间）都会创建一个新的（相同的）主键索引条目，并且扫描这些条目将使我们访问表的每一页。因此，如果有的话，索引会使UPDATE速度更慢。

检查表pageinspect

推理是一回事，但看看实际的表数据更有说服力。通常，我们看不到死行版本，但使用pageinspect扩展中的功能，超级用户可以看到表中的所有数据。如果我们足够快并在COPY完成后运行以下查询，但在 autovacuum 清理表之前，我们可以看到第一页中的内容sum：

SELECT format('(0,%s)', lp) AS tid,
       t_ctid AS replacement,
       t_xmax <> 0 AS dead,
       t_infomask2 & 16384 <> 0 AS hot_updated,
       t_attrs[1] AS total
FROM heap_page_item_attrs(get_raw_page('sum', 0), 'sum');

结果如下：

   tid   │ replacement │ dead │ hot_updated │       total        
═════════╪═════════════╪══════╪═════════════╪════════════════════
 (0,1)   │ (0,2)       │ t    │ t           │ \x0000000000000000
 (0,2)   │ (0,3)       │ t    │ t           │ \x214e000000000000
 (0,3)   │ (0,4)       │ t    │ t           │ \xe766000000000000
 (0,4)   │ (0,5)       │ t    │ t           │ \xeaa0000000000000
 (0,5)   │ (0,6)       │ t    │ t           │ \x9113010000000000
 (0,6)   │ (0,7)       │ t    │ t           │ \x5c23010000000000
 ...
 (0,225) │ (0,226)     │ t    │ t           │ \x1356380000000000
 (0,226) │ (1,1)       │ t    │ f           │ \xab99380000000000
(226 rows)

每个条目都是前一个条目的替换，所有条目都是死的（xmax设置），UPDATE除了最后一个（在下一页创建一行）之外的所有 s 都是 HOT。

更新变慢的补救措施

现在我们了解了问题，解决方案就很清楚了：不要在单个事务中重复更新同一行。避免这种情况的最佳方法将取决于原始工作负载的详细信息。在我们的例子中，一种可能的解决方案是使用转换表的语句级触发器：

DROP TRIGGER add_to_sum ON item;
 
CREATE OR REPLACE FUNCTION add_to_sum() RETURNS trigger
   LANGUAGE plpgsql AS
$$BEGIN
   UPDATE sum SET total = total +
      (SELECT sum(value) FROM new_rows);
   RETURN NULL;
END;$$;
 
CREATE TRIGGER add_to_sum
   AFTER INSERT ON item
   REFERENCING NEW TABLE AS new_rows
   FOR EACH STATEMENT
   EXECUTE FUNCTION add_to_sum();

使用此触发器，每个语句只会执行一次UPDATEon 。这样，批量加载在我的机器上只需要略长于 90 毫秒的时间！sumCOPY

结论

更新随时间变慢的最常见原因是您在单个事务中重复更新相同的行。简单的解决方案是避免这种重复更新：毕竟，事务完成后，只有事务中一行的最后一次更新是可见的。

原文标题：WHY ARE MY POSTGRESQL UPDATES GETTING SLOWER?

原文作者：Laurenz Albe

原文链接：https://www.cybertec-postgresql.com/en/why-are-my-postgresql-updates-getting-slower/