QA@IT

PostgreSQLとMySQLはどちらかに明確な優位性がありますか?

82458 PV

現在オープンソースのRDBMSとしては、PostgreSQLとMySQLがほぼデファクトスタンダードになっているかと思います。

普段はRuby on Railsで開発を行なっているため、ActiveRecordの恩恵もあり、あまりDBの差異を意識せずにプログラミングを行う事も出来るので、そこまでどちらでなければのような拘りは無いのですが、PostgreSQLとMySQLで、個人の好み以上の明確な優位性や違いなどはあるのでしょうか?

全般に置いてどちらかという事はあまり無いような気がしますが、こういう場合はこちらの方が良い、こういう特性、特徴を持ったシステムやアプリケーションではこちらの方が良いなど、ケースバイケースの具体的な例などもあれば教えて頂ければと思います。

また、HerokuがPostgreSQLをデフォルトで採用している事や、周辺ツール、開発元、開発コミュニティの活発さなど、それぞれを取り巻く周辺技術や現在のトレンド、流れなどの観点からの回答でも構いません。

よろしくお願いします。

回答

Flame warにもってこいの燃料投下ですね。 :)

元PostgreSQL派、現MySQL派、大昔にOracle社員だった時代もある人間の意見としては、

  • まず、どちらも非常に完成度が高い。商用DBにほとんど遜色ない。(Oracle RACとかみちゃうと、どっちも10年遅れてる感がなくもないけど)
  • RDBMSに求められるものが過不足なく搭載されているのはPostgreSQL。
    • MySQLは仕様がアンバランスだったり無駄が多かったりする。ぶっちゃけ今となってはInnoDB(PostgreSQLっぽいトランザクショナルなエンジンです)とそれ以外のストレージエンジンに差がつきすぎて、プラガブルなアーキテクチャが足かせになってる面のほうが大きい気がする。
  • 大規模なシステムでスケールさせる上で性能を限界まで引き出せるのはMySQL。書きだすときりがないけど、いくつか挙げると、
    • PostgreSQLはマルチプロセス、MySQLはマルチスレッド。やはりサーバ台数が増えて接続数が増えたり、秒間1万クエリを超える次元になってくるとコンテキストスイッチのオーバーヘッドが効いてくるし、Linuxの進化の方向も多スレッドに有利。(ただしNUMAになるとまた状況変わってきそう)
    • レプリケーション構成の歴史が長く、柔軟性も大きいのでスケールアウトやマルチマスタなどの設計に柔軟性があり、そのぶん様々な実験がサードパーティ交えて行われてきており、ノウハウが蓄積されてきている。Percona XtraDB Clusterとかもある。
    • 実績面でも世界最大のユーザ(FacebookやGoogle)が直接パッチをコミットしてる。しかもアクティブに。
  • データのカッチリ感ではPostgreSQL
    • 歴史的にMySQLではデータの制約がゆるいので、例外を投げずに値をcoerceして保持することがある。
  • MySQLは買収を繰り返して現在はOracle傘下という非常に不健全なスポンサーシップ状態にあり、将来的に不安がないといえば嘘になる。

などなど。全般的に、PostgreSQLはバランスよく筋肉質で優等生。これからRDBを学ぶという人にはベストだと思います。その必要十分感もあってか、最近Hacker Newsなどでみる限りトレンドはPostgreSQLにあるといってよいと思います。

MySQLはパフォーマンス面で限界までしゃぶりつくせたり多機能だったりする一方で、学ぶことが多く罠にはまらずちゃんと使えるようになるまでの学習曲線が長いです。が、本格的なサービスを運用していると、細かいところでMySQLじゃないと厳しい場面が結構あります。したがって、熟練度の高い人にはMySQLかなと思います。

以下、最近目にした関連リンクです。ご参考まで。

追記

大事なことを指摘するのを忘れてました。PostgreSQLが追記型アーキテクチャってことです。これはアプリケーションの向き不向きを選ぶ重大な選択です。

