PostgreSQL ON CONFLICT DO UPDATE 增加 WHERE 条件优化性能

ON CONFLICT DO UPDATE 增加 WHERE 条件优化性能

📌 核心思路

在 PostgreSQL 的 ON CONFLICT DO UPDATE 语句中添加 WHERE 条件，只在数据真正变化时才执行更新，避免无效写入。

— 优化前：无条件更新（即使数据完全相同也会执行）
ON CONFLICT (sell_through_record_id)
DO UPDATE SET
version_number = excluded.version_number,
released_dt = excluded.released_dt,
qty = excluded.qty,
— … 191 个字段

— 优化后：只有数据变化时才更新
ON CONFLICT (sell_through_record_id)
DO UPDATE SET
version_number = excluded.version_number,
released_dt = excluded.released_dt,
qty = excluded.qty,
— … 191 个字段
WHERE
magellan_sell_through.version_number IS DISTINCT FROM excluded.version_number
OR magellan_sell_through.released_dt IS DISTINCT FROM excluded.released_dt
OR magellan_sell_through.qty IS DISTINCT FROM excluded.qty
— … 所有字段比较

🎯 为什么要加 WHERE 条件？

问题场景

在高频率 UPSERT（INSERT OR UPDATE）场景中，大量数据可能是重复的或未变化的。

典型业务场景：
– 每天接收 100 万条销售数据
– 其中 80-90% 的数据与数据库中已存在的完全相同
– 只有 10-20% 的数据有实际变化

如果不加 WHERE：
❌ 100 万次全部执行 UPDATE
❌ 即使数据完全相同，也要写 WAL 日志
❌ 即使数据完全相同，也要维护索引
❌ 即使数据完全相同，也要生成新元组（HOT 链）

如果加了 WHERE：
✅ 80-90 万次被过滤掉（不执行 UPDATE）
✅ 只有 10-20 万次真实更新才执行
✅ 大幅减少 I/O、WAL、索引维护开销

💡 这样做的好处

1️⃣ 大幅减少无效更新

场景：90% 的数据完全重复

优化前：
– 100 万次写入 → 100 万次 UPDATE
– 每次 UPDATE 都要：
✅ 写数据页
✅ 写 WAL 日志
✅ 更新索引
✅ 创建新元组

优化后：
– 100 万次写入 → 10 万次 UPDATE（90% 被 WHERE 过滤）
– 节省 90% 的资源消耗

性能提升： 🚀 80-90%

2️⃣ 降低 WAL 日志量

PostgreSQL 的每个 UPDATE 都会生成 WAL（Write-Ahead Logging）日志。

WAL 日志作用：
– 事务持久性保证
– 崩溃恢复
– 主从复制

优化前：
– 100 万次 UPDATE → 100 MB WAL 日志

优化后：
– 10 万次 UPDATE → 10 MB WAL 日志
– WAL 减少 90% 📉

收益：

• 📉 磁盘 I/O 减少
• 📉 网络传输减少（主从复制场景）
• 📉 备份时间缩短

3️⃣ 减少索引维护开销

每次 UPDATE 都需要检查和维护所有相关索引。

假设表有 5 个索引：

优化前：
– 100 万次 UPDATE × 5 个索引 = 500 万次索引操作

优化后：
– 10 万次 UPDATE × 5 个索引 = 50 万次索引操作
– 索引维护减少 90% 📉

收益：

• 📉 索引页更新减少
• 📉 索引锁竞争减少
• 📉 并发性能提升

4️⃣ 提升 HOT 命中率

配合 fillfactor=75，让 Heap Only Tuple (HOT) 优化更容易生效。

HOT 优化原理：
– UPDATE 时如果在同一页内完成，不需要更新索引
– 需要页面有空闲空间（fillfactor < 100）

优化前：
– 大量无效 UPDATE 占满页面空间
– HOT 命中率低（~20%）

优化后：
– 只有真实更新才占用空间
– 页面有更多空闲给 HOT
– HOT 命中率提升（~70-90%）

收益：

