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 索引实验。
按自己的节奏,确认真正理解。