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

概要
----

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

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

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

調査内容:

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


準備
----

基データは、日本郵政の[郵便番号データ](http://www.post.japanpost.jp/zipcode/dl/readme.html)全国版を使用

```
$ wc -l < KEN_ALL.CSV 
123721
```

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

```sql
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コード ###

```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カラム

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

JSON

```sql
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

```sql
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 |
----------------------------------------------|---------:|-------:|-------:|
文字列::完全一致 <br> (CITY = '江東区')       |     14.9 |  137.9 |   29.0 |
文字列::前方一致 <br> (CITY LIKE '千代田区%') |     18.7 |  139.3 |   34.5 |
文字列::中間一致 <br> (CITY LIKE '%中央%')    |     23.0 |  138.7 |   31.2 |
文字列::後方一致 <br> (CITY LIKE '%港区')     |     17.9 |  140.0 |   35.3 |
文字列::否定     <br> (CITY <> '新宿区')      |    111.2 |  228.5 |  131.6 |
数値::一致       <br> (IDX = 1001)            |     10.3 |  140.0 |   35.0 |
数値::範囲一致   <br> (IDX < 100)             |     12.3 |   40.3 |  144.5 |
数値::否定       <br> (IDX <> 12345)          |    119.5 |  237.0 |  153.6 |

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

```sql
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 |
----------------------------------------------|---------:|-------:|-------:|
文字列::完全一致 <br> (CITY = '江東区')       |      0.8 |    0.4 |    0.4 |
文字列::前方一致 <br> (CITY LIKE '千代田区%') |     19.3 |  140.7 |   32.2 |
文字列::中間一致 <br> (CITY LIKE '%中央%')    |     16.1 |  140.0 |   32.3 |
文字列::後方一致 <br> (CITY LIKE '%港区')     |     16.7 |  142.0 |   37.0 |
文字列::否定     <br> (CITY <> '新宿区')      |    113.7 |  231.5 |  139.9 |
数値::一致       <br> (IDX = 1001)            |      0.1 |    0.1 |    0.1 |
数値::範囲一致   <br> (IDX < 100)             |      0.2 |    0.4 |    0.4 |
数値::否定       <br> (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:
----

* [郵便番号データの説明 - 日本郵便](http://www.post.japanpost.jp/zipcode/dl/readme.html)
* [PostgreSQL 9.3 の JSON サポートについて（長いよッ） - Qiita](http://qiita.com/kumazo@github/items/483f47360f8b61a9fbb9)
* [SIOS "OSSよろず" ブログ出張所: PostgreSQL 9.3 の JSON データのインデックス](http://sios-oss.blogspot.jp/2014/01/postgresql-93-json.html)
* [PostgreSQLのJSON型とhstore型のパフォーマンス — still deeper](http://chopl.in/blog/2013/04/20/json_vs_hstore.html)

あとがき
-------

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