PostgreSQLのKVS hstoreの紹介

Presentation on theme: "PostgreSQLのKVS hstoreの紹介"— Presentation transcript:

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

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

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

4 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 （分割耐性）

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

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

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

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

9 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 サンマーメン スキーマ変更は不要だけど、検索時に結合が入ってしまう。高速化のためには、なるべく結合は避けたい・・・

10 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 | 王泉亭 | 横浜市西区XXX | “タンメン”=>“600”, “サンマーメン”=>“700” 2 | 杉村家 | 横浜市西区ZZZ | “キャベツ”=>“50”, “ラーメン”=>“700”, “チャーシュー”=>“200” 3 | ラーメン三郎関内店 | 横浜市中区AAA | “小”=>“650”, “小ぶた”=>“700”, “汁なし”=>“750”

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

12 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圧縮も同じように適用される（はず）

13 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=#

14 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) キーが重複

15 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が返却される。

16 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)

17 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) こっちは、サンマーメン、ウマニそばの どれかを含んでいるから真と評価される。

18 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)

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

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

21 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 = ‘亀廣';

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

23 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);

24 この推定値(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= rows=4626 width=64) (actual time= rows=236 loops=1) Filter: (score ? 'U8999'::text) Total runtime: ms (3 rows) この推定値(rows)は単純にrow数/1000という単純な方法で推測している？。 EXPLAIN ANALYZE SELECT name, score FROM item WHERE (score ? 'U8999') ORDER by name;; QUERY PLAN Sort (cost= rows=1000 width=64) (actual time= rows=236 loops=1) Sort Key: name Sort Method: quicksort Memory: 43kB -> Bitmap Heap Scan on item (cost= rows=1000 width=64) (actual time= rows=236 loops=1) Recheck Cond: (score ? 'U8999'::text) > Bitmap Index Scan on item_socre_gin (cost= rows=1000 width=0) (actual time= rows=236 loops=1) Index Cond: (score ? 'U8999'::text) Total runtime: ms (8 rows)

25 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= rows= width=75) (actual time= rows=236 loops=1) -> Sort (cost= rows= width=75) (actual time= rows=236 loops=1) Sort Key: n.name, s.value Sort Method: quicksort Memory: 43kB > Hash Join (cost= rows= width=75) (actual time= rows=236 loops=1) Hash Cond: (s.id = n.id) > Seq Scan on item_score s (cost= rows= width=68) (actual time= rows=236 loops=1) Filter: (key = 'U8999'::text) > Hash (cost= rows= width=15) (actual time= rows= loops=1) Buckets: Batches: 64 Memory Usage: 750kB > Seq Scan on item_name n (cost= rows= width=15) (actual time= rows= loops=1) Total runtime: ms (12 rows) モデルや検索パターンによっては、hstoreを使ったほうが、 RDB方式で結合するよりも高速になるケースもある。

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

27 追記 - 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= rows=1000 width=406) (actual time= rows= loops=1) Filter: ('{70}'::text[] avals(score)) Total runtime: 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= rows=1000 width=406) (actual time= rows= loops=1) Recheck Cond: ('{70}'::text[] avals(score)) -> Bitmap Index Scan on item_score_value (cost= rows=1000 width=0) (actual time= rows= loops=1) Index Cond: ('{70}'::text[] avals(score)) Total runtime: ms (5 rows) test=# hstore型へのインデックス設定ではvalueを条件にした場合、インデックスは使われない。 “1 = ANY(avals(socre)::text[]” のような条件の書き方だと、 インデックスを使ってくれない・・・

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

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

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

31 参考 PostgreSQL 9.0.1 ソース PostgreSQL文書（9.0.1） - F.13. hstore
PGPortal - hstore Let’s Postgres - ログ解析にhstoreを利用してみよう