ここまでの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の戻り値と、真偽値の型が、必ず引っかかる
この記事でやること
- PostgreSQL を入れて、
sudo -u postgres psqlで中に入る - ロールとデータベースを作る(MySQLの
CREATE USER+GRANTと対応させる) - 同じテーブルを作り、同じSQLを打つ。 ほとんど同じことを確かめる
- 違うところを、1つずつ手で確かめる(
SERIAL/ 引用符 / 型 /EXPLAIN ANALYZE/JSONB/ 分離レベル) - Knex の接続先を差し替えて、マイグレーションがどこで落ちるかを見る
- どちらか片方を止める(両方入れっぱなしにしない)
先に知る言葉
この記事で初めて出てくる言葉です。 基本的な言葉は用語集にもまとめてあります。
| 言葉 | 意味 |
|---|---|
| PostgreSQL | データベースソフトのひとつ。「ポストグレスキューエル」。会話では「ポスグレ」「Postgres」と略される |
| psql | PostgreSQL に付いてくる対話コマンド。MySQL の mysql コマンドにあたる |
| ロール(role) | PostgreSQL でのユーザーとグループを兼ねた概念。MySQL の「ユーザー」にあたるが、ロールにロールを入れられる |
| peer 認証 | 「OSで誰としてログインしているか」をそのまま信用する認証方式。パスワードを聞かれない代わりに、OSのユーザー名と同じ名前のロールでしか入れない |
| スキーマ(schema) | PostgreSQL のデータベースの中にある区切り。既定で public が1つある。MySQLには無い階層(MySQLは「スキーマ=データベース」) |
| SERIAL / IDENTITY | PostgreSQL の自動連番。MySQL の AUTO_INCREMENT にあたる |
| JSONB | PostgreSQL の型のひとつ。JSONを解析済みの形で保存する。 中身に索引を張れる |
| シーケンス(sequence) | 番号を配るだけの小さな部品。 PostgreSQL の連番は、裏でこれが動いている |
「ロール」と「ユーザー」は、PostgreSQL ではほぼ同じものです。 正確には「ログインできるロール」がユーザーです。
CREATE USERと打っても、実際にはCREATE ROLE ... WITH LOGINが実行されます。同じものの別名だと思ってください。
なぜ2つ目を触るのか
「どちらが優れているか」を調べるためではありません。 その質問には、この記事の最後で正直に答えますが、答えは「どちらでも大抵は足りる」です。
本当の理由はこれです。
1つしか知らないと、境界線が見えない
冒頭の CREATE TABLE を、もう一度見てください。
| 書いたもの | 正体 |
|---|---|
CREATE TABLE | SQLの標準。 どこでも通る |
INT / VARCHAR(200) / NOT NULL / PRIMARY KEY | SQLの標準。 どこでも通る |
AUTO_INCREMENT | MySQL専用。 PostgreSQL では構文エラー |
CHARACTER SET utf8mb4 | MySQL専用。 PostgreSQL に文字コードの概念はあるが、書き方が違う |
この表は、MySQLしか触っていないと絶対に作れません。 全部が同じ色に見えるからです。
2つ目を触った瞬間に、「共通の部分」と「その製品の都合」が、勝手に分離して見えるようになります。 これが、2つ目を触る唯一にして最大の理由です。
そして、判断ができるようになる
境界線が見えると、こういう判断ができます。
- 「この書き方は移せない」が事前に分かる。 だから、移す可能性があるところは標準の書き方に寄せておく
- 「これはデータベースの限界ではなく、MySQLの都合だ」と分かる。 だから、乗り換えという選択肢が持てる
- 求人票や設計書に
PostgreSQLと書いてあっても、怯まなくなる。 9割は知っていることだと分かっているから
逆に言うと、3つ目・4つ目を触る必要はあまりありません。 2つで境界線は見えます。
① PostgreSQL を入れる
apt の回と同じ手順です。VMの中のUbuntuで打ちます。
Ubuntu 24.04 の postgresql は、PostgreSQL 16 が入ります。 確認します。
psql (PostgreSQL) 16.9 (Ubuntu 16.9-0ubuntu0.24.04.1)
この「16」という数字は、あとで設定ファイルのパスに出てきます。 覚えておいてください。
動いているかを確認します。 start と enable の回でやったものと同じです。
● 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のほうを使います。
ポートを確認する
LISTEN 0 151 127.0.0.1:3306 0.0.0.0:*
LISTEN 0 244 127.0.0.1:5432 0.0.0.0:*
| データベース | ポート |
|---|---|
| MySQL | 3306 |
| PostgreSQL | 5432 |
ポートが違うので、2つを同時に動かせます。 ただし、両方ともメモリを食います。 この記事の最後で、片方を必ず止めます。
そして、どちらも 127.0.0.1 だけで待っています。 MySQLの回でやったのと同じで、データベースを外に出さないのは、PostgreSQL でも既定です。
② 最初の関門 — 入れない
ここが、MySQLから来た人が最初に必ず詰まるところです。
MySQLと同じ気持ちで、コマンドを打ってみてください。
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 認証です。
設定ファイルを見ると、はっきり書いてあります。(見るだけです。まだ書き換えません)
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のユーザー名をそのまま信用する |
host | TCP接続(-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 を打てば通ります。
psql (16.9 (Ubuntu 16.9-0ubuntu0.24.04.1))
Type "help" for help.
postgres=#
入れました。 sudo -u postgres は「postgres というユーザーとしてこのコマンドを実行する」という意味です。
postgres=#の#は、パーミッションの回で見た#と同じ意味です。「いま管理者権限で繋いでいる」という印です。 一般ロールで繋ぐと=>になります。 これは目で見て確認できるので、いま誰として繋いでいるかを見失ったら、プロンプトを見てください。
psql の中で使うコマンド
MySQL の SHOW 系は、PostgreSQL ではバックスラッシュ始まりのコマンドになります。
| やりたいこと | MySQL | PostgreSQL |
|---|---|---|
| データベース一覧 | 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 |
| 作成時のSQL | SHOW 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と同じです。
③ ロールとデータベースを作る
appuser— アプリが使う。SELECT/INSERT/UPDATE/DELETEだけappmigrator— マイグレーションが使う。表を作れる
対応表
先に、MySQLでの書き方と並べます。
| MySQL | PostgreSQL |
|---|---|
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つ、はっきり違うところがあります。
@'localhost'が無い。 MySQL は「ユーザー名 + どこから繋ぐか」で1人ですが、PostgreSQL のロールに接続元は含まれません。 接続元の制御はpg_hba.confが全部持っていますOWNERがある。 PostgreSQL ではデータベースにも表にも「持ち主」がいます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 として、パスワードで入ります。
Password for user appuser:
psql (16.9 ...)
appdb=>
プロンプトが => になりました。 管理者ではありません。
-h 127.0.0.1 を付けたのが要点です。 これを外すと、また peer 認証になって弾かれます。試してみてください。
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の回でやったのと同じことをします。
先に、表を作る権限だけ一時的に渡します。 手で実験するためです。あとで剥がします。
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 — 連番のつけ方
| MySQL | PostgreSQL | |
|---|---|---|
| 書き方 | id INT AUTO_INCREMENT | id 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 — " と ' の意味が違う
さっき見たとおりですが、独立した項目として覚えてください。
| 記号 | PostgreSQL | MySQL |
|---|---|---|
'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の中身で検索しても索引が効きます。
JSON | JSONB | |
|---|---|---|
| 保存 | 入力された文字列そのまま | 解析済みのバイナリ |
| キーの順番・重複 | 保たれる | 保たれない・重複は消える |
| 索引 | 張れない | 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 | トランザクションを始めた時点の景色が、最後まで変わらない |
| PostgreSQL | READ 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 で接続先を差し替える
ここからが「移行の現実」です。
手で作った表は、もう役目を終えました。 マイグレーションが正なので、捨てます。
2行目で、さっき一時的に渡した「表を作る権限」を剥がしています。 appuser は CRUD だけに戻りました。
ドライバを入れる
mysql2 を消してはいけません。 両方入れておきます。
knexfile に段を足す
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が同じところを指しているのが要点です。
接続情報を分ける
DB_USER=appmigrator
DB_PASSWORD=appmigratorのパスワード
DB_NAME=appdb
DB_USER=appuser
DB_PASSWORD=appuserのパスワード
DB_NAME=appdb
DB_CLIENT=postgres
.env が4枚になりました。 MySQL用に2枚、PostgreSQL用に2枚です。
流す
--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 は「道具を通さず、直接そのデータベースに言う」という意味なので、移せないのが当たり前です。
直す
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には「このファイルは適用済み」と記録済みなので、二度と実行されないからです。
流し直します。
Batch 1 run: 1 migrations
確認します。
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つ書いて、実際に踏みます。
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 で動かします。
insert の戻り値: [ 1 ]
done の型: number 0
PostgreSQL で動かします。
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の実装が長く枯れている |
そして、実務での本当の決め手は、この表のどれでもありません。
- チームが運用できるほうを選ぶ。 深夜に落ちたとき、直せる人がいるかどうか
- 使うマネージドサービスが対応しているほうを選ぶ(クラウドのDBサービスは大抵どちらもありますが、料金と機能が微妙に違います)
- フレームワークやライブラリが前提にしているほうを選ぶ
やってはいけないこと
すでに動いているものを、「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 public | PostgreSQL 15以降、public に表を作る権限が既定で外れた。 データベースの OWNER にするか GRANT CREATE ON SCHEMA public |
SELECT は通るのに INSERT だけ permission denied for sequence tasks_id_seq | シーケンスの権限を渡していない。 GRANT USAGE, SELECT ON SEQUENCES |
新しく作った表だけ permission denied | ON 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 exist | knex.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
確認用のファイルは、もう要りません。
psql の履歴にパスワードが残っています。 MySQL のときと同じで、消します。
両方入れっぱなしにしない
まず、いまどれだけ食っているかを見てください。
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)止めるだけ。設定もデータも残す。 ← おすすめ
disable --now は「いま止めて、次の起動時にも上げない」です。 start と enable の回でやった2つを、まとめて外しています。
また触りたくなったら、これで戻ります。
(B)PostgreSQL を完全に消す。
データが消えます。 戻せません。
(C)MySQL のほうを消して、PostgreSQL で進む。
このサイトの以降の回は MySQL を前提に書いています。 それでも PostgreSQL で進めたい場合は、こうします。
そのうえで、app.js の接続先を postgres の段に固定し、セッションストアを入れ替えてください。
この記事の既定は(A)です。 PostgreSQL を
disableして、MySQL で先に進みます。 消さないのは、「触ったものを残しておくと、あとで比べられるから」です。 メモリはdisableで返ってきます。
確認します。
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に進む前に、メモリを返しておく
参考リンク
- roadmap.sh — Backend(Databases / PostgreSQL の項目)
- roadmap.sh — PostgreSQL DBA
- PostgreSQL 16 公式ドキュメント
- PostgreSQL — pg_hba.conf ファイル
- PostgreSQL — トランザクションの分離
- PostgreSQL — JSON 型
- PostgreSQL — EXPLAIN の使い方
- 日本PostgreSQLユーザ会(日本語の資料とドキュメント)
- Knex — 対応しているデータベース
次に読む
この記事は 【第5段階】バックエンドとデータベース の 15 / 29 本目です。
上から順に読めば積み上がるように並べています。 飛ばしても構いませんが、詰まったら前の回に戻ってください。
全体の順路は はじめに にあります。
roadmap.sh のどこをやっているか
- Relational Databases — PostgreSQL
- MySQL との違い(SERIAL / 識別子 / 型 / 分離レベル)
- Database Access Control(ロールとデフォルト権限)
この記事は、上の項目を実際に手を動かして確かめる回です。

コメント