PostgreSQL / 系统课时
记录未分区基线
记录未分区基线
分区改造前先给同一份数据建立可复查的未分区基线。本课记录关系大小、代表性查询计划,以及一次可回滚的历史数据删除成本;之后只有在数据、索引和测试口径保持一致时,才能判断分区到底改善了什么。
学习目标
- 固定未分区表的索引与统计信息,避免比较条件漂移
- 记录关系大小、查询计划和缓冲区访问等关键证据
- 用事务回滚测量历史数据删除成本,同时保持样本不变
固定索引和统计信息
先给常见的“租户 + 时间范围”查询建立复合索引,再执行 ANALYZE。后续如果拿分区表比较,也应提供等价索引,而不是把“分区 + 新索引”与“未分区 + 少索引”混在一起比较。
CREATE INDEX IF NOT EXISTS idx_events_tenant_time
ON partition_lab.events_unpartitioned (tenant_id, occurred_at);
ANALYZE partition_lab.events_unpartitioned;预期结果
CREATE INDEX
ANALYZE如果索引已经存在,第一条命令会返回提示而不是重复创建;这不会改变后续基线。
保存关系体积快照
关系大小受 PostgreSQL 版本、页面填充和具体环境影响,所以不要抄一组固定字节数。把实际值写进基线表,后续直接与分区方案的总堆大小、总索引大小对照。
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;预期结果
row_count | heap_bytes | index_bytes | total_bytes
-----------+------------+-------------+------------
730000 | <VALUE> | <VALUE> | <VALUE>三个大小值应都是正整数,且 total_bytes 不小于 heap_bytes + index_bytes。实际字节数随环境变化,因此用 <VALUE> 表示。
记录代表性查询计划
先测一个租户在 2025 年 6 月的范围查询。固定数据集中每个租户每天有 10 行,因此结果应为 300 行。执行计划的精确成本和耗时会变化,但应重点记录扫描节点、实际行数、Buffers 与使用的索引。
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';预期结果
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 行。
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;预期结果
BEGIN
<PLAN_JSON>
ROLLBACK
rows_after_rollback
---------------------
730000在删除计划的扫描子节点中,应能确认候选行数约为 91000;计划形态、Buffers 和执行时间按实际环境记录。最后总行数必须回到 730000,否则说明测试没有在同一事务中完整回滚。
让基线具备可比性
以后比较分区表时,至少保持数据内容、查询谓词、等价索引、ANALYZE 状态和服务器参数一致。缓存冷热也会显著影响时间,因此同一查询应重复执行并以多次结果判断趋势,而不是用一次偶然的 Execution Time 下结论。
真正有价值的分区收益应能落到证据上:例如只访问相关分区、减少无关页面扫描,或把按季度删除大量行变成快速移除整个分区。若只是总耗时变小但计划、Buffers 和维护路径都无法解释,就还不能证明收益来自分区。