PostgreSQLのKVS hstoreの紹介 ちょっとだけスキーマレスなデータを管理してみよう 2011.3.12（追記版） NTT Software Corporation ＯＳＳプラットフォーム事業ユニット　原田登志 http://www.ntts.co.jp/ http://www.nttsoft.com/

目次 はじめに KVSとは hstore 他RDBMSにおけるKVS おわりに 参考 hstoreとは 使いどころ インストール 定義 格納 検索 更新 インデックス その他 他RDBMSにおけるKVS おわりに 参考

はじめに なぜこのテーマなのか この資料の目的 最近、何かと話題となるKVS。 PostgreSQLでもそれっぽい機能があります。 単に私が知らなかったので、勉強して社内の若手向けの勉強会資料としてまとめたものです。 なので、そんなに高度な内容ではないです・・・ この資料の目的 hstoreって機能の紹介＆応用を考えるきっかけ 「便利そうだな～」 「こういう場面で使えそうだな」 ソース解析とか、深いところまではやりません。

KVSとは KVSとは 言うまでもなく、KVSはRDBMSではない。 “Key Value Store” の略称 キーと値の組を管理するシンプルなデータベース スクリプト系言語（awk, perl, ・・・）ではお馴染みの連想配列みたいなもの。 シンプルなアクセス方法 キーと値をセットにして格納 キーを元に値を取り出し Googleで使われているBigtableもこのKVSの延長上にある（と言っていいかな？）。 言うまでもなく、KVSはRDBMSではない。 RDBMSとは用途は違う。（RDBMS：CA, NoSQL：AP or CP） KVSがRDBMSに取って代わるものでもない。 だけど、RDBMSと組み合わせて使うと便利なケースもあるかも・・・ C：Consistency （一貫性） A：Availability（可用性） P：Partition Tolerance （分割耐性）

hstore – hstoreとは hstoreとは hstoreモジュールが提供するもの PostgreSQL上でKVSを実現するためのcontribモジュール PostgreSQL 8.3以降で使用可能 8.4と9.0のhstoreはデータ形式の互換がないので、メジャーバージョンアップ時に注意が必要。 hstoreモジュールが提供するもの テキスト型で表現されたキーと値を管理するhstore型 hstore型を扱う演算子 hstore型を扱う補助関数 hstore型に対するGiST/GINインデックス

hstore – 使いどころ どういうときにhstoreのご利益があるのか。 半構造データの格納に使えるのでは？ 例 別にPostgreSQL上でBigtableを真似したい訳ではない。 半構造データの格納に使えるのでは？ 一般のRDBのモデルは「構造化データ」 スキーマレスなXMLなどのモデルは「非構造データ」 構造化データ＋一部非構造のデータを持つ⇒半構造データ ⇒このようなときにhstoreは使えるのでは？ 例 イベント情報のデータベースを作りたい 共通的な属性 イベント名、開催場所、開催日・・・ 内容によって変わる付加的な属性 コンサートならアーティスト情報や席毎の料金 マラソン大会なら参加料

ブログなどのタグなんかには向いているけど・・・ hstore – 使いどころ 半構造データの管理にも、いろんな実現方法はあるが・・・ 単純に１枚の表で表現 付加的な属性が増えたら、いちいちカラムを増やすことに・・・ 別テーブル化 付加的な属性の属性名と値を管理するテーブルを別テーブルにする 当然結合が発生する・・・ 結果の重複排除（OR条件で複数ヒットした場合など）が必要。 配列 PostgreSQLの配列は可変配列なので、付加的な属性が幾ら増えても要素として追加は出来る が、属性名ではなく配列の要素番号でアクセスする必要がある・・・。 XML PostgreSQLのXML型に格納 表現能力は一番高い XMLパースやXPathアクセスが必要。何気に重い。APも結構面倒かも・・・。 ブログなどのタグなんかには向いているけど・・・ 階層構造/順序も扱えるが・・・

