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

概要

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:

• 郵便番号データの説明 - 日本郵便
• PostgreSQL 9.3 の JSON サポートについて（長いよッ） - Qiita
• SIOS "OSSよろず" ブログ出張所: PostgreSQL 9.3 の JSON データのインデックス
• PostgreSQLのJSON型とhstore型のパフォーマンス — still deeper

あとがき

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