PostgreSQL / 系统课时
建立分区实验数据集
建立分区实验数据集
这一课先固定一个可重复装载的事件表样本:时间范围、每日行数、租户分布和字段取值都由公式生成。这样后续无论比较分区裁剪、索引还是历史数据维护,都能把差异归因到表设计,而不是随机数据变化。
学习目标
- 创建可重复生成的未分区实验表和 73 万行样本数据
- 固定候选分区键
occurred_at的时间边界与每日数据量 - 用可核对的行数和日期分布确认实验数据没有装偏
固定实验口径
实验表模拟按时间持续增长的事件流水。数据覆盖 730 个自然日,每天固定 1000 行,共 730000 行;tenant_id 在 1~100 之间均匀循环,其他业务字段也只由行号推导,不使用 random()。这种规则分布不是为了模拟真实流量,而是为了让每次重建后都得到同一份样本。
候选分区键使用 timestamptz 类型的 occurred_at。脚本先把会话时区设为 UTC,避免同一条时间边界在不同客户端上被解释成不同日期。
创建并装载样本
下面是一段可直接执行的完整 SQL。主键把时间列放在第一位,既能唯一标识样本,也保留按时间范围访问的基础索引条件。
sql
SET TIME ZONE 'UTC';
DROP SCHEMA IF EXISTS partition_lab CASCADE;
CREATE SCHEMA partition_lab;
CREATE TABLE partition_lab.events_unpartitioned (
id bigint NOT NULL,
occurred_at timestamptz NOT NULL,
tenant_id integer NOT NULL,
event_type text NOT NULL,
amount_cents integer NOT NULL,
payload jsonb NOT NULL,
PRIMARY KEY (occurred_at, id)
);
INSERT INTO partition_lab.events_unpartitioned (
id,
occurred_at,
tenant_id,
event_type,
amount_cents,
payload
)
SELECT
g,
TIMESTAMPTZ '2024-01-01 00:00:00+00'
+ ((g - 1) / 1000) * INTERVAL '1 day'
+ ((g - 1) % 1000) * INTERVAL '1 second',
((g - 1) % 100) + 1,
(ARRAY['view', 'click', 'purchase', 'refund'])[((g - 1) % 4) + 1],
((g - 1) % 50000) + 100,
jsonb_build_object(
'source', (ARRAY['web', 'api', 'batch'])[((g - 1) % 3) + 1],
'bucket', (g - 1) % 20
)
FROM generate_series(1, 730000) AS s(g);
ANALYZE partition_lab.events_unpartitioned;
SELECT
count(*) AS rows,
min(occurred_at) AS first_time,
max(occurred_at) AS last_time,
count(DISTINCT occurred_at::date) AS days
FROM partition_lab.events_unpartitioned;
SELECT
occurred_at::date AS day,
count(*) AS rows_per_day
FROM partition_lab.events_unpartitioned
GROUP BY 1
ORDER BY 1
LIMIT 3;预期结果
text
rows | first_time | last_time | days
-------+------------------------+-------------------------+------
730000 | 2024-01-01 00:00:00+00 | 2025-12-30 00:16:39+00 | 730
day | rows_per_day
------------+-------------
2024-01-01 | 1000
2024-01-02 | 1000
2024-01-03 | 1000如果总行数、首尾时间或每日行数不同,先停止后续实验。最常见原因是脚本没有从头执行、旧 schema 没有被清理,或装载 SQL 被手工修改。
为什么要固定这些边界
分区测试最怕“数据也变了、结构也变了”。固定 730 天和每天 1000 行后,一个月、一个季度或一年的谓词能对应稳定的数据规模;同一条 SQL 在未分区表和后续分区表上执行时,扫描范围才可直接比较。
这份数据也刻意避免把“查询更快”预设成分区必然收益。后续应同时观察扫描计划、缓冲区读取、索引体积和历史数据维护成本;只要样本保持不变,这些指标的变化才有解释价值。
按自己的节奏,确认真正理解。