2015年8月24日月曜日

MySQL使いが知るべきPostgreSQLとの違いと変わらない一つのこと



私が尊敬してるDBスペシャリストの@yoku0825さんがこんな事言ってた。



@yoku0825さんはMySQLのスペシャリスト中のスペシャリストだ。
その@yoku0825さんがこんな事を言うなんて只事じゃない！！
で話になった元ネタはコレ。

日々の覚書: #yapcasia でMySQL 5.7の罠についてLTしてきました


要はMySQL5.7は「目指している正しい姿」になるために下位互換性を幾つかを捨ててるよって話だ。
その中には360日問題があるけどそれ言うと正しさとは？みたいな話になるので今回は話題にしない。
(デフォルトでパスワードが360日後に無効になるという仕様がデフォルトになったという話)
MySQLを使ってる人は@yoku0825さんのブログを一読したほうがいい。
必ず知らなかった罠が幾つか見つかると思う。
そのツイート見て



とブーメラン投げて見事に刺さってるので今から記事書く。
両サイドにはかなり厳しい話もするが俺の本音を聴いておけ（関白宣言）
まぁ歴史の長いRDBなのでお互いの比較記事は沢山ある。
なのでマルチスレッド(MySQL)とマルチプロセス(PostgreSQL)だとかVACUUMだって話はしない。
むしろ実際に使ってみた際の違いをにフォーカスする。

1. SQLの違い


基本的にMySQLでやっていたことはPostgreSQL出来る。
しかし関数の挙動の違いは幾つかある。
例えば時間から曜日に該当する数字に変換した場合に


  • MySQL　→　date_format(time,"%w")　0から始まり、日曜日に該当する
  • PostgreSQL　→　to_char(time,'D')　1から始まり、日曜日に該当する


など挙動に互換性がない場合も多い。
関数を使う場合は一度仕様を確認した方がいい。
幸い、PostgreSQLは有志によって日本語ドキュメントが充実している。
今はGithubで管理されてるので誤字脱字、表現の曖昧さなどはプルリクエストすることが出来る。

PostgreSQL日本語ドキュメント




MySQLを使ってた人はPostgreSQLを使うことでWindow関数など新たに知る機能が多いだろう。
MySQLしか触ったことが無く、OracleやMSDB等を触ったことが無いなら一度SQLの勉強をした方がいい。
必ず業務を効率化してくれるはずだ。
SQL勉強する人向けには最近読んだ本でPostgreSQLベースで書いてあって読みやすかった本をオススメしとく。



じゃあPostgreSQL優秀じゃん！大は小を兼ねるじゃん！！と言いたいところだがMySQLにしか無い文法もある。
MySQLにもPostgreSQLにもMerge文はSQL標準なのに実装されてない。
しかしMySQLには代替案として




がある。
これに準ずる機能はPostgreSQLには無い。
しかしWITH句とRETURNING句（MySQLには無い機能だね）を使えば一応PostgreSQLでも表現出来る。
第九回 中国地方DB勉強会 in 米子でこの話題は取り上げたので興味がある人は資料を見て欲しい。
このリンクの先に梶山さんのMySQL5.7の話が出てるのでそっち見て欲しい、いや見たほうが良い、絶対見るべきだ。
ただ事ある毎にPostgreSQLにMerge文相当が無いことをDis指摘していたら遂に9.5からそれ相当の構文が実装された。
これはMySQLの「INSERT...ON DUPLICATE KEY UPDATE構文」と似た構文で実装されている。

INSERT ... ON CONFLICT {UPDATE | IGNORE}構文


PostgreSQL9.5に興味が出た人はリリース予定の機能が以下にまとまってる。

What's new in PostgreSQL 9.5(英語)


英語なんて読めるか！って人は下記のブログをcheckするといいだろう。

日々の記録 別館


Twitterで行われるラーメン飯テロに耐えれるなら著者のぬこさんをフォローするのもいい。
あとはみかかな人たちが頑張ってるのでSlideShareをWatchすると幸せになれるかもしれない。
オススメのSlideShareのリンクを貼っておく。

