📣 AIによるコンテンツ執筆に関するZennの方針について
🐘

MySQLが好きな私が、今はPostgreSQLを勧めたい理由

に公開
43
MySQL
PostgreSQL
Database
MySQL 8
tech

私はMySQLが好きです。長く使ってきましたし、オンプレミスでの運用もやってきました。

しかし現職に来てからは、PostgreSQLを使う機会が増えました。最初は正直かなり抵抗感がありました。ずっとMySQLを使ってきたので、慣れの問題もありますし、PostgreSQLに対して必要以上に構えていたところもあったと思います。

ただ、実際に使っていくうちに、PostgreSQLの良さが少しずつ見えてきました。最近では、新規開発でどちらを選ぶかと聞かれたら、PostgreSQLを選びたいと思うようになっています。

私はMySQLを長く使ってきたので、昔のMySQLの雑さも知っています。ただ同時に、今でも昔の印象だけでMySQLを語るのは不正確だとも思っています。sql_modeをきちんと設定すれば危ない挙動の多くは避けられますし、MySQL 8でかなり多くの機能が入りました。

また、今回はオンプレミス時代の印象ではなく、今のMySQLとPostgreSQLを、クラウドで動かすことを前提に比較したいと思っています。昔はPostgreSQLのデメリットとして語られていたことの中にも、今ではあまり大きな論点ではなくなったものがあるからです。

これは「MySQLはダメ」という話ではありません。また、「PostgreSQLの思想が美しい」みたいな話でもありません。

結論だけ書くと、次の2点です。

  • PostgreSQLのデメリットとされてきたことは、機能差がかなり埋まり、マネージドサービス前提では気にしなくてよいものも増えた
  • その一方で、アプリケーション実装の観点では、今でもPostgreSQLのほうが明確に有利な点が残っている

この記事では、その観点で整理します。

PostgreSQLのデメリットとされてきたことは、かなり薄まった

昔の比較では、PostgreSQLの弱点として運用の重さやDDLまわりの扱いづらさがよく挙げられていました。

ただ、このあたりは今そのまま持ち出すのは少し古いと思っています。

MySQL側はオンラインDDLがかなり充実しましたが、少なくとも日常的なカラム追加のような作業で、MySQLとPostgreSQLに明確な差があるとは思っていません。パーティショニングも、昔の印象ほど大きな論点ではなくなりました。

また、VACUUMのようなPostgreSQL特有の運用論も、マネージドサービス前提なら利用者が直接面倒を見る場面はかなり減っています。オンプレで全部自分で抱える前提の比較を、そのまま今のクラウド時代に持ち込むのはあまりフェアではありません。

レプリケーション周りの差も、最近はマネージドサービスが主流になり、ユーザーが直接触れない部分も増えました。以前ほど、MySQLの優位性を強く意識する場面は減っていると感じています。

つまり、昔はPostgreSQLを勧めにくい理由として成立していたものの一部が、今はかなり弱くなっているというのが前提です。

そのうえで、アプリケーション実装ではPostgreSQLのほうが強い

本題はここです。

MySQL 8でかなり差は埋まりました。それでも、実際にアプリケーションを書く立場で見ると、PostgreSQLのほうが勧めやすい理由がまだ残っています。

まず、MySQL 8で入って差が埋まったもの

ここは最初に明確にしておきます。以下は「昔はPostgreSQLの優位として語れたが、MySQL 8で入ったので今は決定打ではないもの」です。

  • CHECK制約
  • Window関数
  • SKIP LOCKED

このあたりは、今さら「PostgreSQLにしかない強み」として語るのはフェアではありません。

ただし、後で書くように、Window関数そのものがMySQL 8で入ったことと、Window関数を更新処理に自然に持ち込めることは別の話です。後者は今でもPostgreSQLのほうがかなり扱いやすいと思っています。

ON CONFLICT DO NOTHINGはINSERT IGNOREの代わりではない

これはMySQLに元々ない機能で、PostgreSQLを勧めるかなり大きな理由の一つです。

MySQLにはINSERT IGNOREがあります。
ただ、これはON CONFLICT DO NOTHINGの代わりとしては扱いにくいです。

PostgreSQLのON CONFLICT DO NOTHINGは、基本的には「一意制約の競合が起きたときだけ挿入しない」を明示的に書く機能です。やりたいことがそのままSQLになっています。

一方でMySQLのINSERT IGNOREは、重複だけを無視するための専用機能ではありません。エラーをwarning化して処理を継続する方向の機能なので、「重複だけ無視したい」という用途には広すぎます。

この差は小さく見えて、実務ではかなり大きいです。
レビュー時にも挙動が読みやすいですし、意図しない入力不正まで飲み込みにくいからです。

