为高性能 NoSQL 风格查询实现 PostgreSQL JSONB 索引策略

简介

PostgreSQL JSONB (JSON Binary) 将文档数据库的灵活性与关系型 SQL 的强大功能相结合。与传统的 JSON 存储不同，JSONB 以分解的二进制格式存储，能够实现高效的索引和查询。本指南涵盖了高级 JSONB 索引策略，旨在维持 ACID 保证的同时实现类 NoSQL 的性能。

JSONB vs JSON

JSON (文本存储)

• 存储精确的文本表示
• 插入速度更快（无处理过程）
• 查询速度较慢
• 保留空格和键的顺序
• 不支持索引

JSONB (二进制存储)

• 以分解的二进制形式存储
• 插入开销略高
• 查询速度快得多
• 不保留空格
• 全索引支持
• 推荐用于大多数用例

基本 JSONB 操作

表结构设置

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    metadata JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);

-- 插入示例数据
INSERT INTO products (name, metadata) VALUES
    ('Laptop', '{"brand": "Dell", "price": 999, "specs": {"ram": 16, "cpu": "i7"}}'),
    ('Phone', '{"brand": "Apple", "price": 899, "specs": {"ram": 6, "storage": 128}}'),
    ('Tablet', '{"brand": "Samsung", "price": 599, "specs": {"ram": 8, "storage": 256}}');

查询操作符

-- 通过键访问：->
SELECT name, metadata->'brand' AS brand FROM products;

-- 以文本形式访问：->>
SELECT name, metadata->>'brand' AS brand_text FROM products;

-- 嵌套访问
SELECT name, metadata->'specs'->>'ram' AS ram FROM products;

-- 检查键是否存在：?
SELECT * FROM products WHERE metadata ? 'brand';

-- 检查是否存在任一键：?|
SELECT * FROM products WHERE metadata ?| ARRAY['brand', 'manufacturer'];

-- 检查是否所有键都存在：?&
SELECT * FROM products WHERE metadata ?& ARRAY['brand', 'price'];

-- 包含：@>
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';

-- 被包含于：<@
SELECT * FROM products WHERE '{"brand": "Apple"}' <@ metadata;

JSONB 索引策略

1. GIN 索引 (广义倒排索引)

默认且最通用的索引方法。

-- 在整个 JSONB 列上创建 GIN 索引
CREATE INDEX idx_products_metadata ON products USING GIN (metadata);

-- 支持的操作符：@>, ?, ?|, ?&
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';
SELECT * FROM products WHERE metadata ? 'brand';

索引大小 vs 查询性能：
- 索引体积较大
- 非常适合包含查询 (containment queries)
- 适合键存在性检查
- 更新较慢（整个 JSON 需要重新索引）

2. 使用 jsonb_path_ops 的 GIN 索引

仅针对包含查询进行了优化。

CREATE INDEX idx_products_metadata_ops ON products USING GIN (metadata jsonb_path_ops);

-- 仅支持：@>
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';
SELECT * FROM products WHERE metadata @> '{"specs": {"ram": 16}}';

-- 不支持：?, ?|, ?&
-- SELECT * FROM products WHERE metadata ? 'brand';  -- 不会使用索引

优势：
- 索引大小减少 50-70%
- 包含查询速度更快
- 更适合写密集型工作负载

何时使用：
- 主要使用 @> 操作符
- 需要更小的索引占用
- 高插入/更新频率

3. 特定键的表达式索引 (Expression Indexes)

对特定 JSONB 路径建立索引以获得极限性能。

-- 为特定文本值建立索引
CREATE INDEX idx_products_brand ON products ((metadata->>'brand'));

-- 为特定数值建立索引
CREATE INDEX idx_products_price ON products (((metadata->>'price')::NUMERIC));

-- 为嵌套值建立索引
CREATE INDEX idx_products_ram ON products (((metadata->'specs'->>'ram')::INTEGER));

-- 查询将高效地使用这些索引
SELECT * FROM products WHERE metadata->>'brand' = 'Apple';
SELECT * FROM products WHERE (metadata->>'price')::NUMERIC > 500;

优势：
- 索引大小最小
- 特定字段的查询性能最快
- 享有标准 B-tree 索引的所有好处（范围查询、排序）

