PostgreSQL / 系统课时

归档与冷热分层

归档与冷热分层

大表归档的关键不是“把旧数据删掉”,而是让历史分区按可查询性、恢复成本和存储价格逐级降温。本节用 PostgreSQL 分区表演示热、温、冷三层策略,并给出可回滚的分区摘除、权限收紧与容量控制方法。

学习目标

  • 能按访问频率与恢复要求划分热、温、冷数据
  • 能安全摘除历史分区并保留独立查询能力
  • 能用权限、表空间和容量指标控制归档成本

先定义热、温、冷三层

热数据仍参与主要业务查询和写入,通常保留在分区父表下并使用性能较好的存储。温数据访问频率下降,但仍希望通过父表透明查询,可以继续挂载为分区,只把对应分区迁移到成本更低的表空间。冷数据通常超过在线查询窗口,应从父表摘除,转入归档 schema、备份介质或独立归档库。

层级 是否挂在父表 典型访问方式 主要目标
热 是 高频读写 延迟优先
温 是 低频只读为主 兼顾查询与成本
冷 否 按需独立查询或恢复 容量与保留周期优先

历史分区从热数据转入温层和冷归档层的生命周期

用 DETACH 做可回滚归档

先摘除、验证,再删除,是历史分区最稳妥的处理顺序。下面的示例创建两个月分区,把 1 月分区从在线父表中摘除并移动到 archive schema。摘除后,父表查询不再扫描它,但归档表仍然保留,可以单独查询,也可以在需要时重新挂载。

sql
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;

预期结果(最后一个查询)

text
 online_rows | archived_rows
-------------+---------------
           1 |             1
(1 row)

如果归档校验失败,可以把表移回原 schema,再使用 ATTACH PARTITION ... FOR VALUES ... 恢复到父表。生产环境处理大分区时,应把摘除操作安排在低峰期,并根据当前 PostgreSQL 版本评估锁等待;不要把“删除旧分区”作为第一步。

把只读与低成本存储分开设计

PostgreSQL 没有一个通用的“把某张普通表永久设为只读”的开关。常见做法是让应用写角色失去归档表的写权限,并确保应用不以表所有者或超级用户身份连接。下面是片段示例,前提是角色 app_writer 已存在。

sql
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 且其底层目录位于目标存储介质。

sql
ALTER TABLE archive.event_log_2026_01
SET TABLESPACE archive_ts;

验证时使用 pg_relation_filepath('archive.event_log_2026_01') 检查关系文件位置,并单独确认相关索引的表空间;迁移表本体时不要假设所有索引都会自动移动到同一存储层。

用容量阈值驱动归档

容量控制最好按“保留窗口 + 空间阈值”双条件执行,而不是只看日期。可以定期统计各分区总占用,找出最老且超过在线保留期的分区,再执行摘除、校验、备份和迁移。

sql
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;

预期结果

text
 schema_name |   partition_name   | bytes   | total_size
-------------+--------------------+---------+------------
 archive     | event_log_2026_01  | <VALUE> | <VALUE>
 public      | event_log_2026_02  | <VALUE> | <VALUE>

bytes 和格式化后的大小会随页面、索引和 PostgreSQL 版本变化,因此这里用 <VALUE> 表示动态值。真正的归档验收至少要确认三件事:在线父表不再包含超期数据、归档副本能够独立读取、备份恢复路径已经演练。只有这三项都成立,删除本地冷分区才是容量治理,而不是不可逆的数据清理。

按自己的节奏,确认真正理解。

目录与阅读设置

阅读设置

17

记录一个想法

归档与冷热分层

0 / 10000 · 笔记保存到账号。未提交的修改会在关闭、离开或刷新后丢失。

登录哪都通