メニューが増える度に、カラムを追加していくのはあんまりだ。 hstore – 使いどころ もちろん、普通はこんな 設計はしないけど・・・ 単純に１枚の表で表現 店舗ID 店舗名 住所 ラーメン チャーシューメン 1 鶴廣 横浜市神奈川区XXX 550 700 2 龍王 横浜市西区ZZZ 450 NULL 店舗を追加したら、新しいメニューが増えた 店舗ID 店舗名 住所 ラーメン チャーシューメン サンマーメン 1 鶴廣 横浜市神奈川区XXX 550 700 NULL 2 龍王 横浜市西区ZZZ 450 3 玉泉亭 横浜市西区DDD 500 メニューが増える度に、カラムを追加していくのはあんまりだ。

hstore – 使いどころ 別テーブル化 店舗を追加したら、新しいメニューが増えた 普通はこうするかな・・・ 別テーブル化 関連 店舗ID 店舗名 住所 1 鶴廣 横浜市神奈川区XXX 2 龍王 横浜市西区ZZZ ID 店舗ID メニュー名 値段 1 ラーメン 550 2 チャーシューメン 700 3 450 店舗を追加したら、新しいメニューが増えた 関連 店舗ID 店舗名 住所 1 鶴廣 横浜市神奈川区XXX 2 龍王 横浜市西区ZZZ 3 玉泉亭 横浜市西区DDD ID 店舗ID メニュー名 値段 1 ラーメン 550 2 チャーシューメン 700 3 450 4 5 サンマーメン スキーマ変更は不要だけど、検索時に結合が入ってしまう。高速化のためには、なるべく結合は避けたい・・・

hstore – 使いどころ 共通的な項目が多く、一部に非構造的な情報を持つ場合、共通的な項目をPostgreSQLの普通の型で、非構造的な情報をhstore型で管理する。 （あまり良い例ではないかもしれないが）飲食店のメニューと値段などを管理する例 test=# \d ramen Table "public.ramen“ Column | Type | Modifiers ---------+---------+----------- id | integer | name | text | address | text | menu | hstore | test=# メニューやトッピングは各店によって数も種類もまちまちなので、 通常のカラムでは管理しにくい。 test=# select * from ramen; id | name | address | menu ----+--------------------+---------------+------------------------------------------------------------ 1 | 王泉亭 | 横浜市西区XXX | “タンメン”=>“600”, “サンマーメン”=>“700” 2 | 杉村家 | 横浜市西区ZZZ | “キャベツ”=>“50”, “ラーメン”=>“700”, “チャーシュー”=>“200” 3 | ラーメン三郎関内店 | 横浜市中区AAA | “小”=>“650”, “小ぶた”=>“700”, “汁なし”=>“750”

hstore – インストール hstoreはcontribモジュールとして提供されている。 ソースビルドなら・・・ cd <ビルドディレクトリ>/contrib/hstore make make install RPMインストールなら・・・（RHEL5 64bit OSの場合） rpm –ivh postgresql90-contrib-9.0.0-1PGDG.rhel5.x86_64.rpm 使いたいデータベースにhstoreを登録する。 psql <データベース名> –f <インストール先>/share/contrib/hstore.sql 9.0からplpgsqlの場合、createlangで言語登録不要となったので少し楽になった。 8.4までは事前に“createlang plpgsql データベース名“による言語登録が事前に必要。

hstore – 定義 カラムのデータ型としてhstoreを記述する。 hstore固有の制約は特にない。 TEXT型ベースで実装されているので、制約はTEXT型にならう。 例：ラーメン店のメニューとトッピングをhstoreで管理する。 test=# CREATE TABLE ramen (id int, name text, address text, menu hstore, topping hstore); CREATE TABLE test=# \d+ ramen Table "public.ramen“ Column | Type | Modifiers | Storage | Description ---------+---------+-----------+----------+------------- id | integer | | plain | name | text | | extended | address | text | | extended | menu | hstore | | extended | topping | hstore | | extended | Has OIDs: no test=# 普通にhstore型として 定義するだけ。 複数定義も可能。 Storage格納戦略のデフォルト値は TEXT型と同じ。 TOAST圧縮も同じように適用される（はず）

