PostgreSQL 中的 JSONB 性能测试

PostgreSQL 中的 JSONB 性能测试

Postgres 从 9.3 就开始支持 JSON 了，mysql 也在 5.7 加入了 JSON。对于存储非结构化数据，如果大部分的操作都是整段存储和读取，那么完全不需要构建复杂的表或者模式。很多场景下非常适合选择 JSON 数据结构。我一直非常好奇 PostgreSQL 的 JSONB 性能如何。假定我们要在一个 http 请求内查询含有 JSON 列的内容，如果只做简单的优化，一个表多可以支持多少行数据。在这里声明，我做测试的目的主要是满足公司业务需求，并非是全方位的测试。

准备工作

数据库版本：10.7
机器配置：2CPU, 15.25GB 内存 (AWS db.r4.large)

插入数据

首先创建一个表(object)用来写入数据。因为要数据量很大，所以我在这里使用的是 postgres \COPY 命令行（https://www.postgresql.org/docs/9.2/sql-copy.html）。另外也可以用sql自动生成随机数据。首先写一个脚本把数据以csv的形式存在本地，之后先连接数据库：
PGPASSWORD=<密码> psql -U postgres -h <数据库地址> -d postgres
连接上之后把本地数据插入到数据库中：
\COPY object(id,name,metadata) FROM '/home/ubuntu/d1.csv' DELIMITER ';' CSV HEADER;
插好之后可以在数据库中看到：

开始测试

这里选择的测试 sql 语句是：

SELECT count(*)
FROM object
WHERE CASE jsonb_typeof(metadata->'is') WHEN 'number' THEN (metadata->>'is')::numeric > 1 ELSE FALSE END;