English
devexamples
← 中文资源

简体中文版本

把冷数据归档到历史表

以有界批次把旧行迁入历史表，让热表保持小巧、查询计划保持稳定。

阅读英文原文

目标

热事件表无限增长，拖慢工作集并加重 autovacuum 负担。本配方先建同构历史表，用 CTE 一次原子地移动并归档一批数据，再用带 LIMIT 的循环分批删除，避免长时间持锁，同时提醒历史表也需要自己的保留策略，并同步修改报表查询以联合历史表。

步骤要点

  1. 创建与热表同构的历史表。
  2. 用 CTE 一次性移动并归档一批数据。
  3. 用带 LIMIT 的循环分批删除，保持事务短小。
  4. 修改报表查询以联合历史表。
CREATE TABLE IF NOT EXISTS events_history (LIKE events INCLUDING ALL);
WITH moved AS (
  DELETE FROM events WHERE created_at < now() - interval '90 days'
  RETURNING *
)
INSERT INTO events_history SELECT * FROM moved;

排错提示

  • 大数据量下单条 CTE 插入较慢，宜分批。
  • 历史表若无保留策略会无限增长。
  • 外键可能阻止移动数据。

适用边界

  • 分批删除仍产生 WAL 与 vacuum 负载。
  • 需要同步调整下游查询。