PostgreSQL / 系统课时

设计归档库查询结构

设计归档库查询结构

归档库的目标不是复刻在线库,而是在更低成本下稳定支撑历史查询。本课从真实查询模式反推归档表的分区、索引与存储策略,并通过执行计划和系统函数验证结构是否真正匹配历史数据访问方式。

学习目标

  • 根据历史查询条件决定归档表的分区键和索引,而不是复制在线库全部索引
  • 区分时间范围扫描、单对象追溯和统计查询对结构设计的不同要求
  • 使用 EXPLAIN 与系统函数验证分区裁剪、索引路径和存储规模

先从历史查询反推结构

归档数据通常写少读少,但单次查询覆盖的数据跨度可能远大于在线表。因此,在线库为高频写入和实时点查设计的索引,不一定值得原样保留到归档库。

先整理真实历史查询的主要过滤条件。例如审计记录可能主要存在三种模式:按时间范围导出、按 tenant_id 和时间追溯、按单个 event_id 定位。结构设计应优先服务这些稳定模式。

假设归档表保存事件历史,可以按时间范围分区,并仅保留仍有明确查询收益的索引:

sql
CREATE TABLE event_archive (
    event_id bigint NOT NULL,
    tenant_id bigint NOT NULL,
    occurred_at timestamptz NOT NULL,
    event_type text NOT NULL,
    payload jsonb NOT NULL
) PARTITION BY RANGE (occurred_at);

CREATE TABLE event_archive_2025_q1
PARTITION OF event_archive
FOR VALUES FROM ('2025-01-01 00:00:00+00')
         TO ('2025-04-01 00:00:00+00');

CREATE INDEX event_archive_2025_q1_tenant_time_idx
ON event_archive_2025_q1 (tenant_id, occurred_at);

CREATE INDEX event_archive_2025_q1_event_id_idx
ON event_archive_2025_q1 (event_id);

预期结果

text
CREATE TABLE
CREATE TABLE
CREATE INDEX
CREATE INDEX

这里没有机械复制在线表中所有针对实时业务的索引。归档库只为仍会发生的查询承担索引空间和批量装载时的维护成本。

用分区边界减少历史扫描

时间通常是归档场景最稳定的访问维度。若查询总是限定历史区间,以 occurred_at 做范围分区,可以让 PostgreSQL 排除边界不相交的分区,减少需要访问的数据范围。

sql
EXPLAIN (COSTS OFF)
SELECT event_id, occurred_at, event_type
FROM event_archive
WHERE occurred_at >= TIMESTAMPTZ '2025-02-01 00:00:00+00'
  AND occurred_at <  TIMESTAMPTZ '2025-03-01 00:00:00+00';

预期结果

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

这个示例只创建了一个季度分区,因此计划中只能看到 event_archive_2025_q1。实际存在多个时间分区时,应重点确认计划只访问与查询区间重叠的分区;至于分区内部使用顺序扫描还是索引扫描,则取决于数据量、选择性和统计信息,不能把 Seq Scan 本身视为失败。

归档查询模式到分区与索引的设计关系

索引只保留有历史收益的部分

对于 tenant_id 加时间范围的追溯查询,复合 B-tree 索引通常比两个彼此独立的索引更直接,因为它可以先定位租户,再在该租户的索引范围内扫描时间区间。按 event_id 的单点定位则由单列索引承担,两种路径分别对应不同的历史访问模式。

sql
EXPLAIN (ANALYZE, BUFFERS)
SELECT event_id, occurred_at, event_type
FROM event_archive
WHERE tenant_id = 42
  AND occurred_at >= TIMESTAMPTZ '2025-01-01 00:00:00+00'
  AND occurred_at <  TIMESTAMPTZ '2025-02-01 00:00:00+00';

预期结果

text
<PLAN_NODE> on event_archive_2025_q1
  Index Cond: ((tenant_id = 42) AND (occurred_at >= <TIME>) AND (occurred_at < <TIME>))
  Buffers: <VALUE>
Planning Time: <TIME> ms
Execution Time: <TIME> ms

实际计划节点可能是 Index Scan、Bitmap Heap Scan,也可能在低选择性条件下使用 Seq Scan。其中时间、缓冲区统计和具体节点会随数据分布变化。判断索引是否值得保留,应结合执行频率、实际扫描页数、缓冲区访问和响应时间,而不是只检查“是否命中索引”。

压缩策略要和查询代价一起评估

PostgreSQL 会通过 TOAST 处理较大的变长字段,并可对可压缩值进行压缩,但“归档”并不意味着只追求最高压缩率。若历史查询频繁读取完整 payload,数据读取、解压和 I/O 代价仍应纳入评估。

可以先量化表主体、索引以及 TOAST 等附属存储合计后的实际体积:

sql
SELECT
    pg_size_pretty(pg_relation_size('event_archive_2025_q1')) AS heap_size,
    pg_size_pretty(pg_indexes_size('event_archive_2025_q1')) AS index_size,
    pg_size_pretty(pg_total_relation_size('event_archive_2025_q1')) AS total_size;

预期结果

text
 heap_size | index_size | total_size
-----------+------------+-----------
 <VALUE>   | <VALUE>    | <VALUE>

pg_relation_size 主要反映表主体关系本身的大小,pg_indexes_size 汇总该表索引,而 pg_total_relation_size 会把表、索引以及 TOAST 等附属关系计算在内。如果历史查询很少访问大字段,应优先只读取必要列;若数据极少访问且容量压力显著,再评估更激进的压缩或外部、分层归档方案。

用查询成本决定是否继续精简

一个实用的归档查询结构通常满足三个条件:时间范围能够有效裁剪分区,核心追溯条件存在明确且有收益的索引路径,表与索引体积和真实访问频率相称。在线库结构只能作为起点,最终应以归档数据自己的查询模式、执行计划和空间成本为依据。

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

目录与阅读设置

阅读设置

17

记录一个想法

设计归档库查询结构

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

登录哪都通