PostgreSQL JSONB 性能实战：二进制存储、GIN 索引、查询优化与文档工作负载建模

CREATE TABLE products (
  id BIGSERIAL PRIMARY KEY,
  name TEXT,
  attributes JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 查看字段是否被 TOAST 外置
SELECT
  a.attname,
  CASE WHEN a.attstorage = 'e' THEN 'external(extended)'
       WHEN a.attstorage = 'm' THEN 'main'
       WHEN a.attstorage = 'p' THEN 'plain'
  END AS storage
FROM pg_attribute a
JOIN pg_class c ON c.oid = a.attrelid
WHERE c.relname = 'products' AND a.attname = 'attributes';
-- 'e' 表示可压缩外置（默认，也是建议）

-- 重复键只保留最后一个
SELECT '{"a":1,"a":2}'::jsonb;      -- {"a": 2}
-- 键被排序
SELECT '{"z":1,"a":2}'::jsonb;      -- {"a": 2, "z": 1}
-- 数字类型被规范化（去尾零）
SELECT '{"n":1.10}'::jsonb;         -- {"n": 1.1}

-- 键路径访问
SELECT name, attributes->>'color' AS color
FROM products
WHERE attributes->>'color' = 'red';

-- 包含查询（可走 GIN 索引）
SELECT * FROM products
WHERE attributes @> '{"brand": "Nike", "category": "shoes"}';

-- 数组/嵌套路径
SELECT * FROM products
WHERE attributes #> '{spec, weight}' @> '{"unit":"kg"}';

-- 键存在性
SELECT * FROM products WHERE attributes ? 'warranty';

-- 错误：text 与 numeric 比较失败
SELECT * FROM products WHERE attributes->>'price' > 100;
-- 正确：显式转换
SELECT * FROM products
WHERE (attributes->>'price')::numeric > 100;

CREATE INDEX idx_products_attr ON products USING GIN (attributes);

-- 命中 GIN 索引
EXPLAIN SELECT * FROM products
WHERE attributes @> '{"category": "shoes"}';
-- ✅ Bitmap Index Scan on idx_products_attr

-- 更小更快的 jsonb_path_ops（适合纯 @> 场景）
CREATE INDEX idx_products_attr_path
  ON products USING GIN (attributes jsonb_path_ops);

-- 针对 attributes->>'sku' 的等值查询
CREATE INDEX idx_products_sku ON products ((attributes->>'sku'));

-- 针对价格数值的范围查询
CREATE INDEX idx_products_price
  ON products (((attributes->>'price')::numeric));

EXPLAIN SELECT * FROM products
WHERE (attributes->>'price')::numeric BETWEEN 100 AND 200;
-- ✅ Index Scan using idx_products_price

-- 用 hash 索引/唯一约束保证某文档唯一（不常用，慎重）
CREATE UNIQUE INDEX idx_unique_attr ON products ((attributes->>'sku'));

-- 优化前：全表扫描
SELECT * FROM products
WHERE attributes @> '{"tags":["vip"]}';

-- 优化后：确认 GIN 生效
EXPLAIN (ANALYZE, BUFFERS)
SELECT * FROM products
WHERE attributes @> '{"tags":["vip"]}';
-- 期望看到 Bitmap Heap Scan + Bitmap Index Scan on GIN

-- ❌ 对列做函数处理，B-tree 表达式索引可能不匹配
WHERE lower(attributes->>'name') = 'nike';
-- ✅ 建立匹配的表达式索引
CREATE INDEX idx_products_lname
  ON products ((lower(attributes->>'name')));
WHERE lower(attributes->>'name') = 'nike';

-- 统计解压情况：观察整行读取 vs 仅取字段
EXPLAIN (ANALYZE, BUFFERS)
SELECT id, attributes->>'color'
FROM products WHERE id = 100;
-- 如果 attributes 很大，建议拆出高频小字段到普通列

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  user_id BIGINT NOT NULL,           -- 关系型：外键、索引
  status TEXT NOT NULL,              -- 固定高频过滤字段
  amount NUMERIC NOT NULL,           -- 范围查询、聚合
  raw_payload JSONB,                 -- 半结构化扩展字段
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- 高频字段建索引
CREATE INDEX idx_orders_user ON orders (user_id);
CREATE INDEX idx_orders_status ON orders (status);
-- 半结构化扩展字段走 GIN
CREATE INDEX idx_orders_payload ON orders USING GIN (raw_payload);

-- 反模式：整块存大文档
UPDATE documents SET payload = '[{"severe":true,"score":99}, ... 10万项]';

-- 改进 1：高频统计字段抽成列
ALTER TABLE documents ADD COLUMN doc_size INT;

-- 改进 2：把大数组拆成子表（可 join/索引）
CREATE TABLE document_items (
  doc_id BIGINT,
  item JSONB,
  item_score NUMERIC
);
CREATE INDEX idx_doc_items_score ON document_items ((item->>'score')::numeric);

-- 看似"只改一个键"，实际重写整个 attributes
UPDATE products
SET attributes = jsonb_set(attributes, '{stock}', to_jsonb(100))
WHERE id = 1;

-- 优化前：全表扫描，3 秒
EXPLAIN SELECT count(*) FROM products
WHERE attributes @> '{"brand":"Nike","category":"shoes"}';
-- Seq Scan on products  (cost=0.00..29000.00 rows=... )

CREATE INDEX idx_products_attr_gin
  ON products USING GIN (attributes jsonb_path_ops);

EXPLAIN SELECT count(*) FROM products
WHERE attributes @> '{"brand":"Nike","category":"shoes"}';
-- Bitmap Index Scan on idx_products_attr_gin
-- 结果：20ms，提速约 150 倍

-- 优化：建带类型转换的 B-tree 表达式索引
CREATE INDEX idx_products_price_num
  ON products (((attributes->>'price')::numeric));

EXPLAIN SELECT * FROM products
WHERE (attributes->>'price')::numeric BETWEEN 100 AND 200;
-- ✅ Index Scan using idx_products_price_num

-- 优化 1：高频字段抽列
ALTER TABLE documents ADD COLUMN status TEXT;
UPDATE documents SET status = payload->>'status';
CREATE INDEX idx_docs_status ON documents (status);

-- 优化 2：低频大字段独立存储（必要时懒加载）
ALTER TABLE documents ALTER COLUMN payload SET STORAGE EXTERNAL;

-- 提高 GIN pending list，降低高并发写放大
ALTER TABLE products SET (gin_pending_list_limit = 8192);

-- 查询时观察 buffer 命中
EXPLAIN (ANALYZE, BUFFERS) SELECT ... ;