たとえば、アクセスカウンターのように、レコードのあるカラムを+1していくだけのような用途では、追記型よりもin-place型のほうが有利です。in-placeなら、何度繰り返しても同じメモリ位置のそのバイトのみををひたすら書き換えていくだけ(+リングバッファな固定サイズWALへの書き込み)で済むのに対して、追記型だと、レコード自体をどんどん末尾に追加していくので、サイジングも難しく、重複によるかなりの無駄が生じます。結果として参照の局所性も損なわれますし、フラグメンテーションのリスク、もともとナチュラルな順序性があったデータがばらばらになってランダムI/Oになりやすいなど(InnoDBならクラスタードインデックスなので順序性はむしろ強制されていますが)、ダウンサイドのリスクは根の深いものがあります。

追記型アーキテクチャは、冪等性を担保しつつMVCCを実現するという理論的な意味ではよりシンプルで美しいのですが、Disk I/Oを極限まで減らしてカリカリにチューニングしたいという局面ではかなりの足かせになります。また、これにともなうvacuumのコストも、ガベージコレクタの世界でスループットとレイテンシのトレードオフがあるように、深淵なトレードオフが存在するわりに、現在の実装はまだまだプリミティブな感じです。ぶっちゃけいろいろな意味で、性能を予想しにくいです。

というわけで、結果としては「性能(を出す・維持するための運用)を考えるならMySQL」という論点の繰り返しになりますね。

更に追記

tabizouさんから、PostgreSQL 8.3からHOTで大幅に改善したと指摘がありました。HOTは素晴らしい最適化ではありますが、これによって追記型アーキテクチャそのものが変わるわけではありません。インデックスが更新されない場合でも、依然レコード本体は(一部ではなく全部が)コピーされます。つまり (key1, key2, key3, value) のようなタプルでvalueを+1する処理を考えるとき、in-placeなら完全に局所化されたvalueだけの上書き処理ですが、追記だと良くても (key1, key2, key3, value) 全体のコピー処理です。これは、ゼロコピー化やCopy-on-writeなどでコピー量を極限まで減らそうという最近のトレンドに反するもので、また行サイズが大きくなればなるほど性能差は顕著になります。メモリやI/Oの帯域的にも空間的にも無視できない無駄です。

なお、公平のために記すと、Vacuumがないと思われているMySQLですが、厳密にいうと、全くないわけではありません。InnoDBはMVCCをサポートしていますから、結局は複数の行バージョンを保持する仕組みを持っており、つまり不要になった古いバージョンを掃除する機構が必要です。これはOracle同様にロールバックセグメントを使って実装されており、パージ処理はメインスレッドが暇なとき(5.1)あるいは専用スレッド(5.5)にて実行されます。また、5.6からは複数のパージスレッドで並列実行も可能になり、このあたりは同じアドレス空間で動作する強みを活かしやすい分野だといえるかもしれません。ロールバックセグメントは基本的に独立したゴミため場ですから、MVCCによって通常のページが汚染されることはなく、したがってPostgreSQLのように定常的にページコンパクションのコストを払うこともありません。

また、HOTを効かせるためにFill factorを下げると空間効率が落ち、Fill factorを上げるとHOTが効かないという、プロでも設定に悩むトレードオフもあります。これらのもろもろを総合して、「ぶっちゃけいろいろな意味で、性能を予想しにくいです。」と表現しました。

なお、NoSQLの世界だと、CouchDBが追記型であったのに対して、MongoDBがin-placeを選択したというのも、面白いですね。MongoDBの場合には、逆にドキュメント(=行)単位のpadding factorを設定してすき間を作っておき、レコードサイズの増加に備えるという逆転の発想になっているのが面白いところです。この根底には、やはりレコードの移動(コンパクションなど)は不要なコストだし非直感的だ、という考え方が根強いのだと思います。

編集 履歴 (6)
  • なるほど。とても参考になります。
    ある程度、無難な扱い易さでいったらPostgreSQL、限界的な性能を必要とするのであればMySQLという感じでしょうか。
    回答ありがとうございます。
    -
  • HOTについての説明がちと違っている感じですね。条件によっては上書きされます(らしいです。ソースは石井さんのtweetなのですが・・) -
  • 追記でMVCCを実現してるアーキテクチャで上書きしたら、原理的にACIDのIを実現しようがないですよね。それとも並列に動いてるトランザクションが一個もない場合に限って上書きするということでしょうか?そのような情報は見つからなかったので、詳細へのリンクを希望します。 -

Ruby on Railsの話ということで、Rails界隈のトレンドの話を少し(kennさんの回答の後で気が引けますが)

