PostgreSQL中MERGE操作更新列数不同的性能差异问询

我正在对不同的MERGE场景进行基准测试，想了解以下三种场景下的性能是否存在差异：

场景1：仅显式更新1列 #

MERGE INTO customer_account ca
USING recent_transactions t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
  UPDATE SET balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, first_name, last_name, city, country, balance, currency_code)
  VALUES (t.customer_id, t.first_name, t.last_name, t.city, t.country, t.transaction_value, t.currency_code);

场景2：定义所有列（即使列值不会变化） #

MERGE INTO customer_account ca
USING recent_transactions t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
  UPDATE SET
    -- These should never change
    ca.customer_id = t.customer_id,
    ca.first_name = t.first_name,
    ca.last_name = t.last_name,
    ca.city = t.city,
    ca.country = t.country,
    ca.currency_code = t.currency_code,

    -- This can change
    balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, first_name, last_name, city, country, balance, currency_code)
  VALUES (t.customer_id, t.first_name, t.last_name, t.city, t.country, t.transaction_value, t.currency_code);

场景3：行中所有列均变更 #

MERGE INTO customer_account ca
USING recent_transactions t
ON t.customer_id = ca.customer_id
WHEN MATCHED THEN
  UPDATE SET
    -- These all are new values
    ca.customer_id = t.customer_id,
    ca.first_name = t.first_name,
    ca.last_name = t.last_name,
    ca.city = t.city,
    ca.country = t.country,
    ca.currency_code = t.currency_code,
    balance = balance + transaction_value
WHEN NOT MATCHED THEN
  INSERT (customer_id, first_name, last_name, city, country, balance, currency_code)
  VALUES (t.customer_id, t.first_name, t.last_name, t.city, t.country, t.transaction_value, t.currency_code);

核心问题 #

是否有必要针对这些场景开展基准测试？还是说无论仅更新1列还是多列，PostgreSQL后台都会重写整行，更新工作量一致？

回答 #

PostgreSQL基于MVCC架构，无论是普通UPDATE还是MERGE中的UPDATE操作，只要有列值发生变化，就会生成新的行版本，旧版本会被标记为删除——这意味着只要最终有列值变更，整行都会被重写。但这三个场景的性能并非完全一致，因此有必要针对你的实际环境做基准测试，具体差异点如下：

• 场景1与场景2的差异：场景2中显式更新了多个无变化的列，PostgreSQL会对每个被更新的列做新旧值的比较操作。虽然单条记录的比较开销极小，但在数据量极大、并发较高的场景下，累积的开销可能会显现出来。不过因为balance列确实会变化，最终都会生成新行版本，核心差异仅在于额外的列比较步骤。

• 场景1与场景3的差异：场景3需要从recent_transactions表读取更多列的数据来赋值，这会增加源表的IO读取量（如果这些列不在内存缓存中）。如果涉及大字段类型（如text、bytea），读取和写入的开销会更明显；而场景1仅需读取transaction_value列，IO开销更低。

• 触发器与约束的影响：如果目标表存在UPDATE触发器或列约束，场景2和场景3会触发更多的触发器逻辑或约束检查（因为显式更新了更多列），这会带来额外的性能开销，而场景1只会触发与balance列相关的逻辑。

总结来说，虽然PostgreSQL更新时会重写整行，但三个场景在IO开销、列比较操作、触发器/约束触发等方面存在实际差异，具体性能表现会因你的数据量、表结构、并发情况而不同，因此直接认为工作量一致是不准确的，有必要开展基准测试。

内容的提问来源于stack exchange，提问作者TreeWater