Help us understand the problem. What is going on with this article?

PostgreSQL JSON型についての使い勝手とパフォーマンス、時々ハマりどころの調査

More than 1 year has passed since last update.

概要

PostgreSQL 9.2で搭載され、9.3で強化されたJSON型の使い勝手とパフォーマンス、ハマりどころを調査した

ついでに比較対象として、hstoreも調べてあります

環境: Docker PostgreSQL 9.3.5 | Core i7 / 4GB memory / Ubuntu 14.04

調査内容:

  • COPYによるデータロード
  • 検索(ステートメント、パフォーマンス)

準備

基データは、日本郵政の郵便番号データ全国版を使用

$ wc -l < KEN_ALL.CSV 
123721

テーブル作成とデータロード

CREATE TABLE ZIP_RAW (
  ID INTEGER PRIMARY KEY,
  IDX INTEGER,
  ZIPCODE VARCHAR(7),
  PREF VARCHAR(200),
  CITY VARCHAR(200),
  ST VARCHAR(200)
);

CREATE TABLE ZIP_JSON (
  ID INTEGER PRIMARY KEY,
  DATA JSON
);

CREATE EXTENSION HSTORE;
CREATE TABLE ZIP_HSTORE (
  ID INTEGER PRIMARY KEY,
  DATA HSTORE
);

COPYによるデータロード

JSON型(hstore型含む)カラムにも、COPYによるデータロードが使用可能です

ただし、JSONデータ内のカンマ文字の取り扱いが面倒なため、フィールドセパレータをTAB(\t)等にして、COPY時に指定するのが現状のベストプラクティスと思われます

e.g.)

※フィールドセパレータをTABとしています

json.txt
0   {"idx":0,"zipcode":"0600000","pref":"北海道","city":"札幌市中央区","street":"以下に掲載がない場合"}
1   {"idx":1,"zipcode":"0640941","pref":"北海道","city":"札幌市中央区","street":"旭ケ丘"}
2   {"idx":2,"zipcode":"0600041","pref":"北海道","city":"札幌市中央区","street":"大通東"}

※フィールドセパレータをTABとしています

hash.txt
0   "idx"=>0, "zipcode"=>"0600000", "pref"=>"北海道", "city"=>"札幌市中央区", "street"=>"以下に掲載がない場合"
1   "idx"=>1, "zipcode"=>"0640941", "pref"=>"北海道", "city"=>"札幌市中央区", "street"=>"旭ケ丘"
2   "idx"=>2, "zipcode"=>"0600041", "pref"=>"北海道", "city"=>"札幌市中央区", "street"=>"大通東"

COPY

psql# COPY ZIP_JSON   FROM '/tmp/json.txt' DELIMITER '  ';
psql# COPY ZIP_HSTORE FROM '/tmp/hash.txt' DELIMITER '  ';

※TerminalからTAB文字を入力するには、Ctrl+v の後、TABキーで入力できます

KEN_ALL.CSVからCOPY用データ生成するためのrubyコード

conv.rb
=begin
$ ruby conv.rb [-f tsv|json|hash]
=end
require "csv"
require "json"
require "optparse"

opts = ARGV.getopts("f:")
format = opts["f"].downcase.to_sym rescue :tsv

SEPT = "\t"
idx = 0
open("KEN_ALL.CSV", "r+:Shift_JIS").each do |line|
  CSV.parse(line.encode(Encoding.default_external)) do |r|
    data = {"idx" => idx, "zipcode" => r[2], "pref" => r[6], "city" => r[7], "street" => r[8]}
    line = case format
           when :tsv
             [idx, data.values].flatten.to_csv(col_sep:SEPT)
           when :json
             [idx, data.to_json].join(SEPT)
           when :hash
             [idx, data.inspect.sub(/^{/, '').sub(/}$/, '')].join(SEPT)
           end
    puts line
    idx += 1
  end
end

idxはJSON内の数値データ検索用として加えています

検索 (ステートメント、パフォーマンス)

SELECTステートメント、WHERE句

DBカラム

SELECT * FROM ZIP_RAW WHERE ZIPCODE = '1010021';
SELECT * FROM ZIP_RAW WHERE CITY LIKE '千代田区%';
SELECT * FROM ZIP_RAW WHERE IDX < 10;

JSON

SELECT * FROM ZIP_JSON WHERE DATA->>'zipcode' = '1010021';
SELECT * FROM ZIP_JSON WHERE DATA->>'city' LIKE '千代田区%';
SELECT * FROM ZIP_JSON WHERE cast(DATA->>'idx' AS INTEGER) < 10;

hstore

SELECT * FROM ZIP_HSTORE WHERE DATA->'zipcode' = '1010021';
SELECT * FROM ZIP_HSTORE WHERE DATA->'city' LIKE '千代田区%';
SELECT * FROM ZIP_HSTORE WHERE cast(DATA->'idx' AS INTEGER) < 10;

パフォーマンス

SELECT * FROM ZIP_*を対象に下記条件を設定。\timingを表示するようにし、5回計測中、最大最小を以外の3回の平均
※単位 ms

