PostgreSQL非索引列UPSERT性能低下问题排查与优化咨询

• ON CONFLICT UPDATE子查询冗余：当前UPDATE逻辑中，每次更新都要通过子查询重新扫描CTE并匹配excluded行，单条查询耗时虽低，但每秒150次并发下累计开销被放大。同时子查询中的类型转换（cmp_id::bigint、action::text）增加了额外计算成本。
• 检查约束重复计算：两个检查约束counters_overflow和counters_underflow在每次更新时都需重新计算表达式，其中包含冗余的类型转换（如('-0.01'::numeric)::double precision），高并发下持续占用CPU资源。
• 冲突过滤条件额外开销：ON CONFLICT后的WHERE条件要求PostgreSQL在冲突检测时，除了通过唯一索引定位行，还要额外验证4个列的NULL状态，延长了冲突检查耗时和锁持有时间。
• 并发锁竞争累积：尽管热更新率达98.9%，但每秒150次的并发更新仍会导致行锁等待队列堆积，即使锁持有时间微小延长，在高并发下也会形成明显瓶颈。

1. 简化UPDATE逻辑，消除子查询 #

提前在CTE中转换好所需类型，通过FROM子句关联CTE，避免重复扫描：

WITH data (cmp_id, action, delta, insert_counter) 
AS (
    VALUES
        (42::bigint,'action1',1::float8, GREATEST(0, 1::numeric)),
        (42::bigint,'action2',0.00081991800::float8, GREATEST(0, 0.00081991800::numeric))
) 
INSERT INTO counters (cmp_id, action, counter, limitval)
SELECT
    data.cmp_id::numeric,
    data.action,
    data.insert_counter,
    0
FROM data
ORDER BY cmp_id, action 
ON CONFLICT (cmp_id, action)
WHERE (cou_id IS NULL AND ref_id IS NULL AND pro_id IS NULL AND identifier IS NULL)
DO UPDATE
SET counter = counters.counter + data.delta
FROM data
WHERE data.cmp_id = excluded.cmp_id::bigint
  AND data.action = excluded.action;

2. 优化检查约束，移除冗余转换 #

将约束中的常量表达式提前转换为目标类型，避免每次计算重复转换：

-- 删除原有约束
ALTER TABLE counters DROP CONSTRAINT counters_overflow;
ALTER TABLE counters DROP CONSTRAINT counters_underflow;

-- 创建优化后的约束
ALTER TABLE counters ADD CONSTRAINT counters_overflow 
CHECK ((limitval = 0::double precision) OR (counter <= (limitval + 0.01::double precision)));

ALTER TABLE counters ADD CONSTRAINT counters_underflow 
CHECK (counter >= -0.01::double precision);

3. 优化冲突检查逻辑（业务允许时） #

如果cou_id、ref_id、pro_id、identifier的NULL状态是固定业务规则，可将该条件加入部分唯一索引，减少冲突检查开销：

-- 创建部分唯一索引
CREATE UNIQUE INDEX counters_unique_cmp_id_action_nulls ON counters (cmp_id, action)
WHERE (cou_id IS NULL AND ref_id IS NULL AND pro_id IS NULL AND identifier IS NULL);

-- 修改ON CONFLICT子句使用该索引
ON CONFLICT USING INDEX counters_unique_cmp_id_action_nulls
DO UPDATE
SET counter = counters.counter + data.delta
FROM data
WHERE data.cmp_id = excluded.cmp_id::bigint
  AND data.action = excluded.action;

4. 调整表fillfactor #

降低热更新表的fillfactor，减少页分裂概率：

ALTER TABLE counters SET (fillfactor = 80);
VACUUM FULL counters;

5. 改用“先更新后插入”模式 #

在高并发场景下，该模式有时比UPSERT更高效，避免冲突检查开销：

WITH data (cmp_id, action, delta, insert_counter) 
AS (
    VALUES
        (42::bigint,'action1',1::float8, GREATEST(0, 1::numeric)),
        (42::bigint,'action2',0.00081991800::float8, GREATEST(0, 0.00081991800::numeric))
)
-- 先更新匹配行
UPDATE counters c
SET counter = c.counter + d.delta
FROM data d
WHERE c.cmp_id = d.cmp_id::numeric
  AND c.action = d.action
  AND c.cou_id IS NULL 
  AND c.ref_id IS NULL 
  AND c.pro_id IS NULL 
  AND c.identifier IS NULL;

-- 插入未匹配的行
INSERT INTO counters (cmp_id, action, counter, limitval)
SELECT
    d.cmp_id::numeric,
    d.action,
    d.insert_counter,
    0
FROM data d
WHERE NOT EXISTS (
    SELECT 1 FROM counters c
    WHERE c.cmp_id = d.cmp_id::numeric
      AND c.action = d.action
      AND c.cou_id IS NULL 
      AND c.ref_id IS NULL 
      AND c.pro_id IS NULL 
      AND c.identifier IS NULL
);

注：需在事务中执行，若存在并发插入竞态，可通过FOR UPDATE SKIP LOCKED调整。

6. 缩短锁持有时间 #

确保更新操作是事务的最后一步，移除事务中不必要的操作，尽可能缩短锁的持有时间，减少锁等待堆积。

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