PostgreSQL vs MySQL 차이와 선택 가이드 | 스키마·트랜잭션·운영

작성 0xmote · 2026년 5월 19일 · 26분 읽기 · 수정 2026년 9월 25일 중급 튜토리얼

이 글의 핵심

두 데이터베이스 모두 관계형이고 SQL을 쓰기 때문에 어느 쪽을 골라도 처음에는 차이가 잘 보이지 않습니다. 차이는 부분 인덱스나 CHECK 제약, 스키마 변경을 트랜잭션으로 묶을 수 있는지, 복제와 확장 방식처럼 운영 단계에서 드러납니다. 이 글은 벤치마크 수치보다 이런 의사결정 축을 기준으로 비교합니다.

들어가며

PostgreSQL과 MySQL(및 MariaDB)은 관계형 DB 시장에서 가장 자주 마주치는 선택지입니다. 둘 다 ACID 트랜잭션, 복제, 풍부한 클라이언트 라이브러리를 갖추지만, 스키마 표현력, 쿼리 최적화기, 운영 도구, 호스팅 생태에서 차이가 납니다. “어느 쪽이 더 빠르다” 한 줄로 끝나지 않으며, 워크로드·팀 역량·레거시에 맞춰 고르는 것이 안전합니다. 이 글은 벤더 홍보가 아니라 실무에서 의사결정에 쓰는 비교 축—스키마, 트랜잭션, SQL 기능, 운영—을 정리합니다. PostgreSQL이 무엇인지부터 보려면 PostgreSQL 뜻과 특징을 먼저 참고하세요. 비유로 말씀드리면, PostgreSQL은 규칙과 도구를 많이 갖춘 설계 사무실, MySQL은 가볍게 짓고 널리 호스팅되는 현장에 가깝습니다. 둘 다 집을 짓지만(데이터를 저장하지만), 복잡한 제약·분석 쿼리를 DB에 두고 싶은지, 운영·호스팅 친숙도를 우선할지가 갈림길이 됩니다.

언제 PostgreSQL을, 언제 MySQL(MariaDB)을 쓰나요?

관점	PostgreSQL을 검토하시면 좋은 경우	MySQL/MariaDB를 검토하시면 좋은 경우
성능·기능	복잡한 SQL, `jsonb`, 배열·범위 타입, 분석·윈도 함수에 강하게 의존할 때	단순 CRUD, 익숙한 레이어·호스팅 위주일 때
사용성	스키마·제약·DDL을 트랜잭션 안에서 다루는 패턴이 중요할 때	기존 팀·레거시·매뉴얼이 MySQL 중심일 때
적용 시나리오	데이터 무결성·표현력을 DB에 두는 도메인	웹 호스팅·레플리카 생태·주변 도구와의 궁합

개념: RDB 공통점과 비교 축

기본 개념

둘 다 SQL을 사용하는 관계형 데이터베이스이며, 테이블·행·열, 기본키·외래키, 조인으로 데이터를 모델링합니다. Node.js에서는 pg, mysql2, Prisma, Sequelize, Knex 등으로 접근합니다.

왜 “하나의 정답”이 없는가

성능은 스키마 설계, 인덱스, 쿼리 패턴, 하드웨어, 설정에 좌우됩니다. 따라서 기능 적합성(필요한 SQL·타입·무결성)과 운영 비용(모니터링, 백업, 인력)이 더 중요한 결정 요인인 경우가 많습니다.

스키마·데이터 타입

스키마 구조 차이

PostgreSQL: 계층적 네임스페이스

-- 하나의 데이터베이스에 여러 스키마
CREATE DATABASE myapp;
\c myapp
CREATE SCHEMA sales;
CREATE SCHEMA inventory;
CREATE TABLE sales.orders (id SERIAL PRIMARY KEY, ...);
CREATE TABLE inventory.products (id SERIAL PRIMARY KEY, ...);
-- 스키마 경로 설정
SET search_path TO sales, public;

MySQL: 데이터베이스 = 스키마

-- 각 스키마가 별도 데이터베이스
CREATE DATABASE sales;
CREATE DATABASE inventory;
USE sales;
CREATE TABLE orders (id INT AUTO_INCREMENT PRIMARY KEY, ...);
USE inventory;
CREATE TABLE products (id INT AUTO_INCREMENT PRIMARY KEY, ...);

고급 데이터 타입

PostgreSQL의 풍부한 타입:

-- 배열 타입
CREATE TABLE posts (
    id SERIAL PRIMARY KEY,
    title TEXT,
    tags TEXT[]  -- 문자열 배열
);
INSERT INTO posts (title, tags) 
VALUES ('C++ 가이드', ARRAY['cpp', 'programming', 'tutorial']);
SELECT * FROM posts WHERE 'cpp' = ANY(tags);
-- 범위 타입
CREATE TABLE reservations (
    id SERIAL PRIMARY KEY,
    room_id INT,
    period TSRANGE  -- 시간 범위
);
INSERT INTO reservations (room_id, period)
VALUES (101, '[2026-03-31 14:00, 2026-03-31 16:00)');
-- 겹치는 예약 검색
SELECT * FROM reservations 
WHERE period && '[2026-03-31 15:00, 2026-03-31 17:00)';
-- UUID 타입
CREATE TABLE users (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name TEXT
);
-- JSONB (바이너리 JSON, 인덱싱 가능)
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    specs JSONB
);
INSERT INTO products (name, specs)
VALUES ('노트북', '{"cpu": "i7", "ram": "16GB", "storage": "512GB SSD"}');
-- JSONB 쿼리
SELECT * FROM products WHERE specs->>'cpu' = 'i7';
SELECT * FROM products WHERE specs @> '{"ram": "16GB"}';
-- JSONB 인덱스
CREATE INDEX idx_specs_gin ON products USING GIN (specs);