Ruby on Rails界隈では、MySQLよりもPostgreSQLに人気がシフトしているトレンドがあるようです。例えば、最近話題になったRailsホスティングに関する、このアンケート調査(調査対象や方法は要確認ですが、1200~1300ユーザーに聞いているので一定の信頼性はあるかもしれません)を見ると、

  • 2009年に80%の人がMySQLを使っていて、60%の人が新規開発でもMySQLを選ぶと回答
  • 2009年にPostgreSQLを使っていたのは14%
  • 2012年の調査では、PostgreSQLを使っているのは40%に増加していて、新規開発にPostgreSQLを選ぶとした人は60%

ということです。逆転しつつあるということですね。

もう1つ、Ruby PaaSのEngine Yardが2012年8月14日に、デフォルトのRDBをMySQLからPostgreSQLへ変更すると発表しているのも気になります。Engine YardのCEOに先日直接聞いたのですが、RubyコミュニティのOracleに対する憂慮を反映して、という側面もあるようです。Hudson→Jenkins、OpenOffice→LibreOfficeと、これまでOracleはオープンソースコミュニティとうまく付き合ってきた実績に欠けています。Engine Yardとしては、本当はMySQLとPostgreSQLの2つを同時にサポートするのはコスト増なので1本化するのが戦略上の合理性があるのだけど、ユーザーの声を聞いた結果だということです。

先のブログ、PostgreSQL is Our New Defaultには、PostgreSQLの優れたメリットが書いてあります。少しだけ抄訳すると:

  • Solr、Sphinxなど別にスタンドアローンのエンジンを立てなくても、標準で全文検索をサポート
  • ロックが必要とされる場面が少ない
  • ActiveRecordやPostgreSQL(SQL)では十分にやりたいことができないときには、ユーザー定義のプロシージャ言語が使えて、例えばV8エンジンを使ったPLV8なんかがある
  • 非同期レプリケーションを標準サポート
  • 数日前にリリースされたPostgreSQL 9.2ではネイティブでJSON対応など機能開発が盛ん

といったところでしょうか。

【choplinさんの指摘を受けてJSON対応に関する記述を修正】

PostgreSQLのJSON型ができることは主に、

  • 値を書く際のバリデーション
  • 行や配列型の値からのJSON文字列の組み立て

の2つで、クエリには非対応のようです。

http://www.postgresql.org/docs/9.2/static/datatype-json.html にあるように、TEXT型で入れるのと違ってJSON型で入れるメリットはRFC的にvalidなJSONかどうかをチェックしてくれます。http://www.postgresql.org/docs/9.2/static/functions-json.html にあるように、arrayやrowをJSONに変換する関数が利用できるとうことですね。

ちなみに、ここのQA@ITもPostgreSQLを使っていますが、PostgreSQLのエクステンションとして使えるKVSのhstoreをある種のユーザーアクティビティの抽象化に使っていたり、質問の検索でTrigramを使って文書の近縁度を判定するクエリを投げたりしていて、いろいろとPostgreSQLは高機能だなという印象を受けています。

kennさんの回答にあるように大規模サービスで限界の性能を引き出すならMySQLというのが妥当だとすると、Rails界のトレンドシフトというのは、Railsの適用事例が比較的中小規模のサービスや業務アプリで増えている、ということもあるのかもしれないなと想像していたりします。