• 🚀 索引更新进一步减少
• 🚀 整体性能再提升 30-50%

5️⃣ 降低锁竞争，提升并发

UPDATE 会获取行锁（Row Lock），减少 UPDATE 次数意味着减少锁竞争。

高并发场景：

优化前：
– 100 万次 UPDATE → 大量行锁
– 事务等待时间长
– 并发吞吐量低

优化后：
– 10 万次 UPDATE → 行锁减少 90%
– 事务等待时间短
– 并发吞吐量提升 2-3 倍 🚀

6️⃣ 延长硬件寿命

减少磁盘写入次数，对 SSD 尤其重要。

SSD 有写入寿命限制（TBW – Terabytes Written）

优化前：
– 每天写入 100 GB
– SSD 寿命：3 年

优化后：
– 每天写入 10 GB
– SSD 寿命：30 年（理论值）

收益：

• 💰 降低硬件更换成本
• 🌱 更环保（减少能耗）

⚠️ 重要说明：WHERE 成立时的行为

关键理解

如果 WHERE 条件中任意一个字段不同，SET 子句中的所有字段都会被更新（即使它们的值没变）

示例说明

ON CONFLICT (sell_through_record_id)
DO UPDATE SET
version_number = excluded.version_number, — 字段1
released_dt = excluded.released_dt, — 字段2
qty = excluded.qty, — 字段3
amount = excluded.amount — 字段4
WHERE
magellan_sell_through.version_number IS DISTINCT FROM excluded.version_number
OR magellan_sell_through.qty IS DISTINCT FROM excluded.qty;

场景：只有 version_number 变了

数据库中的旧数据：
– version_number: 1
– released_dt: '2024-01-01'
– qty: 10
– amount: 100.00

新数据：
– version_number: 2 ← 变化了
– released_dt: '2024-01-01' ← 没变
– qty: 10 ← 没变
– amount: 100.00 ← 没变

执行流程：

1. WHERE 条件评估：
– version_number: 1 != 2 ✅ 成立
– qty: 10 = 10 ❌ 不成立

结果：WHERE = true（因为 version_number 不同）

2. 执行 UPDATE：
✅ 会更新 ALL 4 个字段（包括值相同的字段）

实际赋值：
– version_number: 1 → 2 （真正变化）
– released_dt: '2024-01-01' → '2024-01-01' （值相同但仍执行赋值）
– qty: 10 → 10 （值相同但仍执行赋值）
– amount: 100.00 → 100.00 （值相同但仍执行赋值）

PostgreSQL 的内部优化

虽然 SQL 层面更新了所有字段，但 PostgreSQL 存储引擎会优化：

存储层行为：

1. 检查每个字段的新值 vs 旧值
2. 如果值相同 → 不修改该字段的物理存储内容
3. 如果值不同 → 修改该字段

但仍有的开销：
– ✅ WAL 日志仍需记录整个元组
– ✅ 索引维护仍需检查所有索引列
– ✅ HOT 链仍需创建新元组（即使大部分字段未变）

结论

WHERE 的主要价值：
✅ 过滤掉"完全无变化"的更新（90%+ 场景）
✅ 对于"部分字段变化"的场景，PostgreSQL 内部已有优化

不需要进一步优化 SET 子句（只更新变化的字段），因为：
– SQL 会变得极其复杂
– 需要在应用层比较新旧值
– 维护成本高
– 收益有限（PostgreSQL 已经优化了）

🚀 进阶方案：data_hash 哈希比较（简要介绍）

核心思路

不比较所有字段，而是计算所有字段的哈希值，只比较哈希值。

— 表中添加哈希字段
ALTER TABLE magellan_sell_through ADD COLUMN data_hash VARCHAR(64);

— INSERT 时计算哈希
INSERT INTO magellan_sell_through (..., data_hash)
SELECT
...,
MD5(CONCAT_WS('|',
version_number::text,
released_dt::text,
qty::text,
amount::text
— … 所有业务字段
))
FROM ...