예제의 SERIAL은 오래된 관례이고, PostgreSQL 10부터는 SQL 표준인 id INT GENERATED ALWAYS AS IDENTITY가 권장됩니다. SERIAL은 테이블과 별개의 시퀀스 객체를 만들기 때문에 권한 부여나 테이블 복사 시 시퀀스가 따라오지 않는 문제가 있습니다. gen_random_uuid()는 PostgreSQL 13부터 내장이라 uuid-ossp나 pgcrypto 확장 없이 바로 쓸 수 있습니다.

GIN 인덱스는 기본 연산자 클래스(jsonb_ops)일 때 @>, ?, ?| 같은 포함·키 존재 연산자에 쓰이고, specs->>'cpu' = 'i7'처럼 특정 경로 값을 꺼내 비교하는 쿼리에는 쓰이지 않습니다. 후자를 빠르게 하려면 CREATE INDEX ON products ((specs->>'cpu')); 같은 표현식 B-tree 인덱스가 필요합니다. “GIN을 만들었는데 EXPLAIN에 Seq Scan이 나온다”는 문의의 대부분이 이 차이 때문입니다. MySQL의 JSON 지원:

-- JSON 타입 (MySQL 5.7+)
CREATE TABLE products (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    specs JSON
);
INSERT INTO products (name, specs)
VALUES ('노트북', '{"cpu": "i7", "ram": "16GB", "storage": "512GB SSD"}');
-- JSON 쿼리
SELECT * FROM products WHERE JSON_EXTRACT(specs, '$.cpu') = 'i7';
SELECT * FROM products WHERE specs->>'$.cpu' = 'i7';  -- ->> 는 MySQL 5.7.13+
-- JSON 가상 컬럼 + 인덱스
ALTER TABLE products 
ADD COLUMN cpu VARCHAR(50) AS (specs->>'$.cpu') VIRTUAL;
CREATE INDEX idx_cpu ON products(cpu);

MySQL은 JSON 컬럼 자체에 범용 인덱스를 만들 수 없기 때문에, 자주 검색하는 경로를 위처럼 생성 컬럼(generated column)으로 꺼내 인덱스를 거는 것이 기본 패턴입니다. 8.0.13부터는 CREATE INDEX idx_cpu ON products ((CAST(specs->>'$.cpu' AS CHAR(50))));처럼 함수 인덱스로 바로 만들 수도 있고, 8.0.17부터 JSON 배열의 원소 검색을 위한 다중 값 인덱스(CAST(... AS UNSIGNED ARRAY))가 추가되었습니다. 검색 경로가 미리 정해져 있다면 두 DB의 차이는 크지 않고, “어떤 키로 검색할지 모르는” 반구조화 데이터를 다룰 때 PostgreSQL GIN의 이점이 커집니다.

CHECK 제약 및 도메인 무결성

PostgreSQL:

CREATE TABLE employees (
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    age INT CHECK (age >= 18 AND age <= 100),
    email TEXT CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
    salary NUMERIC(10, 2) CHECK (salary > 0),
    department TEXT CHECK (department IN ('engineering', 'sales', 'hr'))
);
-- 도메인 정의 (재사용 가능한 타입)
CREATE DOMAIN email_type AS TEXT
CHECK (VALUE ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$');
CREATE TABLE users (
    id SERIAL PRIMARY KEY,
    email email_type
);

MySQL:

-- CHECK 제약 (MySQL 8.0.16+)
CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    age INT CHECK (age >= 18 AND age <= 100),
    email VARCHAR(255) CHECK (email REGEXP '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\\.[A-Za-z]{2,}$'),
    salary DECIMAL(10, 2) CHECK (salary > 0),
    department ENUM('engineering', 'sales', 'hr')  -- ENUM 타입 활용
);

MySQL의 CHECK 제약은 8.0.16부터 실제로 검사됩니다. 그 이전 버전은 CHECK 구문을 에러 없이 받아들이고 조용히 무시했기 때문에, 오래된 MySQL에서 만든 스키마에 CHECK가 적혀 있다고 해서 데이터가 그 조건을 만족한다고 가정하면 안 됩니다. 8.0으로 올린 뒤 기존 데이터가 조건을 어기면 ALTER TABLE로 제약을 다시 추가할 때 Check constraint 'employees_chk_1' is violated. 에러가 납니다. ENUM은 편하지만 값을 추가하려면 ALTER TABLE이 필요하고 정렬이 문자열이 아니라 정의 순서로 이루어지므로, 값 목록이 자주 바뀐다면 참조 테이블 + 외래키가 더 유연합니다.

부분 인덱스

PostgreSQL:

-- 조건부 인덱스 (활성 사용자만)
CREATE INDEX idx_active_users ON users(email) WHERE active = true;
-- 특정 범위만 인덱싱
CREATE INDEX idx_recent_orders ON orders(created_at) 
WHERE created_at > '2026-01-01';
-- 인덱스 크기 절감 + 쿼리 속도 향상

MySQL:

-- 부분 인덱스 미지원 (전체 인덱스만 가능)
CREATE INDEX idx_users_email ON users(email);
-- 대안 1: 조건 컬럼을 앞에 둔 복합 인덱스
CREATE INDEX idx_active_email ON users(active, email);
-- 대안 2: 비활성 행은 NULL이 되는 생성 컬럼에 UNIQUE 인덱스
--        ("활성 사용자끼리만 이메일 중복 금지" 같은 조건부 유일성)
ALTER TABLE users ADD COLUMN active_email VARCHAR(255)
    AS (CASE WHEN active THEN email END) STORED;
CREATE UNIQUE INDEX uq_active_email ON users(active_email);