何时使用：
- 频繁查询特定的字段
- 需要范围查询或排序
- 希望最小化索引开销

4. 部分索引 (Partial Indexes)

仅对数据的相关子集建立索引。

-- 仅为包含 'brand' 键的产品建立索引
CREATE INDEX idx_products_branded ON products USING GIN (metadata)
    WHERE metadata ? 'brand';

-- 仅为高价产品建立索引
CREATE INDEX idx_expensive_products ON products USING GIN (metadata)
    WHERE (metadata->>'price')::NUMERIC > 500;

-- 为特定品牌的规格建立索引
CREATE INDEX idx_apple_specs ON products USING GIN (metadata->'specs')
    WHERE metadata->>'brand' = 'Apple';

优势：
- 索引体积大大缩小
- 非索引行的更新速度更快
- 存储需求降低

5. 复合索引 (Composite Indexes)

将 JSONB 与常规列结合。

-- 索引 分类 + JSONB 元数据
CREATE TABLE products_v2 (
    id SERIAL PRIMARY KEY,
    category VARCHAR(50),
    metadata JSONB
);

CREATE INDEX idx_category_metadata ON products_v2 (category, metadata jsonb_path_ops);

-- 同时结合两者的组合查询效率很高
SELECT * FROM products_v2 
WHERE category = 'electronics' 
  AND metadata @> '{"brand": "Apple"}';

性能对比

测试数据集设置

CREATE TABLE test_jsonb (
    id SERIAL PRIMARY KEY,
    data JSONB
);

-- 插入 100 万条记录
INSERT INTO test_jsonb (data)
SELECT jsonb_build_object(
    'user_id', generate_series(1, 1000000),
    'name', 'User ' || generate_series(1, 1000000),
    'age', (random() * 60 + 18)::INTEGER,
    'city', (ARRAY['NYC', 'LA', 'Chicago', 'Houston', 'Phoenix'])[floor(random() * 5 + 1)],
    'active', random() > 0.5,
    'metadata', jsonb_build_object(
        'score', (random() * 1000)::INTEGER,
        'level', floor(random() * 10 + 1)
    )
);

基准测试不同索引类型

-- 无索引
EXPLAIN ANALYZE
SELECT * FROM test_jsonb WHERE data @> '{"city": "NYC"}';
-- 结果：~200ms，顺序扫描 (Seq Scan)

-- GIN 索引
CREATE INDEX idx_gin ON test_jsonb USING GIN (data);
EXPLAIN ANALYZE
SELECT * FROM test_jsonb WHERE data @> '{"city": "NYC"}';
-- 结果：~5ms，位图索引扫描 (Bitmap Index Scan)

-- GIN jsonb_path_ops
DROP INDEX idx_gin;
CREATE INDEX idx_gin_ops ON test_jsonb USING GIN (data jsonb_path_ops);
EXPLAIN ANALYZE
SELECT * FROM test_jsonb WHERE data @> '{"city": "NYC"}';
-- 结果：~3ms，位图索引扫描（索引体积减小 40%）

-- 表达式索引
DROP INDEX idx_gin_ops;
CREATE INDEX idx_city ON test_jsonb ((data->>'city'));
EXPLAIN ANALYZE
SELECT * FROM test_jsonb WHERE data->>'city' = 'NYC';
-- 结果：~2ms，位图索引扫描（最小的索引）

高级查询模式

数组操作

CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    profile JSONB
);

INSERT INTO users (profile) VALUES
    ('{"name": "Alice", "tags": ["developer", "golang", "postgresql"]}'),
    ('{"name": "Bob", "tags": ["designer", "figma", "ui"]}');

-- 检查数组是否包含元素
SELECT * FROM users WHERE profile->'tags' ? 'golang';

-- 检查数组是否包含任一元素
SELECT * FROM users WHERE profile->'tags' ?| ARRAY['golang', 'python'];

-- 检查数组是否包含所有元素
SELECT * FROM users WHERE profile->'tags' ?& ARRAY['developer', 'golang'];

-- 展开数组元素
SELECT 
    id,
    profile->>'name' AS name,
    jsonb_array_elements_text(profile->'tags') AS tag
