PostgreSQL / 系统课时

搭建可复现实验环境

搭建可复现实验环境

后续比较 JSONB 查询与索引时,必须先固定表结构、数据规模、字段分布和初始索引状态。本课用一份确定性 SQL 重建两张实验表、生成固定分布的数据并建立基础 B-tree 索引,再通过校验查询确认环境可以反复重建和复用。

学习目标

  • 创建适合 JSONB 查询实验的表结构
  • 生成规模和分布稳定的商品与事件数据
  • 验证记录数、JSONB 类型和初始索引状态

一次性建立实验基线

products 保存稳定业务列与动态商品属性,events 保存事件类型、时间和半结构化元数据。为避免后续索引实验被旧对象干扰,主脚本会重建表、生成固定数据,并只创建普通关系型索引,不提前添加 GIN 或 JSONB 表达式索引。

sql
DROP TABLE IF EXISTS events;
DROP TABLE IF EXISTS products;

CREATE TABLE products (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku text NOT NULL UNIQUE,
    category text NOT NULL,
    price numeric(10, 2) NOT NULL,
    attributes jsonb NOT NULL
);

CREATE TABLE events (
    id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    event_type text NOT NULL,
    occurred_at timestamptz NOT NULL,
    metadata jsonb NOT NULL
);

INSERT INTO products (sku, category, price, attributes)
SELECT
    'SKU-' || lpad(g::text, 5, '0'),
    CASE g % 3
        WHEN 0 THEN 'book'
        WHEN 1 THEN 'electronics'
        ELSE 'outdoor'
    END,
    (10 + g % 500)::numeric(10, 2),
    jsonb_build_object(
        'color', CASE g % 4
            WHEN 0 THEN 'black'
            WHEN 1 THEN 'white'
            WHEN 2 THEN 'blue'
            ELSE 'green'
        END,
        'brand', 'brand-' || (g % 20),
        'stock', g % 100,
        'tags', jsonb_build_array(
            'tag-' || (g % 10),
            'group-' || (g % 5)
        )
    )
FROM generate_series(1, 10000) AS g;

INSERT INTO events (event_type, occurred_at, metadata)
SELECT
    CASE g % 3
        WHEN 0 THEN 'view'
        WHEN 1 THEN 'cart'
        ELSE 'purchase'
    END,
    timestamptz '2026-01-01 00:00:00+00' + g * interval '1 second',
    jsonb_build_object(
        'user_id', g % 1000,
        'product_id', (g % 10000) + 1,
        'source', CASE g % 3
            WHEN 0 THEN 'web'
            WHEN 1 THEN 'app'
            ELSE 'campaign'
        END,
        'request', jsonb_build_object(
            'region', 'region-' || (g % 5)
        )
    )
FROM generate_series(1, 20000) AS g;

CREATE INDEX idx_products_category
ON products (category);

CREATE INDEX idx_events_type_time
ON events (event_type, occurred_at);

ANALYZE products;
ANALYZE events;

预期结果

text
DROP TABLE
DROP TABLE
CREATE TABLE
CREATE TABLE
INSERT 0 10000
INSERT 0 20000
CREATE INDEX
CREATE INDEX
ANALYZE
ANALYZE

如果表原先不存在,DROP TABLE IF EXISTS 可能额外显示 NOTICE,这不影响执行。数据生成不使用随机函数;g % 10、g % 5 等表达式固定控制标签、分组和其他字段分布,因此每次重建都会得到相同规模和相同分布,适合比较不同查询与索引方案。

验证数据与 JSONB 类型

先检查两张表的记录数、JSONB 顶层类型和一条固定商品记录。由于每次都会删除并重建表,新的 Identity 序列从初始值开始,id = 1 对应 g = 1,因此颜色应稳定为 white。

sql
SELECT
    (SELECT count(*) FROM products) AS product_count,
    (SELECT count(*) FROM events) AS event_count,
    jsonb_typeof((SELECT attributes FROM products WHERE id = 1)) AS attributes_type,
    jsonb_typeof((SELECT metadata FROM events WHERE id = 1)) AS metadata_type;

SELECT sku, attributes->>'color' AS color
FROM products
WHERE id = 1;

预期结果

text
 product_count | event_count | attributes_type | metadata_type
---------------+-------------+-----------------+---------------
         10000 |       20000 | object          | object
(1 row)

    sku    | color
-----------+-------
 SKU-00001 | white
(1 row)

这两组结果同时验证了数据规模、JSONB 存储类型和确定性分布。如果记录数或固定值不一致,应重新执行完整基线脚本,而不是在旧数据上继续后续实验。

确认初始索引状态

最后列出当前两张表的索引,确认只有主键、唯一约束和普通 B-tree 索引,没有提前加入 JSONB 专用索引。

sql
SELECT indexname
FROM pg_indexes
WHERE schemaname = current_schema()
  AND tablename IN ('products', 'events')
ORDER BY indexname;

预期结果

text
        indexname
--------------------------
 events_pkey
 idx_events_type_time
 idx_products_category
 products_pkey
 products_sku_key
(5 rows)

建议在专用实验 schema 中执行脚本,避免同名对象或额外索引干扰清单。完成这些验证后,products.attributes 与 events.metadata 就构成两个稳定的 JSONB 数据集,可直接用于后续路径查询、包含查询、表达式索引与 GIN 索引实验。

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

目录与阅读设置

记录一个想法

搭建可复现实验环境

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

登录哪都通