hstore – 格納 格納する場合には、hstore型の外部表現を意識する必要がある。 形式： key => value { [, key => value] }... 日本語キー、値もOK 例 '味噌ラーメン => 600' '味噌 => 600, 醤油 => 550, 塩 => 550' '"味噌 白" => 600' ・・・引用すればOK. INSERT文で格納するときに、上記外部表現を指定して格納。 厳密にはhstoreでcastするが、しなくても格納は可能。 test=# insert into ramen values (1, ‘亀廣', '横浜市神奈川区XXX', 'ラーメン => 500, サンマーメン => 600, ウマニそば => 750'::hstore ); INSERT 0 1 test=#

hstore – 格納（細かいこと） キーや値に「空白」を含める場合には二重引用符が必要 日本語キー、値もOK（UTF-8で確認） ‘味噌 白 => 600’ ・・・これはダメ。構文エラーになる。 ‘“味噌 白” => 600’ ・・・引用すればOK. 日本語キー、値もOK（UTF-8で確認） キーが重複した場合、どれかの一つのキーのみが格納される。 エラーにはならない。 どれが残るのかは保障されない。→重複キーの格納は避けるべし。 組み合わせの順序は保障されない。 つまり、格納時にn番目の組に入れたから、取り出し時にもn番目になるとは限らない。配列と組み合わせる場合には注意が必要。 insert into ramen values (6, ‘だんだん’, ‘横浜市神奈川区YYY’,‘タンタンメン => 700, タンタンメン => 600’::hstore, ‘ネギ => 100, ネギ => 50’::hstore); INSERT 0 1 select name, menu from ramen; name | menu | topping ----------+-----------------------+--------------- だんだん | “タンタンメン”=>“700” | “ネギ”=>“100” (1 row) キーが重複

keyに対応する値がない場合は NULLが返却される。 hstore – 検索 単にカラムを指定した場合には、外部表現が取得される。 このときには一律文字列前後に二重引用符がつく。 取り出したカラムから、さらにkeyを使って値を取り出す。 test=# select name, menu from ramen; name | menu ----------+--------------------------------------------------------------- 亀廣 | “ラーメン”=>“500”, “ウマニそば”=>“750”, “サンマーメン”=>“600” 杉村家 | “ラーメン”=>“650”, “チャーシュー丼”=>“500” 王泉亭 | “ラーメン”=>“600”, “サンマーメン”=>“700” 百家 | “ラーメン”=>“650” だんだん | “タンタンメン”=>“700”, “ミソラーメン”=>“700” (5 rows) test=# select name, menu->‘ラーメン’ from ramen; name | ?column? ----------+---------- 亀廣 | 500 杉村家 | 650 王泉亭 | 600 百家 | 650 だんだん | (5 rows) keyに対応する値がない場合は NULLが返却される。

hstore – 検索 WHERE句にも当然使用可能。 toppingに「キャベツ」を含む店を探そう。 “?” というオペレータを使うと「～というキーを含む」ものを評価する。 SELECT name, topping FROM ramen; name | topping ----------+-------------------------------------------------------- 亀廣 | 杉村家 | “ネギ”=>“100”, “キャベツ”=>“50”, “チャーシュー”=>“200” 王泉亭 | 百家 | “ネギ”=>“50”, “チャーシュー”=>“100” だんだん | “ニラ”=>“50”, “ネギ”=>“100” (5 rows) SELECT name, topping FROM ramen WHERE topping ? ‘キャベツ’; name | topping --------+-------------------------------------------------------- 杉村家 | “ネギ”=>“100”, “キャベツ”=>“50”, “チャーシュー”=>“200” (1 row)

hstore – 検索 複数のキーをALL/ANY的に評価する場合 menuに「ラーメン」「サンマーメン」を含む店を探そう。 こっちは、サンマーメン、ウマニそばの 全てを含んでいるものを真と評価する。 SELECT name, menu FROM ramen WHERE menu ?& ARRAY[‘サンマーメン’,‘ウマニそば’]; name | menu ------+--------------------------------------------------------------- 亀廣 | “ラーメン”=>“500”, “ウマニそば”=>“750”, “サンマーメン”=>“600” (1 row) SELECT name, menu FROM ramen WHERE menu ?| ARRAY[‘サンマーメン’,‘ウマニそば’]; name | menu --------+--------------------------------------------------------------- 亀廣 | “ラーメン”=>“500”, “ウマニそば”=>“750”, “サンマーメン”=>“600” 王泉亭 | “ラーメン”=>“600”, “サンマーメン”=>“700” (2 rows) こっちは、サンマーメン、ウマニそばの どれかを含んでいるから真と評価される。

