MySQLユーザーがPostgreSQLを使う事になった時の比較まとめ

これはなに？

今までMySQLしか使った事なかったのですが、PostgreSQLを使うことになった為、MySQLとの違いを纏めた記事です。

[PostgreSQLとMySQL、使うならどっち？ データベース専門家が8つの視点で徹底比較！](https://employment.en-
japan.com/engineerhub/entry/2017/09/05/110000#%E6%AF%94%E8%BC%83%E3%83%9D%E3%82%A4%E3%83%B3%E3%83%88%E3%82%B9%E3%83%88%E3%82%A2%E3%83%89%E3%83%97%E3%83%AD%E3%82%B7%E3%83%BC%E3%82%B8%E3%83%A3%E3%83%88%E3%83%AA%E3%82%AC%E3%83%BC)
を参考にさせてもらいつつ、概要まとめ/他記事の参照/公式ドキュメントの参照/補足をしたものです。

※ 最新のドキュメント、参考にした比較記事などは現時点（2018/10)でなるべく新しい目のものを見るようにしていますが、比較する上での対象バージョン、末リリース版などを追いきれてない部分はあります。
※ MySQLの説明についてはinnodbエンジンを基本としています。

特徴（設計思想）

・MySQL
高速に動作すること。複雑な処理や厳格な処理は積極的にサポートしない。
・PostgreSQL
高機能、堅牢性

詳細

MySQLは、速度面を優先する設計となっている面が多いようです。
（詳細は細かい比較項目を下にいくつか書きますが、文字列キャストや判定をデフォルトでゆるく行うことで速度を重視したり）
しかし、indexを1度に一つしか使えないことやJoinのアルゴリズムが少ないなど複雑な検索処理が苦手だったりします。

一方、PostgreSQLはウィンドウ関数や、SELECT文の実行前にサブクエリを作成できるWITH句が先行して実装されていたり（MySQLにも結局入るが）、JSON型もMySQLよりも早く導入されたようです。
また、文字列の暗黙的なキャストは無く、明示的にキャストが必要など判定が厳しくなっていたりします（こちらも詳細は下記)。