MySQL에서는 뷰에 인덱스를 만들 수 없으므로, 뷰는 쿼리를 짧게 해 줄 뿐 부분 인덱스를 대신하지 못합니다. 부분 인덱스가 실무에서 가장 아쉬운 경우는 크기 절감보다 조건부 유일성입니다. 예를 들어 “탈퇴하지 않은 사용자끼리만 이메일이 유일해야 한다”는 규칙은 PostgreSQL에서는 CREATE UNIQUE INDEX ... ON users(email) WHERE deleted_at IS NULL 한 줄이지만, MySQL에서는 위의 생성 컬럼 트릭(UNIQUE 인덱스는 NULL 중복을 허용한다는 성질 이용)이 필요합니다. 실무 해석: 도메인이 복잡한 제약·타입을 DB에 두고 싶다면 PostgreSQL이 유리한 경우가 많습니다. 단순한 키·값 CRUD 위주면 둘 다 충분한 경우가 많습니다.

트랜잭션·동시성

MVCC (Multi-Version Concurrency Control)

둘 다 MVCC를 사용하지만 구현 방식이 다릅니다.

PostgreSQL:

-- 트랜잭션 격리 수준 설정
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;
-- 동시성 충돌 시 자동 재시도 필요
-- Serialization failure 에러 발생 가능

특징:

읽기는 락을 걸지 않음 (MVCC)
VACUUM으로 오래된 버전 정리 필요
Serializable 격리 수준이 정확히 동작 MySQL (InnoDB):

-- 기본 격리 수준: REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;
COMMIT;

특징:

읽기도 MVCC 사용
Undo 로그로 이전 버전 관리
Gap Lock으로 팬텀 읽기 방지

같은 “MVCC”라도 옛 버전을 어디에 두느냐가 달라서 운영 문제의 모양이 다릅니다. PostgreSQL은 UPDATE할 때 행을 제자리에서 고치지 않고 새 버전의 행을 테이블에 추가합니다. 옛 행(dead tuple)은 VACUUM이 치울 때까지 테이블과 인덱스에 남으므로, 갱신이 잦은 테이블은 autovacuum이 따라가지 못하면 크기가 계속 커지는 bloat가 생깁니다. 또 인덱스 컬럼이 바뀌지 않는 갱신만 HOT(Heap-Only Tuple)으로 인덱스 쓰기를 피할 수 있어, 인덱스가 많은 테이블의 잦은 UPDATE는 쓰기 증폭이 큽니다. InnoDB는 행을 제자리에서 고치고 옛 값을 undo 로그에 두므로 테이블 bloat는 적지만, 오래 열려 있는 트랜잭션 하나가 undo 정리(purge)를 막아 History list length가 계속 늘고 읽기가 느려지는 문제가 생깁니다. 어느 쪽이든 오래 열린 트랜잭션이 가장 큰 적이라는 점은 같습니다. 애플리케이션이 트랜잭션을 연 채로 외부 API를 호출하거나, 커넥션 풀에서 idle in transaction 상태로 방치되는 경우를 모니터링해야 합니다.

REPEATABLE READ의 의미도 미묘하게 다릅니다. PostgreSQL의 REPEATABLE READ는 스냅샷 격리라서, 내가 읽은 뒤 다른 트랜잭션이 고친 행을 내가 다시 UPDATE하려 하면 ERROR: could not serialize access due to concurrent update로 실패하고 애플리케이션이 재시도해야 합니다. InnoDB의 REPEATABLE READ에서 일반 SELECT는 스냅샷을 읽지만, UPDATE와 SELECT ... FOR UPDATE는 최신 커밋 값을 읽고 잠급니다. 그래서 “스냅샷으로 읽은 값에 기반해 계산한 뒤 쓰는” 코드가 에러 없이 다른 트랜잭션의 변경을 덮어쓸 수 있습니다(lost update). 같은 격리 수준 이름이라도 이관할 때 동시성 테스트를 다시 해야 하는 이유입니다.

격리 수준 비교

격리 수준	PostgreSQL	MySQL (InnoDB)
READ UNCOMMITTED	구문만 허용 (READ COMMITTED로 동작)	지원
READ COMMITTED	기본값	지원
REPEATABLE READ	지원	기본값
SERIALIZABLE	SSI 알고리즘	락 기반

락 메커니즘

PostgreSQL: 명시적 락

-- 행 락
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- 공유 락 (읽기 락)
SELECT * FROM orders WHERE id = 1 FOR SHARE;
-- 테이블 락
LOCK TABLE orders IN EXCLUSIVE MODE;
-- Advisory Lock (애플리케이션 레벨 락)
SELECT pg_advisory_lock(12345);
-- 작업 수행
SELECT pg_advisory_unlock(12345);

MySQL: 행 락 및 갭 락

-- 행 락
SELECT * FROM orders WHERE id = 1 FOR UPDATE;
-- 공유 락 (8.0부터는 FOR SHARE 권장, LOCK IN SHARE MODE는 하위 호환용)
SELECT * FROM orders WHERE id = 1 FOR SHARE;
-- Gap Lock (범위 락, REPEATABLE READ에서 자동)
SELECT * FROM orders WHERE id BETWEEN 10 AND 20 FOR UPDATE;
-- id 10-20 사이의 "존재하지 않는 행"도 락
-- Next-Key Lock = Record Lock + Gap Lock

갭 락은 팬텀을 막아 주는 대신 예상 밖의 대기와 데드락을 만드는 주범이기도 합니다. 인덱스가 없는 컬럼으로 UPDATE ... WHERE status = 'pending'을 하면 InnoDB는 조건을 확인하려고 훑은 모든 행과 그 사이 간격을 잠그므로, 사실상 테이블 전체가 잠긴 것처럼 동작합니다. 두 트랜잭션이 같은 간격에 동시에 INSERT하려다 Deadlock found when trying to get lock; try restarting transaction이 나는 것도 흔한 패턴입니다. 원인 분석에는 SHOW ENGINE INNODB STATUS의 LATEST DETECTED DEADLOCK 섹션이 가장 유용합니다. 격리 수준을 READ COMMITTED로 낮추면 대부분의 갭 락이 사라지므로, 팬텀 방지가 꼭 필요하지 않은 서비스는 이 설정을 쓰기도 합니다.

