PostgreSQL高负载下补全缺失数据时主键冲突问题求助

碰到过类似的高并发补数+实时写入冲突场景，结合PostgreSQL 9.4的特性，给你几个实用的落地方案，按优先级排序：

1. 补数前先过滤已存在的记录（减少冲突概率） #

在补数的INSERT ... SELECT语句里，通过NOT EXISTS提前排除已经存在的主键记录，从根源上减少冲突的可能。虽然理论上存在“SELECT后到INSERT前被其他操作插入”的竞态窗口，但在高并发场景下能大幅降低冲突频率。示例代码：

INSERT INTO your_partitioned_table (id, date_gmt, col1, col2)
SELECT missing_id, missing_date, val1, val2
FROM your_missing_data_source
WHERE NOT EXISTS (
    SELECT 1 FROM your_partitioned_table
    WHERE id = missing_id AND date_gmt = missing_date
);

如果你的表是按date_gmt做的继承分区（PostgreSQL 9.4仅支持这种分区方式），可以进一步缩小查询范围到对应分区，既提升查询速度，又减少锁的影响：

-- 假设补数的是2024-05-20 10:00这个分钟的数据，对应分区是your_table_2024052010
INSERT INTO your_table_2024052010 (id, date_gmt, col1, col2)
SELECT missing_id, missing_date, val1, val2
FROM your_missing_data_source
WHERE date_gmt = '2024-05-20 10:00:00'
AND NOT EXISTS (
    SELECT 1 FROM your_table_2024052010
    WHERE id = missing_id AND date_gmt = missing_date
);

2. 用PL/pgSQL函数封装原子性检查+插入 #

通过函数把“检查存在性+插入”做成一个原子操作，利用FOR UPDATE锁定可能存在的行（避免竞态），如果行不存在再执行插入。这种方式能彻底避免冲突，缺点是单条处理的效率略低，适合补数数据量不是特别大的场景：

CREATE OR REPLACE FUNCTION fill_missing_record(p_id INT, p_date_gmt TIMESTAMP, p_col1 TEXT, p_col2 INT)
RETURNS VOID AS $$
BEGIN
    -- 尝试锁定目标行（如果已存在的话）
    PERFORM 1 FROM your_partitioned_table
    WHERE id = p_id AND date_gmt = p_date_gmt
    FOR UPDATE;
    
    -- 未找到则插入
    IF NOT FOUND THEN
        INSERT INTO your_partitioned_table (id, date_gmt, col1, col2)
        VALUES (p_id, p_date_gmt, p_col1, p_col2);
    END IF;
END;
$$ LANGUAGE plpgsql;

调用时可以批量处理补数数据，比如用SELECT fill_missing_record(...) FROM your_missing_data_source;

3. 调整补数时机到业务低峰期 #

如果业务允许，把补数任务安排在每日凌晨等低并发时段执行。此时实时写入的请求量大幅降低，补数和实时插入的冲突概率会显著下降，这是最省心的方案之一。

额外注意事项 #

• 因为你用的是PostgreSQL 9.4，不支持9.5才推出的INSERT ... ON CONFLICT DO NOTHING语法，所以没法直接用这个最简便的冲突处理方式。如果后续有升级计划，升级到9.5+后可以直接用该语法解决问题。
• 确保分区表的约束（比如每个分区的date_gmt范围）配置正确，这样PostgreSQL会自动路由查询到对应分区，提升存在性检查的效率，同时减少锁的范围。

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