Percona 連載到 PostgreSQL 存 JSON object 以及增加 Index 的方式了...

先前 Percona 的人在講 MySQL 存 JSON object 的方式,現在開始講在 PostgreSQL 裡存 JSON object,並且增加 index 的方式了:「Storing and Using JSON Within PostgreSQL Part One」。

這基本上就是不想用 MongoDB,但還是有需要極為彈性而選擇用 JSON object 的需求。

首先先先建立一個表格,這邊直接用 JSONB:

alice=# CREATE TABLE table1 (id SERIAL PRIMARY KEY, jb JSONB);

接著拿「A dataset of English plaintext jokes」這邊的 reddit_jokes.json 來玩,我先把 JSON 裡面的內容變成 JSON Lines 格式:

cat reddit_jokes.json | jq -c '.[]' > reddit_jokes.jsonl

然後 COPY 了十次,多一點資料,後面可以看效能:

alice=# COPY table1 (jb) FROM '/tmp/reddit_jokes.jsonl' CSV QUOTE e'\x01' DELIMITER e'\x02';
-- (repeat this command 10 times)

接著跑個 SELECT 看看速度,我跑了幾次大約都在 260ms 上下:

alice=# SELECT COUNT(*) FROM table1 WHERE (jb->>'score')::int = 10;
 count 
-------
 25510
(1 row)

Time: 264.023 ms

然後針對 score 生個數字的 index:

alice=# CREATE INDEX ON table1 (((jb->>'score')::int));
CREATE INDEX
Time: 1218.503 ms (00:01.219)

接著再跑 SELECT 下去,可以看到速度快超多:

alice=# SELECT COUNT(*) FROM table1 WHERE (jb->>'score')::int = 10;
 count 
-------
 25510
(1 row)

Time: 12.735 ms

另外也可以加 column:

alice=# ALTER TABLE table1 ADD COLUMN score INT GENERATED ALWAYS AS ((jb->>'score')::int) STORED;

然後可以看到速度也不快:

alice=# SELECT COUNT(*) FROM table1 WHERE score = 10;
 count 
-------
 25510
(1 row)

Time: 222.163 ms

幫他補 index:

alice=# CREATE INDEX ON table1 (score);

速度有變快,但不知道為什麼沒有 JSONB 的版本快:

alice=# SELECT COUNT(*) FROM table1 WHERE score = 10;
 count 
-------
 25510
(1 row)

Time: 81.346 ms

算是還蠻好用的,不過得學 JSON query 語法... (應該是還好)

Leave a Reply

Your email address will not be published. Required fields are marked *