RETURNINGがかなり強い

これもMySQLに元々ない機能で、PostgreSQLを勧めるかなり大きな理由です。

PostgreSQLではINSERT/UPDATE/DELETE ... RETURNINGが使えます。変更した結果をその場で返せるので、更新結果の取得を自然に1文で閉じられます。

例えば次のようなことができます。

INSERT INTO users(name, email)
VALUES('catatsuy', 'catatsuy@example.com')
RETURNING id, name, email, created_at;

これがあると、

  • insertした結果のIDをそのまま受け取る
  • デフォルト値や保存後の値をそのまま受け取る
  • update後の行をそのまま返してAPIのレスポンスに使う
  • upsertした結果をそのまま受け取る

といった処理がかなり自然になります。

MySQLでありがちなLAST_INSERT_ID()ベースの処理は、正直かなり厳しいです。
返せる情報が狭く、基本的にはauto_incrementな数値IDの取得に寄っています。
insertした結果の任意の列を自然に受け取れませんし、デフォルト値やgenerated columnを含んだ完成形の行をそのまま返すこともできません。

たとえばほしいのはこういうことです。

  • UUIDを主キーにしているので、そのまま返したい
  • generated columnやデフォルト値を含んだ完成形の行を返したい
  • insertした結果の行全体をそのまま次の処理に渡したい

LAST_INSERT_ID()ではこれができません。

さらに、LAST_INSERT_ID()は複数行を1つのステートメントで挿入した場合でも、挿入された結果をそのまま返してくれるわけではありません。取得できるのは先頭のAUTO_INCREMENT値だけです。

そのため、複数行INSERTの結果をそのままアプリケーションで扱いたい場合にも不便です。PostgreSQLのRETURNINGであれば、挿入された行をそのまま返せるので、この差はかなり大きいです。

「変更した結果をそのまま返せる」というのは、単なる便利機能ではなく、アプリケーションの実装の組み立て方そのものに効いてきます。

VALUESが実装に効く

これはMySQLに存在しない機能という話ではなく、PostgreSQLのほうがかなり自然に使える機能だと思っています。

小さな定数表をその場で作ってJOINしたり、そのまま更新処理につないだりしやすい。これがあると、アプリケーション側に中途半端な一時テーブル的処理を逃がさずに済みます。

例えば、アプリケーションから受け取った少数の値をそのままJOINして更新したいとき、PostgreSQLではこう書けます。

UPDATE users u
SET plan = v.plan
FROM (
  VALUES
    (1, 'pro'),
    (2, 'free'),
    (3, 'team')
) AS v(id, plan)
WHERE u.id = v.id;

この種の処理は実装でかなりよく出ます。
少数件のマスタ的な値をその場で渡して更新したいとか、APIから受け取った値群をそのままSQLに流し込みたいとか、そういう場面です。

別の例として、アプリケーションから受け取った値群をJOIN対象として扱うのも自然です。

SELECT u.*
FROM users u
JOIN (
  VALUES
    (1),
    (2),
    (5)
) AS v(id)
ON u.id = v.id;

MySQLでも似たことがまったくできないわけではありません。MySQL 8.0.19以降はVALUES文が入り、テーブル値コンストラクタとして扱えます。

ただ、MySQLではROW(...)で書く必要があり、列名もそのままだとcolumn_0、column_1のような形になります。PostgreSQLのように、その場で小さな定数表を作って、自然な列名を付けて、そのままJOINやUPDATEに流し込む感覚とは少し違います。

例えばMySQLだと、同じ発想の処理でもこういう書き方になります。

SELECT u.*
FROM users u
JOIN (
  VALUES ROW(1), ROW(2), ROW(5)
) AS v
ON u.id = v.column_0;

派手な機能ではありませんが、こういうところが日常的な実装のしやすさに効いてきます。

Window関数を更新処理に持ち込めるのが強い

ここは重要です。

Window関数自体はMySQL 8で入りました。
なので、Window関数そのものをPostgreSQLだけの強みとして語るのは違います。

ただ、PostgreSQLではWITHやUPDATE ... FROMと組み合わせて、Window関数の結果を更新処理に自然に持ち込みやすいです。ここは今でも差があると思っています。

例えば「ユーザーごとに最新1件だけフラグを立てる」なら、こう書けます。

