极小型缓存表单条记录更新的最优实现方案是什么？

方案对比 #

你的场景属于极小规模数据集（仅20条记录）、低更新频率（数分钟一次）、多数更新仅变动1条记录，三种可选方案的表现如下：

• 你当前在用的INSERT ON CONFLICT + DELETE事务方案
  优势：在仅修改1条记录的常见场景下写放大极低，仅操作需要新增/删除的少量记录，锁持有时间短，同时不会频繁重置表的统计信息，查询规划器表现更稳定。
  注意点：需要确保id列有唯一索引才能触发ON CONFLICT逻辑，两个语句必须放在同一个事务中执行，避免中间状态被业务侧读到脏数据。
• 清空全表后批量插入方案
  优势：代码逻辑最简洁，没有复杂的冲突处理逻辑，几乎不会出现逻辑错误。
  劣势：哪怕仅修改1条记录也要全表重写，不过20条记录的规模下性能差异可以完全忽略。如果使用TRUNCATE会持有表级排他锁，并发极高的场景下有极低概率出现短暂查询阻塞；如果表上有依赖的外键、行级DELETE触发器，TRUNCATE会被禁用/无法触发触发器，需要改用DELETE FROM cache全表删除的写法，会产生少量死元组需要vacuum定期清理。
• CTE合并单语句方案
  参考写法：

  WITH new_values AS (
      SELECT unnest($1::text[]) AS id
  ),
  del AS (
      DELETE FROM cache WHERE id NOT IN (SELECT id FROM new_values)
  )
  INSERT INTO cache(id)
  SELECT id FROM new_values
  ON CONFLICT DO NOTHING;
  

  优势：单语句天然具备原子性，不需要手动控制事务，不会出现中间状态。
  劣势：和分开写的事务方案性能几乎没有差异，仅写法不同，可读性略低于分开写的两个SQL。

最佳实践建议 #

• 如果该缓存表的查询并发不高：优先选清空全表重插的方案，维护成本最低，直接用以下逻辑就足够满足需求：

  BEGIN;
  -- 如果有触发器/外键限制，把TRUNCATE换成DELETE FROM cache即可
  TRUNCATE TABLE cache;
  INSERT INTO cache(id) SELECT unnest($1::text[]);
  COMMIT;
  

• 如果该缓存表查询并发很高，需要尽量降低锁开销和写放大：选你当前在用的事务方案，或者CTE合并的单语句方案都可以，二者性能差异极小，根据团队代码风格选择即可。

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