hstore – 検索 値を評価するのはちょっと面倒・・・？ 検索例 一旦、avals()で値のみを配列で取り出して、それを配列演算子で評価する必要がある。 検索例 例１：500円で食べられるメニューが1つでもある店を探そう。 例２：メニュー全てが650円以内で食べられる店を探そう。 SELECT name, menu FROM ramen WHERE 500 >= ANY (avals(menu)::int[]) ; name | menu --------+--------------------------------------------------------------- 亀廣 | “ラーメン”=>“500”, “ウマニそば”=>“750”, “サンマーメン”=>“600” 杉村家 | “ラーメン”=>“650”, “チャーシュー丼”=>“500” (2 rows) SELECT name, menu FROM ramen WHERE 650 >= ALL (avals(menu)::int[]) ; name | menu --------+-------------------------------------------- 杉村家 | “ラーメン”=>“650”, “チャーシュー丼”=>“500” 百家 | “ラーメン”=>“650” (2 rows)

hstore – 検索 hstoreの検索を容易にするための演算子や関数は他にもありますが、今回は説明を割愛。 興味がある人はここを見てください。 http://www.postgresql.jp/document/current/html/hstore.html#AEN120304 有効なキーを入手するためのクエリ、キー数取得など、統計的な情報をとりたい場合のクエリ例も文書に書いてあります。 http://www.postgresql.jp/document/current/html/hstore.html#AEN120736

hstore – 更新 hstore型に格納されたキー＆値を更新することも可能。 追加 更新 削除 “||” 演算子によって追加したいキー＆値の組を指定する。 更新 “||” 演算子によって更新したいキー＆値の組を指定する。 要するに更新というのは、既に存在しているキーを追加するのと同じ。 存在しないキーの場合には追加される。 削除 “-”演算子を使う 上記の演算子はあくまでもhstore型に対する操作であり、永続的に更新したいなら、UPDATE文を発行する必要がある。

hstore – 更新 hstore型に対する、追加・更新・削除の例。 実際にデータベース上のhstore型を更新する例 検索結果menuに対して、「味噌ラーメン」を追加し、「サンマーメン」の値段を600円から650円に変更し、「ウマニそば」を削除する。 実際にデータベース上のhstore型を更新する例 上記例をUPDATE文に組み込んでみた例。 SELECT menu FROM ramen WHERE name = ‘亀廣’; menu --------------------------------------------------------------- “ラーメン”=>“500”, “ウマニそば”=>“750”, “サンマーメン”=>“600” (1 row) SELECT ((menu || ‘味噌ラーメン => 600’) || ‘サンマーメン => 650’) - ‘ウマニそば’::text FROM ramen WHERE name = ‘亀廣’; ?column? ----------------------------------------------------------------- “ラーメン”=>“500”, “サンマーメン”=>“650”, “味噌ラーメン”=>“600” (1 row) UPDATE ramen SET menu = ((menu || ‘味噌ラーメン => 600’) || ‘サンマーメン => 650’) - ‘ウマニそば’::text WHERE name = ‘亀廣';

hstore – インデックス 大量のデータからの高速な検索にはインデックスは欠かせない。 htore型にもインデックスは利用可能 配列型と同じようにGiST/GINインデックスを用いる。 GiST：汎用検索ツリー（任意のインデックス構築の基盤となるもの） GIN：汎用転置インデックス （組み合わせ集合を格納する） 詳細はPostgreSQLマニュアルを見てください。 http://www.postgresql.jp/document/current/html/gist.html http://www.postgresql.jp/document/current/html/gin.html 重要なのは、通常のB-treeインデックスではないということ。