DDL 트랜잭션

PostgreSQL: DDL도 트랜잭션 가능

BEGIN;
CREATE TABLE users (id SERIAL PRIMARY KEY, name TEXT);
ALTER TABLE users ADD COLUMN email TEXT;
CREATE INDEX idx_email ON users(email);
-- 에러 발생 시 모든 DDL 롤백
ROLLBACK;
-- 성공 시 커밋
COMMIT;

MySQL: DDL은 암묵적 커밋

START TRANSACTION;
INSERT INTO logs (message) VALUES ('start');
CREATE TABLE temp (id INT);  -- 여기서 자동 COMMIT 발생!
INSERT INTO logs (message) VALUES ('end');  -- 새 트랜잭션
ROLLBACK;  -- 'end'만 롤백, 'start'는 이미 커밋됨

실무 영향:

PostgreSQL: 마이그레이션 스크립트를 트랜잭션으로 감싸서 안전하게 실행
MySQL: DDL 실패 시 수동 롤백 필요, 신중한 스크립트 작성 필요

MySQL 8.0부터는 “원자적 DDL”이 도입되어 DDL 문장 하나가 중간에 실패해 반쯤 적용된 상태로 남는 일은 없어졌지만, 여러 문장을 하나의 트랜잭션으로 묶을 수 없다는 점은 그대로입니다. 그래서 MySQL 마이그레이션 도구(Flyway, Liquibase, Rails 등)는 “5번째 문장에서 실패하면 1~4번은 이미 적용된 상태”를 전제로 복구 절차를 준비해야 합니다.

PostgreSQL에서도 예외가 하나 있습니다. 운영 중인 큰 테이블에 락 없이 인덱스를 만드는 CREATE INDEX CONCURRENTLY는 트랜잭션 안에서 실행할 수 없어서, 마이그레이션 도구가 파일 전체를 BEGIN으로 감싸면 ERROR: CREATE INDEX CONCURRENTLY cannot run inside a transaction block이 납니다. 이런 마이그레이션은 트랜잭션 없이 실행하도록 따로 분리해야 하고, 실패하면 INVALID 상태의 인덱스가 남으므로 DROP INDEX로 정리한 뒤 다시 만들어야 합니다. 또 트랜잭션 안의 ALTER TABLE은 커밋할 때까지 강한 락(ACCESS EXCLUSIVE)을 유지하므로, DDL과 대량 데이터 수정을 한 트랜잭션에 넣으면 그동안 해당 테이블의 모든 읽기까지 막힙니다.

동시성 제어 예제

아래 두 예제는 엔진의 특징이 아니라 락 전략의 예입니다. 낙관적 락과 비관적 락 모두 두 DB에서 똑같이 쓸 수 있으며, 충돌이 드물면 낙관적 락, 같은 행에 경합이 잦으면 비관적 락이 유리합니다.

Optimistic Locking (PostgreSQL 예)

CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name TEXT,
    stock INT,
    version INT DEFAULT 0  -- 버전 컬럼
);
-- 재고 감소 (낙관적 락)
UPDATE products 
SET stock = stock - 1, version = version + 1
WHERE id = 1 AND version = 5;  -- 현재 버전 확인
-- 영향받은 행이 0이면 충돌 발생 → 재시도

Pessimistic Locking (MySQL 예)

-- 재고 감소 (비관적 락)
START TRANSACTION;
SELECT stock FROM products WHERE id = 1 FOR UPDATE;
-- 다른 트랜잭션은 대기
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

실무 해석: 긴 트랜잭션·복잡한 리포트 쿼리가 많다면 데드락·대기 프로파일링이 필수이며, 엔진별로 기본 격리 수준과 인덱스 설계가 다르게 먹힙니다.

쿼리·최적화·인덱싱

CTE (Common Table Expression)

PostgreSQL: 재귀 CTE

-- 조직도 계층 조회
WITH RECURSIVE org_tree AS (
    -- 기본 케이스: 최상위 관리자
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    -- 재귀: 하위 직원
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, name;
-- 재귀 CTE로 그래프 탐색
WITH RECURSIVE path AS (
    SELECT id, name, ARRAY[id] as path
    FROM nodes
    WHERE id = 1  -- 시작 노드
    
    UNION ALL
    
    SELECT n.id, n.name, p.path || n.id
    FROM nodes n
    INNER JOIN edges e ON n.id = e.to_id
    INNER JOIN path p ON e.from_id = p.id
    WHERE NOT (n.id = ANY(p.path))  -- 순환 방지
)
SELECT * FROM path;

MySQL: 재귀 CTE (8.0+)

-- 동일한 재귀 CTE 지원
-- 실행 예제
WITH RECURSIVE org_tree AS (
    SELECT id, name, manager_id, 1 as level
    FROM employees
    WHERE manager_id IS NULL
    
    UNION ALL
    
    SELECT e.id, e.name, e.manager_id, ot.level + 1
    FROM employees e
    INNER JOIN org_tree ot ON e.manager_id = ot.id
)
SELECT * FROM org_tree ORDER BY level, name;

윈도 함수

PostgreSQL: 고급 윈도 함수

-- 순위 함수
SELECT 
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank,
    DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) as dense_rank,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) as row_num
FROM employees;
-- 이동 평균
SELECT 
    date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7days
FROM daily_sales;
-- FILTER 절 (조건부 집계)
SELECT 
    department,
    COUNT(*) as total,
    COUNT(*) FILTER (WHERE salary > 50000) as high_earners
FROM employees
GROUP BY department;

MySQL: 윈도 함수 (8.0+)

-- 기본 윈도 함수 지원
SELECT 
    name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as rank
