フレームワーク、ホスティング、チームがすでに MySQL を使っているのでなければ、新しいプロジェクトには PostgreSQL を選んでください。PostgreSQL は SQL の機能が豊富です (RETURNING、FULL OUTER JOIN、MERGE、部分インデックス、トランザクション内でのスキーマ変更)。GIN インデックスが使えるバイナリ形式の jsonb 型、配列、PostGIS などの拡張機能もあります。毎日書く SELECT、JOIN、INSERT はどちらでもほぼ同じです。
| PostgreSQL 17 | MySQL 8.4 | |
|---|---|---|
| ライセンス | PostgreSQL License (制限が緩い) | Community 版は GPLv2、Oracle の商用版あり |
| 変更した行を返す | RETURNING | なし |
| upsert | INSERT ... ON CONFLICT、MERGE | INSERT ... ON DUPLICATE KEY UPDATE |
FULL OUTER JOIN | あり | なし |
| トランザクション内の DDL | ロールバックできる | トランザクションがコミットされる |
| JSON | json と jsonb、GIN インデックス | JSON、生成列か関数インデックスでインデックス化 |
| 配列、部分インデックス | あり | なし |
| デフォルトの分離レベル | Read committed | Repeatable read |
| デフォルトの文字列比較 | 大文字と小文字を区別する | 区別しない (utf8mb4_0900_ai_ci) |
INSERT した行をそのまま受け取る
RETURNING は INSERT、UPDATE、DELETE が処理した行を返します。生成された id を 2 回目のクエリなしで受け取れます。
id が 1 と 2 の 2 行が返ります。MySQL には RETURNING がありません。LAST_INSERT_ID() で、直前の INSERT が最初に生成した id を取得します。
INSERT INTO users (email) VALUES ('asha@example.com');
SELECT LAST_INSERT_ID();
1 つの文で挿入または更新する
PostgreSQL では競合するキーを指定し、挿入しようとした行を excluded として参照します。
A-1 は 8 になり、B-2 は 7 で挿入されます。MySQL 8.4 では行エイリアスを使います (以前の VALUES(qty) 関数は非推奨です)。
INSERT INTO stock VALUES ('A-1', 3), ('B-2', 7) AS new
ON DUPLICATE KEY UPDATE qty = stock.qty + new.qty;
DO NOTHING と条件付きの更新は upsert のページ で扱います。
MySQL にない JOIN
FULL OUTER JOIN は両側の一致しない行も残します。MySQL では LEFT JOIN と RIGHT JOIN を UNION でつないで代用します。
free には登録がなく、team には価格がありません。それでも両方とも表示され、欠けている列は NULL になります。
スキーマ変更をロールバックする
PostgreSQL では CREATE、ALTER、DROP もトランザクションの一部です。マイグレーションが失敗しても、スキーマが中途半端な状態で残りません。
残るのは orders.id だけです。MySQL では DDL 文を実行するたびに開いているトランザクションがコミットされるため、マイグレーションツールは失敗した文より前の文を取り消せません。
JSON を保存して検索する
PostgreSQL の jsonb は解析済みの JSON を保存し、GIN インデックスが包含 (@>) とキー (?) の判定に使われます。MySQL の JSON 型もバイナリ形式です。->> のパスで検索し、インデックスは生成列か関数インデックスを使ってパスごとに 1 つずつ作ります。
SELECT name FROM users WHERE profile->>'$.plan' = 'pro';
PostgreSQL の演算子は JSONB のページ で紹介しています。
読み込みと書き込みの同時実行
どちらもマルチバージョン同時実行制御 (MVCC) を使うので、読み込みが書き込みをブロックしません。PostgreSQL は更新のたびに新しい行バージョンを書き込み、古いバージョンは VACUUM が回収するまでテーブルに残します。この処理は autovacuum がバックグラウンドで行い、更新の多いテーブルは autovacuum が追いつくことが前提になります。InnoDB は行をその場で更新し、古いバージョンを undo ログに保持します。これはパージスレッドが消去します。
デフォルトの分離レベルが違います。PostgreSQL は read committed で、各文は開始前にコミットされたデータを見ます。MySQL は repeatable read で、トランザクション内の通常の読み込みはすべて同じスナップショットを見ます。さらに UPDATE、DELETE、ロック付きの読み込みは、スキャンしたインデックスの範囲もロックします (ネクストキーロック)。
レプリケーション、フェイルオーバー、ツール
PostgreSQL にはストリーミングレプリケーションが標準で付いています。サーバー全体の物理コピーを保ち、読み込みにも使えます。パブリケーションとサブスクリプションによる、指定したテーブルの論理レプリケーションもあります。自動フェイルオーバーには外部ツールが必要です。MySQL はバイナリログでレプリケーションを行い、InnoDB Cluster の土台である Group Replication も提供しています。
クライアントは、PostgreSQL なら psql と pgAdmin、MySQL なら mysql クライアント、MySQL Shell、MySQL Workbench があります。どちらも AWS、Google Cloud、Azure のマネージドサービスで動かせます。
MySQL の書き方を PostgreSQL に置き換える
| MySQL | PostgreSQL |
|---|---|
`order` | "order" |
AUTO_INCREMENT | GENERATED ALWAYS AS IDENTITY |
IFNULL(a, b) | COALESCE(a, b) |
LIMIT 20, 10 | LIMIT 10 OFFSET 20 |
WHERE name = 'ana' が Ana に一致する | WHERE name ILIKE 'ana' |
TINYINT(1) | boolean |
よくある質問
PostgreSQL は MySQL より速いですか?
一般論としてどちらが速いとは言えません。結果はワークロード、スキーマ、インデックス、サーバー設定で変わります。速さで選ぶなら、自分のデータで自分のクエリをベンチマークしてから決めてください。
PostgreSQL は MySQL より習得が難しいですか?
毎日書く SQL (SELECT、JOIN、INSERT、UPDATE) はどちらもほぼ同じです。PostgreSQL は型に厳しく、デフォルトで大文字と小文字を区別します。そのため MySQL が受け付けるクエリの一部は、明示的なキャストや ILIKE が必要になります。
MySQL のデータベースを PostgreSQL に移行できますか?
できます。pgloader などのツールでスキーマとデータをコピーできます。そのあとクエリを書き換えます。バッククォートはダブルクォートに、AUTO_INCREMENT は identity 列に、ON DUPLICATE KEY UPDATE は ON CONFLICT に、IFNULL は COALESCE に置き換えます。
PostgreSQL と MySQL は無料ですか?
どちらも無料で使えます。PostgreSQL は PostgreSQL License という制限の緩いライセンスで、有料版はありません。MySQL Community Edition は GPLv2 で、Oracle が追加機能とサポート付きの商用版を販売しています。