こちらは2008年のけっこう古い記事ですが、当時の情報で比較がまとまっていました。
[PostgreSQLの機能と他のRDBMSの比較] (https://lets.postgresql.jp/documents/tutorial/rdbms-hikaku/1)

DDL操作

・MySQL
多くの操作をオンラインで実行できる（ようになった）

・PostgreSQL
DDL文にもよるが、カラム追加などの構成変更を伴う操作はテーブル参照すらブロックしてしまう。

説明

MySQLのオンラインDDLについては
MySQL 5.6 リファレンスマニュアル オンライン DDL の概要
の「表 14.5 DDL 操作のオンラインステータスのサマリー」にまとまっていますが、かなりの部分で並列DML（通常のSQL操作）が行えるするとされています。

PostgreSQLはDDLにより、ほぼ並列DML（通常のSQL操作）をブロックしてしまう発生してしまうようです。（ACCESS EXCLUSIVEロックを取得する）
PostgreSQL 10.4文書 ALTER TABLE
ただ、pg_repackという外部ツールがあり、ロックを最小限に行えるとのこと。
https://github.com/reorg/pg_repack/blob/master/doc/pg_repack_jp.rst
pg_repackでのALTER TABLEは下記ドキュメントの
https://reorg.github.io/pg_repack/jp/

即ち、 ALTER TABLE ... SET TABLESPACE 相当の処理をオンラインで実施します

のあたりだと思われます。

余談

また、オンラインで実行できる（ブロックしない）というだけで、他のクエリの操作が必ず影響なく実行できるというわけでないと思わない方が完全かもしれません。
「RDS」だったからというのはあるかもしれませんが、数千万件のADD INDEXを行なっている際にSELECT/UPDATE/INSERTの操作が数十秒遅延するという事が発生したこともあります。また、下記記事にも同様の調査が書かれています。
外道父の匠 AuroraのALTER TABLE性能検証とRDS比較

RDSの結果にバラつきがあるのは、ALTER TABLE実行後・数秒は軽い状態が続いてから重くなる、という性質のせいです

SELECT文

・MySQL
ソーとのアルゴリズムがあまり優れていない

・PostgreSQL
ソートの部分以外はそれほどMySQLとの差はない

詳細

単純なSELECTであれば、それほど差はなさそう。
ただ、後で出てくるJoinアルゴリズムやindexの複数利用など、複雑なSQLがある場合はPostgreSQLの方が得意。

UPDATE文

・MySQL
速い

・PostgreSQL
MySQLに比較すると基本的に遅い

詳細

PostgreSQLはUPDATE文がMySQLに比較すると遅い。

MySQLは、UPDATE対象となる行の値を直接上書きするが、PostgreSQLは追記型アーキテクチャというものを採用しているため、UPDATEはINSERTに近い<処理。
・追記型アーキテクチャ
　before/afterの内部レコードを別に保持して、参照を切り替える方式

また、物理レコードはcttidと呼ばれる内部IDをもっていて、セカンダリインデックスの配置時には紐づくctidをインデックス分参照変更をする。つまり、大量のindexをもったテーブルなどは追加/更新でその部分の更新が起こってしまい非効率とのこと。
このアーキテクチャと相性が悪いUber Engineeringは、MySQLに乗り換えた。
https://postd.cc/uber-mysql-migration/

DELETE

・MySQL
遅いと言われていたが、そうでもなくなった。

・PostgreSQL
MySQLとそんなに変わらなそうだが、追記型アーキテクチャにより遅め？

説明

UPDATEの項目にある、追記型アーキテクチャによりレコードの更新は速度的にPostgreSQLが基本的に不利だが、MySQLもDELETEに関しては似たようなことをしていた(セカンダリインデックスの参照を同期的に更新）
ただし、MySQL5.5で非同期のセカンダリインデックス更新が行われるようになったためそれほど遅くなくなった。
そのため、MySQLのほうがPostgreSQLよりは速度面で有利に見える。

JOIN

・MySQL
ネステッドループ結合（Nested Loop Join）くらいしかない

・PostgreSQL
得意。ネステッドループ結合（Nested Loop Join）」「ハッシュ結合（Hash Join）」「ソートマージ結合（Sort Merge Join）」をサポートしている

説明

MySQLはNested Loopしか基本的に実装されていない。
Nested Loop 結合アルゴリズム 8.2.1.10 Nested Loop 結合アルゴリズム

PostgreSQLはMySQLには無い「ハッシュ結合（Hash Join）」「ソートマージ結合（Sort Merge Join）」もサポートしいる。
ハッシュ結合はハッシュ表をメモリに作るので、ハッシュの付き合わせはその分速いが、表が大きなる場合はメモリ量もその分必要となるので注意が必要。

備考

それぞれのJOINの説明はここがわかりやすかった。
[エディラボ 　4.5.4 実行計画の読み方とSQLチューニングのための考慮点] (https://www.edifist.co.jp/lecture/dbdesign/04_05/04.aspx)
[TECH SCORE BLOG PostgreSQL Index Only Scan 奮闘記 その3]
(http://www.techscore.com/blog/2013/06/07/postgresql-index-only-scan-%E5%A5%AE%E9%97%98%E8%A8%98-%E3%81%9D%E3%81%AE3/)

トランザクション

・MySQL
デフォルトは、「REPEATABLE-READ」。トランザクション内でSELECT分の結果が他のトランザクションに影響されない（同じものが見える）。

・PostgreSQL
デフォルトは「READ-COMMITED」。他のトランザクションからの更新結果が反映される。運用する上で注意は必要。

説明

デフォルトのモードが違うこと、またPostgreSQLはトランザクション分離レベルをREPEATABLE-READに変更したとしても、ネクストキーロックを取らないのでファントムリードを防げる。

なぜ防げるか？については、「述語ロック」という仕組みを使っている。
下記が分かりやすかったです。
https://qiita.com/yuba/items/89496dda291edb2e558c
where条件となる述語(where 'username = 'alice'など）を印として、別トランザクションで検知する仕組みのようですね。

つまり、同じトランザクション分離レベルでも実装の違い（挙動の違い）があるので注意が必要そうですね。

備考

Wikipedia トランザクション分離レベル

ストアドプロシージャ、トリガー

・MySQL
ストアドプロシージャはSQLのみ、トリガーはFOR EACH ROWしかなくてFOR EACH STATEMENTがない

・PostgreSQL
SQL、Pythonなどを利用した外部プロシージャも使える（すごい!）

レプリケーションの論理型と物理型

・MySQL

論理型（SQL文そのものをコピー）、物理型（変更後の行イメージをコピー）のいずれか選べる。
論理型がデフォルト設定だったが、MySQL 5.7以降では物理型がデフォルトに。

・PostgreSQL
PostgreSQLは物理型のみだったが、バージョン10からは論理型も使える。

説明

安全性重視のPostgreSQLにもついに論理型が入ってくるようです。
物理型ではデータ不整合が起こらず、堅牢性としてあるべきものに見えます。
（ただ、余談にも書きましたが論理型を利用した運用もあり）

その他、レプリケーションの同期/非同期周りでの違いもありそうですが最新事情については詳しく調べきれていません。
https://www.slideshare.net/masahikosawada98/mysqlpotgresql

余談

論理型はデータ構成が違ってもSQLさえ通ればレプリケーションは動作するので、
それを利用して

1. レプリケーションされているSlave側で巨大なALTER TABLEを実行しておく（巨大なALTER TABLE実行中はレプリケーションはロックにより遅延してしまう）
2. SlaveのALTER TABLEが完了し、レプリケーションが追いついたらMasterと入れ替える
3. 表向きには、巨大なALTER TABLEの影響なしに運用できる
   みたいな事をやってた事もありました（遠い日）。

その他機能

・MySQL
ウィンドウ関数、WITHも今はないがバージョン8.0から導入予定

・PostgreSQL
パラレルクエリなど、独自のものあり
https://www.postgresql.jp/document/9.6/html/parallel-query.html

PostGISというサードパーティのOSSツール。地図や幾何データを扱う大人気ツールがある
http://postgis.net/

データ型

・MySQL
バージョン5.6以前はゆるかったが、5.7から厳格になった。

・PostgreSQL
厳格。

説明

MySQLは暗黙的な型変換がそれなりにあり、
version 5.6 MySQL 5.6 リファレンスマニュアル 12.2 式評価での型変換
にもあるが、

mysql> SELECT 1+'1';
        -> 2
mysql> SELECT CONCAT(2,' test');
        -> '2 test'

のようにMySQL側でよしなに扱ってしまうため、便利というより経験上トラブルが多かった。
ただ、新しいバージョンではこのような事もなく暗黙的な処理は無くなっている。
その他、MySQLでは
PostgreSQLとMySQL、使うならどっち？ データベース専門家が8つの視点で徹底比較！にもあるように、

「は」と「ぱ」と「ば」はイコールになりますし、「びょういん」と「びよういん」もイコールになります。

というようにUnicodeの照合順序（並べ替え）の厳格さの設定が、レベル1からレベル4の中の1(一番ゆるい）を処理速度のためデフォルトとしているとのこと。

PostgreSQLは、もともと暗黙的なキャストは無く厳格だった。

VACUUME(OPTIMIZE)

・MySQL

・PostgreSQL
通常動作では、削除されたタプルや更新によって不要となったタプルは、テーブルから物理的には削除されない。
（タプル＝内部レコード。PostgreSQLは追記型であるため、更新前後でタプルが存在する）

説明

VACUUME(OPTIMIZE)は、データの断片化、レコード削除などで出た不要スペースを解消するコマンド。
MySQLでもPostgresqlでも、この作業は必要。

Postgresqlでは自動で行われる、
PostgreSQL 10.4文書19.10. 自動Vacuum作業
のようなものもありますが、

VACUUM FULLでは、テーブルの内容全体を新しいディスクファイルに領域を余すことなく書き換えるため、オペレーティングシステムに未使用の領域を返すことができます。 この形式では、実行速度がかなり低速になります。また、処理中のテーブルに対する排他的ロックが必要になります。

となるので、結局タイミングを見てやる必要がありそうですね。

MySQLの方はオンラインDDLが実装されていらい、OPTIMIZE TABLEもロックはしなくなったとの事ですね。（ただ、他のDDLと同様にロック以外の影響が無いとは断言できないと思われる）
MySQL 5.6 リファレンスマニュアル / 13.7.2.4 OPTIMIZE TABLE 構文

まとめ

MySQLとPostgreSQLの違いを[PostgreSQLとMySQL、使うならどっち？ データベース専門家が8つの視点で徹底比較！](https://employment.en-
japan.com/engineerhub/entry/2017/09/05/110000#%E6%AF%94%E8%BC%83%E3%83%9D%E3%82%A4%E3%83%B3%E3%83%88%E3%82%B9%E3%83%88%E3%82%A2%E3%83%89%E3%83%97%E3%83%AD%E3%82%B7%E3%83%BC%E3%82%B8%E3%83%A3%E3%83%88%E3%83%AA%E3%82%AC%E3%83%BC)やその他もろもろ参考にしつつ纏めました。

どっち使うか？については、基本機能はお互い近づいているようなので差分はそれほど考えるところでも無いのかもしれません。
好きな方、エコシステムやツールの都合、などで決めても良いのかもしれません。

書いてある内容、特にPostgreSQLについてはほぼ経験がなく、誤っている箇所があるかもしれません。何か見つけたら教えてください！