高并发写入负载下PostgreSQL的Selsert实现方案咨询

嘿，这个问题我太熟了——在高并发场景下实现「查得到就返回ID，查不到就插入再返回新ID」的逻辑，绝对是PostgreSQL里的高频痛点。你提到的两个方案我都折腾过，咱们好好拆解下，帮你选最优解：

这绝对是高并发下的最优选择，PostgreSQL原生支持的语法，内部锁机制经过高度优化，比手动加锁靠谱太多。

核心思路是：先尝试插入，遇到唯一冲突就啥也不做，然后通过CTE+UNION ALL拿到已存在的ID。假设你的表结构是my_table(id SERIAL PRIMARY KEY, value TEXT UNIQUE)，代码如下：

WITH ins AS (
  INSERT INTO my_table (value)
  VALUES ('你的目标文本')
  ON CONFLICT (value) DO NOTHING
  RETURNING id
)
SELECT id FROM ins
UNION ALL
SELECT id FROM my_table WHERE value = '你的目标文本'
LIMIT 1;

为什么这个方案好？ #

• 无额外锁开销：只有真正插入新行时，PostgreSQL才会加短暂的行锁/索引锁，查询操作走快照读，完全不会阻塞其他请求。
• 并发性能拉满：多个请求同时查同一个已存在的value时，完全并行处理，不会像手动锁那样串行化。
• 代码简洁可靠：不需要写复杂的PL/pgSQL逻辑，靠原生SQL就能搞定，出错概率极低。

如果要封装成你要的selsert函数，直接写个PL/pgSQL函数就行：

CREATE OR REPLACE FUNCTION selsert(p_value text)
RETURNS integer AS $$
BEGIN
  RETURN (
    WITH ins AS (
      INSERT INTO my_table (value)
      VALUES (p_value)
      ON CONFLICT (value) DO NOTHING
      RETURNING id
    )
    SELECT id FROM ins
    UNION ALL
    SELECT id FROM my_table WHERE value = p_value
    LIMIT 1
  );
END;
$$ LANGUAGE plpgsql VOLATILE;

调用的时候直接SELECT selsert('你的文本值');就行，非常方便。

这个方案是手动给特定value加锁，确保同一时间只有一个进程处理该value的查/插操作。代码大概是这样的：

BEGIN;
-- 用hashtext把文本转成唯一整数锁键（碰撞概率极低）
SELECT pg_advisory_lock(hashtext('你的目标文本'));

-- 先查是否存在
SELECT id INTO v_id FROM my_table WHERE value = '你的目标文本';

-- 不存在就插入
IF v_id IS NULL THEN
  INSERT INTO my_table (value) VALUES ('你的目标文本') RETURNING id INTO v_id;
END IF;

SELECT pg_advisory_unlock(hashtext('你的目标文本'));
COMMIT;

SELECT v_id;

这个方案的优缺点： #

• 优点：逻辑直观，完全由你控制锁的范围，适合一些特殊场景（比如需要在锁里做额外操作）。
• 缺点：并发性能差——即使是查询相同value的请求，也要先拿锁串行处理，严重影响吞吐量；另外如果锁键生成逻辑有问题（比如hashtext碰撞），会导致无关请求互相阻塞，排查起来很麻烦。

优先用INSERT ... ON CONFLICT方案，理由很简单：

1. 原生优化的锁机制比手动锁高效N倍，不会无端阻塞请求；
2. 代码简洁，维护成本低；
3. 面对高频重复查询的场景，性能碾压手动锁方案。

注意事项 #

• 必须给value列加唯一约束，否则ON CONFLICT无法生效，高并发下会出现重复插入的问题；
• 如果是分区表，要注意ON CONFLICT的分区兼容限制；
• 函数要标记为VOLATILE（因为会修改数据），不要用STABLE，避免PostgreSQL的优化导致异常。

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