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 serverPostgreSQL을 선택해야 할 때
PostgreSQL은 고급 SQL 기능, 데이터 무결성 보장 또는 복잡한 쿼리 기능이 필요할 때 적합합니다.
- 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은 특히 읽기 집약적인 웹 애플리케이션에서 여전히 훌륭한 선택입니다.
- 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이 권장 기본 선택입니다.