100万行数据实测！PostgreSQL里用JSON还是JSONB？

你在 PostgreSQL 里存过 JSON 数据吗？

JSON 和 JSONB 两个类型，名字就差一个字母，选的时候是不是经常随便挑一个？反正都能存 JSON，能有多大区别……

区别很大。查询速度差7倍，存储空间差26%，写入速度也能差31%。而且这还不是理论值——是有人在100万行真实数据 上跑出来的基准测试结果。

今天这篇文章，就把这个选择彻底说清楚：什么时候用JSON，什么时候必须用JSONB，以及那些文档里不会告诉你的坑。

一句话总结：

• JSON = 懒模式：存得快，查得慢

• JSONB = 认真模式：存得慢一点，但查起来飞快

以下这些强大的查询运算符只有 JSONB 支持：

-- @> 包含检查：这条记录是否包含指定的键值对？
SELECT * FROM users WHERE data @> '{"role": "admin"}';


-- ? 键存在性检查：是否有这个字段？
SELECT * FROM users WHERE data ? 'email_verified';


-- ?& 多键同时存在检查
SELECT * FROM users WHERE data ?& array['email_verified', 'phone_verified'];

如果用 JSON 类型，上面的查询只能写成笨拙的形式：

-- JSON 类型没有 @> 运算符，只能这样写
SELECT * FROM users 
WHERE data->>'role' = 'admin';
-- 无法高效检查嵌套结构或数组包含关系

-- 为 JSONB 列创建 GIN 索引
CREATE INDEX idx_users_data ON users USING GIN (data);


-- 创建后，包含查询从全表扫描变成索引查找
-- 没有索引：扫描100万行 → 有索引：毫秒级返回
EXPLAIN ANALYZE SELECT * FROM users WHERE data @> '{"status": "active"}';

GIN（Generalized Inverted Index）是专门为复合类型设计的倒排索引。它会将 JSONB 文档中的每个键和值都建入索引，使得包含查询、键存在性查询等操作可以走索引而非全表扫描。

下面这个测试来自 DEV Community 作者 Eugene (@ineron) 开源的真实基准测试。我们一步步来复现。

第一步：创建测试表

-- JSON 类型的表
CREATE TABLE bench_json (
    id SERIAL PRIMARY KEY,
    data JSON NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);


