PostgreSQL / 系统课时

选择生命周期分区键

选择生命周期分区键

分区键决定数据如何进入分区,也直接影响查询能否裁剪无关数据、历史数据能否按边界快速归档。本课围绕创建时间、业务时间和租户维度建立选择方法,并用 PostgreSQL 分区表验证时间条件是否真正触发分区裁剪。

学习目标

  • 区分创建时间、业务时间和租户维度作为分区键时的治理含义
  • 根据查询条件与归档边界选择生命周期主分区键
  • 使用执行计划验证查询是否能够完成分区裁剪

从生命周期动作反推分区键

大表归档最重要的不是“哪个字段查询最多”,而是哪个字段能够稳定定义数据何时进入下一生命周期阶段。如果归档策略是“创建满 180 天的数据转冷”,created_at 通常最直接;如果规则是“业务发生满 3 年后归档”,订单完成时间、事件发生时间等业务时间更合适。

租户 ID 解决的是另一类问题:数据是否需要按租户独立删除、迁移或隔离。它很少能单独表达“数据变旧”,因此生命周期治理通常仍需要时间维度。

创建时间、业务时间与租户维度的分区键选择关系

比较三类候选键

created_at 的优点是值通常写入后不再改变,分区归属稳定,而且“按创建日期淘汰历史数据”容易与月分区或日分区边界对齐。缺点是业务查询若只按事件发生时间过滤,未必能够利用该分区键裁剪。

业务时间例如 occurred_at、completed_at 更贴近事件事实和法规保留期限,但必须确认其语义稳定。如果业务允许补录数年前的数据,或者该时间会被修正,新写入就可能落入旧分区,使分区创建、归档和维护窗口更难预测。

tenant_id 适合租户级隔离、迁移或删除,但如果查询和归档主要按时间运行,仅按租户分区会导致每个租户分区持续增长。对于“租户 + 时间”都重要的系统,应先确定哪个维度承担顶层治理边界,再评估是否需要子分区,而不是机械地把两个字段拼成分区键。

用时间范围建立分区边界

下面用 occurred_at 作为生命周期字段建立月度 RANGE 分区。示例覆盖 2026 年 1 月和 2 月,并插入两条分别落入不同月份的数据。

sql
CREATE TABLE event_history (
    id bigint NOT NULL,
    tenant_id bigint NOT NULL,
    occurred_at timestamptz NOT NULL,
    payload jsonb NOT NULL
) PARTITION BY RANGE (occurred_at);

CREATE TABLE event_history_2026_01
PARTITION OF event_history
FOR VALUES FROM ('2026-01-01 00:00:00+00')
         TO ('2026-02-01 00:00:00+00');

CREATE TABLE event_history_2026_02
PARTITION OF event_history
FOR VALUES FROM ('2026-02-01 00:00:00+00')
         TO ('2026-03-01 00:00:00+00');

INSERT INTO event_history VALUES
    (1, 10, '2026-01-15 12:00:00+00', '{"type":"login"}'),
    (2, 10, '2026-02-10 09:00:00+00', '{"type":"purchase"}');

SELECT tableoid::regclass AS partition_name, id, occurred_at
FROM event_history
ORDER BY id;

预期结果

text
      partition_name       | id |      occurred_at
---------------------------+----+------------------------
 event_history_2026_01     |  1 | 2026-01-15 12:00:00+00
 event_history_2026_02     |  2 | 2026-02-10 09:00:00+00

tableoid 可以直接观察每行实际存放在哪个物理分区。时间显示格式可能受会话时区和客户端设置影响,但两行应分别属于对应月份分区。

验证查询条件能否裁剪分区

确定分区键以后,要拿真实查询谓词验证,而不能只看表定义。下面查询只覆盖 2026 年 2 月,执行计划应排除 1 月分区。

sql
EXPLAIN (COSTS OFF)
SELECT id, payload
FROM event_history
WHERE occurred_at >= '2026-02-01 00:00:00+00'
  AND occurred_at <  '2026-03-01 00:00:00+00';

预期结果

text
Seq Scan on event_history_2026_02 event_history
  Filter: ((occurred_at >= '2026-02-01 00:00:00+00'::timestamp with time zone) AND (occurred_at < '2026-03-01 00:00:00+00'::timestamp with time zone))

具体计划文本、别名和时间常量显示形式可能随 PostgreSQL 版本及环境不同,但计划中应只访问 event_history_2026_02。这里即使出现 Seq Scan 也不代表分区设计失败:分区裁剪解决的是“访问哪些分区”,分区内部是否使用索引是另一层决策。

如果主要查询只带 tenant_id 而不带 occurred_at,上述时间分区就无法依靠租户条件裁剪月份。反过来,如果归档任务明确按 occurred_at 处理整月历史数据,这个键就能让查询与生命周期维护共享同一边界。

选择生命周期分区键时,优先寻找语义稳定、能够直接定义冷热或保留期限,并且真实高频查询会携带的字段。创建时间和业务时间都满足条件时,以归档规则实际使用的时间语义为主;租户维度只有在独立治理本身就是核心需求时,才应提升为主要分区维度。

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

目录与阅读设置

阅读设置

17

记录一个想法

选择生命周期分区键

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

登录哪都通