PostgreSQL并发插入场景下汇总值插入限制的高效解法求助

我有如下表结构：

CREATE TABLE user(
    username VARCHAR(20) PRIMARY KEY,
    quantity INT NOT NULL
);

我需要高频访问quantity列的所有值总和，且总和达到10000后禁止所有新的插入操作，但由于user表可能包含数千条记录，无法使用SELECT SUM(quantity) FROM user;的方式实时计算总和。

我设计了如下解决方案：
定义user_sum汇总表：

CREATE TABLE user_sum(
    sum INT
);

定义do_sum()触发器函数：

CREATE FUNCTION do_sum() RETURNS trigger AS
$BODY$
DECLARE sum_result INT;
BEGIN
    SELECT sum INTO sum_result FROM user_sum;
    sum_result = NEW.quantity + sum_result;
    IF sum_result > 10000 THEN
        RAISE EXCEPTION 'LIMIT_REACHED';
    END IF;
    UPDATE user_sum SET sum = sum_result;
    RETURN NEW;
END;
$BODY$
LANGUAGE 'plpgsql';

定义sum_trigger触发器：

CREATE TRIGGER sum_trigger
BEFORE INSERT ON user
FOR EACH ROW 
EXECUTE PROCEDURE do_sum();

该方案在单插入场景下运行正常，但当user表存在多个并发插入操作时会出现问题，请问最高效的解决方式是什么？

现有方案的问题 #

并发插入场景下，多个事务会同时读取到相同的sum值，各自累加新插入的quantity后更新汇总表，会出现更新丢失问题，最终汇总的sum值会小于实际总和，同时阈值校验也会失效，可能总和超过10000仍能插入数据。

最高效优化方案 #

利用PostgreSQL本身的事务原子性和行级锁特性，将sum读取、阈值校验、sum更新三个操作合并为单条原子UPDATE语句，无需额外加锁，性能几乎无损耗，同时完全解决并发冲突问题：

1. 先优化user_sum表结构，增加固定主键约束，保证永远只有1行汇总数据：

CREATE TABLE user_sum(
    id INT PRIMARY KEY DEFAULT 1 CHECK (id = 1),
    sum INT NOT NULL DEFAULT 0
);
-- 初始化汇总数据
INSERT INTO user_sum(sum) VALUES(0);

2. 重写触发器函数：

CREATE OR REPLACE FUNCTION do_sum() RETURNS trigger AS
$BODY$
DECLARE
    update_affected_rows INT;
BEGIN
    -- 单条UPDATE原子完成增量计算和阈值校验，只有符合条件才会更新成功
    UPDATE user_sum
    SET sum = sum + NEW.quantity
    WHERE id = 1 AND sum + NEW.quantity <= 10000;

    -- 读取UPDATE操作影响的行数
    GET DIAGNOSTICS update_affected_rows = ROW_COUNT;

    -- 影响行数为0则说明总和已经超过阈值，禁止插入
    IF update_affected_rows = 0 THEN
        RAISE EXCEPTION 'LIMIT_REACHED';
    END IF;

    RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

3. 原有触发器无需修改，直接复用即可。

方案优势 #

• 完全基于数据库原生特性实现，无额外依赖，稳定性高
• 并发场景下依靠行锁排队执行，不会出现更新丢失，阈值校验100%准确
• 性能和原有方案几乎一致，是当前场景下最高效的解决方案
• 如果后续需要支持user表的UPDATE/DELETE操作，只需要新增对应触发器，按照相同逻辑调整sum值即可。

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