-- JSONB 类型的表
CREATE TABLE bench_jsonb (
    id SERIAL PRIMARY KEY,
    data JSONB NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

第二步：生成 100 万行测试数据

每条数据模拟一个用户配置对象，包含嵌套结构和数组：

import psycopg2
import json
import random
import string
from datetime import datetime
conn = psycopg2.connect("dbname=testdb host=localhost user=postgres")
cur = conn.cursor()
def generate_user_data():
    """生成模拟用户配置的 JSON 数据"""
    roles = ["admin", "editor", "viewer", "moderator"]
    plans = ["free", "pro", "enterprise"]

    # 随机生成 5-15 个偏好设置
    preferences = {}
    for i in range(random.randint(5, 15)):
        key = f"pref_{random.choice(string.ascii_lowercase)}"
        value = random.choice([True, False, random.randint(0, 100), None])
        preferences[key] = value

    data = {
        "user_id": f"u_{random.randint(10000, 99999)}",
        "username": f"user_{random.randint(10000, 99999)}",
        "email": f"user{random.randint(10000, 99999)}@example.com",
        "role": random.choice(roles),
        "plan": random.choice(plans),
        "settings": {
            "theme": random.choice(["dark", "light", "auto"]),
            "notifications_enabled": random.choice([True, False]),
            "language": random.choice(["zh", "en", "ja"]),
            "two_factor_enabled": random.choice([True, False])
        },
        "preferences": preferences,
        "tags": [f"tag_{i}" for i in range(random.randint(0, 8))],
        "metadata": {
            "signup_source": random.choice(["web", "api", "invite"]),
            "last_login": datetime.now().isoformat(),
            "login_count": random.randint(1, 500)
        }
    }
    return json.dumps(data)
# 批量插入到 JSON 表（每次 10000 条）
batch_size = 10000
total = 1000000
for i in range(0, total, batch_size):
    batch = []
    for _ in range(batch_size):
        batch.append((generate_user_data(),))

    cur.executemany(
        "INSERT INTO bench_json (data) VALUES (%s::json)",
        batch
    )
    conn.commit()
    print(f"Inserted {min(i + batch_size, total)}/{total}")
# 同样插入到 JSONB 表
for i in range(0, total, batch_size):
    batch = []
    for _ in range(batch_size):
        batch.append((generate_user_data(),))

    cur.executemany(
        "INSERT INTO bench_jsonb (data) VALUES (%s::jsonb)",
        batch
    )
    conn.commit()
    print(f"JSONB: Inserted {min(i + batch_size, total)}/{total}")


cur.close()
conn.close()

第三步：性能对比测试

-- ========== 写入性能测试 ==========
-- 结果（实测）：
--   JSON:  8.6 秒  (100万行)
--   JSONB: 11.3 秒 (100万行)
-- 结论：JSON 写入快约 31%（因为不需要解析）
-- ========== 存储空间测试 ==========
SELECT 
    'JSON' AS type,
    pg_size_pretty(pg_total_relation_size('bench_json')) AS size
UNION ALL
SELECT 
    'JSONB' AS type,
    pg_size_pretty(pg_total_relation_size('bench_jsonb')) AS size;
-- 结果：
--   JSON:  ~1200 MB
--   JSONB: ~888 MB
-- 结论：JSONB 节省 26% 空间
--       （原因：去除了空白 + 键名去重）

-- ========== 查询性能测试 ==========
-- 测试1：简单键值提取
EXPLAIN ANALYZE SELECT count(*) FROM bench_json WHERE data->>'role' = 'admin';
EXPLAIN ANALYZE SELECT count(*) FROM bench_jsonb WHERE data->>'role' = 'admin';
-- JSONB 比 JSON 快 **6.2倍**


-- 测试2：嵌套字段访问
EXPLAIN ANALYZE SELECT count(*) FROM bench_json 
WHERE data->'settings'->>'theme' = 'dark';
EXPLAIN ANALYZE SELECT count(*) FROM bench_jsonb 
WHERE data->'settings'->>'theme' = 'dark';
-- JSONB 比 JSON 快 **7.6倍**


-- 测试3：数组操作
EXPLAIN ANALYZE SELECT count(*) FROM bench_json 
WHERE data->'tags'->>0 IS NOT NULL;
EXPLAIN ANALYZE SELECT count(*) FROM bench_jsonb 
WHERE data->'tags'->>0 IS NOT NULL;
-- JSONB 比 JSON fast **7.3倍**


-- 测试4：复杂多条件查询
EXPLAIN ANALYZE SELECT count(*) FROM bench_json 
WHERE data->>'role' = 'admin' 
  AND data->'settings'->>'two_factor_enabled' = 'true'
  AND data->'plan' = 'enterprise';
EXPLAIN ANALYZE SELECT count(*) FROM bench_jsonb 
WHERE data->>'role' = 'admin' 
  AND data->'settings'->>'two_factor_enabled' = 'true'
  AND data->'plan' = 'enterprise';
-- JSONB 比 JSON 快 **9.1倍**

性能汇总表：

场景A：日志/事件存储 → 选 JSONB ✅

CREATE TABLE application_logs (
    id BIGSERIAL PRIMARY KEY,
    timestamp TIMESTAMPTZ DEFAULT NOW(),
    service_name TEXT NOT NULL,
    level TEXT CHECK (level IN ('DEBUG', 'INFO', 'WARN', 'ERROR')),
    payload JSONB NOT NULL,      -- ← 用 JSONB
    -- GIN 索引让日志检索起飞
);


-- 创建 GIN 索引
CREATE INDEX idx_logs_payload ON application_logs USING GIN (payload);


-- 高效查询：找所有 ERROR 级别且包含特定错误码的日志
SELECT timestamp, service_name, payload
FROM application_logs
WHERE level = 'ERROR'
  AND payload @> '{"error_code": "AUTH_001"}'
ORDER BY timestamp DESC
LIMIT 50;


-- 查询计划：使用 GIN Index Scan，毫秒级返回

为什么选 JSONB？

• 日志通常是写一次读多次

• 经常需要按 payload 内的字段做条件过滤

• GIN 索引让亿级日志秒查成为可能

场景B：原始数据暂存 → 可以考虑 JSON ⚠️

CREATE TABLE raw_webhook_payloads (
    id BIGSERIAL PRIMARY KEY,
    received_at TIMESTAMPTZ DEFAULT NOW(),
    source_app TEXT NOT NULL,
    raw_payload JSON NOT NULL,     -- ← 用 JSON
    processed BOOLEAN DEFAULT FALSE
);


-- 写入速度优先，几乎不做查询
-- 只在异常排查时才去看 raw_payload 的内容
-- 这种场景 JSON 的写入速度优势有意义

为什么可以考虑 JSON？

• 纯写密集型：webhook 接收后立即处理，很少回查

• 只写不读（或极少读）：不需要查询优化的红利

• 需要保留原始格式（含空格、键顺序）：方便跟上游对账

但要注意：如果你后来发现"好像偶尔要查一下"，那还是老老实实用 JSONB 吧。改列类型的代价远大于一开始就选对的代价。

场景C：动态属性/Schema 自由字段 → 必须用 JSONB ✅

CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    sku TEXT UNIQUE NOT NULL,
    price DECIMAL(10, 2),
    -- 不同品类的商品有完全不同的属性
    attributes JSONB NOT NULL,   -- ← 必须用 JSONB
    updated_at TIMESTAMPTZ DEFAULT NOW()
);


-- 创建 GIN 索引支持任意属性搜索
CREATE INDEX idx_products_attr ON products USING GIN (attributes);


-- 示例：电子产品有屏幕尺寸、电池容量
INSERT INTO products (name, sku, price, attributes) VALUES (
    'iPhone Pro Max',
    'IPHONE-PM-256',
    9999.00,
    '{"screen_size": "6.9 inch", "battery_mAh": 5000, "color": "black", "storage_gb": 256}'
);