FROM employees;
-- 이동 평균
SELECT 
    date,
    revenue,
    AVG(revenue) OVER (
        ORDER BY date 
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) as moving_avg_7days
FROM daily_sales;

풀 텍스트 검색

PostgreSQL: 내장 FTS

-- tsvector 타입으로 검색 인덱스
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title TEXT,
    content TEXT,
    search_vector TSVECTOR
);
-- 검색 벡터 생성 ('english'는 내장 설정. 한국어 설정은 기본 제공되지 않음)
UPDATE articles 
SET search_vector = 
    setweight(to_tsvector('english', title), 'A') ||
    setweight(to_tsvector('english', content), 'B');
-- GIN 인덱스
CREATE INDEX idx_search ON articles USING GIN(search_vector);
-- 검색
SELECT title, ts_rank(search_vector, query) as rank
FROM articles, to_tsquery('english', 'memory & leak') as query
WHERE search_vector @@ query
ORDER BY rank DESC;
-- 하이라이트
SELECT ts_headline('english', content, to_tsquery('english', 'memory'))
FROM articles
WHERE id = 1;
-- 한국어: 형태소 분석 대신 pg_trgm(3-gram) 인덱스로 부분 일치 검색
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_content_trgm ON articles USING GIN (content gin_trgm_ops);
SELECT title FROM articles WHERE content ILIKE '%메모리 누수%';

PostgreSQL 내장 전문 검색에는 한국어 설정이 없습니다. to_tsvector('korean', ...)을 쓰면 ERROR: text search configuration "korean" does not exist가 나고, 'simple' 설정으로 대신하면 공백 기준으로만 자르기 때문에 “메모리가”, “메모리를” 같은 조사가 붙은 어절이 모두 다른 단어가 되어 검색이 잘 되지 않습니다. 한국어 검색이 필요하면 위처럼 pg_trgm으로 ILIKE '%...%'를 인덱스로 가속하거나, 한국어·일본어용 확장인 PGroonga, 2-gram 기반의 pg_bigm, 형태소 분석기를 붙인 사전 설정을 설치해야 합니다. 검색 품질이 제품의 핵심이라면 Elasticsearch·OpenSearch 같은 전문 검색 엔진을 따로 두는 경우가 많습니다. MySQL: FULLTEXT 인덱스

-- FULLTEXT 인덱스 생성
CREATE TABLE articles (
    id INT AUTO_INCREMENT PRIMARY KEY,
    title VARCHAR(255),
    content TEXT,
    FULLTEXT KEY idx_fulltext (title, content)
) ENGINE=InnoDB;
-- 자연어 검색
SELECT title, MATCH(title, content) AGAINST('C++ 메모리') as score
FROM articles
WHERE MATCH(title, content) AGAINST('C++ 메모리' IN NATURAL LANGUAGE MODE)
ORDER BY score DESC;
-- 불린 모드 (AND, OR, NOT)
SELECT title
FROM articles
WHERE MATCH(title, content) AGAINST('+C++ +메모리 -포인터' IN BOOLEAN MODE);

MySQL FULLTEXT도 한국어에는 설정이 필요합니다. 기본 파서는 공백과 구두점으로 단어를 나누므로 조사가 붙은 어절 문제가 똑같이 생기고, InnoDB는 기본적으로 3글자 미만 토큰을 색인하지 않아(innodb_ft_min_token_size=3) 두 글자 한국어 단어가 대부분 빠집니다. MySQL 5.7.6부터 제공되는 ngram 파서(FULLTEXT KEY idx_ft (title, content) WITH PARSER ngram, 기본 2-gram)를 쓰면 한국어·중국어·일본어를 글자 단위 조각으로 색인해 이 문제를 상당 부분 해결할 수 있습니다. 또 +C++처럼 +, -, *가 들어간 검색어는 불린 모드 연산자로 해석되고 C++는 토큰화 과정에서 C만 남기 때문에, 기호가 포함된 기술 용어 검색은 두 DB 모두 기대대로 동작하지 않는 경우가 많습니다.

쿼리 최적화 및 EXPLAIN

PostgreSQL:

-- 상세한 실행 계획
EXPLAIN (ANALYZE, BUFFERS, VERBOSE) 
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2026-01-01';
-- 출력 예시:
-- Buffers: shared hit=1234 read=56
-- Planning Time: 0.123 ms
-- Execution Time: 45.678 ms

MySQL:

-- 실행 계획
EXPLAIN FORMAT=JSON
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2026-01-01';
-- 실제 실행 분석
EXPLAIN ANALYZE
SELECT * FROM orders o
JOIN customers c ON o.customer_id = c.id
WHERE o.created_at > '2026-01-01';

인덱스 전략

PostgreSQL: 다양한 인덱스 타입

-- B-tree (기본)
CREATE INDEX idx_email ON users(email);
-- Hash 인덱스 (등호 비교만)
CREATE INDEX idx_hash_email ON users USING HASH(email);
-- GIN (배열, JSONB, 풀텍스트)
CREATE INDEX idx_tags ON posts USING GIN(tags);
-- GiST (지리 데이터, 범위)
CREATE INDEX idx_location ON stores USING GIST(location);
-- BRIN (대용량 시계열 데이터)
CREATE INDEX idx_created ON logs USING BRIN(created_at);
-- 복합 인덱스
CREATE INDEX idx_user_date ON orders(user_id, created_at DESC);
-- 표현식 인덱스
CREATE INDEX idx_lower_email ON users(LOWER(email));

MySQL: 주로 B-tree

-- B-tree 인덱스 (기본)
CREATE INDEX idx_email ON users(email);
-- 복합 인덱스
CREATE INDEX idx_user_date ON orders(user_id, created_at DESC);
-- 함수 기반 인덱스 (MySQL 8.0+)
CREATE INDEX idx_lower_email ON users((LOWER(email)));
-- FULLTEXT 인덱스
CREATE FULLTEXT INDEX idx_content ON articles(content);
-- 공간 인덱스
CREATE SPATIAL INDEX idx_location ON stores(location);