編集 履歴 (3)
  • 回答ありがとうございます。
    そうですね。Rails界隈に身を置いているとHerokuがデフォルトでPostgreSQLを採用していたり、何かとPostgreSQLの方がよく名を聞く気がします。
    Railsとの親和性という観点で言うとPostgreSQLの方が向いていると言えるかも知れませんね。
    -
  • あと「ORMがDBの違いを吸収する」というのは、シンプルなアプリでないとファンタジーという側面もありますよね。なので、HerokuやEngine Yardのデフォルトが各開発者の手元の開発環境に還流して、開発者がますますPostgreSQLを使うようになるという流れというのもあり得るのかな、というのが、QA@IT開発の末席にいて感じたことでした -
  • そうですね。結局、パフォーマンスがシビアな部分や、集計など処理が重い部分は生SQLを書かざるを得ないケースもまだまだありますね。スタートアップ的な、Herokuなどを使って小規模なアプリケーションから始めるケースでは、PostgreSQLが使われるケースも多いのかも知れませんね。 -
  • 私の場合は、Rails 3からActiveRecordがものすごくパワフルになったので、生SQLを書く総量はかなり減りました。関連表側のスコープもmergeで取り込めるし(http://benhoskin.gs/2012/07/04/arel-merge-a-hidden-gem)、サブクエリさえ、to_sqlした結果を埋め込むなどできるので。関係代数の数学的な裏付けがあることのパワーですね。 -
  • やはりArelの完成度は相当高いみたいですね。まだまだ使いこなせていない感じなので、勉強しようと思います。 -
  • 些事ですが、PostgreSQLのJSON型は、9.2の時点ではクエリには未対応です。できることは主に、1.値を書くのする際のバリデーション、2.行や配列型の値からのJSON文字列の組立、の2つです。 -
  • choplinさんありがとうございます。本文のJSONの記述を修正しました。 -

業務システム構築において、他の RDBMS から移行するという観点をもとに回答します。

MySQL で困ること

MySQLでは、1 つのクエリに対して、1 つのテーブルにつき 1 つのインデックスしか使用できない

参考: http://labs.unoh.net/2007/06/mysql5.html

他の RDBMS から MySQL に移行した時に、特に困るのが この仕様です。
これを避けるために、なにやら 他の RDBMS では ふつーはおこなわない謎のクエリ構造を組み上げる営みが、往々にして必要となります。

ストレージエンジンに MyISAM を選択したテーブルはテーブルロックになる

参考: http://mysql.manual.php.to/storage-engines.html#storage-engine-choosing

特に困るのがバックアップ実行時です。バックアップ実行中に該当テーブルがバックアップ終了するまでテーブルロックが発生してしまいます。
これは通常運用時に特に困ります。(バックアップ機能が、スナップショットを取得してからバックアップ、という流れの方式になっていないようです) バックアップ中にシステムがフリーズするのを避けるには、なにかしら方式を工夫する必要があります。

JDBC ドライバーのデフォルト値が とてつもなく変

参考: http://sourceforge.jp/projects/manjyu/wiki/JNDI_MySQL
JDBC ドライバーのデフォルト値が、とても変です。これは歴史的経緯によるものですが、、、。
他の RDBMS の JDBC ドライバーと似た挙動をさせたい場合には、驚くほどの大量の追加設定をおこなう必要があります。

PostgreSQL で困ること

トランザクション中に SQL 例外が発生すると、トランザクションが完了するまで、それ以降の SQL 文がすべて例外状態になってしまう【JDBC ドライバ経由でしか裏取っていません】

たとえばトランザクション中に一意制約違反の SQL 例外を発生させてしまうと、以降の SQL 文がすべてエラーになってしまいます。
業務的に一意制約違反を発生させて、それ以降の処理を分岐しようというのに慣れている人は注意しましょう。
なお、最近のバージョンの PostgreSQL では、新しい SQL 構文により これの代替手段が提供されています。

個人的な所見

他の RDBMS から移行するときには PostgreSQL のほうが、すべてを台無しにするような危険な罠のタグイは少ないように思えます。

編集 履歴 (6)

技術的な比較はさておき、サンフランシスコ・ベイエリアでの求人件数の比較を見てみると、

MySQL: 205件
http://sfbay.craigslist.org/search/sof?query=mysql&srchType=A

PostgreSQL: 23件
http://sfbay.craigslist.org/search/sof?query=PostgreSQL&srchType=A

まだまだ、MySQLの方が使われているみたいですね。

編集 履歴 (0)
  • 回答ありがとうございます。
    求人という観点だと、やはりMySQLの突き詰めたチューニングが出来る技術者などへの要望が多いのかも知れませんね。
    -

気になるので1件
Postgresは8.2系列までは上記の説明どおりなのですが
http://lets.postgresql.jp/documents/tutorial/hot_1/
にもあるとおり8.3系列以降はアーキテクチャが変わっています
参考まで

編集 履歴 (0)
  • ありがとうございます。HOTについて追記しました。 -
  • tabizouさん、貴重な追加情報ありがとうございます。こうした追加情報は、次回からは「ノートを書く」を使って投稿をお願いします。 -
ウォッチ

この質問への回答やコメントをメールでお知らせします。