PostgreSQL / 系统课时
归档与冷热分层
归档与冷热分层
大表归档的关键不是“把旧数据删掉”,而是让历史分区按可查询性、恢复成本和存储价格逐级降温。本节用 PostgreSQL 分区表演示热、温、冷三层策略,并给出可回滚的分区摘除、权限收紧与容量控制方法。
学习目标
- 能按访问频率与恢复要求划分热、温、冷数据
- 能安全摘除历史分区并保留独立查询能力
- 能用权限、表空间和容量指标控制归档成本
先定义热、温、冷三层
热数据仍参与主要业务查询和写入,通常保留在分区父表下并使用性能较好的存储。温数据访问频率下降,但仍希望通过父表透明查询,可以继续挂载为分区,只把对应分区迁移到成本更低的表空间。冷数据通常超过在线查询窗口,应从父表摘除,转入归档 schema、备份介质或独立归档库。
| 层级 | 是否挂在父表 | 典型访问方式 | 主要目标 |
|---|---|---|---|
| 热 | 是 | 高频读写 | 延迟优先 |
| 温 | 是 | 低频只读为主 | 兼顾查询与成本 |
| 冷 | 否 | 按需独立查询或恢复 | 容量与保留周期优先 |

用 DETACH 做可回滚归档
先摘除、验证,再删除,是历史分区最稳妥的处理顺序。下面的示例创建两个月分区,把 1 月分区从在线父表中摘除并移动到 archive schema。摘除后,父表查询不再扫描它,但归档表仍然保留,可以单独查询,也可以在需要时重新挂载。
DROP TABLE IF EXISTS event_log CASCADE;
DROP SCHEMA IF EXISTS archive CASCADE;
CREATE SCHEMA archive;
CREATE TABLE event_log (
id bigint GENERATED ALWAYS AS IDENTITY,
created_at date NOT NULL,
payload text NOT NULL
) PARTITION BY RANGE (created_at);
CREATE TABLE event_log_2026_01
PARTITION OF event_log
FOR VALUES FROM ('2026-01-01') TO ('2026-02-01');
CREATE TABLE event_log_2026_02
PARTITION OF event_log
FOR VALUES FROM ('2026-02-01') TO ('2026-03-01');
INSERT INTO event_log (created_at, payload)
VALUES
('2026-01-15', 'cold candidate'),
('2026-02-15', 'online row');
ALTER TABLE event_log
DETACH PARTITION event_log_2026_01;
ALTER TABLE event_log_2026_01
SET SCHEMA archive;
SELECT
(SELECT count(*) FROM event_log) AS online_rows,
(SELECT count(*) FROM archive.event_log_2026_01) AS archived_rows;预期结果(最后一个查询)
online_rows | archived_rows
-------------+---------------
1 | 1
(1 row)如果归档校验失败,可以把表移回原 schema,再使用 ATTACH PARTITION ... FOR VALUES ... 恢复到父表。生产环境处理大分区时,应把摘除操作安排在低峰期,并根据当前 PostgreSQL 版本评估锁等待;不要把“删除旧分区”作为第一步。
把只读与低成本存储分开设计
PostgreSQL 没有一个通用的“把某张普通表永久设为只读”的开关。常见做法是让应用写角色失去归档表的写权限,并确保应用不以表所有者或超级用户身份连接。下面是片段示例,前提是角色 app_writer 已存在。
REVOKE INSERT, UPDATE, DELETE, TRUNCATE
ON archive.event_log_2026_01
FROM app_writer;
GRANT SELECT
ON archive.event_log_2026_01
TO app_writer;生效行为:app_writer 仍可读取归档表,但不能通过常规 DML 修改它;表所有者和超级用户不受这组权限的同等约束,因此生产环境还要配合账号隔离和变更流程。
如果服务器配置了归档表空间,可以进一步迁移数据文件。下面同样是片段示例,依赖管理员已创建 archive_ts 且其底层目录位于目标存储介质。
ALTER TABLE archive.event_log_2026_01
SET TABLESPACE archive_ts;验证时使用 pg_relation_filepath('archive.event_log_2026_01') 检查关系文件位置,并单独确认相关索引的表空间;迁移表本体时不要假设所有索引都会自动移动到同一存储层。
用容量阈值驱动归档
容量控制最好按“保留窗口 + 空间阈值”双条件执行,而不是只看日期。可以定期统计各分区总占用,找出最老且超过在线保留期的分区,再执行摘除、校验、备份和迁移。
SELECT
n.nspname AS schema_name,
c.relname AS partition_name,
pg_total_relation_size(c.oid) AS bytes,
pg_size_pretty(pg_total_relation_size(c.oid)) AS total_size
FROM pg_class AS c
JOIN pg_namespace AS n
ON n.oid = c.relnamespace
WHERE c.relkind = 'r'
AND c.relname LIKE 'event_log_2026_%'
ORDER BY c.relname;预期结果
schema_name | partition_name | bytes | total_size
-------------+--------------------+---------+------------
archive | event_log_2026_01 | <VALUE> | <VALUE>
public | event_log_2026_02 | <VALUE> | <VALUE>bytes 和格式化后的大小会随页面、索引和 PostgreSQL 版本变化,因此这里用 <VALUE> 表示动态值。真正的归档验收至少要确认三件事:在线父表不再包含超期数据、归档副本能够独立读取、备份恢复路径已经演练。只有这三项都成立,删除本地冷分区才是容量治理,而不是不可逆的数据清理。