PostgreSQL：高频增量更新场景下INSERT...ON CONFLICT语句优化问询

针对你这个高频更新、极少插入的场景，确实可以调整逻辑顺序来避免主键序列的无意义增长——原来的INSERT ... ON CONFLICT之所以会浪费序列值，是因为PostgreSQL在执行INSERT时会先分配主键序列值，哪怕最后触发冲突走了UPDATE分支，这个序列值已经被消耗掉了。

最优的思路是优先尝试UPDATE，只有当UPDATE没有命中任何行时，再执行INSERT，这样只有真正需要插入新记录的时候才会消耗序列值。下面给你两种可行的实现方式：

1. 用PL/pgSQL存储函数（推荐，适合高并发场景） #

写一个原子性的函数，先执行UPDATE，检查是否有行被修改；如果没有，再执行INSERT。这样能避免竞态条件，同时完全杜绝序列浪费：

CREATE OR REPLACE FUNCTION increment_record(
    p_target_id INT, -- 你的主键字段值
    -- 如果有其他需要初始化的字段，可以在这里添加参数
    p_optional_field TEXT DEFAULT NULL
) RETURNS INT AS $$
BEGIN
    -- 优先执行更新操作
    UPDATE your_table
    SET count_column = count_column + 1 -- 你的增量更新逻辑
    WHERE primary_key_column = p_target_id;

    -- 如果UPDATE没有找到匹配的行，说明需要插入新记录
    IF NOT FOUND THEN
        INSERT INTO your_table (primary_key_column, count_column, optional_field)
        VALUES (p_target_id, 1, p_optional_field) -- 初始化计数为1
        RETURNING primary_key_column INTO p_target_id;
    END IF;

    RETURN p_target_id;
END;
$$ LANGUAGE plpgsql VOLATILE;

调用的时候直接执行：

SELECT increment_record(123, 'some_initial_value');

这个函数的优势是：

• 逻辑原子性强，在高并发场景下不会出现重复插入的问题（因为UPDATE会先锁定行，后续的会话会等待锁释放后再执行，不会同时进入INSERT分支）
• 只有真正插入新记录时才会消耗主键序列（如果你的主键是自增序列的话；如果是业务主键，连序列都不会用到）

2. 纯SQL语句实现（无需函数，适合低并发场景） #

如果不想写函数，可以用CTE先执行UPDATE，再判断是否需要INSERT：

WITH update_result AS (
    UPDATE your_table
    SET count_column = count_column + 1
    WHERE primary_key_column = ? -- 替换为你的主键值
    RETURNING primary_key_column
)
INSERT INTO your_table (primary_key_column, count_column)
SELECT ?, 1 -- 替换为你的主键值和初始计数
WHERE NOT EXISTS (SELECT 1 FROM update_result);

不过要注意：在高并发场景下，可能会出现两个会话同时执行时，都发现update_result为空，然后同时尝试INSERT，这时候会触发唯一约束冲突。如果你的插入操作极少，这种概率很低；如果需要处理这种情况，可以在INSERT后面加ON CONFLICT DO UPDATE兜底：

WITH update_result AS (
    UPDATE your_table
    SET count_column = count_column + 1
    WHERE primary_key_column = ?
    RETURNING primary_key_column
)
INSERT INTO your_table (primary_key_column, count_column)
SELECT ?, 1
WHERE NOT EXISTS (SELECT 1 FROM update_result)
ON CONFLICT(primary_key_column) DO UPDATE SET count_column = your_table.count_column + 1;

这种方式虽然能处理冲突，但极端情况下还是可能消耗少量序列值，但比原来的INSERT ... ON CONFLICT要少得多，因为只有并发插入的时候才会触发。

为什么原来的方式会浪费序列？ #

再补充一下原理：PostgreSQL的序列是预分配并立即消耗的，当执行INSERT语句时，不管最终是否插入成功，序列都会递增一次。而优先UPDATE的方式，只有当确实需要插入新记录时才会触发INSERT，自然就避免了无意义的序列增长。

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