FROM users;

聚合与分组

-- 按 JSONB 字段计数
SELECT 
    data->>'city' AS city,
    COUNT(*) AS user_count
FROM test_jsonb
GROUP BY data->>'city'
ORDER BY user_count DESC;

-- 嵌套数值字段的平均值
SELECT 
    data->>'city' AS city,
    AVG((data->'metadata'->>'score')::INTEGER) AS avg_score
FROM test_jsonb
GROUP BY data->>'city';

-- 聚合 JSONB 对象
SELECT 
    data->>'city' AS city,
    jsonb_agg(data->'name') AS user_names
FROM test_jsonb
WHERE (data->>'age')::INTEGER > 30
GROUP BY data->>'city';

JSONB 中的全文搜索

CREATE TABLE documents (
    id SERIAL PRIMARY KEY,
    content JSONB
);

INSERT INTO documents (content) VALUES
    ('{"title": "PostgreSQL Guide", "body": "Learn PostgreSQL JSONB indexing"}'),
    ('{"title": "NoSQL vs SQL", "body": "Comparison of database paradigms"}');

-- 创建文本搜索索引
CREATE INDEX idx_documents_fts ON documents 
    USING GIN (to_tsvector('english', content->>'body'));

-- 全文搜索
SELECT * FROM documents
WHERE to_tsvector('english', content->>'body') @@ to_tsquery('PostgreSQL & indexing');

更新 JSONB 字段

-- 更新整个 JSONB
UPDATE products SET metadata = '{"brand": "HP", "price": 1099}'::JSONB
WHERE id = 1;

-- 使用 jsonb_set 更新特定键
UPDATE products 
SET metadata = jsonb_set(metadata, '{price}', '1199')
WHERE id = 1;

-- 更新嵌套值
UPDATE products
SET metadata = jsonb_set(metadata, '{specs,ram}', '32')
WHERE id = 1;

-- 添加新键
UPDATE products
SET metadata = metadata || '{"warranty": "2 years"}'::JSONB
WHERE id = 1;

-- 移除键
UPDATE products
SET metadata = metadata - 'warranty'
WHERE id = 1;

-- 移除嵌套键
UPDATE products
SET metadata = metadata #- '{specs,storage}'
WHERE id = 1;

模式设计最佳实践

1. 提取频繁查询的字段

差的做法：全部存入 JSONB

CREATE TABLE orders_bad (
    id SERIAL PRIMARY KEY,
    data JSONB  -- 包含：order_date, customer_id, status, total, items...
);

-- 查询缓慢
SELECT * FROM orders_bad 
WHERE data->>'status' = 'pending'
  AND (data->>'order_date')::DATE > '2024-01-01';

好的做法：混合方案

CREATE TABLE orders_good (
    id SERIAL PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status VARCHAR(20) NOT NULL,
    order_date DATE NOT NULL,
    total DECIMAL(10,2),
    metadata JSONB,  -- 较少查询的字段
    CONSTRAINT fk_customer FOREIGN KEY (customer_id) REFERENCES customers(id)
);

CREATE INDEX idx_orders_status ON orders_good(status);
CREATE INDEX idx_orders_date ON orders_good(order_date);

-- 查询快速
SELECT * FROM orders_good
WHERE status = 'pending' AND order_date > '2024-01-01';

2. 标准化重复数据

差的做法：在 JSONB 中去规范化

CREATE TABLE logs_bad (
    id SERIAL PRIMARY KEY,
    log_data JSONB
);

-- 每条日志都重复用户信息
INSERT INTO logs_bad (log_data) VALUES
    ('{"user": {"id": 1, "name": "Alice", "email": "[email protected]"}, "action": "login"}'),
    ('{"user": {"id": 1, "name": "Alice", "email": "[email protected]"}, "action": "view_page"}');

好的做法：使用 ID 引用

CREATE TABLE logs_good (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL,
    action VARCHAR(50),
    metadata JSONB,  -- 仅存储与动作相关的数据
    FOREIGN KEY (user_id) REFERENCES users(id)
);

3. 在 JSONB 上使用约束

