每日Upsert60万条房产listing数据并跟踪历史变更的最优方案咨询

问题1：简化Upsert全字段更新逻辑 #

你可以通过以下3个方案简化冗余的ON CONFLICT和UPDATE赋值逻辑：

• 先优化唯一约束设计：如果id字段本身已经是主表的主键/唯一键，无需在ON CONFLICT子句中额外追加prop_type等字段，仅保留ON CONFLICT (id)即可大幅缩短冲突判断逻辑。
• 用元组批量赋值简化更新语句：PostgreSQL、MySQL 8.0+等主流数据库都支持元组赋值语法，不需要逐字段写赋值逻辑，示例如下：

INSERT INTO prop_listings (id, prop_type, price, rooms)
VALUES
  (33, 'house', 60000, 4),
  (22, 'apartment', 30000, 2)
ON CONFLICT (id)
DO UPDATE SET
  (prop_type, price, rooms) = (EXCLUDED.prop_type, EXCLUDED.price, EXCLUDED.rooms);

字段较多时可以直接从表结构元数据批量生成这段赋值语句，无需手动编写。

• 临时表分步骤更新：先把每日60万条增量数据写入临时表，先执行UPDATE关联临时表更新主表的旧数据，再执行INSERT写入主表不存在的新数据，逻辑比单条UPSERT更灵活，适合字段数量极多的场景。

问题2：变更历史存储方案选型 #

触发器方案适用场景 #

触发器是绝大多数中小规模场景下的最优解，优势非常明确：

• 对上层业务完全透明，不需要修改任何写入侧的业务代码
• 数据一致性有保障，变更和历史写入在同一个事务内，不会出现数据差
• 实现成本极低，只需要写一次触发器逻辑，把OLD行整行写入历史表即可，示例如下：

-- 先创建历史表，比主表多两个字段记录变更信息
CREATE TABLE prop_listings_history (
  change_time timestamptz default now(),
  change_type text,
  LIKE prop_listings INCLUDING ALL
);

-- 创建触发器函数
CREATE OR REPLACE FUNCTION log_prop_listing_change()
RETURNS TRIGGER AS $$
BEGIN
  IF TG_OP = 'UPDATE' THEN
    INSERT INTO prop_listings_history (change_type, id, prop_type, price, rooms)
    VALUES ('UPDATE', OLD.id, OLD.prop_type, OLD.price, OLD.rooms);
  ELSIF TG_OP = 'DELETE' THEN
    INSERT INTO prop_listings_history (change_type, id, prop_type, price, rooms)
    VALUES ('DELETE', OLD.id, OLD.prop_type, OLD.price, OLD.rooms);
  END IF;
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 绑定触发器到主表
CREATE TRIGGER trigger_prop_listing_change
AFTER UPDATE OR DELETE ON prop_listings
FOR EACH ROW EXECUTE FUNCTION log_prop_listing_change();

其他可选方案 #

如果你的写入量级持续增长、对主库性能损耗要求极高，可以考虑另外两个方案：

• 数据库日志解析：通过解析WAL（PostgreSQL）/Binlog（MySQL）日志捕获数据变更，异步写入历史表，完全不占用主库的事务开销，但需要额外部署日志解析服务，运维成本更高。
• 数据库内置版本功能：PostgreSQL可使用pg_temporal扩展、MySQL可使用系统版本表，直接由数据库内核自动维护数据历史版本，不需要手动建触发器和历史表，是最省心的方案，前提是你的数据库版本支持对应特性。

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