sql - Postgres - 在版本 10+ 上更新单个记录缓慢

Question

我们有一个很简单的表，DDL如下:

CREATE TABLE public.next_id (
  id varchar(255) NOT NULL,
  next_value int8 NOT NULL,
  CONSTRAINT next_id_pk PRIMARY KEY (id)
);

该表只有大约 120 行，除主键外没有任何索引。

当我在 DBeaver 中对运行 PostgreSQL 10.5 或 11.2 的两个 Linux 服务器中的任何一个执行以下更新查询时，大约需要 50 毫秒:

update NEXT_ID set next_value=next_value+1 where id='Session';

但是，如果我将 DBeaver 指向运行 9.5.3 PostgreSQL 的服务器，平均需要大约 3 毫秒。

现在，如果我创建一个 FOR 循环，如下所示:

do $$
begin
    for i in 1..100000 loop
        update NEXT_ID set next_value=next_value+1 where id='Session';
    end loop;
end;
$$;

在所有机器上花费的时间大致相同(~1.5 秒)。换句话说，误差幅度可能相当于一条记录更新所经历的额外延迟。

感觉语句周围的事务涉及某种开销。

我如何获得有关 PostgreSQL 将时间花在哪里的更多信息？

我尝试对“较慢”服务器上的上述单条记录更新执行EXPLAIN ANALYSE，我得到以下信息:

Update on next_id  (cost=0.00..2.58 rows=1 width=36) (actual time=0.057..0.057 rows=0 loops=1)
  ->  Seq Scan on next_id  (cost=0.00..2.58 rows=1 width=36) (actual time=0.043..0.044 rows=1 loops=1)
        Filter: ((id)::text = 'Session'::text)
        Rows Removed by Filter: 125
Planning Time: 0.066 ms
Execution Time: 0.080 ms

这似乎表明查询实际上只需要几毫秒的时间来计划和执行。那么剩下的时间都花在了哪里呢？

所有涉及的服务器都使用同一个数据库，该数据库已在所有服务器之间复制。

顺便说一句，我对有人告诉我使用 VARCHAR(255) 作为主键是个坏主意不感兴趣，因为这在所有服务器上都是一样的，这不是这个问题的重点.

---

更新:我们注意到，慢速(我的)Linux 机器和快速的 Linux 机器之间的主要区别是文件系统。我的机器使用 BTRFS 作为 Postgres 所在的文件系统，速度更快的机器使用 XFS。

对各种文件系统上的 Postgres 进行快速谷歌搜索后，发现一些人说在 BTRFS 上使用 Postgres 是(不要跨流!)不好。

我们将尝试重新格式化我的机器以使用 XFS 以查看是否会有所不同。

与此同时，我仍然有兴趣将赏金奖励给任何可以告诉我如何记录花费额外时间的人。

---

更新 2:按照 Nick Barnes 在评论中的建议，我运行了一系列 BEGIN；更新 ...; COMMIT; 明确声明，日志给了我以下输出:

LOG:  duration: 0.025 ms  parse <unnamed>: begin
LOG:  duration: 0.014 ms  bind <unnamed>: begin
LOG:  duration: 0.003 ms  execute <unnamed>: begin
LOG:  duration: 0.045 ms  parse <unnamed>:  update NEXT_ID set next_value=next_value+1 where id='Session'
LOG:  duration: 0.055 ms  bind <unnamed>:  update NEXT_ID set next_value=next_value+1 where id='Session'
LOG:  duration: 0.059 ms  execute <unnamed>:  update NEXT_ID set next_value=next_value+1 where id='Session'
LOG:  duration: 0.004 ms  parse <unnamed>:  commit
LOG:  duration: 0.003 ms  bind <unnamed>:  commit
LOG:  duration: 50.237 ms  execute <unnamed>:  commit

是的，Nick，开销肯定在 COMMIT 中。但它在做什么？有什么方法可以在日志中获取更详细的信息，了解它在那 50 毫秒内正在做什么？

Answer 1

UPDATE 本身相当便宜；在提交事务之前，您的新数据无需在崩溃中幸存下来，因此仅对内存缓冲区进行更改(服务器在空闲时将其刷新到磁盘)。

直到您提交事务，服务器才需要为您提供持久性保证。 Postgres 使用 write-ahead log 处理碰撞安全(WAL)，当您COMMIT 时，您正在等待 WAL 被同步写入磁盘。

这使得提交延迟高度依赖于文件系统和底层硬件，如果您的 PG10 实例正在等待 BTRFS 完成写时复制或其他操作，那肯定可以解释您看见。

要确认这是原因，您可以通过禁用 fsync 来跳过同步磁盘写入。 (尽管这会使您面临数据损坏的风险，因此请务必在一次性实例上对其进行测试)。一个更安全、侵入性更小的选择是 SET LOCAL synchronous_commit = off在您的事务开始时，这应该具有相同的效果，前提是您没有运行同步复制。