PostgreSQL / 系统课时
选择历史数据归档目标
选择历史数据归档目标
归档不是简单地把旧数据换个表存放,而是在查询能力、存储成本、恢复速度和运维复杂度之间做取舍。本课建立一套选择方法,并用实际数据规模与访问需求判断应落到同库归档表、独立 PostgreSQL、对象存储还是外部分析系统。
学习目标
- 区分四类常见归档目标的能力边界与主要成本
- 根据历史数据访问方式、恢复要求和容量规模选择目标
- 在迁移前用 SQL 估算待归档数据量,避免只凭表总大小决策
先明确归档后的访问方式
选择目标前,先回答三个问题:归档数据还会不会被在线业务查询,是否要求用 SQL 随时检索,以及发生审计、客诉或数据恢复时允许等待多久。目标系统必须首先满足这些使用方式,再比较成本。
同库归档表仍属于当前 PostgreSQL 实例,应用改造最小,事务和 SQL 能力完整,但它并没有消除实例级容量、备份时间和维护窗口压力。它更适合数据量尚可控、历史记录仍经常被在线查询的场景。
独立 PostgreSQL 集群将历史数据从主业务集群中隔离,可以继续保留熟悉的 SQL、索引和权限体系,同时减少生产实例的存储和维护负担。代价是需要额外的数据库集群、备份、监控和连接管理,并处理跨库查询问题。
对象存储更适合低频访问、规模大、允许分钟级甚至更慢恢复的数据。CSV、Parquet 等文件可以显著降低长期存储成本,但它不是 PostgreSQL 的透明替代品:事务、索引和低延迟点查能力都需要放弃或由其他查询引擎补充。
外部分析系统适合归档数据仍需持续参与大范围聚合、报表和历史趋势分析的场景。它通常能把分析负载从 PostgreSQL 卸载出去,但数据模型、同步链路和一致性语义会变得更复杂。
用访问要求筛选目标
可以先用下表排除明显不合适的方案,再考虑具体产品和成本。
| 需求 | 同库归档表 | 独立 PostgreSQL | 对象存储 | 外部分析系统 |
|---|---|---|---|---|
| 保留完整 PostgreSQL SQL 能力 | 强 | 强 | 弱 | 取决于系统 |
| 在线低延迟点查 | 强 | 较强 | 弱 | 通常不是首选 |
| 降低主库容量压力 | 弱 | 强 | 强 | 强 |
| 长期低成本保存 | 一般 | 一般 | 强 | 一般 |
| 大范围历史分析 | 一般 | 一般 | 需配合查询引擎 | 强 |
| 运维改造复杂度 | 低 | 中 | 中 | 中到高 |
如果业务要求“归档后查询方式基本不变”,优先从同库归档表和独立 PostgreSQL 中选择。如果需求只是合规保存与偶发恢复,对象存储通常更合理。如果历史数据仍有大量扫描和聚合,应考虑外部分析系统,而不是继续让 OLTP 主库承担分析负载。
先测待归档数据而不是表总大小
归档决策需要知道真正满足归档条件的数据比例。假设订单表按 created_at 判断历史数据,可以先统计行数和时间范围,不要直接执行删除或迁移。
SELECT
count(*) AS archive_rows,
min(created_at) AS oldest_row,
max(created_at) AS newest_archive_row
FROM orders
WHERE created_at < DATE '2025-01-01';预期结果
archive_rows | oldest_row | newest_archive_row
--------------+-----------------------+-----------------------
<VALUE> | <TIME> | <TIME><VALUE> 和 <TIME> 会随实际数据变化。这里的目标是确认候选行规模和时间边界,而不是获得固定数值。对于大表,直接 count(*) 可能产生明显 I/O,应在可接受的维护窗口执行,或先通过分区元数据、抽样和已有统计信息做初筛。
还应同时查看主表和索引总体积,判断归档后是否真的能释放值得迁移的数据规模。
SELECT
pg_size_pretty(pg_relation_size('orders')) AS heap_size,
pg_size_pretty(pg_indexes_size('orders')) AS index_size,
pg_size_pretty(pg_total_relation_size('orders')) AS total_size;预期结果
heap_size | index_size | total_size
-----------+------------+-----------
<VALUE> | <VALUE> | <VALUE>这些数字是整张表的物理占用,并不等于可归档部分的空间。尤其是普通非分区表,删除历史行后空间通常先变为 PostgreSQL 可复用空间,并不会立即归还文件系统,因此不能把“归档多少行”直接换算成“磁盘立刻减少多少”。
把恢复路径纳入设计
归档目标不能只看写出去是否成功,还要提前规定如何找回来。同库表可以直接查询或回写;独立 PostgreSQL 可以通过应用双数据源、FDW 或受控导回来访问;对象存储通常需要先定位文件,再通过批处理或查询引擎读取;分析系统则要确认是否允许将结果重新导回事务库。
最终选择可以归结为一个原则:仍需频繁事务型访问的数据留在 PostgreSQL 体系内;低频、长期保存的数据优先转向低成本存储;持续进行大范围分析的数据进入面向分析的系统。先确定访问与恢复要求,再讨论介质价格,通常能避免把归档做成另一个难以使用的数据孤岛。