쿼리 힌트

PostgreSQL: 설정 기반

-- 특정 쿼리에만 설정 변경
SET LOCAL enable_seqscan = off;  -- 순차 스캔 비활성화
SELECT * FROM large_table WHERE id > 1000;
-- 병렬 쿼리 설정
SET max_parallel_workers_per_gather = 4;

MySQL: 힌트 구문

-- 인덱스 힌트
SELECT * FROM orders USE INDEX (idx_created) 
WHERE created_at > '2026-01-01';
-- 조인 순서 힌트
SELECT /*+ JOIN_ORDER(o, c) */ *
FROM orders o
JOIN customers c ON o.customer_id = c.id;
-- 인덱스 강제
SELECT * FROM orders FORCE INDEX (idx_user_date)
WHERE user_id = 123;

실무 해석: PostgreSQL은 오래부터 분석 쿼리에 강점이 있다는 평가가 많습니다. 복잡한 CTE, 윈도 함수, 다양한 인덱스 타입이 필요하면 PostgreSQL이 유리합니다.

운영·복제·생태계

복제 (Replication)

PostgreSQL: 스트리밍 복제

# 프라이머리 (postgresql.conf)
wal_level = replica
max_wal_senders = 3
wal_keep_size = 1GB        # PostgreSQL 13+ (이전 버전은 wal_keep_segments)
# 스탠바이 (postgresql.conf) + 데이터 디렉터리에 빈 standby.signal 파일 (PostgreSQL 12+)
primary_conninfo = 'host=primary port=5432 user=replicator password=...'

특징:

물리적 복제 (바이트 단위)
논리적 복제 (테이블 단위, PostgreSQL 10+)
동기/비동기 복제 선택 가능 논리적 복제 예제:

-- 마스터: 발행
CREATE PUBLICATION my_pub FOR TABLE users, orders;
-- 슬레이브: 구독
CREATE SUBSCRIPTION my_sub
CONNECTION 'host=master dbname=mydb user=replicator'
PUBLICATION my_pub;

MySQL: 바이너리 로그 복제

# 소스 (my.cnf)
[mysqld]
server-id = 1
log-bin = mysql-bin
binlog-format = ROW
# 레플리카 (my.cnf)
[mysqld]
server-id = 2
relay-log = relay-bin

-- 레플리카에서 복제 시작 (8.0.23+ 구문. 이전 버전은 CHANGE MASTER TO / START SLAVE)
CHANGE REPLICATION SOURCE TO
    SOURCE_HOST='source',
    SOURCE_USER='replicator',
    SOURCE_PASSWORD='...',
    SOURCE_LOG_FILE='mysql-bin.000001',
    SOURCE_LOG_POS=154;
START REPLICA;

특징:

Statement-based, Row-based, Mixed 복제
GTID (Global Transaction ID) 지원
멀티 소스 복제 가능

두 방식의 가장 큰 운영상 차이는 무엇을 복제하느냐입니다. PostgreSQL의 스트리밍 복제는 WAL, 즉 디스크 페이지 수준의 변경을 그대로 보내므로 스탠바이는 프라이머리와 바이트 단위로 같은 복사본이 되고, 메이저 버전이 다르면 복제할 수 없습니다. 메이저 버전 업그레이드나 일부 테이블만 옮기는 작업에는 논리적 복제를 써야 합니다. MySQL의 바이너리 로그는 행 변경 이벤트라서 버전이 다른 레플리카나 일부 DB만 받는 레플리카를 구성하기 쉽고, Debezium 같은 CDC 도구가 binlog를 읽어 Kafka로 흘려보내는 구성도 흔합니다. 위 예제처럼 파일명·위치를 직접 지정하는 방식은 장애 전환 때 위치를 계산하기 번거로우므로, 새로 구성한다면 GTID(gtid_mode=ON, SOURCE_AUTO_POSITION=1)를 쓰는 것이 일반적입니다. 어느 쪽이든 기본은 비동기 복제라서, 프라이머리가 갑자기 죽으면 아직 전달되지 않은 최근 트랜잭션은 잃을 수 있습니다(RPO > 0).

백업 및 복구

PostgreSQL:

# 논리적 백업
pg_dump mydb > backup.sql
pg_dump -Fc mydb > backup.dump  # 커스텀 포맷 (압축)
# 복구
psql mydb < backup.sql
pg_restore -d mydb backup.dump
# 물리적 백업 (PITR - Point-In-Time Recovery)
pg_basebackup -D /backup/base -Fp -Xs -P
# WAL 아카이빙으로 특정 시점 복구 가능

MySQL:

# 논리적 백업
mysqldump mydb > backup.sql
mysqldump --single-transaction mydb > backup.sql  # 일관성 보장
# 복구
mysql mydb < backup.sql
# 물리적 백업 (Percona XtraBackup)
xtrabackup --backup --target-dir=/backup/
xtrabackup --prepare --target-dir=/backup/
xtrabackup --copy-back --target-dir=/backup/

모니터링

PostgreSQL:

-- 활성 쿼리 확인
-- 실행 예제
SELECT pid, usename, state, query, now() - query_start as duration
FROM pg_stat_activity
WHERE state != 'idle'
ORDER BY duration DESC;
-- 느린 쿼리 찾기 (shared_preload_libraries에 pg_stat_statements 추가 + CREATE EXTENSION 필요,
--  컬럼명 total_exec_time/mean_exec_time은 PostgreSQL 13+)
SELECT query, calls, total_exec_time, mean_exec_time
FROM pg_stat_statements
ORDER BY mean_exec_time DESC
LIMIT 10;
-- 테이블 통계 (이 뷰의 테이블 이름 컬럼은 relname)
SELECT schemaname, relname, n_live_tup, n_dead_tup, last_autovacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC;
-- 인덱스 사용률
SELECT schemaname, relname, indexrelname, idx_scan, idx_tup_read
FROM pg_stat_user_indexes
WHERE idx_scan = 0  -- 사용되지 않는 인덱스
ORDER BY pg_relation_size(indexrelid) DESC;
-- 캐시 히트율
SELECT 
    sum(heap_blks_read) as heap_read,
    sum(heap_blks_hit) as heap_hit,
    sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) as ratio
