MySQL vs PostgreSQL
はじめに
最近render.comというクラウドベンダーを使う機会があったのですが、DBは標準ではPostgreSQLになっているんですよね。
ひと昔前だと、小規模だったらsqlite、中規模くらいでMySQL、大規模になってきたらそのシステム構成に応じてMySQLかPostgreSQL。レガシーシステムだとOracle Databaseみたいな時代があったよな。。。と思っていたのですが、こういうサービスで初期からPostgreSQLを推奨ってことは色々変化があったってことなんでしょうか？
PostgreSQLってあまり使ったことがなく、強いていえばAmazon Redshiftくらいだったので、ちょっと調べてみました。
まずはなにが違うのか？
もうイチから調べるのも時間かかるのでサクッとChatGPTさんに聞いてみました。
ここから
MySQLとPostgreSQLはどちらが優れていますか？
それぞれの特徴とメリットデメリット、適した利用シーンの紹介をしてください。
MySQLとPostgreSQLは、いずれも広く利用されているオープンソースのリレーショナルデータベース管理システム(RDBMS)ですが、その設計哲学や機能特性、運用性などに違いがあります。それぞれに固有の強みや弱みがあり、利用シーンによって適した選択肢が変わってきます。以下に特徴・メリット・デメリット、そして想定される利用シーンを詳しく解説します。
MySQL
特徴・アーキテクチャ
- 歴史・普及度: MySQLは古くから広く利用されており、特にWebアプリケーション（LAMPスタック: Linux, Apache, MySQL, PHP/Perl/Python）のデファクトスタンダード的存在です。
- エンジン構造: MySQLはInnoDBやMyISAMなど複数のストレージエンジンをサポートしています。現在はInnoDBがトランザクションとACID特性をサポートする標準的な選択肢となっています。
- シンプルで扱いやすい: 設定や運用が比較的容易で、学習コストが低めです。また、MySQLは軽量で高速なクエリ実行が可能なため、小～中規模なシステムで広く採用されています。
メリット
- パフォーマンスが良好なケースが多い：読み込み中心のWebサイトや小～中規模システムでは非常に高速です。
- エコシステムの充実：WordPress, Drupal, Joomla! など多くのCMSやフレームワークでMySQLが標準的に利用されるため、サポート情報やチュートリアルが豊富です。
- 学習コストが低い：シンプルな構成と管理ツール（phpMyAdminなど）の存在により、初心者も扱いやすい環境が整っています。
デメリット
- 機能的柔軟性が相対的に劣る：PostgreSQLに比べ、高度なSQL機能（ウィンドウ関数、CTE、外部データラッパーなど）やデータ型拡張性は限定的な傾向がありました。ただし近年はCTEや一部の高度な機能もサポートされています。
- トランザクション性能・整合性面での限界：InnoDB以前はトランザクション性が弱かった（現在は改善済み）、また高い同時実行性の要求に対してはPostgreSQLほどの強さがない場合もあります。
- 標準SQL準拠度：PostgreSQLほどSQL標準に準拠していないため、厳密な標準SQLとの互換性を重視する場合には物足りないことがある。
適した利用シーン
- 小～中規模のWebサービス: ブログ、ECサイト、中小規模のSaaSサービスなどで手軽に導入・拡張が可能。
- CMSとの組み合わせ: WordPressなどのMySQL想定のツールを用いたWebサイト構築に最適。
- 迅速なプロトタイプ開発: シンプルでセットアップが容易なため、初期段階でのプロトタイプや MVP (Minimum Viable Product) 開発で有利。
PostgreSQL
特徴・アーキテクチャ
- 堅牢性と機能の豊富さ: PostgreSQLは「世界で最も先進的なオープンソースデータベース」と称されることがあり、トランザクション整合性や拡張性、SQL標準対応度が非常に高いです。
- 拡張可能なアーキテクチャ: カスタムデータ型、ストアドプロシージャ、トリガー、ウィンドウ関数、CTE(Common Table Expression)など高度な機能が充実。さらに、JSONBやHstoreといった非構造化データ型にも強く、NoSQL的なユースケースにも部分的に対応可能。
- ACID特性の厳格な保証: トランザクションと同時実行制御(MVCC)がしっかりしており、大規模・高負荷な環境で信頼性あるパフォーマンスを発揮します。
メリット
- 豊富な機能と標準準拠度：高度なSQL機能、複雑なクエリ要件、データモデルに柔軟に対応できます。
- 高い拡張性と柔軟性：ユーザー定義の関数、演算子、データ型の追加が容易。外部データラッパー(FDW)で他のデータソースと統合も可能。
- 優れた整合性と信頼性：ACID保証が強固で、トランザクション処理やデータの一貫性が非常に重要なシステムに適しています。
デメリット
- 学習コストとセットアップの複雑さ：機能が豊富な反面、初心者には設定やチューニングが難しく感じられることが多い。
- やや重めのリソース要求：軽量なシステムでMySQLに比べてオーバーヘッドが大きく、メモリ使用量や初期セットアップに関するコストが高め。
- 一部エコシステム面での差：Webアプリケーションフレームワークや特定のCMSツールでは、MySQLへの最適化・サポートが豊富な一方でPostgreSQL対応は必ずしも同水準でないケースもある（近年は改善傾向）。
適した利用シーン
- 大規模な業務システムやエンタープライズ向けアプリ：金融システムや企業内データウェアハウスなど、整合性と安定性が求められるケース。
- 複雑なデータモデルや高度なクエリ要件：GISデータ(PostGIS拡張)を扱うシステム、分析用の複雑なクエリが必要なデータ分析基盤など。
- NoSQL的ユースケースとのハイブリッド：JSONBを活用した柔軟なスキーマレスデータ管理。
まとめ・どちらが優れているか？
MySQLは軽量で扱いやすく、Webサービスなど小～中規模での運用、またはCMS構築などで即戦力となります。簡易なインフラで高速に動作し、多くの学習リソースやコミュニティサポートがあるため、初心者から中規模システムまでに非常に適しています。
一方で、PostgreSQLは機能が非常に豊富で拡張性が高く、データの一貫性・整合性を重要視し、かつ複雑なクエリやデータモデルを扱う大規模・エンタープライズ環境で真価を発揮します。長期的な拡張性や厳密なデータ管理が必要であれば、PostgreSQLは有力な選択肢となります。
結論としては、「どちらが優れているか」はユースケース次第であり、
- 簡易で高速なWebシステム構築 → MySQL
-
高信頼性・拡張性・機能的リッチさが求められる → PostgreSQL
といった基準で使い分けることが一般的です。
ここまで
ChatGPTさん、流石っす👍
とはいえ、これだけの記事だとChatGPTをそのまま貼り付けただけの記事になるので、ちゃんと性能評価もやってみます。
MySQLの性能評価
dockerに最新のubuntuイメージを作成し、その中で最新のmysqlとsysbenchをインストールして実行します。
FROM ubuntu:latest
RUN apt-get update && apt-get install -y \
mysql-server \
sysbench \
&& rm -rf /var/lib/apt/lists/*
EXPOSE 3306
CMD ["mysqld"]
# mysql --version
mysql Ver 8.0.40-0ubuntu0.24.04.1 for Linux on x86_64 ((Ubuntu))
sysbenchによる性能評価
テストデータの準備:
# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=localhost --mysql-user=sbtest --mysql-password=password --mysql-db=sbtest --table_size=1000000 --tables=3 prepare
読み書きテストの実行:
# sysbench /usr/share/sysbench/oltp_read_write.lua --mysql-host=localhost --mysql-user=sbtest --mysql-password=password --mysql-db=sbtest --table_size=1000000 --tables=3 --threads=6 --time=60 run
実行結果
SQL statistics:
queries performed:
read: 347382
write: 99252
other: 49626
total: 496260
transactions: 24813 (413.24 per sec.)
queries: 496260 (8264.79 per sec.)
ignored errors: 0 (0.00 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0436s
total number of events: 24813
Latency (ms):
min: 6.52
avg: 14.51
max: 136.19
95th percentile: 25.74
sum: 360121.56
Threads fairness:
events (avg/stddev): 4135.5000/19.47
execution time (avg/stddev): 60.0203/0.01
PostgreSQLの性能評価
こちらもdockerに最新のubuntuイメージを作成し、その中で最新のPostgreSQLとsysbenchをインストールして実行します。
・・・ちょっとこちらはDockerfileでPostgreSQLとsysbenchをインストールできなかったので、ubuntuイメージを作成し、次にコンテナに入ってPostgreSQLとMYSQLとsysbenchをインストールする、というやり方をとりました。（条件違っちゃうじゃん・・うーん導入部分だから許容かなぁ。。）
$ docker pull ubuntu:latest
$ docker run -it --name my-ubuntu ubuntu:latest
コンテナ内に入って
# apt-get update
# apt-get install -y software-properties-common
PostgreSQLリポジトリを追加
# apt-add-repository -y ppa:postgresql/ppa
# apt-get update
PostgreSQLのインストール
# apt-get install -y postgresql postgresql-contrib
sysbenchのインストール
# apt-get install -y sysbench
PostgreSQLの設定
# service postgresql start
# su - postgres
PostgreSQLのバージョン確認
$ psql --version
psql (PostgreSQL) 16.6 (Ubuntu 16.6-0ubuntu0.24.04.1)
sysbenchによる性能評価
テストデータの準備:
$ sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=pgsql --pgsql-db=testdb --pgsql-user=postgres --pgsql-password='your_password_here' --tables=3 --table-size=1000000 prepare
読み書きテストの実行:
$ sysbench /usr/share/sysbench/oltp_read_write.lua --db-driver=pgsql --pgsql-db=testdb --pgsql-user=postgres --pgsql-password='your_password_here' --tables=3 --threads=6 --time=60 run
実行結果
SQL statistics:
queries performed:
read: 657762
write: 187354
other: 94245
total: 939361
transactions: 46835 (780.48 per sec.)
queries: 939361 (15653.89 per sec.)
ignored errors: 148 (2.47 per sec.)
reconnects: 0 (0.00 per sec.)
General statistics:
total time: 60.0066s
total number of events: 46835
Latency (ms):
min: 2.83
avg: 7.68
max: 1017.84
95th percentile: 9.39
sum: 359853.06
Threads fairness:
events (avg/stddev): 7805.8333/15.78
execution time (avg/stddev): 59.9755/0.01
両者の結果を比較
PostgreSQLとMySQLのOLTP性能評価結果を比較してみましょう。
統計情報の比較
1. クエリ数とトランザクション数
| 指標 | PostgreSQL | MySQL |
|---|---|---|
| 読み取りクエリ数 | 657,762 | 347,382 |
| 書き込みクエリ数 | 187,354 | 99,252 |
| その他のクエリ数 | 94,245 | 49,626 |
| トランザクション数 | 46,835 (780.48 per sec.) | 24,813 (413.24 per sec.) |
| 全体のクエリ数 | 939,361 (15,653.89 per sec.) | 496,260 (8,264.79 per sec.) |
2. 一般統計
| 指標 | PostgreSQL | MySQL |
|---|---|---|
| 総時間 | 60.0066秒 | 60.0436秒 |
| イベント総数 | 46,835 | 24,813 |
3. レイテンシ（遅延）
| 指標 | PostgreSQL | MySQL |
|---|---|---|
| 最小レイテンシ | 2.83 ms | 6.52 ms |
| 平均レイテンシ | 7.68 ms | 14.51 ms |
| 最大レイテンシ | 1017.84 ms | 136.19 ms |
| 95パーセンタイル | 9.39 ms | 25.74 ms |
4. スレッドの公平性
| 指標 | PostgreSQL | MySQL |
|---|---|---|
| イベント（平均/標準偏差） | 7805.83 / 15.78 | 4135.50 / 19.47 |
| 実行時間（平均/標準偏差） | 59.9755 / 0.01 | 60.0203 / 0.01 |
分析と考察
-
トランザクション数:
- PostgreSQLは46,835トランザクションを処理し、MySQLは24,813トランザクションです。PostgreSQLは約1.88倍多くのトランザクションを処理しています。
-
クエリ数:
- PostgreSQLは全体で939,361のクエリを実行しましたが、MySQLは496,260でした。PostgreSQLがより多くの読み取りと書き込み操作を処理しています。
-
レイテンシ:
- PostgreSQLの平均レイテンシは7.68 msで、MySQLの14.51 msよりも優れています。これは、PostgreSQLがより迅速にクエリを処理していることを示しています。
- 最大レイテンシに関しては、PostgreSQLが1017.84 msと非常に高い値を示しています。これは特定のクエリでボトルネックが発生している可能性があります。
-
スレッドの公平性:
- PostgreSQLはイベントの平均数が7805.83で、MySQLよりも多くなっています。これは、PostgreSQLがより多くのイベントを処理しつつ、スレッド間で均等に負荷を分散していることを示します。
結論
- パフォーマンス: PostgreSQLはMySQLに比べて全体的に優れたパフォーマンスを発揮しています。特にトランザクション数や平均レイテンシにおいて、PostgreSQLが優位です。
- ボトルネック調査: PostgreSQLの最大レイテンシが高いことから、特定のクエリや設定に問題がある可能性があります。PostgreSQLのほうにはエラーが148件 （1秒あたり約2.47）出ている点も引っ掛かります。実はPostgreSQLの方は環境の準備に少々の苦戦があり、ちゃんと作れていない可能性もあります。。今回の検証結果はその点も加味すべきかも。。
まとめ
うーん、、、PostgreSQL優秀だな。。
これまで特に理由なく敬遠していたけど反省しなくては。。。
ただ、やはり少しいじった感想として、MySQLに慣れている身としては癖を感じたのもまた事実。
最近はアプリケーション側からORM噛ませて処理したりするのでPostgreSQL本体の操作感はあまり関係ないかもしれませんがね。
他にも今回は触れていないけど、マテリアライズドビューとか使えるのはシステムが複雑化してきた時に便利ですよね。。
今個人開発で作っているやつ、PostgreSQLに置き換えようかな。。。
とまぁ、まとめにもなってないまとめですが、色々勉強になったなーというのが個人的な感想でした。
Discussion