-- 示例：服装商品有尺码、材质
INSERT INTO products (name, sku, price, attributes) VALUES (
    '羊毛大衣',
    'COAT-WOOL-L',
    2999.00,
    '{"size": "L", "material": "wool", "season": "winter", "weight_g": 800}'
);


-- 跨品类查询：找所有价格 < 5000 且有库存的商品
SELECT name, sku, attributes->>'color' as color
FROM products
WHERE price < 5000
  AND attributes ? 'stock';  -- 检查是否有库存字段

这种"每个记录的属性结构都不同"的场景，正是 JSONB 的主场。

可以，而且很简单：

-- 一行搞定
ALTER TABLE my_table ALTER COLUMN data TYPE JSONB USING data::jsonb;

注意事项：

• 这会对整张表做 重写（REWRITE），大表会锁一段时间

• 生产环境建议在低峰期执行

• 如果表特别大（千万级行数），可以用 CONCURRENTLY 友好的方式分批迁移

-- 大表安全迁移方案
-- 1. 新增一个 JSONB 列
ALTER TABLE my_table ADD COLUMN data_new JSONB;


-- 2. 分批填充数据（每次更新 50000 行）
UPDATE my_table SET data_new = data::jsonb 
WHERE data_new IS NULL 
LIMIT 50000;
-- 重复执行直到全部迁移完


-- 3. 创建索引
CREATE INDEX idx_data_new ON my_table USING GIN (data_new);


-- 4. 切换应用层到新列，验证无误后删除旧列
-- ALTER TABLE my_table DROP COLUMN data;
-- ALTER TABLE my_table RENAME data_new TO data;

会有一点影响，但通常可接受：

权衡建议：

• 如果你的 JSONB 列频繁更新（比如每秒几十次），GIN 索引的开销值得关注

• 如果主要是 INSERT + SELECT（大多数场景），GIN 索引的查询收益远远覆盖写入开销

• 还有个折中选项：partial index（部分索引），只为常用查询条件建索引

-- 只为 status = active 的记录建索引
CREATE INDEX idx_active_users_data ON users USING GIN (data)
WHERE (data->>'status') = 'active';

这样索引体积更小，写入开销更低，同时覆盖了最常用的查询场景。

这是个好问题。很多人一看到"灵活"两个字就上 JSON，但其实常规字段应该用常规类型。

-- ❌ 不推荐：把所有东西都塞进 JSON
CREATE TABLE bad_design (
    id SERIAL PRIMARY KEY,
    data JSONB  -- 用户名、邮箱、年龄、注册时间...全塞这里
);


-- ✅ 推荐：固定属性用常规列，只有真正的变体属性用 JSONB
CREATE TABLE good_design (
    id SERIAL PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    email TEXT NOT NULL,
    age INTEGER,
    registered_at TIMESTAMPTZ DEFAULT NOW(),
    extra_attributes JSONB  -- 这里放真正不确定的字段
);

决策树：

这个字段的值格式固定吗？
├─ 是 → 用常规列（TEXT / INTEGER / TIMESTAMP 等）
│         能加约束（CHECK/UNIQUE/NOT NULL），能建 B-tree 索引
│
└─ 否 → 这个字段的结构会变化吗？
   ├─ 是 → 用 JSONB（+ GIN 索引）
   │
   └─ 否，只是纯文本存取 → 用 JSON 或直接用 TEXT

PostgreSQL 提供了 SQL/JSON Path 标准（Postgres 12+），支持类似 XPath 的路径表达式查询：

-- 找所有 settings.theme = dark 且 tags 数组长度 > 3 的用户
SELECT id, data
FROM bench_jsonb
WHERE jsonb_path_exists(data, '$.settings.theme == "dark" && $.tags.size() > 3');


-- 更复杂的：递归查找任意层级的 error 字段
SELECT id, jsonb_path_query_first(data, '$.**.error') as first_error
FROM application_logs
WHERE jsonb_path_exists(data, '$.**.error');

优点：

• 表达能力强，一行搞定复杂条件

• 支持过滤器（?()）、递归（**）、数组切片等

缺点：

• 语法学习成本较高

• 性能在某些情况下不如 @> + GIN 组合

• 对已有 PromQL/SQL 基础的人来说不够直觉

建议：日常简单查询用 @> / ->> 就够了，遇到特别复杂的嵌套查询再考虑 jsonb_path_query。

不完全一样，主要差异：

如果你在做技术选型，单就 JSON 能力来说，PostgreSQL 的 JSONB 明显更强。这也是很多团队从 MySQL 迁移到 PG 的原因之一。

JSON 还是 JSONB？答案其实很简单：

• 99% 的场景，直接用 JSONB，别纠结

• 那 1% 的情况是：纯写密集型 + 几乎不读 + 需要保留原始格式

别为了省那 31% 的写入时间而选了 JSON，然后在查询时付出 7 倍的性能代价。这笔账怎么算都不划算。

作者丨运维渔夫

来源丨公众号：运维渔夫（ID：wx_yzywg）

dbaplus社群欢迎广大技术人员投稿，投稿邮箱：editor@dbaplus.cn