FROM pg_statio_user_tables;

MySQL:

-- 활성 쿼리 확인
SELECT id, user, host, db, command, time, state, info
FROM information_schema.processlist
WHERE command != 'Sleep'
ORDER BY time DESC;
-- 느린 쿼리 (Performance Schema)
SELECT 
    DIGEST_TEXT,
    COUNT_STAR as exec_count,
    AVG_TIMER_WAIT/1000000000000 as avg_sec,
    SUM_TIMER_WAIT/1000000000000 as total_sec
FROM performance_schema.events_statements_summary_by_digest
ORDER BY SUM_TIMER_WAIT DESC
LIMIT 10;
-- 테이블 통계
SELECT 
    table_schema,
    table_name,
    table_rows,
    data_length,
    index_length
FROM information_schema.tables
WHERE table_schema NOT IN ('mysql', 'information_schema', 'performance_schema')
ORDER BY data_length DESC;
-- 인덱스 사용률
SELECT 
    object_schema,
    object_name,
    index_name,
    count_star,
    count_read
FROM performance_schema.table_io_waits_summary_by_index_usage
WHERE index_name IS NOT NULL
ORDER BY count_star DESC;

연결 풀링

PostgreSQL: pgBouncer

# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb
[pgbouncer]
listen_addr = 0.0.0.0
listen_port = 6432
auth_type = md5
pool_mode = transaction  # session, transaction, statement
max_client_conn = 1000
default_pool_size = 25

PostgreSQL에서 커넥션 풀러가 사실상 필수에 가까운 이유는 커넥션 하나가 프로세스 하나이기 때문입니다. 연결마다 백엔드 프로세스가 생겨 메모리를 쓰므로, 애플리케이션 인스턴스가 늘면서 연결이 수백~수천 개가 되면 FATAL: sorry, too many clients already를 만나거나 연결 자체가 서버 자원을 잡아먹습니다. MySQL은 연결을 스레드로 처리해 상대적으로 가볍지만, 연결 수가 매우 많아지면 역시 풀링이 필요합니다.

pool_mode = transaction은 트랜잭션이 끝날 때마다 서버 연결을 다른 클라이언트에게 넘기므로 효율이 좋지만, 세션 상태에 의존하는 기능이 깨집니다. SET 문으로 바꾼 세션 설정, LISTEN/NOTIFY, 세션 단위 advisory lock, 그리고 드라이버의 이름 붙은 prepared statement가 대표적입니다. 마지막 경우는 prepared statement "S_1" does not exist 같은 간헐적 에러로 나타나 원인을 찾기 어렵습니다. PgBouncer 1.21부터는 max_prepared_statements 설정으로 프로토콜 수준 prepared statement를 추적할 수 있으므로, 버전을 확인하고 설정하거나 드라이버에서 이름 붙은 문장을 끄는 방법을 택해야 합니다. Node.js 연동:

const { Pool } = require('pg');
const pool = new Pool({
    host: 'localhost',
    port: 6432,  // pgBouncer 포트
    database: 'mydb',
    user: 'myuser',
    password: 'mypass',
    max: 20,  // 애플리케이션 레벨 풀
    idleTimeoutMillis: 30000
});
const result = await pool.query('SELECT * FROM users WHERE id = $1', [123]);

MySQL: ProxySQL

# proxysql.cnf
mysql_servers =
(
    { address="127.0.0.1", port=3306, hostgroup=0 }
)
mysql_users =
(
    { username="myuser", password="mypass", default_hostgroup=0 }
)

Node.js 연동:

const mysql = require('mysql2/promise');
const pool = mysql.createPool({
    host: 'localhost',
    port: 6033,  // ProxySQL 포트
    database: 'mydb',
    user: 'myuser',
    password: 'mypass',
    waitForConnections: true,
    connectionLimit: 20,
    queueLimit: 0
});
const [rows] = await pool.query('SELECT * FROM users WHERE id = ?', [123]);

확장 및 플러그인

PostgreSQL: 풍부한 확장

-- PostGIS (지리 데이터)
CREATE EXTENSION postgis;
CREATE TABLE stores (
    id SERIAL PRIMARY KEY,
    name TEXT,
    location GEOGRAPHY(POINT, 4326)
);
-- 반경 검색
SELECT name FROM stores
WHERE ST_DWithin(
    location,
    ST_MakePoint(127.0, 37.5)::geography,
    1000  -- 1km
);
-- pg_trgm (유사 문자열 검색)
CREATE EXTENSION pg_trgm;
CREATE INDEX idx_name_trgm ON users USING GIN(name gin_trgm_ops);
SELECT * FROM users WHERE name % 'Alice';  -- 유사도 검색
-- uuid-ossp (UUID 생성; PostgreSQL 13+에서는 내장 gen_random_uuid()로 충분)
CREATE EXTENSION "uuid-ossp";
SELECT uuid_generate_v4();

확장은 PostgreSQL의 큰 장점이지만, 관리형 서비스(RDS, Cloud SQL, Azure 등)에서는 허용된 확장 목록이 정해져 있다는 점을 먼저 확인해야 합니다. 로컬에서 쓰던 확장이 운영 환경에서 CREATE EXTENSION이 안 되어 설계를 다시 해야 하는 경우가 생각보다 흔합니다. MySQL: 플러그인

