PostgreSQL vs MySQL 2026: どちらのデータベースを選ぶべきか?

PostgreSQL と MySQL の選択は、バックエンド開発における最も重要な決断の一つです。

クイック概要

PostgreSQLは標準準拠、データ整合性、拡張性を優先します。MySQLは読み取り負荷の高いワークロードへの簡便さと速度を優先します。

Feature              PostgreSQL          MySQL 8.x
------------------------------------------------------------
License              PostgreSQL (free)   GPL / Commercial
ACID Compliance      Full                Full (InnoDB)
JSON Support         JSONB (excellent)   JSON (good)
Full-Text Search     Built-in tsvector   FULLTEXT index
Replication          Streaming + Logical Binary log + GTID
Max DB Size          Unlimited           256TB
Partitioning         Declarative         RANGE/LIST/HASH
Window Functions     Full support        Partial (8.x+)
Extensions           Rich ecosystem      Plugins (fewer)
Default in Cloud     Supabase, RDS       PlanetScale, RDS

機能比較

PostgreSQLは歴史的に機能セットが豊富です。

-- Feature Comparison: PostgreSQL vs MySQL

-- 1. JSON Support
-- PostgreSQL JSONB (binary, indexed)
SELECT data->>'name' FROM users WHERE data @> '{"active": true}';
CREATE INDEX idx_users_data ON users USING GIN (data);

-- MySQL JSON
SELECT JSON_EXTRACT(data, '$.name') FROM users
WHERE JSON_EXTRACT(data, '$.active') = true;

-- 2. Full-Text Search
-- PostgreSQL (built-in tsvector)
SELECT * FROM articles
WHERE to_tsvector('english', content) @@ to_tsquery('postgresql & performance');

-- MySQL FULLTEXT
SELECT * FROM articles
WHERE MATCH(content) AGAINST ('postgresql performance' IN NATURAL LANGUAGE MODE);

-- 3. CTEs (Common Table Expressions)
-- PostgreSQL supports recursive CTEs natively (since 8.4)
WITH RECURSIVE category_tree AS (
  SELECT id, name, parent_id, 0 AS level
  FROM categories WHERE parent_id IS NULL
  UNION ALL
  SELECT c.id, c.name, c.parent_id, ct.level + 1
  FROM categories c JOIN category_tree ct ON c.parent_id = ct.id
)
SELECT * FROM category_tree ORDER BY level, name;

-- 4. Window Functions (both support, PostgreSQL more complete)
SELECT
  employee_id,
  salary,
  AVG(salary) OVER (PARTITION BY department_id) as dept_avg,
  RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) as rank
FROM employees;

パフォーマンス比較

生パフォーマンスはワークロードの種類によって大きく異なります。

-- Performance Tuning Examples

-- PostgreSQL: EXPLAIN ANALYZE
EXPLAIN (ANALYZE, BUFFERS, FORMAT JSON)
SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 100;

-- PostgreSQL-specific indexes
CREATE INDEX CONCURRENTLY idx_orders_user_status
ON orders (user_id, status)
WHERE status != 'cancelled';  -- Partial index

CREATE INDEX idx_products_attrs ON products USING GIN (attributes); -- JSONB index

-- MySQL: EXPLAIN FORMAT=JSON
EXPLAIN FORMAT=JSON
SELECT u.id, u.name, COUNT(o.id) as order_count
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2025-01-01'
GROUP BY u.id, u.name
ORDER BY order_count DESC
LIMIT 100;

-- Connection pooling configuration
-- PostgreSQL (PgBouncer or pg pool settings)
max_connections = 100           -- in postgresql.conf
-- Use connection pooler for high concurrency
-- PgBouncer pool_mode = transaction  (recommended for web apps)

-- MySQL
max_connections = 200           -- in my.cnf
innodb_buffer_pool_size = 4G    -- 70-80% of RAM for dedicated MySQL server

PostgreSQLを選ぶべき場合

高度なSQL機能、データ整合性の保証、複雑なクエリ機能が必要な場合はPostgreSQLが適切です。

• Complex queries — Advanced window functions, CTEs, lateral joins
• JSON/document storage — JSONB with GIN indexes rivals MongoDB
• Geospatial data — PostGIS extension is the gold standard
• Strict data integrity — CHECK constraints, exclusion constraints, custom domains
• High-concurrency writes — MVCC handles concurrent writes better than MySQL
• Full-text search — Built-in, no external service needed
• Analytics/reporting — Better at complex aggregations and window functions

MySQLを選ぶべき場合

MySQLは多くのユースケース、特に読み取り負荷の高いWebアプリケーションで優れた選択肢です。

• Simple CRUD applications — WordPress, Drupal, and many CMSes default to MySQL
• Read-heavy workloads — InnoDB is highly optimized for read-heavy patterns
• Existing MySQL ecosystem — If your team has deep MySQL expertise
• PlanetScale — MySQL-compatible serverless database with excellent DX
• Replication simplicity — MySQL replication is well-understood and widely deployed

データベース間の移行

MySQLからPostgreSQLへの移行（またはその逆）にはSQL方言の違いや型マッピングの注意が必要です。

-- MySQL to PostgreSQL Migration: Common Differences

-- 1. AUTO_INCREMENT -> SERIAL/IDENTITY
-- MySQL
CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100)
);

-- PostgreSQL
CREATE TABLE users (
  id SERIAL PRIMARY KEY,  -- or BIGSERIAL for large tables
  name VARCHAR(100)
);
-- Modern PostgreSQL (v10+):
-- id INT GENERATED ALWAYS AS IDENTITY PRIMARY KEY

-- 2. String functions differ
-- MySQL: IFNULL
SELECT IFNULL(phone, 'N/A') FROM users;
-- PostgreSQL: COALESCE (also works in MySQL)
SELECT COALESCE(phone, 'N/A') FROM users;

-- 3. String concatenation
-- MySQL
SELECT CONCAT(first_name, ' ', last_name) FROM users;
-- PostgreSQL
SELECT first_name || ' ' || last_name FROM users;
-- Or use CONCAT (PostgreSQL also supports it)

-- 4. LIMIT/OFFSET syntax (both support the same)
SELECT * FROM users ORDER BY id LIMIT 10 OFFSET 20;

-- 5. Boolean values
-- MySQL: TRUE/FALSE or 1/0
-- PostgreSQL: TRUE/FALSE or 't'/'f' or 'true'/'false'

-- 6. Timestamp with timezone
-- MySQL: DATETIME or TIMESTAMP (local time only)
-- PostgreSQL: TIMESTAMPTZ (timezone-aware, recommended)
ALTER TABLE events
  ALTER COLUMN created_at TYPE TIMESTAMPTZ
  USING created_at AT TIME ZONE 'UTC';

よくある質問

PostgreSQLはMySQLより速いですか？

ワークロードによります。MySQLはシンプルな読み取り中心クエリで速い場合が多いです。PostgreSQLは複雑な分析クエリで優れています。

主要クラウドプロバイダーはどちらを推奨しますか？

主要クラウドプロバイダーは両方提供しています。SupabaseはPostgreSQL、PlanetScaleはMySQLをベースにしています。

PostgreSQLはJSONをサポートしますか？

はい、PostgreSQLはJSONBタイプで優れたJSONサポートがあり、インデックス付きでバイナリ形式で保存します。

2026年の新プロジェクトにはどちらが適していますか？

2026年のほとんどの新プロジェクトには、PostgreSQLが推奨デフォルト選択です。

関連ツール

• JSON Formatter
• JSON to CSV Converter
• API Design Best Practices
• Node.js Performance Tips