MySQLとPostgreSQLの違いは1割 — 移して確かめる

MySQLとPostgreSQL。二つのデータベースと共通のSQL書類、比較する虫眼鏡。

ここまでの5本で、MySQL はかなり触りました。SQLを書き、索引を張り、EXPLAIN を読み、トランザクションを張り、マイグレーションを流しました。

前回: 全部やるか、1つもやらないか — トランザクションとACID 作業場所はVMの中(Ubuntu Server 24.04)です。MySQL 8 に appdb、~/server/ に Express + Knex が動いている状態から始めます。

ここで質問です。

CREATE TABLE tasks (
  id INT AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL
) CHARACTER SET utf8mb4;

この文の中で、「SQLの機能」はどれで、「MySQLの機能」はどれでしょうか。

答えられなくて当然です。比べる相手がいないからです。 1つしか触っていないと、目の前の挙動がデータベース一般の話なのか、MySQLがそう作られているだけなのかが、原理的に区別できません。

(ちなみに AUTO_INCREMENT も CHARACTER SET utf8mb4 も、MySQLの機能です。 他所では通りません)

なので、2つ目を触ります。

先に言っておきます。この記事は「PostgreSQLのほうが優れている」という話ではありません。 結論から言うと、どちらでも大抵は足ります。 ただ、2つ知っていると、初めて設計の判断ができるようになります。

対象レベル: 初級〜中級 / 区分: 必修

目次

この記事でわかること

  • 2つ目を触る理由は優劣を決めるためではないこと。1つしか知らないと、SQLの仕様とMySQLの仕様を分離できない
  • PostgreSQLの最初の関門は、SQLではなく認証であること。OSのユーザーとデータベースのロールが結びついている(peer 認証)ので、MySQLと同じ気持ちで打つと入れない
  • SQLそのものは9割同じで、違うのは連番・大文字小文字・引用符・型の厳しさ・既定の分離レベルという、はっきり数えられる場所だけであること
  • ORMを使っていても、移行は無傷では終わらないこと。raw で書いたSQLと、INSERTの戻り値と、真偽値の型が、必ず引っかかる

この記事でやること

  1. PostgreSQL を入れて、sudo -u postgres psql で中に入る
  2. ロールとデータベースを作る(MySQLの CREATE USER + GRANT と対応させる)
  3. 同じテーブルを作り、同じSQLを打つ。 ほとんど同じことを確かめる
  4. 違うところを、1つずつ手で確かめる(SERIAL / 引用符 / 型 / EXPLAIN ANALYZE / JSONB / 分離レベル)
  5. Knex の接続先を差し替えて、マイグレーションがどこで落ちるかを見る
  6. どちらか片方を止める(両方入れっぱなしにしない)

先に知る言葉

この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。

言葉意味
PostgreSQLデータベースソフトのひとつ。「ポストグレスキューエル」。会話では「ポスグレ」「Postgres」と略される
psqlPostgreSQL に付いてくる対話コマンド。MySQL の mysql コマンドにあたる
ロール(role)PostgreSQL でのユーザーとグループを兼ねた概念。MySQL の「ユーザー」にあたるが、ロールにロールを入れられる
peer 認証「OSで誰としてログインしているか」をそのまま信用する認証方式。パスワードを聞かれない代わりに、OSのユーザー名と同じ名前のロールでしか入れない
スキーマ(schema)PostgreSQL のデータベースの中にある区切り。既定で public が1つある。MySQLには無い階層(MySQLは「スキーマ=データベース」)
SERIAL / IDENTITYPostgreSQL の自動連番。MySQL の AUTO_INCREMENT にあたる
JSONBPostgreSQL の型のひとつ。JSONを解析済みの形で保存する。 中身に索引を張れる
シーケンス(sequence)番号を配るだけの小さな部品。 PostgreSQL の連番は、裏でこれが動いている

「ロール」と「ユーザー」は、PostgreSQL ではほぼ同じものです。 正確には「ログインできるロール」がユーザーです。CREATE USER と打っても、実際には CREATE ROLE ... WITH LOGIN が実行されます。同じものの別名だと思ってください。

なぜ2つ目を触るのか

「どちらが優れているか」を調べるためではありません。 その質問には、この記事の最後で正直に答えますが、答えは「どちらでも大抵は足りる」です。

本当の理由はこれです。

1つしか知らないと、境界線が見えない

冒頭の CREATE TABLE を、もう一度見てください。

書いたもの正体
CREATE TABLESQLの標準。 どこでも通る
INT / VARCHAR(200) / NOT NULL / PRIMARY KEYSQLの標準。 どこでも通る
AUTO_INCREMENTMySQL専用。 PostgreSQL では構文エラー
CHARACTER SET utf8mb4MySQL専用。 PostgreSQL に文字コードの概念はあるが、書き方が違う

この表は、MySQLしか触っていないと絶対に作れません。 全部が同じ色に見えるからです。

2つ目を触った瞬間に、「共通の部分」と「その製品の都合」が、勝手に分離して見えるようになります。 これが、2つ目を触る唯一にして最大の理由です。

そして、判断ができるようになる

境界線が見えると、こういう判断ができます。

  • 「この書き方は移せない」が事前に分かる。 だから、移す可能性があるところは標準の書き方に寄せておく
  • 「これはデータベースの限界ではなく、MySQLの都合だ」と分かる。 だから、乗り換えという選択肢が持てる
  • 求人票や設計書に PostgreSQL と書いてあっても、怯まなくなる。 9割は知っていることだと分かっているから

逆に言うと、3つ目・4つ目を触る必要はあまりありません。 2つで境界線は見えます。

① PostgreSQL を入れる

apt の回と同じ手順です。VMの中のUbuntuで打ちます。

bash
$ sudo apt update
$ sudo apt install -y postgresql

Ubuntu 24.04 の postgresql は、PostgreSQL 16 が入ります。 確認します。

bash
$ psql --version
psql (PostgreSQL) 16.9 (Ubuntu 16.9-0ubuntu0.24.04.1)

