PostgreSQL / 系统课时
分析历史数据访问热度
分析历史数据访问热度
历史数据是否应该归档,不能只按“超过多少天”机械决定。本课把业务时间、查询统计和实际 SQL 谓词放到同一套分析框架中,识别热、温、冷数据,并找出仍被报表、审计或批处理访问的历史区间,形成可复核的在线保留边界。
学习目标
- 按业务时间维度统计不同历史区间的数据规模
- 从查询统计和 SQL 时间谓词判断历史数据是否仍被读取
- 将数据年龄、访问证据和业务约束组合成归档决策
先按业务时间切分历史区间
归档判断应优先使用能够表达数据生命周期的业务时间列,例如事件发生时间 occurred_at、订单完成时间或日志生成时间,而不是单纯依据物理写入顺序。
下面把 event_history 按年龄分成四个区间,并统计每个区间的行数:
SELECT
CASE
WHEN occurred_at >= now() - interval '7 days' THEN '0-7d'
WHEN occurred_at >= now() - interval '30 days' THEN '8-30d'
WHEN occurred_at >= now() - interval '90 days' THEN '31-90d'
ELSE '90d+'
END AS age_bucket,
count(*) AS row_count
FROM public.event_history
GROUP BY 1
ORDER BY min(occurred_at) DESC;预期结果
age_bucket | row_count
------------+----------
0-7d | <VALUE>
8-30d | <VALUE>
31-90d | <VALUE>
90d+ | <VALUE>这一步只回答“数据有多老、每段有多少”,还不能回答“能不能归档”。例如 90 天以前的数据可能占据大部分空间,但月底报表仍会周期性读取其中一部分。
从查询统计寻找真实访问证据
如果实例已经启用 pg_stat_statements,可以先找出经常访问目标表的查询模式。它保存规范化语句及累计执行统计,不会直接告诉你某次执行使用了哪个具体日期,因此适合回答“哪些 SQL 在读这张表、调用和耗时是否显著”,随后还要检查这些 SQL 的时间谓词。
SELECT
calls,
rows,
round(total_exec_time::numeric, 2) AS total_exec_ms,
round(mean_exec_time::numeric, 2) AS mean_exec_ms,
query
FROM pg_stat_statements
WHERE query ILIKE '%event_history%'
ORDER BY total_exec_time DESC
LIMIT 10;预期结果
calls | rows | total_exec_ms | mean_exec_ms | query
---------+---------+---------------+--------------+------------------------------
<VALUE> | <VALUE> | <VALUE> | <VALUE> | <normalized SQL>
... | ... | ... | ... | ...这些统计是累计值,观察窗口会受统计重置影响。若当前版本提供 pg_stat_statements_info,可以查询其中的 stats_reset 判断累计区间;否则应由监控系统记录采样时间并计算相邻采样差值,避免把很久以前的高频查询误判为当前仍然活跃。
把查询谓词映射到历史区间
找到高频 SQL 后,重点检查它们如何限制生命周期时间列。稳定只读取近期数据的接口,与允许用户任意查询多年历史的审计报表,对归档边界的影响完全不同。
例如下面的查询明确把访问范围限制在最近 30 天,可以通过执行计划确认实际扫描对象和访问路径:
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, event_type, occurred_at
FROM public.event_history
WHERE occurred_at >= now() - interval '30 days'
AND event_type = 'payment_failed';预期结果
<QUERY PLAN>
Planning Time: <VALUE> ms
Execution Time: <VALUE> ms具体扫描节点、成本和缓冲区数量取决于数据分布与索引设计。这里的重点不是得到某一种固定执行计划,而是确认真实业务查询是否存在明确的时间边界。如果大量查询没有 occurred_at 条件,或者报表经常传入数月前的时间范围,就不能仅凭数据年龄判断这些记录已经变冷。
合并三类证据判断冷热
最终决策需要同时观察数据年龄、访问证据和业务约束。三类信息共同决定某个历史区间应继续在线、进入低频层,还是成为归档候选。

近期持续被在线事务和接口读取的数据属于热数据;读取频率较低,但月底报表、审计或补数任务仍依赖的数据属于温数据;超过业务在线保留窗口,并且在足够长观察期内没有发现有效访问的数据,才适合作为优先归档候选。
不能把“没有观测到访问”等同于“不存在访问”。归档前还要核对法规保留、审计查询、灾备恢复、离线补数等不一定出现在日常 SQL 统计中的业务约束。
为访问热度建立可复核记录
一次人工检查很容易受统计窗口和偶发流量影响。更可靠的做法是保存每个历史区间的评估结果,至少记录区间边界、最近确认访问时间、访问来源、冷热等级和评估时间,并按固定周期重新评估。
CREATE TABLE IF NOT EXISTS ops_history_access_profile (
relation_name text NOT NULL,
range_start timestamptz,
range_end timestamptz NOT NULL,
last_observed_access timestamptz,
access_source text,
heat_level text NOT NULL CHECK (heat_level IN ('hot', 'warm', 'cold')),
evaluated_at timestamptz NOT NULL DEFAULT now()
);
INSERT INTO ops_history_access_profile (
relation_name,
range_start,
range_end,
last_observed_access,
access_source,
heat_level
)
VALUES (
'event_history',
NULL,
now() - interval '90 days',
NULL,
'query-log-review',
'cold'
);预期结果
CREATE TABLE
INSERT 0 1这个示例只展示记录结构。实际标记为 cold 前,必须完成足够长时间的查询观察和业务依赖核对;last_observed_access 为 NULL 只能表示当前观察窗口内没有记录到访问,不能证明历史上从未访问。
归档候选应同时满足“足够老”和“足够冷”,并且不违反审计、报表、补数等业务要求。只按时间切分可能误归档仍有价值的数据,只看访问次数又可能让极少使用的数据长期占据在线存储;把年龄、真实访问证据和业务约束合并,才能得到可执行的生命周期边界。