hstore – インデックス インデックス定義 GINとGiSTのどちらを使えばいいのか？ さほど特別な書き方はしなくてもいい。 対象となる hstore 型のカラムを指定して、USING句でginあるいはgistを指定するのみ。 GINインデックスの例 GiSTインデックスの例 GINとGiSTのどちらを使えばいいのか？ 非常に大雑把に言えば（注：数値は目安） GINは検索が3倍高速 GiSTはインデックス作成・更新が3倍高速 要件として検索と更新のどちらを優先するか、だと思う。 詳細はPostgreSQLの全文検索のところを参照。 CREATE INDEX item_socre_gin ON item USING gin (score); CREATE INDEX item_socre_gist ON item USING gist (score);

この推定値(rows)は単純にrow数/1000という単純な方法で推測している？。 hstore – インデックス 効果をEXPLAINで確認 モデル レコード数は1,000,000件 1レコード内のhstoreには、1～50個のキー＆値を持つ項目が格納される。 1つのキーには1～100までのランダムな値が設定される。 インデックスなし インデックスあり（GINの場合） EXPLAIN ANALYZE SELECT name, score FROM item WHERE (score ? 'U8999') ORDER by name;; QUERY PLAN -------------------------------------------------------------------------------------------------------------- Seq Scan on item (cost=0.00..113558.02 rows=4626 width=64) (actual time=36.226..24248.113 rows=236 loops=1) Filter: (score ? 'U8999'::text) Total runtime: 24248.433 ms (3 rows) この推定値(rows)は単純にrow数/1000という単純な方法で推測している？。 EXPLAIN ANALYZE SELECT name, score FROM item WHERE (score ? 'U8999') ORDER by name;; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Sort (cost=3829.62..3832.12 rows=1000 width=64) (actual time=3135.095..3135.249 rows=236 loops=1) Sort Key: name Sort Method: quicksort Memory: 43kB -> Bitmap Heap Scan on item (cost=193.82..3779.79 rows=1000 width=64) (actual time=52.080..3133.704 rows=236 loops=1) Recheck Cond: (score ? 'U8999'::text) -> Bitmap Index Scan on item_socre_gin (cost=0.00..193.57 rows=1000 width=0) (actual time=52.002..52.002 rows=236 loops=1) Index Cond: (score ? 'U8999'::text) Total runtime: 3135.448 ms (8 rows)

hstore – インデックス 参考：同じデータをRDBモデルにマッピングして、同等のクエリを発行してプランを確認してみた。 EXPLAIN ANALYZE select distinct n.name, s.key, s.value from item_name as n, item_score s where n.id = s.id AND s.key = 'U8999' ORDER BY n.name; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------------------ Unique (cost=512107.10..513357.07 rows=124997 width=75) (actual time=24466.761..24467.276 rows=236 loops=1) -> Sort (cost=512107.10..512419.59 rows=124997 width=75) (actual time=24466.759..24466.913 rows=236 loops=1) Sort Key: n.name, s.value Sort Method: quicksort Memory: 43kB -> Hash Join (cost=32789.00..490416.15 rows=124997 width=75) (actual time=4642.245..24465.615 rows=236 loops=1) Hash Cond: (s.id = n.id) -> Seq Scan on item_score s (cost=0.00..447626.70 rows=124997 width=68) (actual time=22.055..22215.710 rows=236 loops=1) Filter: (key = 'U8999'::text) -> Hash (cost=15406.00..15406.00 rows=1000000 width=15) (actual time=1888.252..1888.252 rows=1000000 loops=1) Buckets: 4096 Batches: 64 Memory Usage: 750kB -> Seq Scan on item_name n (cost=0.00..15406.00 rows=1000000 width=15) (actual time=0.019..801.829 rows=1000000 loops=1) Total runtime: 24467.604 ms (12 rows) モデルや検索パターンによっては、hstoreを使ったほうが、 RDB方式で結合するよりも高速になるケースもある。