この「16」という数字は、あとで設定ファイルのパスに出てきます。 覚えておいてください。

動いているかを確認します。 start と enable の回でやったものと同じです。

bash
$ systemctl status postgresql@16-main --no-pager
● postgresql@16-main.service - PostgreSQL Cluster 16-main
     Loaded: loaded (/lib/systemd/system/postgresql@.service; enabled-runtime)
     Active: active (running) since ...

サービス名が postgresql ではなく postgresql@16-main なのは、PostgreSQL が「1台に複数バージョンを同居させる」前提で作られているからです。 postgresql のほうは、その全部をまとめて起動する側の名前です。止めるときは postgresql のほうを使います。

ポートを確認する

bash
$ ss -ltn | grep -E '3306|5432'
LISTEN 0  151  127.0.0.1:3306   0.0.0.0:*
LISTEN 0  244  127.0.0.1:5432   0.0.0.0:*
データベースポート
MySQL3306
PostgreSQL5432

ポートが違うので、2つを同時に動かせます。 ただし、両方ともメモリを食います。 この記事の最後で、片方を必ず止めます。

そして、どちらも 127.0.0.1 だけで待っています。 MySQLの回でやったのと同じで、データベースを外に出さないのは、PostgreSQL でも既定です。

② 最初の関門 — 入れない

ここが、MySQLから来た人が最初に必ず詰まるところです。

MySQLと同じ気持ちで、コマンドを打ってみてください。

bash
$ psql
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  role "ubuntu" does not exist

[スクリーンショット: 01_psqlに入れない.png] psql と打って role "ubuntu" does not exist で弾かれた画面を撮ってください。この失敗が出るのが正常なので、そのまま撮って構いません。(ubuntu の部分は、あなたのVMのユーザー名になります)

パスワードを聞かれてすらいません。 そこが重要です。

何が起きたのか

エラーは role "ubuntu" does not exist です。「ubuntu というロールが無い」。

ubuntu などとは、一言も打っていません。 なのに勝手に使われました。これが peer 認証です。

設定ファイルを見ると、はっきり書いてあります。(見るだけです。まだ書き換えません)

bash
$ sudo grep -vE '^\s*#|^\s*$' /etc/postgresql/16/main/pg_hba.conf
local   all             postgres                                peer
local   all             all                                     peer
host    all             all             127.0.0.1/32            scram-sha-256
host    all             all             ::1/128                 scram-sha-256

3列目までを読んでください。

1列目意味認証方式
localソケット接続(psql とだけ打ったとき)peer = OSのユーザー名をそのまま信用する
hostTCP接続(-h 127.0.0.1 を付けたとき)scram-sha-256 = パスワードを聞く

つまり、同じサーバーに対して、繋ぎ方によって認証方式が変わります。

  • psql → ソケット → peer → 「OSで ubuntu としてログインしている人は、ロール ubuntu としてなら入れる」→ そんなロールは無い → 失敗
  • psql -h 127.0.0.1 -U appuser → TCP → パスワードを聞かれる

MySQL には、この「OSのユーザー名を信用する」という考え方が、既定ではありません。(auth_socket という似た仕組みはありますが、Ubuntu の MySQL では root だけに掛かっています)

正しい入り方

インストール時に、postgres という名前のOSユーザーと、postgres という名前のロールが、両方作られています。 だから、OSのユーザーを postgres に切り替えてから psql を打てば通ります。

bash
$ sudo -u postgres psql
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1))
Type "help" for help.

postgres=#

入れました。 sudo -u postgres は「postgres というユーザーとしてこのコマンドを実行する」という意味です。

postgres=# の # は、パーミッションの回で見た # と同じ意味です。「いま管理者権限で繋いでいる」という印です。 一般ロールで繋ぐと => になります。 これは目で見て確認できるので、いま誰として繋いでいるかを見失ったら、プロンプトを見てください。

psql の中で使うコマンド

MySQL の SHOW 系は、PostgreSQL ではバックスラッシュ始まりのコマンドになります。

やりたいことMySQLPostgreSQL
データベース一覧SHOW DATABASES;\l
データベースを選ぶUSE appdb;\c appdb
テーブル一覧SHOW TABLES;\dt
テーブルの構造DESCRIBE tasks;\d tasks
索引の一覧SHOW INDEX FROM tasks;\di
ユーザー一覧SELECT user FROM mysql.user;\du
作成時のSQLSHOW CREATE TABLE tasks;\d+ tasks
抜けるexit\q

打ってみてください。

\l
                                  List of databases
   Name    |  Owner   | Encoding | Collate | Ctype |   Access privileges
-----------+----------+----------+---------+-------+-----------------------
 postgres  | postgres | UTF8     | C.UTF-8 | C.UTF-8 |
 template0 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +
 template1 | postgres | UTF8     | C.UTF-8 | C.UTF-8 | =c/postgres          +

\ で始まるコマンドに、セミコロンは要りません。 これは PostgreSQL への命令ではなく、psql というプログラムへの命令だからです。 SQLのほうには、セミコロンが要ります。 これはMySQLと同じです。

③ ロールとデータベースを作る

MySQLの回とORMの回で作ったのと、同じ形を作ります。

  • appuser — アプリが使う。SELECT / INSERT / UPDATE / DELETE だけ
  • appmigrator — マイグレーションが使う。表を作れる

対応表

先に、MySQLでの書き方と並べます。

MySQLPostgreSQL
CREATE USER 'appuser'@'localhost' IDENTIFIED BY 'pw';CREATE ROLE appuser WITH LOGIN PASSWORD 'pw';
CREATE DATABASE appdb;CREATE DATABASE appdb OWNER appmigrator;
GRANT SELECT,INSERT,UPDATE,DELETE ON appdb.* TO ...GRANT SELECT,INSERT,UPDATE,DELETE ON ALL TABLES IN SCHEMA public TO ...
FLUSH PRIVILEGES;不要(PostgreSQL は即座に効く)
DROP USER 'appuser'@'localhost';DROP ROLE appuser;