— UPDATE 时只比较哈希
ON CONFLICT (sell_through_record_id)
DO UPDATE SET
version_number = excluded.version_number,
released_dt = excluded.released_dt,
— … 所有字段
data_hash = excluded.data_hash
WHERE
magellan_sell_through.data_hash != excluded.data_hash;

优势

• ✅ WHERE 条件极简（只比较 1 个字段）
• ✅ SQL 长度从 10000 字符降到 ~100 字符
• ✅ 仍能检测任何字段变化

劣势

• ❌ 需要修改表结构（添加 data_hash 字段）
• ❌ CONCAT_WS 拼接几百个字段有性能开销
• ❌ MD5 计算本身也有 CPU 开销
• ❌ 维护复杂度增加

适用场景

• 字段特别多（>200 个）
• SQL 长度成为维护瓶颈
• 对性能要求极高

建议

当前方案（全字段 WHERE）已经足够好：
– SQL 虽然长（~10000 字符），但 PostgreSQL 完全支持
– PreparedStatement 缓存后，解析开销可忽略
– 性能提升显著（80-90%）

除非遇到以下情况，否则不建议引入 data_hash：
1. 字段数量超过 500 个
2. SQL 长度导致维护困难
3. 经过压测发现 WHERE 比较成为瓶颈

🔧 实施步骤

步骤 1：添加 WHERE 条件

已在 MagellanSellThroughMapper.xml 中实现：

<sql id="DO_UPDATE_SET">
on conflict (sell_through_record_id) do update set
version_number=excluded.version_number,
released_dt=excluded.released_dt,
<!– … 其他字段 –>
top_choice_express_flg=excluded.top_choice_express_flg
WHERE
magellan_sell_through.version_number IS DISTINCT FROM excluded.version_number
OR magellan_sell_through.released_dt IS DISTINCT FROM excluded.released_dt
<!– … 所有 191 个字段 –>
</sql>

关键点：

• ✅ 使用 IS DISTINCT FROM 而非 !=（正确处理 NULL）
• ✅ 覆盖所有被更新的字段
• ✅ 用 OR 连接（任意字段变化即触发更新）

步骤 2：调整 fillfactor

— 设置填充因子为 75%，预留空间给 HOT
ALTER TABLE magellan_sell_through SET (fillfactor = 75);

— 重新整理表（必须在维护窗口执行）
VACUUM FULL magellan_sell_through;

注意：

• ⚠️ VACUUM FULL 会锁表，建议在业务低峰期执行
• ⚠️ 执行前做好备份

步骤 3：验证效果

监控 HOT 命中率

SELECT
relname AS 表名,
n_tup_upd AS 总更新次数,
n_tup_hot_upd AS HOT更新次数,
round(100.0 * n_tup_hot_upd / NULLIF(n_tup_upd, 0), 2) AS HOT命中率
FROM pg_stat_user_tables
WHERE relname = 'magellan_sell_through';

— 期望结果：HOT命中率 > 70%

查看执行计划

EXPLAIN (ANALYZE, BUFFERS, TIMING)
— 你的 INSERT 语句

— 关注指标：
— – Heap Blocks：应该减少
— – Buffers：应该减少
— – Execution Time：应该降低

对比优化前后

— 优化前统计
SELECT
n_tup_upd,
n_tup_hot_upd,
last_update_time
FROM pg_stat_user_tables
WHERE relname = 'magellan_sell_through';

— 执行一批数据后，再次查询
— 对比 n_tup_upd 的增长速度

📊 预期效果

性能提升预估

假设每天 100 万次写入，90% 数据重复：

🎯 最佳实践

✅ 推荐做法

❌ 避免的做法

📝 总结

核心价值

ON CONFLICT DO UPDATE 加 WHERE 条件是高频 UPSERT 场景下性价比最高的优化手段之一

三大收益

实施要点

关于 data_hash 方案

• 可以作为未来优化方向

🔗 相关文档

• HOT 优化详解
• PostgreSQL 官方文档 – ON CONFLICT
• PostgreSQL fillfactor 最佳实践

最后提醒：

这个优化看似简单，但对于高频写入场景，效果立竿见影。建议立即实施！