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 在未分区表和后续分区表上执行时,扫描范围才可直接比较。

这份数据也刻意避免把“查询更快”预设成分区必然收益。后续应同时观察扫描计划、缓冲区读取、索引体积和历史数据维护成本;只要样本保持不变,这些指标的变化才有解释价值。

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

目录与阅读设置

记录一个想法

建立分区实验数据集

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

登录哪都通