条件 DBカラム JSON hstore
文字列::完全一致
(CITY = '江東区')
14.9 137.9 29.0
文字列::前方一致
(CITY LIKE '千代田区%')
18.7 139.3 34.5
文字列::中間一致
(CITY LIKE '%中央%')
23.0 138.7 31.2
文字列::後方一致
(CITY LIKE '%港区')
17.9 140.0 35.3
文字列::否定
(CITY <> '新宿区')
111.2 228.5 131.6
数値::一致
(IDX = 1001)
10.3 140.0 35.0
数値::範囲一致
(IDX < 100)
12.3 40.3 144.5
数値::否定
(IDX <> 12345)
119.5 237.0 153.6

インデックス使用時のパフォーマンス

CREATE INDEX RI0 ON ZIP_RAW (IDX);
CREATE INDEX RI1 ON ZIP_RAW (CITY);

CREATE INDEX JI0 ON ZIP_JSON ( CAST(DATA->>'idx' AS INTEGER) );
CREATE INDEX JI1 ON ZIP_JSON ( (DATA->>'city') );

CREATE INDEX HI0 ON ZIP_HSTORE ( CAST(DATA->'idx' AS INTEGER) );
CREATE INDEX HI1 ON ZIP_HSTORE ( (DATA->'city') );

※上記INDEX作成後環境において、SELECT * FROM ZIP_*を対象に下記条件を設定。\timingを表示するようにし、5回計測中、最大最小を以外の3回の平均
※単位 ms

条件 DBカラム JSON hstore
文字列::完全一致
(CITY = '江東区')
0.8 0.4 0.4
文字列::前方一致
(CITY LIKE '千代田区%')
19.3 140.7 32.2
文字列::中間一致
(CITY LIKE '%中央%')
16.1 140.0 32.3
文字列::後方一致
(CITY LIKE '%港区')
16.7 142.0 37.0
文字列::否定
(CITY <> '新宿区')
113.7 231.5 139.9
数値::一致
(IDX = 1001)
0.1 0.1 0.1
数値::範囲一致
(IDX < 100)
0.2 0.4 0.4
数値::否定
(IDX <> 12345)
125.6 239.5 147.5

考察

まとめ参照のこと

ハマりどころ

キーはcase sensetive

JSON/hstoreキーはDBカラムと違い、case sensetiveです。data->>'IDX'data->>'idx'は別扱いです。

キー存在検証はしてくれないため、WHERE条件ではエラーが発生しません。
一番ハマるのは、CREATE INDEX時です。explainでINDEXが使われているか確認しましょう。

e.g.) このようなデータに対して

ZIP_JSON:

 id |                    data                         
----+-------------------------------------------------
  0 | {"idx":0,"zipcode":"0600000","pref":"北海道",...

DATA->>'ZIPCODE' = '0600000'と検索してもデータは見つかりません

※BAD case
development_db=# SELECT * FROM ZIP_JSON WHERE DATA->>'ZIPCODE' = '0600000';
 id | data 
----+------
(0 rows)

DATA->>'zipcode'として探すと見つかります

development_db=# SELECT * FROM ZIP_JSON WHERE DATA->>'zipcode' = '0600000';
 id |                    data                         
----+-------------------------------------------------
  0 | {"idx":0,"zipcode":"0600000","pref":"北海道",...
(1 row)

JSONオペレータ指定方法

->>オペレータは( )でくくる必要がでてくるケースがあります

development_db=# CREATE INDEX IDX ON ZIP_JSON_I ( DATA->>'zipcode' );
ERROR:  syntax error at or near "->>"
LINE 1: CREATE INDEX IDX ON ZIP_JSON_I ( DATA->>'ZIPCODE' );
※これだと失敗する

development_db=# CREATE INDEX IDX ON ZIP_JSON_I ( (DATA->>'zipcode') );
CREATE INDEX
※これで成功

まとめ

PostgreSQL 9.3におけるJSON型は「使える型」言える

使用に際しての注意点:

  • 検索対象となることが確定しているなら、INDEXを作成は必須。ネイティブのDBカラムと同等の検索性能が得られる
  • INDEXが使われなかった場合の検索性能の劣化は、DBカラムを1とするとhstore=1/2、JSON=1/10となる。また、JSON/hstoreは、格納データに比例して劣化するものと推測される
  • よって、JSON/hstoreにおける文字列データの検索は、完全一致を前提とした設計を検討する必要がある
  • JSON/hstoreにおけるの数値データに対する検索やINDEX作成は、CASTを使用して使う必要があり、若干扱いづらい。しかし、範囲検索が可能で、文字列データに対するLIKEよりは検索性能が高い
  • DATEはJSON仕様に無いため、文字列型で格納せざるを得なく、INDEXの恩恵は受けづらい

加えて、hstoreはobsoluteとなる可能性があるようなので、見極めがが大切

ref:

あとがき

Oyster Tableで牡蠣食べました。美味しいですね

Why not register and get more from Qiita?
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
Comments
No comments
Sign up for free and join this conversation.
If you already have a Qiita account
Why do not you register as a user and use Qiita more conveniently?
You need to log in to use this function. Qiita can be used more conveniently after logging in.
You seem to be reading articles frequently this month. Qiita can be used more conveniently after logging in.
  1. We will deliver articles that match you
    By following users and tags, you can catch up information on technical fields that you are interested in as a whole
  2. you can read useful information later efficiently
    By "stocking" the articles you like, you can search right away
ユーザーは見つかりませんでした