-- 공간 데이터 (내장)
-- 실행 예제
CREATE TABLE stores (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255),
    location POINT NOT NULL SRID 4326
);
CREATE SPATIAL INDEX idx_location ON stores(location);
-- 반경 검색 (SRID 4326에서 MySQL 8.0의 WKT 축 순서는 위도, 경도)
SELECT name FROM stores
WHERE ST_Distance_Sphere(
    location,
    ST_GeomFromText('POINT(37.5 127.0)', 4326)
) < 1000;

좌표 순서는 두 DB를 오가며 가장 많이 틀리는 부분입니다. PostGIS의 ST_MakePoint(127.0, 37.5)는 (경도, 위도) 순서인데, MySQL 8.0은 SRID 4326의 WKT를 EPSG 정의에 따라 (위도, 경도)로 해석합니다. 그래서 PostGIS 습관대로 POINT(127.0 37.5)를 넘기면 Latitude 127.000000 is out of range in function st_geomfromtext. It must be within [-90.000000, 90.000000]. 에러가 납니다. 경도·위도 순서를 유지하고 싶다면 ST_GeomFromText('POINT(127.0 37.5)', 4326, 'axis-order=long-lat')처럼 옵션을 줍니다. 또 이 쿼리처럼 WHERE 절에서 모든 행에 거리 함수를 계산하면 공간 인덱스를 쓰지 못하므로, MBRContains로 경계 사각형을 먼저 걸러야 인덱스가 사용됩니다. 실무 해석: 팀에 MySQL 운영 경험이 압도적으로 많다면 비용이 낮습니다. PostGIS 등 지리 확장이 필요하면 PostgreSQL이 자연스럽습니다.

성능 비교: 벤치마크와 트레이드오프

공개 TPC-C, sysbench 결과는 하드웨어·버전·튜닝에 따라 매번 달라집니다. 아래는 방향성만 잡는 체크리스트입니다.

시나리오	메모
단순 PK 조회·소량 쓰기	둘 다 매우 빠름—인덱스가 핵심
복잡한 조인·집계	스키마·통계·병렬 쿼리 설정에 따라 역전
고동시 쓰기	연결 풀·파티셔닝·샤딩 설계가 DB 종류보다 큰 경우 많음

트레이드오프: “조금 더 빠른 벤치”보다 백업·복구 시간, 장애 시 RPO/RTO, 마이그레이션 난이도를 함께 적으면 결정이 안정됩니다.

실무 사례

금융·재고 등 강한 무결성: CHECK·외래키·트랜잭션 경계를 DB에 두는 편—PostgreSQL 선호 사례가 많음.
기존 CMS·쇼핑몰 스택: MySQL이 기본인 경우 레거시 호환 우선.
JSON 반정규화 + 인덱스: PostgreSQL jsonb로 시작 후 검색 요구가 커지면 전문 검색 엔진을 병행. Node 연동은 Node.js 데이터베이스 연동과 Docker Compose 스택을 함께 보세요. 캐시·엣지·클러스터는 Redis 캐싱, Nginx, Kubernetes(minikube) 순으로, 운영 중 디스크 이슈는 Linux 디스크/inode와 연결됩니다.

트러블슈팅

증상	점검
이관 후 쿼리만 느림	실행 계획·통계 갱신·인덱스 타입 차이
문자열 정렬 불일치	콜레이션·대소문자 규칙
날짜·타임존 버그	`TIMESTAMP` vs `TIMESTAMPTZ`(PostgreSQL), MySQL의 타임존 처리
ORM 마이그레이션 실패	벤더별 DDL 차이—수동 SQL 분기

마무리

PostgreSQL은 타입·SQL 표현력·확장(예: PostGIS)에서 강점이 자주 언급되고, MySQL은 레거시·호스팅·단순 워크로드와의 궁합이 좋습니다.
실제 선택은 팀 운영 역량, 워크로드, 버전으로 확정하세요.
캐시 계층은 Redis 캐싱 패턴에서 이어서 설계할 수 있습니다.

자주 묻는 질문 (FAQ)

Q. MySQL에서 PostgreSQL로 옮긴 뒤 날짜와 시간 값이 어긋나는 이유는 무엇인가요?

A. 두 데이터베이스는 시간 타입의 의미가 다릅니다. MySQL의 TIMESTAMP는 세션 타임존 기준으로 UTC로 변환해 저장하지만 DATETIME은 변환하지 않고, PostgreSQL은 타임존 정보가 없는 TIMESTAMP와 UTC 기준으로 저장하는 TIMESTAMPTZ로 나뉩니다. 이관할 때는 컬럼마다 어떤 의미로 저장했는지 확인해 대응하는 타입으로 매핑하고, 애플리케이션과 커넥션의 타임존 설정도 함께 맞춰야 합니다.

이 글의 핵심

들어가며

언제 PostgreSQL을, 언제 MySQL(MariaDB)을 쓰나요?

개념: RDB 공통점과 비교 축

기본 개념

왜 “하나의 정답”이 없는가

스키마·데이터 타입

스키마 구조 차이

고급 데이터 타입

CHECK 제약 및 도메인 무결성

부분 인덱스

트랜잭션·동시성

MVCC (Multi-Version Concurrency Control)

격리 수준 비교

락 메커니즘

DDL 트랜잭션

동시성 제어 예제

쿼리·최적화·인덱싱

CTE (Common Table Expression)

윈도 함수

풀 텍스트 검색

쿼리 최적화 및 EXPLAIN

인덱스 전략

쿼리 힌트

운영·복제·생태계

복제 (Replication)

백업 및 복구

모니터링

연결 풀링

확장 및 플러그인

성능 비교: 벤치마크와 트레이드오프

실무 사례

트러블슈팅

마무리

자주 묻는 질문 (FAQ)

Q. MySQL에서 PostgreSQL로 옮긴 뒤 날짜와 시간 값이 어긋나는 이유는 무엇인가요?

같이 보면 좋은 글