PostgreSQL / 系统课时

自动创建与清理时间分区

自动创建与清理时间分区

时间分区进入长期运行后,主要风险不再是分区语法,而是未来分区漏建或到期分区忘记处理。本课把两件事收敛为可重复执行的维护任务:始终补齐未来两个月分区,并按保留边界安全摘除到期分区。

学习目标

  • 根据当前时间循环创建未来两个月分区,并保证任务可重复执行
  • 按保留策略识别到期分区,并使用 DETACH PARTITION 将其从在线父表摘除
  • 通过分区边界和继承关系验证维护结果,降低误清理风险

把生命周期规则变成两个窗口

假设业务表按月分区,父表为 event_log,分区键是 occurred_at。自动化任务需要维护两个独立窗口:未来分区预创建窗口和在线数据保留窗口。例如每次执行都保证未来两个月的分区已经挂载,同时只让符合最近六个月保留策略的数据继续留在父表查询路径中。

这里还应区分“摘除”和“删除”。生命周期任务优先执行 DETACH PARTITION,使到期分区退出父表查询路径,但仍保留为一张独立表。确认归档、校验和恢复窗口都结束后,再由后续任务决定是否执行 DROP TABLE。

时间分区自动维护生命周期

自动补齐未来两个月分区

先准备一个最小父表和当前月份分区。示例统一使用 UTC 边界;生产环境应让应用写入、分区边界和维护脚本采用一致的时区规则。

sql
CREATE TABLE IF NOT EXISTS event_log (
    id bigint GENERATED ALWAYS AS IDENTITY,
    occurred_at timestamptz NOT NULL,
    payload jsonb NOT NULL
) PARTITION BY RANGE (occurred_at);

CREATE TABLE IF NOT EXISTS event_log_202609
PARTITION OF event_log
FOR VALUES FROM ('2026-09-01 00:00:00+00')
         TO ('2026-10-01 00:00:00+00');

预期结果

text
CREATE TABLE
CREATE TABLE

对象已经存在时,PostgreSQL 可能额外输出 NOTICE,但语句不会重新创建同名对象。真正的维护任务不应把未来月份写死,而要从运行时间计算完整预创建窗口。下面的主示例以 UTC 当前月份为基准,循环确保接下来的两个月分区都存在。

sql
DO $block$
DECLARE
    month_offset integer;
    start_at date;
    end_at date;
    part_name text;
    start_literal text;
    end_literal text;
BEGIN
    FOR month_offset IN 1..2 LOOP
        start_at := (
            date_trunc('month', CURRENT_TIMESTAMP AT TIME ZONE 'UTC')
            + make_interval(months => month_offset)
        )::date;
        end_at := (start_at + INTERVAL '1 month')::date;
        part_name := 'event_log_' || to_char(start_at, 'YYYYMM');

        -- 显式使用 UTC 偏移,避免边界随维护会话时区变化。
        start_literal := to_char(start_at, 'YYYY-MM-DD') || ' 00:00:00+00';
        end_literal := to_char(end_at, 'YYYY-MM-DD') || ' 00:00:00+00';

        EXECUTE format(
            'CREATE TABLE IF NOT EXISTS %I PARTITION OF event_log FOR VALUES FROM (%L) TO (%L)',
            part_name,
            start_literal,
            end_literal
        );
    END LOOP;
END
$block$;

预期结果

text
DO

例如任务在 2026 年 9 月运行时,循环会分别检查并确保 event_log_202610 和 event_log_202611 存在;实际运行月份不同,目标分区名称会随当前月份变化。任务每次都重新计算完整的两个月窗口,因此某次执行失败后可以直接重跑,不依赖上一次执行状态。

安全识别并摘除到期分区

清理动作不能只根据表名推断数据是否到期。至少应先从系统目录确认对象确实挂在 event_log 下,再核对其实际分区边界是否完全早于保留截止时间。下面查询列出父表当前挂载的分区及其边界。

sql
SELECT
    child.relname AS partition_name,
    pg_get_expr(child.relpartbound, child.oid) AS partition_bound
FROM pg_inherits AS i
JOIN pg_class AS parent ON parent.oid = i.inhparent
JOIN pg_class AS child ON child.oid = i.inhrelid
WHERE parent.oid = 'event_log'::regclass
ORDER BY child.relname;

预期结果类似

text
 partition_name   | partition_bound
------------------+---------------------------------------------------------------
 event_log_202609 | FOR VALUES FROM ('2026-09-01 00:00:00+00') TO ('2026-10-01 00:00:00+00')
 event_log_202610 | FOR VALUES FROM ('2026-10-01 00:00:00+00') TO ('2026-11-01 00:00:00+00')
 event_log_202611 | FOR VALUES FROM ('2026-11-01 00:00:00+00') TO ('2026-12-01 00:00:00+00')

实际边界文本格式可能因 PostgreSQL 版本和会话设置略有差异。只有确认某个分区的结束边界已经早于保留截止时间,并且归档、校验等前置条件均满足后,才执行摘除。例如确认 event_log_202609 已到期后:

sql
ALTER TABLE event_log
DETACH PARTITION event_log_202609;

预期结果

text
ALTER TABLE

摘除后,event_log_202609 仍然可以作为独立表查询,但通过父表 event_log 已无法访问它。这样就把“退出在线查询”和“永久删除数据”拆成两个可分别审计、校验和控制的生命周期动作。

验证维护窗口是否完整

任务完成后应再次检查父表的继承关系,确认未来两个月预创建窗口完整,同时已到期分区已经退出父表。可以复用系统目录查询,只保留分区名称,便于自动化程序进行集合校验。

sql
SELECT child.relname AS partition_name
FROM pg_inherits AS i
JOIN pg_class AS parent ON parent.oid = i.inhparent
JOIN pg_class AS child ON child.oid = i.inhrelid
WHERE parent.oid = 'event_log'::regclass
ORDER BY child.relname;

预期结果示例

text
 partition_name
------------------
 event_log_202610
 event_log_202611

若任务在其他月份运行,名称应对应当时计算出的未来两个月。自动维护只有在“未来两个月分区全部存在”和“到期分区按策略退出父表”两个条件同时满足时才算完成;独立表的永久删除应留给具有归档校验、恢复窗口和审批条件的后续阶段。

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

目录与阅读设置

阅读设置

17

记录一个想法

自动创建与清理时间分区

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

登录哪都通