追記 - valueに対するインデックス 社内の勉強会の質疑（P.23参照）の中で 「valueに対するインデックスは有効なのか？」 という問い合わせがあった。とりあえず、以下を回答。 hstore型カラムに対するインデックスはkeyのみが対象だと思われる。 valueは配列化したものをGINインデックスに設定しないと有効ではないだろう。 上記回答が本当に正しいのか追試してみた。 valueを条件にするとhstoreカラムに対して張ったGINインデックスは使われなかった。 avals()使ってvalue配列を抽出する関数を作成すれば、valueを条件とするクエリでもインデックスは使用可能。 クエリの書き方には一工夫は必要（配列同士を比較するなど）

追記 - valueに対するインデックス 前ページの検証ログ test=# \d item Table "public.item“ Column | Type | Modifiers --------+---------+----------- id | integer | name | text | score | hstore | Indexes: "item_socre_gist" gist (score) test=# EXPLAIN ANALYZE select id ,avals(score) FROM item WHERE ARRAY[ '70'] <@ (avals(score)::text[]) ; QUERY PLAN --------------------------------------------------------------------------------------------------------------- Seq Scan on item (cost=0.00..70737.45 rows=1000 width=406) (actual time=7.328..7638.879 rows=214022 loops=1) Filter: ('{70}'::text[] <@ avals(score)) Total runtime: 7800.522 ms (3 rows) test=# CREATE INDEX item_score_value on item USING GIN ((avals(score)::text[])) ; CREATE INDEX test=# EXPLAIN ANALYZE select id ,avals(score) FROM item WHERE ARRAY[ '70'] <@ (avals(score)::text[]) ; QUERY PLAN ------------------------------------------------------------------------------------------------------------------------------------ Bitmap Heap Scan on item (cost=72.64..3661.10 rows=1000 width=406) (actual time=674.085..5944.916 rows=214022 loops=1) Recheck Cond: ('{70}'::text[] <@ avals(score)) -> Bitmap Index Scan on item_score_value (cost=0.00..72.39 rows=1000 width=0) (actual time=658.730..658.730 rows=214022 loops=1) Index Cond: ('{70}'::text[] <@ avals(score)) Total runtime: 6087.999 ms (5 rows) test=# hstore型へのインデックス設定ではvalueを条件にした場合、インデックスは使われない。 “1 = ANY(avals(socre)::text[]” のような条件の書き方だと、 インデックスを使ってくれない・・・

hstore – その他 hstoreは運用面での制約はないのか？ トランザクションは？ バックアップは？ PostgreSQLの普通のトランザクション機能の中で管理してくれます。 バックアップは？ 普通のデータと同じようにバックアップ・リストアも可能です。 論理ダンプ(pu_dump）／リストアも問題ありません Streaming Replicationは？ 結局データベースの更新は普通のDMLで更新するので、問題ありません。 VACUUMは？ 普通のデータ型と同様、特別考慮することはありません。 運用面では普通のデータ型と同じように考えて問題ないです。

他RDBMSにおけるKVS 他RDBMSの代表的な製品におけるKVS対応状況をWebでざっと見てみました。 Oracle MySQL DB2 各製品に詳しい方からの情報も希望します・・・ Oracle Oracle Application Expressという開発環境の内部DBとしてKVSを利用。ユーザ向けインタフェースとしては公開していない？ MySQL MyCassandra – Apache CassandraのストレージにMySQLを使う DB2 ？

おわりに ご清聴ありがとうございました。 本資料がhstoreの理解の助けになれば幸いです。 追記 半構造のデータを管理するような要件にも、PostgreSQLは適用できます。 追記 最近、グラフDBというものをちょっと触りはじめていて、 これを、もしPostgreSQL上で実装する場合、 各ノード/関連が持つプロパティなどはhstoreが適用できるかな ・・・と思ってます。

参考 PostgreSQL 9.0.1 ソース PostgreSQL文書（9.0.1） - F.13. hstore http://www.postgresql.jp/document/current/html/hstore.html PGPortal - hstore https://oss-gzk.isl.ntt.co.jp/pgportal/dbms/hstore Let’s Postgres - ログ解析にhstoreを利用してみよう http://lets.postgresql.jp/documents/technical/log_analysis/2