3つ、はっきり違うところがあります。

  1. @'localhost' が無い。 MySQL は「ユーザー名 + どこから繋ぐか」で1人ですが、PostgreSQL のロールに接続元は含まれません。 接続元の制御は pg_hba.conf が全部持っています
  2. OWNER がある。 PostgreSQL ではデータベースにも表にも「持ち主」がいます
  3. ON appdb.* にあたる書き方が無い。 ここが一番の罠です。後述します

作る

sudo -u postgres psql の中で打ちます。

CREATE ROLE appmigrator WITH LOGIN PASSWORD 'ここに長い文字列';
CREATE DATABASE appdb OWNER appmigrator;
CREATE ROLE appuser WITH LOGIN PASSWORD 'ここに別の長い文字列';
CREATE ROLE
CREATE DATABASE
CREATE ROLE

パスワードは自分で作ってください。 openssl rand -base64 24 で作れます(別のターミナルで打ちます)。 psql の中で打ったSQLは ~/.psql_history に残ります。 MySQL の ~/.mysql_history と同じです。この記事の最後で消します。

次に、appdb に移動してから権限を渡します。

\c appdb
You are now connected to database "appdb" as user "postgres".
GRANT CONNECT ON DATABASE appdb TO appuser;
GRANT USAGE ON SCHEMA public TO appuser;
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO appuser;

ここが罠です

いま渡した ON ALL TABLES IN SCHEMA public は、「いま存在している表」にしか効きません。

MySQL の ON appdb.* は、これから作る表にも効きます。 PostgreSQL は効きません。このあと appmigrator が新しい表を作ったら、appuser はその表を読めません。

気づかずに本番でやると、「新しい機能だけ permission denied になる」という、一番嫌な形で出ます。

先回りして塞ぎます。

ALTER DEFAULT PRIVILEGES FOR ROLE appmigrator IN SCHEMA public
  GRANT SELECT, INSERT, UPDATE, DELETE ON TABLES TO appuser;
ALTER DEFAULT PRIVILEGES FOR ROLE appmigrator IN SCHEMA public
  GRANT USAGE, SELECT ON SEQUENCES TO appuser;

「これから appmigrator が public に作る表には、自動で appuser にこの4つを渡してください」という予約です。

2行目の SEQUENCES を忘れないでください。 PostgreSQL の自動連番はシーケンスという別の部品が番号を配ります。 これが無いと、SELECT はできるのに INSERT だけ permission denied for sequence tasks_id_seq で落ちます。 MySQL には無い落とし穴です。

確認して、抜けます。

\q

接続テスト

今度は appuser として、パスワードで入ります。

bash
$ psql -h 127.0.0.1 -U appuser -d appdb
Password for user appuser:
psql (16.9 ...)
appdb=>

プロンプトが => になりました。 管理者ではありません。

-h 127.0.0.1 を付けたのが要点です。 これを外すと、また peer 認証になって弾かれます。試してみてください。

bash
$ psql -U appuser -d appdb
psql: error: connection to server on socket "/var/run/postgresql/.s.PGSQL.5432" failed: FATAL:  Peer authentication failed for user "appuser"

同じサーバー、同じロール、同じパスワードなのに、繋ぎ方だけで結果が変わります。 MySQLとの一番大きな違いは、SQLではなくここです。

[スクリーンショット: 02_ロールと接続テスト.png] psql -h 127.0.0.1 -U appuser -d appdb でパスワードを入れて appdb=> になっている画面を撮ってください。

④ 同じSQLを打つ — ここは9割同じ

appdb=> に入ったまま、MySQLの回でやったのと同じことをします。

先に、表を作る権限だけ一時的に渡します。 手で実験するためです。あとで剥がします。

bash
$ sudo -u postgres psql -d appdb -c "GRANT CREATE ON SCHEMA public TO appuser;"

appuser の psql に戻って、打ちます。