10大ニュースで振り返るPGCon2015


おっと話が大分脱線してしまった。
話を戻すがPostgreSQLのSQLは


  • 文字結合が||で出来る
  • 日付操作がday +1で出来る


などどちらかと言えばOracleを意識した記述も多い。
ただ悪名高き(+)は無いしNULL = ""も無いのでそこは安心して欲しい
なのでわかりやすく例えるとしたら

ザ・キング・オブ・ファイターズの95の京と96の京くらい違う


といったところか。
新日本企画が産んだ名作格ゲー、ザ・キング・オブ・ファイターズを知らない若者は

細かいところは違うけど大体一緒でPostgreSQLの方が痒いところに手が届きやすい


くらいのニュアンスで居るといい。
つまりSQLに関してPostgreSQLに乗り換えて不満が出ることは少ないだろう。


2. 機能の違い


これもPostgreSQLの方が多い。
まずマテリアライズド・ビュー（以下マテビュー）がPostgreSQLにはある。
簡潔に言うとviewの結果を実体としてキャッシュするというものだ。
これでtmp_hogeみたいな中間テーブルを作る必要は無くなる。
ただし銀の弾丸ではない。
乱用するとCPUやHDDリソースなどが死ぬ。
そのほかにもCHECK制約があるがこのへんはMySQLで慣れた人には使うシーンが想像出来ないと思う。
データにバグやヒューマンエラーで不正なデータが入って苦しんだ人は覚えておくいい。
次の新規設計時にDBがデータを守ってくれる。
どちらかと言えば


  • JOINのアルゴリズムが複数ある
  • 相関サブクエリが早い


と言う点がMySQLと比較すると直接的にパフォーマンスが上がりメリット感じるところだろう。
ただしUPDATE文に関してはPostgreSQLの方が遅い（場合が多い）
これは追記型アーキテクチャの壁なのでMySQLの方が有利な点だ。
しかしガンガンUPDATEを走らすようなシステムはロックを必要とするはずでロック待ちの方が速度に影響およぼす。
そのため直接的に問題になることは少ないはずだ。

さてここまで話をしたのはPostgreSQLの良いところだ。
勿論MySQLの方が有利な機能もある。
それはレプリケーションだ。
そもそも論としてMySQLとPostgreSQLはアーキテクチャが違うのだがPostgreSQLには自動フェイルオーバーはない。
PostgreSQLの場合はPacemakerなどのサードパーティを利用して実現することになる。
corosyncに苦い思い出がある人は機会があれば一緒に飲もう。
また自動フェイルオーバーはMySQL5.6からの機能だ。
MySQL5.6をまだCHECKしてない人は奥野さんのブログを見るといいだろう。

開発スピードアクセル全開ぶっちぎり！日本よ、これがMySQL 5.6だッ！！


とは行ってもPostgreSQLのレプリケーションに対する投資は素晴らしく今は追いつきつつあると行っても過言ではない。
以上の通り機能面でもSQLと同様の事が言える。
ここも安心してPostgreSQLを推せる内容だ。


3. GUIツールの違い


ここからはPostgreSQLのダメな所になる。
正直言ってPostgreSQLの管理や設定を一つのGUIで全て行うのは諦めた方がいい。
まずSQLエディタ（DBAツール）だがMySQL使いはWorkbenchを使ってると思う。
基本無料なので使ってない人は是非使った方がいい。
それに対してPostgreSQLはpgAdmin3になる。
残念ながら使いやすいとは言い難い。
例えばMacOS版は日本語入力がインラインではない。
この時点でリュウが使ったら殺意の波動に目覚めるレベルだ。
カプコンが産んだ名作、ストリートファイターを知らない若者はウメハラの背水の陣を見ておくといい。
そしてpgAdminは大切な場面で度々落ちる。
エクセル方眼紙を彷彿させると言えば共感してくれる方も多いだろう。
とにかく、PostgreSQLのGUI関連は弱い。
周囲に聞くとみんなコマンドラインの黒い画面でpsqlを直接操作しているらしい。
なので不満がコミュニティからあまり出ていないようだ。
しかし私は補完が利かない環境での開発は耐えれない若者なので代替案として以下のツールを使ってる。




