PowerPoint プレゼンテーション

PostgreSQLのKVS
hstoreの紹介
ちょっとだけスキーマレスなデータを管理してみよう
2011.3.12（追記版）
NTT Software Corporation
ＯＳＳプラットフォーム事業ユニット原田登志
http://www.ntts.co.jp/
http://www.nttsoft.com/
目次
• はじめに
• KVSとは
• hstore
–
–
–
–
–
–
–
–
–
hstoreとは
使いどころ
インストール
定義
格納
検索
更新
インデックス
その他
• 他RDBMSにおけるKVS
• おわりに
• 参考
2
はじめに
• なぜこのテーマなのか
– 最近、何かと話題となるKVS。
– PostgreSQLでもそれっぽい機能があります。
– 単に私が知らなかったので、勉強して社内の若手向けの勉
強会資料としてまとめたものです。
• なので、そんなに高度な内容ではないです・・・
• この資料の目的
– hstoreって機能の紹介＆応用を考えるきっかけ
• 「便利そうだな～」
• 「こういう場面で使えそうだな」
– ソース解析とか、深いところまではやりません。
3
KVSとは
• KVSとは
–
–
–
–
“Key Value Store” の略称
キーと値の組を管理するシンプルなデータベース
スクリプト系言語（awk, perl, ・・・）ではお馴染みの連想配列みたいなもの。
シンプルなアクセス方法
• キーと値をセットにして格納
• キーを元に値を取り出し
– Googleで使われているBigtableもこのKVSの延長上にある（と言っていい
かな？）。
• 言うまでもなく、KVSはRDBMSではない。
C：Consistency （一貫性）
A：Availability（可用性）
P：Partition Tolerance （分割耐性）
– RDBMSとは用途は違う。（RDBMS：CA, NoSQL：AP or CP）
– KVSがRDBMSに取って代わるものでもない。
– だけど、RDBMSと組み合わせて使うと便利なケースもあるかも・・・
4
hstore – hstoreとは
• hstoreとは
– PostgreSQL上でKVSを実現するためのcontribモジュール
– PostgreSQL 8.3以降で使用可能
• 8.4と9.0のhstoreはデータ形式の互換がないので、メジャーバージョン
アップ時に注意が必要。
• hstoreモジュールが提供するもの
–
–
–
–
テキスト型で表現されたキーと値を管理するhstore型
hstore型を扱う演算子
hstore型を扱う補助関数
hstore型に対するGiST/GINインデックス
5
hstore – 使いどころ
• どういうときにhstoreのご利益があるのか。
– 別にPostgreSQL上でBigtableを真似したい訳ではない。
• 半構造データの格納に使えるのでは？
– 一般のRDBのモデルは「構造化データ」
– スキーマレスなXMLなどのモデルは「非構造データ」
– 構造化データ＋一部非構造のデータを持つ⇒半構造データ
⇒このようなときにhstoreは使えるのでは？
• 例
– イベント情報のデータベースを作りたい
– 共通的な属性
• イベント名、開催場所、開催日・・・
– 内容によって変わる付加的な属性
• コンサートならアーティスト情報や席毎の料金
• マラソン大会なら参加料
6
hstore – 使いどころ
• 半構造データの管理にも、いろんな実現方法はあるが・・・
• 単純に１枚の表で表現
– 付加的な属性が増えたら、いちいちカラムを増やすことに・・・
• 別テーブル化
– 付加的な属性の属性名と値を管理するテーブルを別テーブルにする
– 当然結合が発生する・・・
– 結果の重複排除（OR条件で複数ヒットした場合など）が必要。
• 配列
– PostgreSQLの配列は可変配列なので、付加的な属性が幾ら増えても要
素として追加は出来る
– が、属性名ではなく配列の要素番号でアクセスする必要がある・・・。
• XML
ブログなどのタグなんかに
は向いているけど・・・
– PostgreSQLのXML型に格納
階層構造/順序も扱えるが・・・
– 表現能力は一番高い
– XMLパースやXPathアクセスが必要。何気に重い。APも結構面倒かも・・・。
7
hstore – 使いどころ
• 単純に１枚の表で表現
もちろん、普通はこんな
設計はしないけど・・・
店舗ID
店舗名
住所
ラーメン
チャーシューメン
1
鶴廣
横浜市神奈川区XXX
550
700
2
龍王
横浜市西区ZZZ
450
NULL
店舗を追加したら、新しいメニューが増えた
店舗ID
店舗名
住所
ラーメン
チャーシューメン
サンマーメン
1
鶴廣
横浜市神奈川区XXX
550
700
NULL
2
龍王
横浜市西区ZZZ
450
NULL
NULL
3
玉泉亭
横浜市西区DDD
500
NULL
700
8
メニューが増える度に、
カラムを追加していくの
はあんまりだ。
hstore – 使いどころ
普通はこうするかな・・・
• 別テーブル化
関連
店舗ID
店舗名
住所
ID
店舗ID
メニュー名
値段
1
鶴廣
横浜市神奈川区XXX
1
1
ラーメン
550
2
龍王
横浜市西区ZZZ
2
1
チャーシューメン
700
3
2
ラーメン
450
店舗を追加したら、新しいメニューが増えた
関連
店舗ID
店舗名
住所
ID
店舗ID
メニュー名
値段
1
鶴廣
横浜市神奈川区XXX
1
1
ラーメン
550
2
龍王
横浜市西区ZZZ
2
1
チャーシューメン
700
3
玉泉亭
横浜市西区DDD
3
2
ラーメン
450
4
3
ラーメン
550
5
3
サンマーメン
700
スキーマ変更は不要だけど、検索時に結合が入ってしまう。
高速化のためには、なるべく結合は避けたい・・・
9
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”
10
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 データベース名“による言語登録が事前に必要。
11
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
普通にhstore型として
---------+---------+-----------+----------+------------定義するだけ。
id
| integer |
| plain
|
複数定義も可能。
name
| text
|
| extended |
address | text
|
| extended |
menu
| hstore |
| extended |
topping | hstore |
| extended |
Has OIDs: no
test=#
Storage格納戦略のデフォルト値は
TEXT型と同じ。
TOAST圧縮も同じように適用される（はず）
12
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=#
13
hstore – 格納（細かいこと）
• キーや値に「空白」を含める場合には二重引用符が必要
– ‘味噌白 => 600’
– ‘“味噌白” => 600’
・・・これはダメ。構文エラーになる。
・・・引用すればOK.
• 日本語キー、値もOK（UTF-8で確認）
• キーが重複した場合、どれかの一つのキーのみが格納される。
– エラーにはならない。
– どれが残るのかは保障されない。→重複キーの格納は避けるべし。
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)
• 組み合わせの順序は保障されない。
– つまり、格納時にn番目の組に入れたから、取り出し時にもn番目になると
は限らない。配列と組み合わせる場合には注意が必要。
14
hstore – 検索
• 単にカラムを指定した場合には、外部表現が取得される。
– このときには一律文字列前後に二重引用符がつく。
test=# select name, menu from ramen;
name
|
menu
----------+--------------------------------------------------------------亀廣
| “ラーメン”=>“500”, “ウマニそば”=>“750”, “サンマーメン”=>“600”
杉村家
| “ラーメン”=>“650”, “チャーシュー丼”=>“500”
王泉亭
| “ラーメン”=>“600”, “サンマーメン”=>“700”
百家
| “ラーメン”=>“650”
だんだん | “タンタンメン”=>“700”, “ミソラーメン”=>“700”
(5 rows)
• 取り出したカラムから、さらにkeyを使って値を取り出す。
test=# select name, menu->‘ラーメン’
name
| ?column?
----------+---------亀廣
| 500
杉村家 | 650
王泉亭
| 600
百家
| 650
だんだん |
(5 rows)
15
from ramen;
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)
16
hstore – 検索
• 複数のキーをALL/ANY的に評価する場合
– menuに「ラーメン」「サンマーメン」を含む店を探そう。
• “?&” というオペレータを使うと「全てのキーを含む」ものを評価する。（ALL)
• “?|” というオペレータを使うと「どれかのキーを含む」ものを評価する。（ANY)
こっちは、サンマーメン、ウマニそばの
全てを含んでいるものを真と評価する。
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)
こっちは、サンマーメン、ウマニそばの
どれかを含んでいるから真と評価される。
17
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)
18
hstore – 検索
• hstoreの検索を容易にするための演算子や関数は他にもありま
すが、今回は説明を割愛。
– 興味がある人はここを見てください。
– http://www.postgresql.jp/document/current/html/hstore.html#AEN120304
• 有効なキーを入手するためのクエリ、キー数取得など、統計的
な情報をとりたい場合のクエリ例も文書に書いてあります。
– http://www.postgresql.jp/document/current/html/hstore.html#AEN120736
19
hstore – 更新
• hstore型に格納されたキー＆値を更新することも可能。
• 追加
– “||” 演算子によって追加したいキー＆値の組を指定する。
• 更新
– “||” 演算子によって更新したいキー＆値の組を指定する。
– 要するに更新というのは、既に存在しているキーを追加するのと同じ。
– 存在しないキーの場合には追加される。
• 削除
– “-”演算子を使う
• 上記の演算子はあくまでもhstore型に対する操作であり、永続
的に更新したいなら、UPDATE文を発行する必要がある。
20
hstore – 更新
• hstore型に対する、追加・更新・削除の例。
– 検索結果menuに対して、「味噌ラーメン」を追加し、「サンマーメン」の値段
を600円から650円に変更し、「ウマニそば」を削除する。
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)
• 実際にデータベース上のhstore型を更新する例
– 上記例をUPDATE文に組み込んでみた例。
UPDATE ramen
SET menu = ((menu || ‘味噌ラーメン => 600’) || ‘サンマーメン => 650’) - ‘ウマニそば’::text
WHERE name = ‘亀廣';
21
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インデックスではないということ。
22
hstore – インデックス
• インデックス定義
– さほど特別な書き方はしなくてもいい。
– 対象となる hstore 型のカラムを指定して、USING句でginあるいはgistを
指定するのみ。
– GINインデックスの例
CREATE INDEX item_socre_gin ON item USING gin (score);
– GiSTインデックスの例
CREATE INDEX item_socre_gist ON item USING gist (score);
• GINとGiSTのどちらを使えばいいのか？
– 非常に大雑把に言えば（注：数値は目安）
• GINは検索が3倍高速
• GiSTはインデックス作成・更新が3倍高速
– 要件として検索と更新のどちらを優先するか、だと思う。
– 詳細はPostgreSQLの全文検索のところを参照。
23
hstore – インデックス
•
•
効果をEXPLAINで確認
モデル
– レコード数は1,000,000件
– 1レコード内のhstoreには、1～50個のキー＆値を持つ項目が格納される。
– 1つのキーには1～100までのランダムな値が設定される。
•
インデックスなし
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)
•
インデックスあり（GINの場合）
この推定値(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)
24
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方式で結合するよりも高速になるケースもある。
25
追記 - valueに対するインデックス
• 社内の勉強会の質疑（P.23参照）の中で
「valueに対するインデックスは有効なのか？」
という問い合わせがあった。とりあえず、以下を回答。
– hstore型カラムに対するインデックスはkeyのみが対象だと思われる。
– valueは配列化したものをGINインデックスに設定しないと有効ではないだ
ろう。
• 上記回答が本当に正しいのか追試してみた。
– valueを条件にするとhstoreカラムに対して張ったGINインデックスは使わ
れなかった。
– avals()使ってvalue配列を抽出する関数を作成すれば、valueを条件とする
クエリでもインデックスは使用可能。
• クエリの書き方には一工夫は必要（配列同士を比較するなど）
26
追記 - 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
hstore型へのインデックス設定では
(3 rows)
valueを条件にした場合、インデックス
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=#
“1 = ANY(avals(socre)::text[]”
のような条件の書き方だと、
インデックスを使ってくれない・・・
27
hstore – その他
• hstoreは運用面での制約はないのか？
• トランザクションは？
– PostgreSQLの普通のトランザクション機能の中で管理してくれます。
• バックアップは？
– 普通のデータと同じようにバックアップ・リストアも可能です。
– 論理ダンプ(pu_dump）／リストアも問題ありません
• Streaming Replicationは？
– 結局データベースの更新は普通のDMLで更新するので、問題ありません。
• VACUUMは？
– 普通のデータ型と同様、特別考慮することはありません。
• 運用面では普通のデータ型と同じように考えて問題ないです。
28
他RDBMSにおけるKVS
• 他RDBMSの代表的な製品におけるKVS対応状況をWebでざっと
見てみました。
– 各製品に詳しい方からの情報も希望します・・・
• Oracle
– Oracle Application Expressという開発環境の内部DBとしてKVSを利用。
ユーザ向けインタフェースとしては公開していない？
• MySQL
– MyCassandra – Apache CassandraのストレージにMySQLを使う
• DB2
– ？
29
おわりに
• ご清聴ありがとうございました。
• 本資料がhstoreの理解の助けになれば幸いです。
– 半構造のデータを管理するような要件にも、PostgreSQLは適用できます。
• 追記
– 最近、グラフDBというものをちょっと触りはじめていて、
これを、もしPostgreSQL上で実装する場合、
各ノード/関連が持つプロパティなどはhstoreが適用できるかな
・・・と思ってます。
30
参考
• 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
31

Download Report