Postgres时序表批量插入场景下已有观测值更新方案咨询

约束适配说明 #

你当前的业务需要保留同一(id, datetime)组合下不同edit_datetime的历史版本，不适合创建(id, datetime)的主键或唯一约束：唯一约束会强制该组合只能存在一条记录，直接违背留存历史修改记录的需求。

可选实现方案 #

根据业务查询习惯可以选两种方案：

方案1：单表存全量历史版本（无结构改动） #

该方案不需要调整现有表结构，仅调整业务逻辑即可：

• 原有插入去重逻辑可以保留：过滤(id, datetime, value)完全重复的冗余记录，避免无意义的重复插入
• 当(id, datetime)存在但value和最新版本不一致时，直接插入新行，数据库会自动生成最新的edit_datetime，旧版本记录完整保留
• 如需查询某个(id, datetime)的最新观测值，用如下语句即可：

SELECT * FROM time_series
WHERE id = $1 AND datetime = $2
ORDER BY edit_datetime DESC
LIMIT 1;

• 性能优化：添加联合索引大幅提升最新值查询速度

CREATE INDEX idx_ts_id_dt_edit ON time_series(id, datetime, edit_datetime DESC);

方案2：主表+历史表分存（适合高频查最新值场景） #

如果业务绝大多数场景都是查询最新观测值，不需要每次拉取历史，可以拆分双表优化性能：

1. 新建主表time_series_latest，结构和原表一致，设置(id, datetime)为主键，仅存每个观测点的最新值
2. 原表改名time_series_history，保留全量历史版本，无唯一约束
3. 导入逻辑调整为两步：
   • 第一步：将临时表所有数据写入历史表，不需要去重
   • 第二步：用Postgres原生UPSERT能力更新主表最新值

INSERT INTO time_series_latest(id, datetime, value, edit_datetime)
SELECT id, datetime, value, now() FROM temp_time_series
ON CONFLICT (id, datetime) 
DO UPDATE SET 
  value = EXCLUDED.value,
  edit_datetime = EXCLUDED.edit_datetime;

该方案最新值查询直接走主键索引，性能远高于单表排序查询，历史数据按需从历史表拉取即可。

原有逻辑修改建议 #

• 如果选择方案1，仅需要保留原有NOT EXISTS判断逻辑即可，自动跳过完全重复的记录，有value变更的新记录会自动插入生成新版本
• 如果选择方案2，原有去重逻辑可以删除，历史表允许写入重复数据，主表的UPSERT会自动处理最新值更新

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