PostgreSQL / 系统课时

记录未分区基线

记录未分区基线

分区改造前先给同一份数据建立可复查的未分区基线。本课记录关系大小、代表性查询计划,以及一次可回滚的历史数据删除成本;之后只有在数据、索引和测试口径保持一致时,才能判断分区到底改善了什么。

学习目标

  • 固定未分区表的索引与统计信息,避免比较条件漂移
  • 记录关系大小、查询计划和缓冲区访问等关键证据
  • 用事务回滚测量历史数据删除成本,同时保持样本不变

固定索引和统计信息

先给常见的“租户 + 时间范围”查询建立复合索引,再执行 ANALYZE。后续如果拿分区表比较,也应提供等价索引,而不是把“分区 + 新索引”与“未分区 + 少索引”混在一起比较。

sql
CREATE INDEX IF NOT EXISTS idx_events_tenant_time
ON partition_lab.events_unpartitioned (tenant_id, occurred_at);

ANALYZE partition_lab.events_unpartitioned;

预期结果

text
CREATE INDEX
ANALYZE

如果索引已经存在,第一条命令会返回提示而不是重复创建;这不会改变后续基线。

保存关系体积快照

关系大小受 PostgreSQL 版本、页面填充和具体环境影响,所以不要抄一组固定字节数。把实际值写进基线表,后续直接与分区方案的总堆大小、总索引大小对照。

sql
DROP TABLE IF EXISTS partition_lab.baseline_snapshot;

CREATE TABLE partition_lab.baseline_snapshot (
    recorded_at timestamptz NOT NULL DEFAULT clock_timestamp(),
    row_count bigint NOT NULL,
    heap_bytes bigint NOT NULL,
    index_bytes bigint NOT NULL,
    total_bytes bigint NOT NULL
);

INSERT INTO partition_lab.baseline_snapshot (
    row_count,
    heap_bytes,
    index_bytes,
    total_bytes
)
SELECT
    count(*),
    pg_relation_size('partition_lab.events_unpartitioned'),
    pg_indexes_size('partition_lab.events_unpartitioned'),
    pg_total_relation_size('partition_lab.events_unpartitioned')
FROM partition_lab.events_unpartitioned;

SELECT
    row_count,
    heap_bytes,
    index_bytes,
    total_bytes
FROM partition_lab.baseline_snapshot;

预期结果

text
 row_count | heap_bytes | index_bytes | total_bytes
-----------+------------+-------------+------------
    730000 | <VALUE>    | <VALUE>     | <VALUE>

三个大小值应都是正整数,且 total_bytes 不小于 heap_bytes + index_bytes。实际字节数随环境变化,因此用 <VALUE> 表示。

记录代表性查询计划

先测一个租户在 2025 年 6 月的范围查询。固定数据集中每个租户每天有 10 行,因此结果应为 300 行。执行计划的精确成本和耗时会变化,但应重点记录扫描节点、实际行数、Buffers 与使用的索引。

sql
SELECT count(*) AS matched_rows
FROM partition_lab.events_unpartitioned
WHERE tenant_id = 42
  AND occurred_at >= TIMESTAMPTZ '2025-06-01 00:00:00+00'
  AND occurred_at <  TIMESTAMPTZ '2025-07-01 00:00:00+00';

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON)
SELECT *
FROM partition_lab.events_unpartitioned
WHERE tenant_id = 42
  AND occurred_at >= TIMESTAMPTZ '2025-06-01 00:00:00+00'
  AND occurred_at <  TIMESTAMPTZ '2025-07-01 00:00:00+00';

预期结果

text
 matched_rows
--------------
          300

<PLAN_JSON>

<PLAN_JSON> 的具体成本、耗时和缓冲区数字会因环境不同而变化;检查计划中是否使用 idx_events_tenant_time,并保存 Actual Rows、Shared Hit Blocks、Shared Read Blocks 与 Execution Time。不要只截取一个总耗时数字。

测量历史删除成本但不改数据

分区经常改善的不是单条查询,而是按时间淘汰历史数据的维护路径。EXPLAIN ANALYZE 会真的执行 DELETE,所以实验必须放进显式事务并最终回滚。这里删除 2024 年第一季度,共 91 天,也就是 91000 行。

sql
BEGIN;

EXPLAIN (ANALYZE, BUFFERS, TIMING OFF, FORMAT JSON)
DELETE FROM partition_lab.events_unpartitioned
WHERE occurred_at < TIMESTAMPTZ '2024-04-01 00:00:00+00';

ROLLBACK;

SELECT count(*) AS rows_after_rollback
FROM partition_lab.events_unpartitioned;

预期结果

text
BEGIN
<PLAN_JSON>
ROLLBACK

 rows_after_rollback
---------------------
              730000

在删除计划的扫描子节点中,应能确认候选行数约为 91000;计划形态、Buffers 和执行时间按实际环境记录。最后总行数必须回到 730000,否则说明测试没有在同一事务中完整回滚。

让基线具备可比性

以后比较分区表时,至少保持数据内容、查询谓词、等价索引、ANALYZE 状态和服务器参数一致。缓存冷热也会显著影响时间,因此同一查询应重复执行并以多次结果判断趋势,而不是用一次偶然的 Execution Time 下结论。

真正有价值的分区收益应能落到证据上:例如只访问相关分区、减少无关页面扫描,或把按季度删除大量行变成快速移除整个分区。若只是总耗时变小但计划、Buffers 和维护路径都无法解释,就还不能证明收益来自分区。

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

目录与阅读设置

记录一个想法

记录未分区基线

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

登录哪都通