PostgreSQL是否优化无操作更新？多列批量更新实践咨询

我在数据库中有一张包含10余个int4列的表，同时正在开发一个更新这些列值的应用服务器。

我当前的做法需要多次往返数据库，因为会执行如下的单独调用：

UPDATE mytable SET a = a + $2 WHERE id = $1;
UPDATE mytable SET b = b + $2 WHERE id = $1;
UPDATE mytable SET c = c + $2 WHERE id = $1;
UPDATE mytable SET d = d + $2 WHERE id = $1;
UPDATE mytable SET e = e + $2 WHERE id = $1;

我考虑将其抽象为一个可更新任意数量列的通用函数，示例如下：

const EMPTY = {
  a: 0, 
  b: 0, 
  c: 0, 
  d: 0, 
  e: 0
};

let changes = {
  a: +5,
  b: -3,
  ...EMPTY
};

对应的数据库查询语句如下：

UPDATE mytable SET a = a + $2, b = b + $3, c = c + $4, d = d + $5, e = e + $6 WHERE id = $1;

我的问题如下：

1. 这种做法是否属于最佳实践？或者有没有其他避免多次数据库往返及单独调用的方法？
2. PostgreSQL是否足够智能以优化掉无操作的更新调用？此类调用中绝大多数是无实际操作的，比如上述示例中的c = c + 0, d = d + 0, e = e + 0。这些语句是否会实际执行写入操作，还是会被查询规划器忽略？效率如何？

问题1：最佳实践与替代方案 #

你把多次单列更新合并成一次多列更新的做法，本身就是最佳实践之一——减少数据库往返次数是提升应用性能的核心手段，高并发场景下，多次往返带来的网络开销和事务开销会被明显放大。

除了这种通用函数的方式，还有几种可选方案：

• 动态生成SQL语句：只保留changes中实际有变化的列（比如仅保留a:+5, b:-3），动态拼接UPDATE的SET部分，完全避免c = c +0这类无意义子句。这种方式更精准，但要注意用参数化查询规避SQL注入风险。
• 用JSON传递变更：把变更打包成JSON对象传给PostgreSQL，在数据库端解析执行更新。比如：

  UPDATE mytable
  SET 
    a = a + (changes->>'a')::int4,
    b = b + (changes->>'b')::int4
    -- 其他列同理
  WHERE id = $1;
  

  也可以配合PL/pgSQL函数实现更灵活的动态更新，适合列数多、变更列不固定的场景，减少应用端的SQL拼接工作。
• 事务包裹批量更新：如果无法合并成单条UPDATE，至少把多个单条UPDATE放在同一个事务里执行，减少事务提交的开销，但效率仍不如单条UPDATE。

问题2：PostgreSQL对无操作更新的处理 #

PostgreSQL不会自动忽略c = c +0这类无操作更新——哪怕计算后的值和原值完全一致，PostgreSQL仍会标记该行被更新，执行包括WAL日志写入、行版本更新在内的完整写入流程，带来不必要的性能开销，大表或高并发场景下影响更明显。

要避免这种情况，你可以：

1. 在应用端动态生成SQL，只包含变更值不为0的列。
2. 在UPDATE语句中添加条件，仅当变更值非0时才执行更新：

   UPDATE mytable
   SET 
     a = CASE WHEN $2 !=0 THEN a + $2 ELSE a END,
     b = CASE WHEN $3 !=0 THEN b + $3 ELSE b END,
     c = CASE WHEN $4 !=0 THEN c + $4 ELSE c END
   WHERE id = $1
     AND ($2 !=0 OR $3 !=0 OR $4 !=0);
   

   这种方式能避免无变更时的行写入，但写法较繁琐，适合列数不多的场景。

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