CREATE TABLE tasks (
  id         SERIAL PRIMARY KEY,
  title      VARCHAR(200) NOT NULL,
  done       BOOLEAN NOT NULL DEFAULT FALSE,
  created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE

変えたのは AUTO_INCREMENT を SERIAL にしただけです。 残りはそのまま通りました。

続けます。

INSERT INTO tasks (title) VALUES ('牛乳を買う'), ('PostgreSQLを試す'), ('片づける');
SELECT * FROM tasks;
SELECT * FROM tasks WHERE done = FALSE ORDER BY created_at DESC LIMIT 2;
UPDATE tasks SET done = TRUE WHERE id = 1;
DELETE FROM tasks WHERE id = 3;
SELECT count(*) FROM tasks;
 id |      title       | done |         created_at
----+------------------+------+----------------------------
  1 | 牛乳を買う        | t    | 2026-09-08 11:20:31.442113
  2 | PostgreSQLを試す  | f    | 2026-09-08 11:20:31.442113
  3 | 片づける          | f    | 2026-09-08 11:20:31.442113

索引も同じです。 索引の回でやった形が、そのまま通ります。

CREATE INDEX idx_tasks_done ON tasks (done, created_at);
\d tasks

そして、集計も JOIN も同じです。

SELECT done, count(*) FROM tasks GROUP BY done HAVING count(*) > 0;

ここまでで、覚えたSQLの9割が、そのまま通ったはずです。

これが一番大事な体験です。 「別のデータベース」と聞くと全部覚え直しに聞こえますが、実際には AUTO_INCREMENT を1語書き換えただけでした。

⑤ では、残りの1割はどこか

ここからが本題です。 違うところを、1つずつ、実際に打って確かめます。

違い1 — 連番のつけ方

MySQLPostgreSQL
書き方id INT AUTO_INCREMENTid SERIAL または id INT GENERATED ALWAYS AS IDENTITY
実体列の属性別に作られたシーケンス
入れた行のIDを知るLAST_INSERT_ID()RETURNING id

シーケンスが本当に別にあることを見ます。

\d tasks
 id | integer | not null default nextval('tasks_id_seq'::regclass)

nextval('tasks_id_seq') と書いてあります。 tasks_id_seq という別の部品が呼ばれています。だから、さっき SEQUENCES の権限を渡す必要がありました。

そして、これが実務で一番効いてくる違いです。

INSERT INTO tasks (title) VALUES ('IDが欲しい') RETURNING id;
 id
----
  4

INSERT が値を返しました。 MySQL にはこれがありません。INSERT して、そのあと LAST_INSERT_ID() を別に問い合わせます。

RETURNING は UPDATE や DELETE にも付きます。

UPDATE tasks SET done = TRUE WHERE id = 4 RETURNING id, title, done;

SERIAL と IDENTITY のどちらを使うか。 新しく書くなら GENERATED ALWAYS AS IDENTITY が推奨です。SQLの標準で、シーケンスの持ち主関係もきれいになります。SERIAL は古い書き方ですが、まだ大量に見ます。 読めれば十分です。

違い2 — 大文字小文字の扱い

ここは、静かにデータを壊す側の違いです。

まず、識別子(表や列の名前)。

さっき tasks を作りました。 そこに、大文字で Tasks を作ってみます。

CREATE TABLE Tasks (id INT);
ERROR:  relation "tasks" already exists

Tasks と打ったのに、エラーが tasks について言っています。 PostgreSQL は、引用符で囲まない識別子を、全部小文字に畳みます。

囲むと、話が変わります。

SELECT * FROM "Tasks";
ERROR:  relation "Tasks" does not exist

今度は「本当に大文字の Tasks」を探しにいって、無いので落ちました。 つまり、

  • 囲まなければ、大文字で書いても小文字になる
  • 囲んだら、書いたとおりの名前になる(そして、以後は毎回囲まないと呼べなくなる)

MySQL は、これとまったく違います。 MySQL の表名はファイル名がそのまま出るので、Linuxでは大文字小文字が別物、Windowsでは同じものになります。OSによって挙動が変わります。

結論: 表名も列名も、最初から全部小文字とアンダースコアで書いてください。 そうすれば、どちらでもこの問題は起きません。

次に、値のほう。こちらが本命です。

CREATE TABLE users (id SERIAL PRIMARY KEY, email VARCHAR(255) NOT NULL);
INSERT INTO users (email) VALUES ('Alice@example.com');
SELECT * FROM users WHERE email = 'alice@example.com';
 id | email
----+-------
(0 rows)

見つかりません。

同じことを MySQL でやると、見つかります。 MySQL 8 の既定の照合順序 utf8mb4_0900_ai_ci の ci は case-insensitive(大文字小文字を区別しない)の意味だからです。PostgreSQL は既定で区別します。

これは、移したときにログイン機能が壊れる形で表面化します。 認証の回で作った users が、まさにこれです。

PostgreSQL での正しい書き方は、こうです。

SELECT * FROM users WHERE lower(email) = lower('alice@example.com');
CREATE UNIQUE INDEX idx_users_email_lower ON users (lower(email));
EXPLAIN SELECT * FROM users WHERE lower(email) = 'alice@example.com';

lower(email) に索引が張れているところに注目してください。 式に索引を張れるのは PostgreSQL の得意なところです。 MySQL 8 でも生成列を使えばできますが、書き方が回りくどくなります。 索引の回で「列に関数を掛けると索引が効かない」と書きましたが、PostgreSQL では「その式そのものに索引を張る」という逃げ道があります。

違い3 — " と ' の意味が違う

さっき見たとおりですが、独立した項目として覚えてください。

記号PostgreSQLMySQL
'abc'文字列文字列
"abc"識別子(表名・列名)既定では文字列(設定次第で識別子)
abc構文エラー識別子

打って確かめます。

SELECT "hello";
ERROR:  column "hello" does not exist

hello という列を探しにいきました。 MySQL では hello という文字列が返ってきます。

SELECT `hello`;
ERROR:  syntax error at or near "`"

バッククォートは、そもそも文法として存在しません。

結論: 文字列は必ず ' で囲む。バッククォートは使わない。 これを守れば、どちらでも動くSQLになります。

違い4 — LIMIT は同じ、OFFSET の書き方が違う

標準の書き方は、両方で動きます。

SELECT * FROM tasks ORDER BY id LIMIT 2 OFFSET 1;

MySQLで見慣れた短縮形は、通りません。

SELECT * FROM tasks ORDER BY id LIMIT 1, 2;
ERROR:  syntax error at or near ","

LIMIT 1, 2 は MySQL 専用です。 ページ送りを作るときは、必ず LIMIT n OFFSET m の形で書いてください。 これだけで移せます。

違い5 — 型が厳しい

PostgreSQL は、型が合わないものを黙って変換しません。

SELECT * FROM tasks WHERE id = 'abc';
ERROR:  invalid input syntax for type integer: "abc"

MySQL 8 では、これは0件が返って終わります。 エラーになりません。

もう1つ、有名なやつです。

SELECT 'a' || 'b';
 ?column?
----------
 ab

|| は文字列の連結です。 MySQL では || は論理OR で、SELECT 'a' || 'b' は 0 を返します。同じ記号で、まったく違う意味です。

両方で動く書き方は CONCAT です。

SELECT CONCAT('a', 'b');

「厳しい」は、面倒という意味ではありません。 WHERE id = 'abc' が0件で返ってくるのと、エラーで止まるのと、どちらが安全でしょうか。 0件で返ると、バグが「データが無い」という顔をして通り抜けます。 PostgreSQL の型の厳しさは、そういう性格です。 ここは好みが分かれるところで、MySQLの緩さのほうが助かる場面も実際にあります。

違い6 — EXPLAIN ANALYZE の出力の形

パフォーマンス改善の回でやった EXPLAIN は、PostgreSQL にもあります。 実際に実行して測る EXPLAIN ANALYZE は MySQL にも 8.0.18 からありますが、出力の形が違います(MySQL は木の形の TREE 形式だけ)。

EXPLAIN SELECT * FROM tasks WHERE title = '牛乳を買う';
 Seq Scan on tasks  (cost=0.00..1.04 rows=1 width=45)
   Filter: ((title)::text = '牛乳を買う'::text)

Seq Scan は「全部読んだ」。 MySQL の type: ALL と同じ意味です。

ここまでは MySQL と同じで、これは「見積り」です。 実際には実行していません。

ANALYZE を付けると、本当に実行して、実測値を出します。

EXPLAIN ANALYZE SELECT * FROM tasks WHERE title = '牛乳を買う';
 Seq Scan on tasks  (cost=0.00..1.04 rows=1 width=45) (actual time=0.021..0.023 rows=1 loops=1)
   Filter: ((title)::text = '牛乳を買う'::text)
   Rows Removed by Filter: 2
 Planning Time: 0.094 ms
 Execution Time: 0.048 ms

読むところは2つです。

見るもの意味
rows=1 と actual ... rows=1左が見積り、右が実測。 ここが桁違いにずれていたら、統計が古い(ANALYZE tasks; で更新する)
Rows Removed by Filter読んだのに捨てた行数。 ここが大きいほど、索引を張る価値がある

「見積りと実測を並べて出してくれる」のは、地味ですが非常に強力です。 「なぜこの実行計画を選んだのか」が、そのまま読めます。

ここに大きな罠があります。

EXPLAIN ANALYZE DELETE FROM tasks WHERE done = TRUE;

これは、本当に消えます。 「ANALYZE は分析だから安全」ではありません。実行しないと実測できないので、実行します。

UPDATE / DELETE / INSERT で EXPLAIN ANALYZE を使うときは、必ずトランザクションで囲んでください。 トランザクションの回の道具が、ここで効きます。

BEGIN;
EXPLAIN ANALYZE DELETE FROM tasks WHERE done = TRUE;
ROLLBACK;

[スクリーンショット: 03_explain_analyze.png] EXPLAIN ANALYZE の出力に actual time と Rows Removed by Filter が出ている画面を撮ってください。

違い7 — JSONB

「決まった列にできないデータ」を入れる場所です。 MySQL にも JSON 型はありますが、PostgreSQL の JSONB のほうが、はっきり強いところです。

ALTER TABLE tasks ADD COLUMN meta JSONB;
UPDATE tasks SET meta = '{"priority":"high","labels":["仕事","急ぎ"]}' WHERE id = 1;
UPDATE tasks SET meta = '{"priority":"low","labels":["家"]}' WHERE id = 2;

取り出し方が2つあります。 ここが最初に混乱するところです。

SELECT meta -> 'priority', meta ->> 'priority' FROM tasks WHERE id = 1;
 ?column? | ?column?
----------+----------
 "high"   | high
演算子返るもの
->JSONのまま。 だから "high" と引用符が付く。さらに -> で掘れる
->>テキストとして。 引用符が取れる。もう掘れない

「最後の1段だけ ->>」と覚えてください。

SELECT meta -> 'labels' ->> 0 FROM tasks WHERE id = 1;

そして、これが JSON ではなく JSONB である理由です。

SELECT id, title FROM tasks WHERE meta @> '{"priority":"high"}';
CREATE INDEX idx_tasks_meta ON tasks USING GIN (meta);

@> は「この中身を含んでいるか」。 そして GIN 索引を張ると、JSONの中身で検索しても索引が効きます。

JSONJSONB
保存入力された文字列そのまま解析済みのバイナリ
キーの順番・重複保たれる保たれない・重複は消える
索引張れないGIN で張れる
速さ入れるのが速い読むのが速い

迷ったら JSONB です。 JSON を選ぶのは「受け取った文字列を1文字も変えずに保管したい」ときだけです。

そして、一番言いたいことがこれです。

「JSONBがあるから全部そこに入れればいい」は、設計の放棄です。

meta の中に入れたものには、NOT NULL も、外部キーも、型の検査も効きません。 トランザクションの回で「一貫性だけは自動で守られない、NOT NULL を書くのは自分」と書きましたが、JSONBに逃げると、その手段ごと失います。

列にできるものは、列にしてください。 JSONBは「事前に列を決められないもの」(利用者ごとの設定、外部APIの生の応答)のためのものです。

違い8 — 既定の分離レベルが違う

これが、移したときに一番見つけにくい違いです。

SHOW transaction_isolation;
 read committed

MySQL でも見てみてください。(別のターミナルで sudo mysql)

SELECT @@transaction_isolation;
 REPEATABLE-READ

同じコードが、同じ状況で、違う結果を返します。

既定意味
MySQL (InnoDB)REPEATABLE READトランザクションを始めた時点の景色が、最後まで変わらない
PostgreSQLREAD COMMITTED他人がCOMMITしたものは、次の SELECT から見える

実際に見ます。 トランザクションの回と同じで、ターミナルを2つ開いてください。

ターミナルA:

BEGIN;
SELECT count(*) FROM tasks;
 count
-------
     3

ターミナルB(psql -h 127.0.0.1 -U appuser -d appdb で別に繋ぐ):

INSERT INTO tasks (title) VALUES ('あとから足した');

ターミナルAに戻って、まったく同じSQLをもう一度:

SELECT count(*) FROM tasks;
COMMIT;
 count
-------
     4

同じトランザクションの中で、同じSQLが違う数を返しました。 MySQL では 3 のままです。

何が困るのか。 「件数を数えて、その数を使って処理する」というコードが、MySQLでは正しく、PostgreSQLでは途中で数が変わります。

PostgreSQL で MySQL と同じ景色にしたいときは、明示します。

BEGIN ISOLATION LEVEL REPEATABLE READ;

ただし、これで同じにはなりません。

PostgreSQL の REPEATABLE READ で、他人が先に更新した行を更新しようとすると、待たずにエラーで落ちます。

ERROR:  could not serialize access due to concurrent update

MySQL は、待って、続行します。 PostgreSQL は、諦めさせて、アプリに再試行させます。

これはトランザクションの回でやったデッドロックの再試行と、まったく同じ話です。 あのとき「デッドロックはバグではない、正しい対処は再試行」と書きました。PostgreSQL では、デッドロックでなくても再試行が要る場面が増えます。

移すときの結論はこうです。

  • 「MySQLで動いていたから安全」は、通用しない
  • 分離レベルを前提にしたコードを書かない。 SELECT ... FOR UPDATE で明示的に行を押さえるか、1文の UPDATE ... WHERE で済ませる。この2つは、どちらでも同じように動く

⑥ Knex で接続先を差し替える

ここからが「移行の現実」です。

手で作った表は、もう役目を終えました。 マイグレーションが正なので、捨てます。

bash
$ sudo -u postgres psql -d appdb -c "DROP TABLE IF EXISTS tasks, users CASCADE;"
$ sudo -u postgres psql -d appdb -c "REVOKE CREATE ON SCHEMA public FROM appuser;"

2行目で、さっき一時的に渡した「表を作る権限」を剥がしています。 appuser は CRUD だけに戻りました。

ドライバを入れる

bash
$ cd ~/server
$ npm install pg

mysql2 を消してはいけません。 両方入れておきます。

knexfile に段を足す

bash
$ nano ~/server/knexfile.js
module.exports = {
  development: {
    client: "mysql2",
    connection: {
      host: "127.0.0.1",
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME,
    },
    migrations: { directory: "./migrations" },
  },

  postgres: {
    client: "pg",
    connection: {
      host: "127.0.0.1",
      port: 5432,
      user: process.env.DB_USER,
      password: process.env.DB_PASSWORD,
      database: process.env.DB_NAME,
    },
    migrations: { directory: "./migrations" },
  },
};

ORMの回で「development という段が挟まっているのは Knex の決まり」と書きました。 その段を、もう1枚増やしただけです。 マイグレーションのファイルは共通です。 directory が同じところを指しているのが要点です。

接続情報を分ける

bash
$ nano ~/server/.env.pg.migrate
DB_USER=appmigrator
DB_PASSWORD=appmigratorのパスワード
DB_NAME=appdb
bash
$ nano ~/server/.env.pg
DB_USER=appuser
DB_PASSWORD=appuserのパスワード
DB_NAME=appdb
DB_CLIENT=postgres
bash
$ cd ~/server
$ echo ".env.pg" >> .gitignore
$ echo ".env.pg.migrate" >> .gitignore

.env が4枚になりました。 MySQL用に2枚、PostgreSQL用に2枚です。

流す

bash
$ cd ~/server
$ node --env-file=.env.pg.migrate node_modules/knex/bin/cli.js migrate:latest --env postgres

--env postgres で、knexfile のどの段を使うかを指定しています。

そして、落ちます。

migration file "20260902103000_init.js" failed
migration failed with error: 
      CREATE TABLE sessions (
        session_id VARCHAR(128) COLLATE utf8mb4_bin NOT NULL,
        ...
error: type "mediumtext" does not exist

これが答えです。

何が動いて、何が落ちたか

同じマイグレーションファイルの中で、はっきり分かれました。

書いたものPostgreSQL で
t.increments("id")動く。 serial になる
t.string("email", 255).notNullable().unique()動く
t.boolean("done").defaultTo(false)動く。 MySQLでは tinyint(1) だったものが、本物の boolean になる
t.timestamp(...).defaultTo(knex.fn.now())動く
knex.raw で書いた sessions落ちる。 MEDIUMTEXT も COLLATE utf8mb4_bin も PostgreSQL に無い

つまり、ORMが吸収してくれたのは「ORMの書き方で書いたところ」だけです。 raw で書いたところは、自分で直します。

これは ORM の欠陥ではありません。 raw は「道具を通さず、直接そのデータベースに言う」という意味なので、移せないのが当たり前です。

直す

bash
$ nano ~/server/migrations/*_init.js

sessions のところだけ、こう書き換えます。

  if (!(await knex.schema.hasTable("sessions"))) {
    if (knex.client.config.client === "pg") {
      await knex.schema.createTable("sessions", (t) => {
        t.string("session_id", 128).primary();
        t.integer("expires").notNullable();
        t.text("data");
      });
    } else {
      await knex.raw(`
        CREATE TABLE sessions (
          session_id VARCHAR(128) COLLATE utf8mb4_bin NOT NULL,
          expires    INT UNSIGNED NOT NULL,
          data       MEDIUMTEXT COLLATE utf8mb4_bin,
          PRIMARY KEY (session_id)
        )
      `);
    }
  }

「マイグレーションのファイルは作ったあと名前を変えない」という決まりは、ORMの回のとおりです。 名前は変えていません。 中身を書き換えても、MySQL側には影響しません。 MySQL の knex_migrations には「このファイルは適用済み」と記録済みなので、二度と実行されないからです。

流し直します。

bash
$ node --env-file=.env.pg.migrate node_modules/knex/bin/cli.js migrate:latest --env postgres
Batch 1 run: 1 migrations

確認します。

bash
$ sudo -u postgres psql -d appdb -c "\dt"
 public | knex_migrations      | table | appmigrator
 public | knex_migrations_lock | table | appmigrator
 public | sessions             | table | appmigrator
 public | tasks                | table | appmigrator
 public | users                | table | appmigrator

[スクリーンショット: 04_マイグレーション成功.png] Batch 1 run: 1 migrations が出て、\dt に5つの表が並んでいる画面を撮ってください。

まだ終わりません — アプリ側の違い

表はできました。ですが、コードはそのままでは動きません。

小さな確認用のファイルを1つ書いて、実際に踏みます。

bash
$ nano ~/server/pg-check.js
const knexConfig = require("./knexfile")[process.env.DB_CLIENT || "development"];
const knex = require("knex")(knexConfig);

(async () => {
  const inserted = await knex("tasks").insert({ title: "移行テスト" });
  console.log("insert の戻り値:", inserted);

  const rows = await knex("tasks").select("id", "title", "done").limit(1);
  console.log("done の型:", typeof rows[0].done, rows[0].done);

  await knex.destroy();
})();

MySQL で動かします。

bash
$ cd ~/server
$ node --env-file=.env pg-check.js
insert の戻り値: [ 1 ]
done の型: number 0

PostgreSQL で動かします。

bash
$ node --env-file=.env.pg pg-check.js
insert の戻り値: []
done の型: boolean false

2つとも壊れています。

症状原因直し方
insert が [] を返すPostgreSQL には LAST_INSERT_ID() が無い。 IDが欲しいなら RETURNING を明示する.insert({...}).returning("id") を付ける
done が 0 から false になったMySQL の BOOLEAN は tinyint(1) の別名で、実体は数値。 PostgreSQL は本物の真偽値サーバー側で Boolean(row.done) に揃えてから返す

2つ目が厄介です。 APIのJSONが {"done": 0} から {"done": false} に変わります。フロントエンドで if (task.done === 1) と書いてあったら、そこが黙って壊れます。

両方で動く書き方は、こうです。

const inserted = await knex("tasks").insert({ title: "移行テスト" }).returning("id");
const id = typeof inserted[0] === "object" ? inserted[0].id : inserted[0];

const rows = await knex("tasks").select("id", "title", "done").limit(1);
const out = rows.map((r) => ({ ...r, done: Boolean(r.done) }));

.returning("id") は、MySQL では無視されます。(警告が出ますが動きます)だから、この1行を書いておけば両方で動きます。

そして、これも移せません

app.js そのものを PostgreSQL で起動するには、もう1つ壁があります。

セッションの保存先です。 認証の回で入れたセッションストアは、MySQL専用のパッケージです。PostgreSQL では動きません。別のパッケージに入れ替えて、sessions 表の形もそれに合わせる必要があります。

この記事ではそこまでやりません。 ですが、これこそが「移行の現実」です。

まとめると、移行のコストは3層に分かれます。

層どれくらい移るか
SQLそのもの9割そのまま。 LIMIT 1,2 と ` と型の緩さだけ直す
ORMのスキーマ定義ほぼ移る。 raw で書いたところだけ自分で直す
周辺のパッケージ移らない。 セッションストア、全文検索、ジョブキュー。ここが一番重い

「データベースを移す」と言ったとき、大変なのは3層目です。 SQLではありません。

⑦ で、どちらを選ぶのか

正直に書きます。

結論: どちらでも大抵は足ります

個人開発でも、小〜中規模の業務システムでも、「MySQLでは無理だからPostgreSQLにした」という場面には、まず出会いません。 逆も同じです。

この記事でやったことを思い出してください。 違いを7つ8つ数えましたが、そのどれも「片方でしかできない」ではなく「書き方が違う」でした。

それでも決めるなら

こういうときは MySQLこういうときは PostgreSQL
レンタルサーバーやWordPressの世界にいる。 共有サーバーはほぼMySQL/MariaDB型と制約を厳しくしたい。 「変な値が入るくらいならエラーで止まってほしい」
日本語の情報量が欲しい。 初心者向けの記事が多いJSONBを本気で使う。 構造が決まらないデータに索引を張りたい
運用できる人が周りに多い地理情報を扱う(PostGIS)。 ここは代替が無い
とにかく読み取りが多い、単純な構成複雑な集計や分析クエリを書く。 ウィンドウ関数やCTEの実装が長く枯れている

そして、実務での本当の決め手は、この表のどれでもありません。

  1. チームが運用できるほうを選ぶ。 深夜に落ちたとき、直せる人がいるかどうか
  2. 使うマネージドサービスが対応しているほうを選ぶ(クラウドのDBサービスは大抵どちらもありますが、料金と機能が微妙に違います)
  3. フレームワークやライブラリが前提にしているほうを選ぶ

やってはいけないこと

すでに動いているものを、「PostgreSQLのほうが良いらしい」という理由で移すこと。

この記事で見たとおり、移行のコストの大半はSQLではなく、周辺のパッケージとアプリのコードです。そして、移した先で、また別の落とし穴を踏みます。

移す理由は、「困っていることが、移すと解決する」でなければいけません。

つまずいたところ

症状原因
psql で role "ubuntu" does not exist正しい失敗。 peer 認証。sudo -u postgres psql で入る
Peer authentication failed for user "appuser"-h 127.0.0.1 を付け忘れている。 ソケット接続だとパスワードを見てくれない
permission denied for schema publicPostgreSQL 15以降、public に表を作る権限が既定で外れた。 データベースの OWNER にするか GRANT CREATE ON SCHEMA public
SELECT は通るのに INSERT だけ permission denied for sequence tasks_id_seqシーケンスの権限を渡していない。 GRANT USAGE, SELECT ON SEQUENCES
新しく作った表だけ permission deniedON ALL TABLES は既存の表にしか効かない。 ALTER DEFAULT PRIVILEGES を入れる
relation "Tasks" does not exist囲まない識別子は小文字に畳まれる。 表名は最初から全部小文字で書く
column "hello" does not exist" は識別子。 文字列は ' で囲む
syntax error at or near ","LIMIT 1, 2 は MySQL 専用。 LIMIT 2 OFFSET 1 に直す
invalid input syntax for type integer型が厳しい。 MySQLなら0件で返っていたところ。むしろ見つかってよかった側
EXPLAIN ANALYZE DELETE で本当に消えた仕様。 実行しないと実測できない。BEGIN; と ROLLBACK; で囲む
type "mediumtext" does not existknex.raw はORMを通っていない。 クライアントで分岐させる
insert() の戻り値が []PostgreSQL は RETURNING を明示しないとIDを返さない。 .returning("id")
APIの done が 0 から false に変わったMySQLの BOOLEAN は数値。 サーバー側で Boolean() に揃える
Cannot find module 'pg'npm install pg を忘れている
ログイン中のユーザーが分からなくなったプロンプトを見る。 =# は管理者、=> は一般ロール

後片づけ

この回で足したもの。

PostgreSQL 16(apt)
PostgreSQL 側の appdb / appuser / appmigrator
~/server/knexfile.js の postgres 段
~/server/.env.pg
~/server/.env.pg.migrate
~/server/pg-check.js
~/server/migrations/*_init.js の分岐
node_modules の pg

確認用のファイルは、もう要りません。

bash
$ rm -f ~/server/pg-check.js

psql の履歴にパスワードが残っています。 MySQL のときと同じで、消します。

bash
$ rm -f ~/.psql_history

両方入れっぱなしにしない

まず、いまどれだけ食っているかを見てください。

bash
$ free -h
$ ps -eo rss,comm --sort=-rss | head -8
               total        used        free      shared  buff/cache   available
Mem:           3.8Gi       1.4Gi       1.1Gi        18Mi       1.5Gi       2.3Gi

MySQL と PostgreSQL は、どちらも「起動しているだけ」でメモリを数百MB持っていきます。 学習用のVMでは、これが効きます。このあと Docker や Kubernetes に進むと、確実に足りなくなります。

3つの選択肢があります。

(A)止めるだけ。設定もデータも残す。 ← おすすめ

bash
$ sudo systemctl disable --now postgresql
bash
$ free -h

disable --now は「いま止めて、次の起動時にも上げない」です。 start と enable の回でやった2つを、まとめて外しています。

また触りたくなったら、これで戻ります。

bash
$ sudo systemctl enable --now postgresql

(B)PostgreSQL を完全に消す。

データが消えます。 戻せません。

bash
$ sudo systemctl disable --now postgresql
$ sudo apt purge -y postgresql postgresql-16 postgresql-client-16 postgresql-client-common postgresql-common
$ sudo apt autoremove -y
$ sudo rm -rf /var/lib/postgresql
bash
$ cd ~/server
$ rm -f .env.pg .env.pg.migrate
$ npm uninstall pg

(C)MySQL のほうを消して、PostgreSQL で進む。

このサイトの以降の回は MySQL を前提に書いています。 それでも PostgreSQL で進めたい場合は、こうします。

bash
$ sudo systemctl disable --now mysql
$ sudo apt purge -y mysql-server mysql-client mysql-common
$ sudo apt autoremove -y
$ sudo rm -rf /var/lib/mysql
bash
$ cd ~/server
$ rm -f .env .env.migrate
$ mv .env.pg .env
$ mv .env.pg.migrate .env.migrate
$ npm uninstall mysql2

そのうえで、app.js の接続先を postgres の段に固定し、セッションストアを入れ替えてください。

この記事の既定は(A)です。 PostgreSQL を disable して、MySQL で先に進みます。 消さないのは、「触ったものを残しておくと、あとで比べられるから」です。 メモリは disable で返ってきます。

確認します。

bash
$ ss -ltn | grep -E '3306|5432'
LISTEN 0  151  127.0.0.1:3306   0.0.0.0:*

5432 が消えていれば成功です。

まとめ

  • 2つ目を触る理由は優劣ではない。 1つしか知らないと、SQLの仕様とその製品の都合を分離できない。AUTO_INCREMENT も utf8mb4 もMySQLの機能だと、比べて初めて分かる
  • PostgreSQL の最初の関門は認証で、SQLではない。 psql は peer 認証で、OSのユーザー名と同じロールでしか入れない。sudo -u postgres psql で入る
  • 同じサーバーでも、繋ぎ方で認証方式が変わる。 ソケットは peer、TCP(-h 127.0.0.1)はパスワード。pg_hba.conf が全部決めている
  • ON ALL TABLES は既存の表にしか効かない。 MySQL の ON appdb.* と違う。ALTER DEFAULT PRIVILEGES を必ずセットで書く
  • 連番はシーケンスという別部品。 だから SEQUENCES の権限を渡さないと、SELECT は通るのに INSERT だけ落ちる
  • SQLは9割そのまま通る。 CREATE TABLE も WHERE も JOIN も GROUP BY も CREATE INDEX も、書き換え不要
  • 違うのは数えられる場所だけ。 連番(SERIAL / RETURNING)、識別子が小文字に畳まれる、" は識別子、LIMIT n OFFSET m、型が厳しい、|| が連結
  • 文字列の比較で大文字小文字を区別する。 MySQLの既定は区別しない。移すとログインが壊れる。lower() と式索引で直す
  • EXPLAIN ANALYZE は見積りと実測を並べて出す。 ただし UPDATE / DELETE では本当に実行される。BEGIN; 〜 ROLLBACK; で囲む
  • JSONBは索引が張れる。 ただし JSONBに入れたものには NOT NULL も外部キーも効かない。列にできるものは列にする
  • 既定の分離レベルが違う。 MySQLは REPEATABLE READ、PostgreSQLは READ COMMITTED。同じトランザクションの中で件数が変わる
  • 「MySQLで動いたから安全」は移すと崩れる。 分離レベルに頼らず、FOR UPDATE か1文の UPDATE ... WHERE で書く
  • ORMが吸収するのは「ORMの書き方で書いたところ」だけ。 raw で書いたSQLは、自分で分岐させる
  • 移行の重さは3層。 SQL(9割移る)→ スキーマ定義(raw だけ直す)→ 周辺のパッケージ(移らない)。一番重いのは3層目
  • どちらを選ぶかは、正直どちらでも足りる。 決め手は機能ではなく、チームが運用できるか
  • 動いているものを「良いらしい」で移さない。 移す理由は「困っていることが解決する」でなければいけない
  • 両方を起動しっぱなしにしない。 sudo systemctl disable --now postgresql で止める。Dockerに進む前に、メモリを返しておく

参考リンク


次に読む

この記事は 【第5段階】バックエンドとデータベース の 15 / 29 本目です。

記事
← 前の記事全部やるか、1つもやらないか — トランザクションとACID
→ 次の記事スキーマは消えずにアプリへ移る — NoSQLとMongoDB

上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。

全体の順路は はじめに にあります。

roadmap.sh のどこをやっているか

Backend›Databases
  • Relational Databases — PostgreSQL
  • MySQL との違い(SERIAL / 識別子 / 型 / 分離レベル)
  • Database Access Control(ロールとデフォルト権限)

この記事は、上の項目を実際に手を動かして確かめる回です。

よかったらシェアしてね!
  • URLをコピーしました!
  • URLをコピーしました!

コメント

コメントする

目次