WITH ranked AS (
  SELECT
    id,
    ROW_NUMBER() OVER(PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM sessions
)
UPDATE sessions s
SET is_latest = (r.rn = 1)
FROM ranked r
WHERE s.id = r.id;

Window関数自体はMySQL 8で入っています。
ただ、こういう更新系のロジックとの接続はPostgreSQLのほうがかなり自然です。

これは分析用途の便利機能ではなく、アプリケーション実装の武器として効きます。

部分インデックスは明確に機能差がある

これははっきり機能差として言えます。
MySQLには元々なく、今も不足している機能です。

PostgreSQLはpartial indexを持っていて、WHERE deleted_at IS NULLのように一部の行だけにインデックスを張れます。ソフトデリートと非常に相性がいいですし、状態別のレコード管理でも効きます。

CREATE INDEX idx_users_active_email
ON users(email)
WHERE deleted_at IS NULL;

例えば、ソフトデリートを使っているテーブルで「有効ユーザーのメールアドレス検索」だけを速くしたいとき、これを素直に書けます。

MySQLではgenerated columnや関数インデックスを使って近いことをする手はあります。ただ、これはpartial indexの代わりではありません。

PostgreSQLのpartial indexは、WHERE deleted_at IS NULLのような条件を満たす行だけをインデックスに載せられます。つまり、不要な行を最初からインデックスに含めません。

一方でMySQLのgenerated columnや関数インデックスは、基本的には全行に対して式を評価し、その結果にインデックスを張る仕組みです。式をうまく設計すれば近い用途には使えますが、「一部の行だけを物理的に小さく持つインデックス」をそのまま表現できるわけではありません。

そのため、サイズ、更新コスト、意図の明確さのどれを取っても、PostgreSQLのpartial indexのほうが素直です。MySQL側は回避策としては使えても、同じ機能があるとは言いにくいです。

ここは単なる書き味の差ではなく、機能差としてPostgreSQLを勧める理由になります。

外部キーはPostgreSQLのほうがかなり出来が良い

個人的な印象ですが、MySQL界隈では外部キー不要派が多く、PostgreSQL界隈では外部キー必要派が多いように感じています。

これは思想の違いというより、実際に外部キーを使ったときのテストの書きやすさ、運用のしやすさ、バグの入りにくさの差に起因していると思っています。

PostgreSQLでは遅延制約が使える

PostgreSQLでは外部キーをDEFERRABLEにできます。
これが非常に大きいです。

CREATE TABLE authors (
  id bigint PRIMARY KEY
);

CREATE TABLE books (
  id bigint PRIMARY KEY,
  author_id bigint NOT NULL,
  CONSTRAINT books_author_fk
    FOREIGN KEY(author_id)
    REFERENCES authors(id)
    DEFERRABLE INITIALLY DEFERRED
);

これがあると、制約チェックをtransactionの最後まで遅らせられます。

BEGIN;

INSERT INTO books(id, author_id) VALUES(1, 100);
INSERT INTO authors(id) VALUES(100);

COMMIT;

このSQLはPostgreSQLでは成立します。
途中では親が存在しませんが、commit時点で整合していればよいからです。

これは何に効くかというと、次のような場面です。

  • 相互参照を含むデータ投入
  • 複雑なテストデータの作成
  • 一時的に順序が前後する移行処理
  • バルクインサートや入れ替え処理

こういう処理は実務で普通に出ます。
そして、これが素直に書けるかどうかはかなり大きいです。

MySQLでは順序に厳しく縛られる

MySQLにはこの仕組みがありません。
NO ACTIONも実質RESTRICTです。

つまり、「最後に整合すればよい」という形で処理を組めません。常に親を先、子を後にする順序制約に縛られます。

小さな話に見えますが、これがあるとテストデータ投入も移行処理もかなり書きにくくなります。

例えばテストコードで、複数テーブルにまたがるfixtureを雑に流し込みたいとき、PostgreSQLならtransaction内で整合を最後に合わせる書き方ができます。MySQLではそれができないので、fixtureの投入順を常に厳密に管理する必要があります。

外部キーを使うほどテストが面倒になるなら、「じゃあ外部キーをやめよう」という文化が生まれやすいのは自然だと思っています。

MySQLは外部キーを無効化して逃げやすい

MySQLではforeign_key_checks=0で制約を無効化できます。
これは便利そうに見えますが、かなり危ないです。

SET foreign_key_checks = 0;

INSERT INTO books(id, author_id) VALUES(1, 999);

SET foreign_key_checks = 1;

これで、制約を無効化している間に入れた不整合データが残りえます。
再度有効にしても、その間に入った不整合を後から全部検証してくれるわけではありません。

この挙動があると、テストや移行の都合で制約を切って、そのまま不整合を持ち込む事故が起きやすくなります。

PostgreSQLは「制約は守ったまま、チェックタイミングを遅らせる」という道具を持っています。
MySQLは「制約そのものを切る」方向に寄りがちです。

この差はかなり大きいです。

外部キーの例だけでも差が見える

例えば、親子関係を持つよくあるテーブルを考えます。

CREATE TABLE users (
  id bigint PRIMARY KEY
);

CREATE TABLE orders (
  id bigint PRIMARY KEY,
  user_id bigint NOT NULL,
  CONSTRAINT orders_user_fk
    FOREIGN KEY(user_id)
    REFERENCES users(id)
    DEFERRABLE INITIALLY DEFERRED
);

PostgreSQLなら、テスト時に先にordersを入れてあとでusersを入れるようなこともtransactionの中で書けます。

BEGIN;

INSERT INTO orders(id, user_id) VALUES(10, 1);
INSERT INTO users(id) VALUES(1);

COMMIT;

この柔軟さは、fixture作成、データ移行、テストコードの単純化にかなり効きます。

MySQLにはこれがありません。
だからMySQLでは「外部キーは邪魔だから切る」「アプリケーションで担保する」という発想に流れやすく、PostgreSQLでは「外部キーをちゃんと使おう」という発想になりやすいのだと思っています。

PostgreSQLの外部キーが優れているのは、単に機能が多いからではありません。
制約を保ったままテスト・移行・データ投入を書きやすいので、結果として外部キーを実運用に載せやすいことが大きいです。

MySQLだとベクトル演算ができない

最近だとPostgreSQLを採用する理由として一番よく挙げられる気がします。

PostgreSQLにはpgvectorがあり、ベクトルを保持できるだけではなく、距離演算や類似検索まで含めてアプリケーションから普通に使えます。近傍検索用のインデックスもあり、実装にそのまま載せやすいです。

一方でMySQLは、少なくともOSS版を前提にすると、Vector型はMySQL 9.0（Innovation Releaseとして公開済み、LTSは未リリース）で追加されましたが、距離関数はMySQL HeatWave on OCIとMySQL AIでのみ提供され、MySQL CommercialやCommunityには含まれていません。つまり、OSS版ではベクトル演算ができず、実質的に使えません。PostgreSQL+pgvectorとの明確な差になります。

文字コードと照合順序まわりは、今でもMySQLのほうが話がややこしい

ここはかなり重要です。

私は文字コードと照合順序まわりについて、今でもPostgreSQLよりMySQLのほうが事故りやすいと思っています。

ただし、これはMySQL本体だけの問題ではありません。フレームワークやコネクタやデフォルト設定まで含めた話です。

有名な話として、いわゆる「ハハパパ問題」「寿司ビール問題」があります。
どちらも、文字コードそのものというより、照合順序、つまりcollationの問題です。

  • ハハパパ問題は、見た目としては別の文字列なのに、照合順序の都合で同一視される問題
  • 寿司ビール問題は、絵文字などを含む文字列比較が直感通りにならない問題

この種の問題が厄介なのは、「utf8mb4にしたから終わり」ではないことです。実際にはcharacter setとcollationの両方を理解しないといけません。

しかもMySQL 8では、これで単純化したというより、むしろさらに考えることが増えました。新しいcollationが入り、既存システムとの混在も起きるので、「昔の流儀」「MySQL 8以降の流儀」「フレームワークのデフォルト」が噛み合わないことがあります。

つまり、MySQL 8で改善した面は確かにあるものの、改善した結果として過去の流儀と新しい流儀が共存し、全体としてはさらにカオスになったという側面があります。

このあたりは、MySQL本体が悪いというより、歴史が長く、互換性を抱えながら進化してきた代償だと思っています。
ただ、アプリケーション開発者から見ると、その複雑さはそのまま事故の入り口になります。

まとめ

昔はPostgreSQLにも分かりやすい弱点がありました。
でも、今はその多くが薄まっています。機能差も埋まりましたし、マネージドサービス前提なら利用者が直接気にしなくてよいことも増えました。

一方で、アプリケーション実装の観点では、今でもPostgreSQLのほうが勧めやすい理由が残っています。

特に大きいのはこのあたりです。

MySQL 8で入って差が縮んだもの

  • CHECK制約
  • Window関数
  • SKIP LOCKED

PostgreSQLを勧める明確な理由

  • ON CONFLICT DO NOTHING
  • RETURNING
  • VALUES
  • Window関数を更新処理に持ち込めること
  • 部分インデックス
  • 外部キーの完成度
  • pgvectorによるベクトル演算
  • 文字コードと照合順序まわりの事故りにくさ

私はMySQLが好きです。
長く使ってきましたし、今でも性能を出しやすい、良いデータベースだと思っています。

その上でなお、今新規開発でどちらを採用するかという話になれば、私が勧めるのはPostgreSQLです。

MySQLがダメだからではありません。
昔の弱点がかなり埋まった今でも、アプリケーション実装のしやすさという点では、PostgreSQLの優位がまだ残っていると思うからです。

43

Discussion

ログインするとコメントできます
43