CREATE TABLE products_constrained (
    id SERIAL PRIMARY KEY,
    name VARCHAR(200),
    metadata JSONB,

    -- 确保特定键存在
    CONSTRAINT metadata_has_brand CHECK (metadata ? 'brand'),

    -- 确保价格为正数
    CONSTRAINT positive_price CHECK ((metadata->>'price')::NUMERIC > 0),

    -- 确保嵌套结构存在
    CONSTRAINT has_specs CHECK (metadata ? 'specs')
);

监控与优化

分析 JSONB 查询性能

-- 开启计时
\timing on

-- 分析查询
EXPLAIN (ANALYZE, BUFFERS, VERBOSE)
SELECT * FROM products WHERE metadata @> '{"brand": "Apple"}';

-- 检查索引使用情况
SELECT 
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch
FROM pg_stat_user_indexes
WHERE tablename = 'products';

识别缺失的索引

-- 查找在 JSONB 列上进行顺序扫描的表
SELECT 
    schemaname,
    tablename,
    seq_scan,
    seq_tup_read,
    idx_scan,
    seq_tup_read / NULLIF(seq_scan, 0) AS avg_seq_read
FROM pg_stat_user_tables
WHERE seq_scan > 1000
  AND tablename IN (
      SELECT tablename 
      FROM information_schema.columns 
      WHERE data_type = 'jsonb'
  )
ORDER BY seq_scan DESC;

索引膨胀检查

CREATE EXTENSION pgstattuple;

SELECT 
    indexname,
    pg_size_pretty(pg_relation_size(indexrelid)) AS size,
    100 - pgstatindex(indexrelid).avg_leaf_density AS bloat_pct
FROM pg_stat_user_indexes
WHERE schemaname = 'public'
  AND indexname LIKE '%jsonb%';

从 NoSQL 迁移到 PostgreSQL JSONB

从 MongoDB 到 PostgreSQL

// MongoDB 文档查询
db.products.find({
    "brand": "Apple",
    "specs.ram": { $gte: 8 }
})

-- PostgreSQL 等效查询
SELECT * FROM products
WHERE metadata @> '{"brand": "Apple"}'
  AND (metadata->'specs'->>'ram')::INTEGER >= 8;

创建迁移脚本

import psycopg2
from pymongo import MongoClient
import json

# 连接 MongoDB
mongo_client = MongoClient('mongodb://localhost:27017/')
mongo_db = mongo_client['mydb']
mongo_collection = mongo_db['products']

# 连接 PostgreSQL
pg_conn = psycopg2.connect(
    host="localhost",
    database="mydb",
    user="postgres",
    password="password"
)
pg_cursor = pg_conn.cursor()

# 迁移数据
for doc in mongo_collection.find():
    # 移除 MongoDB 的 _id
    doc.pop('_id', None)

    # 插入到 PostgreSQL
    pg_cursor.execute(
        "INSERT INTO products (name, metadata) VALUES (%s, %s)",
        (doc.get('name'), json.dumps(doc))
    )

pg_conn.commit()
pg_cursor.close()
pg_conn.close()

最佳实践总结

1. 使用 JSONB 而非 JSON：二进制格式支持索引。
2. 选择正确的索引类型：GIN 用于灵活性，表达式索引用于特定字段。
3. 混合模式设计：将频繁查询的字段提取到普通列中。
4. 避免过深嵌套：保持 JSONB 结构相对扁平。
5. 使用约束：验证 JSONB 结构和数值。
6. 监控查询模式：根据实际使用情况创建索引。
7. 定期维护：通过 VACUUM 和 REINDEX 确保最佳性能。
8. 生产前进行基准测试：使用真实的数据规模进行测试。
9. 考虑分区：针对极大型 JSONB 表使用分区。
10. 编写模式文档：维护预期 JSONB 结构的文档。

结论

PostgreSQL JSONB 提供了具备 SQL 强力支持的 NoSQL 灵活性：

• 灵活的模式：存储半结构化数据。
• 快速查询：多种索引策略。
• ACID 保证：全事务支持。
• 丰富的操作符：强大的查询能力。
• 混合方案：与关系设计完美结合。

一般用途从 GIN 索引开始，针对特定的高频查询使用表达式索引进行优化，并维持混合模式设计以获取最佳性能。