如何降低PostgreSQL Upsert操作时的锁等待时间？

首先，你的场景是每秒150次的UPSERT（INSERT ON CONFLICT）操作，锁等待导致平均50ms的执行时间，这确实是高并发下常见的性能瓶颈。下面是几个针对性的优化方案，按优先级排序：

1. 避免无意义的更新，减少行锁持有时间 #

当前的UPSERT语句不管原有数据是否变化，都会执行UPDATE操作——哪怕name和attributes和现有值完全一样，数据库也会加行锁并执行更新逻辑。这会大大增加锁竞争的概率，尤其是在用户频繁登录但信息没有变化的场景。

修改语句，仅在字段实际变化时才执行更新：

INSERT INTO "users" ("identifier", "name", "attributes", "team", "project")
VALUES ($1,$2,$3,$4,$5)
ON CONFLICT ("project", "identifier") DO UPDATE
SET "name" = excluded."name", "attributes" = excluded."attributes"
-- 仅当字段值不同时才触发更新
WHERE users."name" IS DISTINCT FROM excluded."name" 
   OR users."attributes" IS DISTINCT FROM excluded."attributes";

这个改动能直接减少大量不必要的行锁占用，是见效最快的优化手段。

2. 批量UPSERT降低请求频率 #

每秒150次单条请求，相当于每秒创建150个独立事务，每个事务都要经历锁检查、执行、释放的过程。如果能在应用层将多个请求合并成批量操作，能显著降低锁竞争的频率。

比如，收集100ms内的登录请求（或每10-20条请求），执行批量UPSERT：

INSERT INTO "users" ("identifier", "name", "attributes", "team", "project")
VALUES 
  ($1,$2,$3,$4,$5),
  ($6,$7,$8,$9,$10),
  ($11,$12,$13,$14,$15)
ON CONFLICT ("project", "identifier") DO UPDATE
SET "name" = excluded."name", "attributes" = excluded."attributes"
WHERE users."name" IS DISTINCT FROM excluded."name" 
   OR users."attributes" IS DISTINCT FROM excluded."attributes";

注意批量大小不要过大（比如超过50条），否则单个事务执行时间过长反而会增加锁持有时间。应用层需要做好请求的批量收集和失败重试逻辑。

3. 排查锁等待的具体来源 #

可以通过PostgreSQL的系统视图定位锁竞争的细节，比如：

• 查询pg_locks查看当前持有和等待的锁类型、关联的表/行
• 结合pg_stat_activity查看哪些会话在持有锁，以及对应的SQL语句

执行以下查询快速定位问题：

SELECT 
  a.query,
  l.locktype,
  l.mode,
  l.granted,
  l.relation::regclass
FROM pg_locks l
JOIN pg_stat_activity a ON l.pid = a.pid
WHERE l.relation = 'users'::regclass;

这能帮你确认是行锁竞争，还是索引锁的问题，或者是否有长事务在持有锁未释放。

4. 考虑乐观锁替代方案（冲突率低时适用） #

如果你的场景中，同一用户（同一identifier+project）的登录冲突率不高，可以尝试乐观锁方案，避免悲观锁的竞争：

先尝试更新，如果更新行数为0（说明记录不存在），再执行插入：

-- 先尝试更新，仅当数据变化时执行
UPDATE "users" 
SET "name" = $2, "attributes" = $3, version = version + 1
WHERE "project" = $5 AND "identifier" = $1 
  AND ("name" IS DISTINCT FROM $2 OR "attributes" IS DISTINCT FROM $3);

-- 如果没有找到记录，执行插入
IF NOT FOUND THEN
  INSERT INTO "users" ("identifier", "name", "attributes", "team", "project", version)
  VALUES ($1,$2,$3,$4,$5, 1);
END IF;

这种方式需要在表中新增一个version字段（整数类型，默认1），应用层需要处理可能的并发插入冲突（比如捕获unique_violation异常并重试）。在冲突率低的场景下，这种方式的锁竞争会远低于UPSERT。

5. 调整PostgreSQL配置辅助优化 #

• 设置lock_timeout：比如设置lock_timeout = '50ms'，让等待锁超过50ms的请求直接报错，避免长时间阻塞，应用层需要配合重试逻辑。
• 确保work_mem足够，避免排序/哈希操作溢出到磁盘，增加事务执行时间。

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