どちらも無料で使え、ER図をリバース・エンジニアリングしたり出来る。
しかもPostgreSQLもMySQLにも繋げれるのでオススメだ。
0xDBEはIDEを開発してるJetBrains製でMacOSでもWindowsでも動くので是非試して欲しい。
A5:SQL Mk-2はmatsubaraさんが個人で開発されてる。
日本語だしExcelにテーブル定義書をリバース・エンジニアリング出来る。
これが無料なのが信じれないレベルのクオリティだ。
なおphpMyAdminに対抗したphpPgAdminも一応ある。
インストールしないことをオススメする。
とモダンなUIとは程遠いPostgreSQLだがPythonでpgAdmin4を作るという話が進んでる。
これがキラーアプリになってくれることを切に願っている。

4. ハードウェアでの違い


長くなってきたので簡潔に言う。
PostgreSQLはMySQLと違ってSSDにしたからといって何十倍の速度も出ない
（せいぜい数倍程度）
さらにFusion-ioは特にMySQLに特化してるのでその差は顕著だ。
PostgreSQLはHDDに特化してシーケンスに書き込もうとするのでそのオーバーヘッドの差が大きい。
もし、金の弾丸でPostgreSQLを殴るときはまずはメモリを増やそう。
PostgreSQLに限らずRDBの多くの問題は金で殴ってメモリを増やすと解決することが多い。
それでもダメな場合は優秀なDBエンジニアを用意しよう。
PostgreSQLはハードウェアを金で殴りにくいのは事実だ。

5. クラウド上での違い


やっとAWS RDSにPostgreSQLが去年追加された。
しかしまだ日本語の全文検索拡張はインストール出来ないしAuroraのPostgreSQL版も無い。
だがHerokuを初め、標準的な使い方であればPostgreSQLをsupportしてるところも出てきた。
国内だとニフティがRDSとしてPostgreSQLをサービスしている。
このへんはOSC 沖縄の時に話をしたので興味がある人はそちらを参考にして欲しい。

OSC沖縄でクラウドの選び方の話で登壇してきたので資料を公開する





随分と話が長くなってしまった。
本当はPostgreSQLには


  • PL/SQLの代わりにPL/PythonやPL/JavaScriptが使える
  • 外部テーブルラッパー(FDW)を使えばPostgreSQLのtableのようにMySQLを参照できる
  • MySQLと違いトリガーを行単位とクエリ単位が選べる（MySQLは行単位のみ）


などの話もしたかったがこのへんの話はまた別の機会にしようと思う。
なお、全然実践的じゃなかったし本当にMySQL派の人がPostgreSQLを使うときのハマリどころは皆無だった。
そこでお詫びにとみたさんの記事を紹介する。



最後になるがMySQLとPostgreSQLはライバル同士だ
しかしながらコミュニティ間はフレンドリーそのものだ。
他にライバル同士がこんなにフレンドリーなOSSコミュニティはあるのだろうか。
実際に合同勉強が開かれたりもする。

MyNA・JPUG合同DB勉強会 in 東京を開催してきた(FDWの話もしてきた)


私はそういうところがRDBのOSS界隈の好きなところである。
これを読んで興味を持った人は是非、DBのコミュニティに参加して欲しい。
MyNAもJPUGも暖かく迎えてくれるだろう。
それはどちらのコミュニティも変わらないことだ。


#### 2015/08/25追記 ####

好評だったので第二弾も作った

MySQL使いの人がPostgreSQLを始めるときの罠をまとめてみた

投稿者 時刻:
ラベル: ,

0 件のコメント:

コメントを投稿

登録: コメントの投稿 (Atom)