Web エンジニアが postgre sql を選ぶ 3 つの理由
-
Upload
soudai-sone -
Category
Technology
-
view
6.282 -
download
1
description
Transcript of Web エンジニアが postgre sql を選ぶ 3 つの理由
Web エンジニアが
PostgreSQL を選ぶ 3 つの理由PostgreSQLカンファレンス 2014
What is it?
データベースは何を基準に選んでますか?
What is it?アプリケーションにとって
データの寿命はコードより長い
という事実
What is it?
なぜPostgreSQLを使うのか
あじぇんだ
1 自己紹介 2 ランキングを作る 3 可変なプロパティを扱う 4 型を極める 5 まとめ
あじぇんだ
1 自己紹介 2 ランキングを作る 3 可変なプロパティを扱う 4 型を極める 5 まとめ
自己紹介名前:曽根 壮大(そね たけとも) 年齢:30歳(三人の子供がいます) 職業:Webエンジニア
所属:日本PostgreSQLユーザ会
中国支部 支部長
技術的にはLL系言語とかRDBが好きです
あじぇんだ
1 自己紹介 2 ランキングを作る 3 可変なプロパティを扱う 4 型を極める 5 まとめ
ランキングを作る
仕様変更に強いランキングを作る
ランキングを作る
Viewに紐づくデータは
仕様変更
の影響を受けやすい
ランキングを作る
ランキング
ランキングを作る
ランキング ↓
要件が多様なので影響を受けやすい
名前 戦闘力
フリーザ 530000
悟飯(幼少期) 1307
クリリン(ラディッツ戦) 206
ヤムチャ(ラディッツ戦) 177
ランキングを作る要件
ランキングを作る要件
1 戦闘力の降順(DESC)
ランキングを作る要件
1 戦闘力の降順(DESC) 2 表示は名前と戦闘力
実際のSQL
SELECT
名前,戦闘力
FROM キャラクター
ORDER BY
戦闘力
DESC
ランキングを作る要件
1 戦闘力の降順(DESC) 2 表示は名前と戦闘力 3 上位三名を表示
実際のSQL
SELECT 名前,戦闘力
FROM キャラクター
ORDER BY 戦闘力
DESCLIMIT 3
ランキングを作る要件
1 戦闘力の降順(DESC) 2 表示は名前と戦闘力 3 上位三名を表示 4 編で分ける
名前 戦闘力 編
フリーザ 530000 フリーザ編
悟飯(幼少期) 1307 ラディッツ編
クリリン 206 ラディッツ編
ヤムチャ 177 ラディッツ編
農夫 5 ラディッツ編
ギニュー 120000 フリーザ編
クリリン 1500 フリーザ編
亀仙人 139 ラディッツ編
※実務では編は正規化するべき
実際のSQLSELECT 名前,戦闘力
FROM キャラクター
WHERE 編 = ‘フリーザ編’ または 編 = ‘ラディッツ編’
ORDER BY 戦闘力
DESCLIMIT 3
ランキングを作る
クライアント「それじゃない。」
名前 戦闘力 編
悟飯(幼少期) 1307 ラディッツ編
クリリン 206 ラディッツ編
ヤムチャ 177 ラディッツ編
亀仙人 139 ラディッツ編
農夫 5 ラディッツ編
フリーザ 530000 フリーザ編
ギニュー 120000 フリーザ編
クリリン 1500 フリーザ編
ランキングを作る
SQLを二回投げるか?
ランキングを作る
ウィンドウ関数
ランキングを作るウィンドウ関数
ウィンドウ関数は現在の行に何らの関係するテーブル行の一纏まり全般の計算を行う。
実際のSQL
SELECT
rank() OVER (
PARTITION BY "編"
ORDER BY "戦闘力" DESC
)
, *
FROM "キャラクター";
名前 戦闘力 編
悟飯(幼少期) 1307 ラディッツ編
クリリン 206 ラディッツ編
ヤムチャ 177 ラディッツ編
亀仙人 139 ラディッツ編
農夫 5 ラディッツ編
フリーザ 530000 フリーザ編
ギニュー 120000 フリーザ編
クリリン 1500 フリーザ編
ランキングを作る
クライアント 「かつキャラの最大戦闘力で並べて」
名前 戦闘力 編フリーザ 530000 フリーザ編フリーザ 10000000 フリーザ編フリーザ 20000000 フリーザ編
悟飯(幼少期) 1307 ラディッツ編クリリン 206 ラディッツ編ヤムチャ 177 ラディッツ編農夫 5 ラディッツ編ギニュー 120000 フリーザ編クリリン 1500 フリーザ編クリリン 0 フリーザ編クリリン 10000 フリーザ編亀仙人 139 ラディッツ編
名前 戦闘力 編
フリーザ 2000000 フリーザ編
ギニュー 120000 フリーザ編
クリリン 10000 フリーザ編
悟飯(幼少期) 1307 ラディッツ編
クリリン 206 ラディッツ編
ヤムチャ 177 ラディッツ編
亀仙人 139 ラディッツ編
農夫 5 ラディッツ編
実際のSQL
SELECTrank() OVER ( PARTITION BY "編"
ORDER BY max("戦闘力") DESC
) , "名前", MAX("戦闘力"), "編"
FROM "キャラクター"
GROUP BY "名前","編";
名前 戦闘力 編
フリーザ 2000000 フリーザ編
ギニュー 120000 フリーザ編
クリリン 1500 フリーザ編
悟飯(幼少期) 1307 ラディッツ編
クリリン 206 ラディッツ編
ヤムチャ 177 ラディッツ編
亀仙人 139 ラディッツ編
農夫 5 ラディッツ編
ランキングを作る
仕様変更に強いランキングを作る
ランキングを作る
開発者 「毎回SQLの差し替えするの辛い」
ランキングを作る
Viewを使う
ランキングを作る
街角の声 「Viewを使うと遅いのでは?」
ランキングを作るView
ランキングを作るView
• INDEXは効く
ランキングを作るView
• INDEXは効く • 参照の際にSQLを実行するだけ
ランキングを作るView
• INDEXは効く • 参照の際にSQLを実行するだけ • 元のSQLが遅い場合は当然遅い
ランキングを作る
参照元のテーブルが大きくなった
ランキングを作る
参照元のテーブルが大きくなった ↓
参照元のSQLが遅い
ランキングを作る
マテリアライズドビュー
ランキングを作るマテリアライズドビュー
実体の存在するView。 参照したクエリ結果を保存するため、参照元を更新した際はマテビューの更新も必要になる。
※ただしPostgreSQL 9.3からの機能
ランキングを作る
クエリ結果を実体化する
ランキングを作る
クエリ結果を実体化する ↓ 高速化
ランキングを作る
マテビューは銀の弾丸ではない
ランキングを作るマテビューの問題点
・リフレッシュ管理が必要(自動更新しない) ※ただし、9.4から自動更新が可能 ・普通のテーブル同様に表領域を消費する ・リフレッシュはそれなりにリソースを使う
ランキングを作る
更新が多いとボトルネックになる
ランキングを作るまとめ
ランキングを作るまとめ
1 データをシンプルに保つ
ランキングを作るまとめ
1 データをシンプルに保つ 2 コード側の実装に依存しない
ランキングを作るまとめ
1 データをシンプルに保つ 2 コード側の実装に依存しない 3 要件に合わせて選択肢を選ぶ
あじぇんだ
1 自己紹介 2 ランキングを作る 3 可変なプロパティを扱う 4 型を極める 5 まとめ
可変なプロパティを扱う
可変なプロパティを扱う
• アンケートフォーム
可変なプロパティを扱う
• アンケートフォーム • ユーザの付属情報
可変なプロパティを扱う
• アンケートフォーム • ユーザの付属情報 • ブログのタブ
可変なプロパティを扱う
• アンケートフォーム • ユーザの付属情報 • ブログのタブ などなど…
可変なプロパティを扱うアンケートフォーム
可変なプロパティを扱うアンケートフォーム
回答者 キャラクター 回答日
そーだい 榛名 2014/11/28
たけとも 高雄 2014/11/28
soudai1025 大和 2014/11/29
可変なプロパティを扱うアンケートフォーム
ここに「択一回答」があるじゃろ? ( ^ω^)←お客様
⊃ 択一回答⊂
可変なプロパティを扱うアンケートフォーム
これを ( ^ω^)←お客様
⊃ )択一回答(⊂
可変なプロパティを扱うアンケートフォーム
こうして… ( ^ω^)←お客様
≡⊃⊂≡
可変なプロパティを扱うアンケートフォーム
こうじゃ! ( ^ω^)←お客様
⊃ 複数回答 ⊂
可変なプロパティを扱うアンケートフォーム
可変なプロパティを扱うアンケートフォーム
テキストフォームまでこっそり追加される
可変なプロパティを扱う
どのように対応するか
可変なプロパティを扱うデータについて
可変なプロパティを扱うデータについて
• データを消せない
可変なプロパティを扱うデータについて
• データを消せない • データを変更できない
可変なプロパティを扱うデータについて
• データを消せない • データを変更できない • データの追加で対応
可変なプロパティを扱う
SQLアンチパターン ↓
カンマ区切り(CSV)で保存 ※ジェイ・ウォーク
回答者 キャラクター 回答日
そーだい 榛名 2014/11/28
たけとも 高雄,榛名 2014/11/28
soudai1025 大和,金剛,武蔵 2014/11/29
回答者 キャラクター 回答日
そーだい 榛名 2014/11/28
たけとも 高雄,榛名 2014/11/28
soudai1025 大和,金剛,武蔵 2014/11/29
保存するデータが カラムのサイズに依存する
可変なプロパティを扱うSQLアンチパターン
可変なプロパティを扱うSQLアンチパターン
• 検索が難しい
可変なプロパティを扱うSQLアンチパターン
• 検索が難しい • 集計が難しい
可変なプロパティを扱うSQLアンチパターン
• 検索が難しい • 集計が難しい • 更新が難しい
可変なプロパティを扱う
SQLアンチパターン ↓
データの数だけカラムを増やす ※メタデータトリブン
回答者 キャラ1 キャラ2 キャラ3 回答日
そーだい 榛名 NULL NULL 2014/11/28
たけとも 高雄 榛名 NULL 2014/11/28
soudai1025 大和 金剛 武蔵 2014/11/29
可変なプロパティを扱うSQLアンチパターン
可変なプロパティを扱うSQLアンチパターン
• 項目追加の度にカラムが増える
可変なプロパティを扱うSQLアンチパターン
• 項目追加の度にカラムが増える • データの可読性が下がる
可変なプロパティを扱うSQLアンチパターン
• 項目追加の度にカラムが増える • データの可読性が下がる • データの整合性を担保が難しい
可変なプロパティを扱う
正規化
回答者 回答日
そーだい 2014/11/28
たけとも 2014/11/28
soudai1025 2014/11/29
回答者 キャラクターそーだい 榛名たけとも 高雄たけとも 榛名soudai1025 大和soudai1025 金剛soudai1025 武蔵
可変なプロパティを扱う
最初から正規化すれば両対応
可変なプロパティを扱う
集合でデータを表現する
可変なプロパティを扱う
変更に強くなる
可変なプロパティを扱う
PostgreSQLのアプローチ
可変なプロパティを扱う
PostgreSQLのアプローチ ↓ 配列型
回答者 キャラクター 回答日
そーだい {榛名} 2014/11/28
たけとも {高雄,榛名} 2014/11/28
soudai1025 {大和,金剛,武蔵} 2014/11/29
可変なプロパティを扱う配列型
可変なプロパティを扱う配列型
• INDEXが効く
可変なプロパティを扱う配列型
• INDEXが効く • 柔軟な検索(内包なども可能)
可変なプロパティを扱う配列型
• INDEXが効く • 柔軟な検索(内包なども可能) • 任意の箇所の更新も可能
可変なプロパティを扱う配列型の注意点
• 外部制約が使えない • ORMが多くの場合使えない
可変なプロパティを扱う配列型のその他の使い方
• タグなどの複数の値を持たせる • 木構造を表現する
可変なプロパティを扱う配列型まとめ
可変なプロパティを扱う配列型まとめ
• 外部制約の不要な場合に使う
可変なプロパティを扱う配列型まとめ
• 外部制約の不要な場合に使う • ORMに依存しない場合に使う
可変なプロパティを扱う配列型まとめ
• 外部制約の不要な場合に使う • ORMに依存しない場合に使う • 最初に正規化を検討する
可変なプロパティを扱うもっと柔軟に対応したい
可変なプロパティを扱うもっと柔軟に対応したい
• ドキュメント志向
可変なプロパティを扱うもっと柔軟に対応したい
• ドキュメント志向 • スキーマレス
可変なプロパティを扱うもっと柔軟に対応したい
• ドキュメント志向 • スキーマレス • Key=>Valueな関係性を保存
可変なプロパティを扱うJSON型
可変なプロパティを扱うJSON型
• JSON本体をカラムに保存
可変なプロパティを扱うJSON型
• JSON本体をカラムに保存 • 高速な参照(INDEXが効く)
可変なプロパティを扱うJSON型
• JSON本体をカラムに保存 • 高速な参照(INDEXが効く) • 各種変換の関数を用意
回答者 JSON 回答日
そーだい {キャラクタ:[榛名]} 2014/11/28
たけとも {キャラクタ:[高雄,榛名]} 2014/11/28
soudai1025 {キャラクタ:[大和,榛名,武蔵]} 2014/11/29
回答者 JSON 回答日
そーだい {キャラクタ:[榛名],Lv:40} 2014/11/28
たけとも {キャラクタ:[高雄,榛名]} 2014/11/28
soudai1025 {キャラクタ:[大和,榛名,武蔵]} 2014/11/29
可変なプロパティを扱うJSON型
可変なプロパティを扱うJSON型
• 柔軟にデータを保存できる
可変なプロパティを扱うJSON型
• 柔軟にデータを保存できる • View変更によるDB変更が不要
可変なプロパティを扱うJSON型
• 柔軟にデータを保存できる • View変更によるDB変更が不要 • 9.4からはより強力なJSONB型
可変なプロパティを扱うJSON型の注意点
• 外部制約の不要な場合に使う • ORMに依存しない場合に使う • 問題点は配列型と同様
あじぇんだ
1 自己紹介 2 ランキングを作る 3 可変なプロパティを扱う 4 型を極める 5 まとめ
型を極める豊富な型の例
• 列挙(enum)型 • ネットワーク・アドレス型 • 範囲型 • 幾何データ型
• 列挙(enum)型 • ネットワーク・アドレス型 • 範囲型 • 幾何データ型
型を極める型を選ぶ利点
型を極める型を選ぶ利点
• 正しいデータのみが保存される
型を極める型を選ぶ利点
• 正しいデータのみが保存される • 正しいソートが行われる
型を極める型を選ぶ利点
• 正しいデータのみが保存される • 正しいソートが行われる • 適切な検索が行える
型を極めるネットワークアドレス型
• 列挙(enum)型 • ネットワーク・アドレス型 • 範囲型 • 幾何データ型
• IPv4もIPv6も対応 • サブネットマスクの整合性 • 文字列ではなくIPとしてソート
IP
192.1.1.1/32
192.2.1.1/32
192.10.1.1/32
型を極める幾何データ型
• 列挙(enum)型 • ネットワーク・アドレス型 • 範囲型 • 幾何データ型
• point、boxなど豊富な型 • 充実した関数と演算子 • 地図の範囲検索など
店名 緯度 経度
品川AP 35.630793 139.73786
品川駅 35.630152 139.74044
五反田駅 35.626446 139.723444
実際のSQL
SELECT
sqrt(power((対象緯度-自分の緯度)*111,2)
+
power((対象経度-自分の経度)* 91,2))
AS distance
平均で緯度1度あたり111km
平均で経度1度あたり91km
検索例半径●●メートルの中の登録店を調べる
自分を中心とした円に含まれているか
実際のSQLSELECT 店名,
sqrt(power((お店.緯度 - 自分の緯度) * 111, 2)
+ power((お店.経度 - 自分の経度) * 91, 2)) AS 距離(km)
FROM お店
WHERE circle(point(お店.緯度*91.0,お店.経度*111.0), 円の半径)
@ circle(point(自分の緯度*91.0,自分の経度*111.0), 円の半径)
店名 距離(km)
品川AP 0.01
品川駅 0.4
型を極めるまとめ
• 列挙(enum)型 • ネットワーク・アドレス型 • 範囲型 • 幾何データ型
•
型を極めるまとめ
• 列挙(enum)型 • ネットワーク・アドレス型 • 範囲型 • 幾何データ型
• 適切な型にデータを入れる
型を極めるまとめ
• 列挙(enum)型 • ネットワーク・アドレス型 • 範囲型 • 幾何データ型
• 適切な型にデータを入れる • 型を使い不正なデータを無くす
型を極めるまとめ
• 列挙(enum)型 • ネットワーク・アドレス型 • 範囲型 • 幾何データ型
• 適切な型にデータを入れる • 型を使い不正なデータを無くす • 特別な検索も可能になる
あじぇんだ
1 自己紹介 2 ランキングを作る 3 可変なプロパティを扱う 4 型を極める 5 まとめ
まとめ
まとめ
Webは日々複雑になっている
まとめ
Webは日々複雑になっている ↓
取り扱うデータも増えている
まとめ
運用が始まるとデータは変えれない
まとめ
運用が始まるとデータは変えれない ↓
どんなにコードが綺麗でもデータ構造がダメだとリファクタリングは難しい
まとめ
SQLや型を使ってデータを守る
まとめ
SQLや型を使ってデータを守る ↓
運用をシンプルにする
まとめ
データの寿命はコードより長い
ご静聴ありがとうございました。