MySQL vs PostgreSQL — 백엔드 개발자가 알아야 할 차이

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255),
    attributes JSON
);

INSERT INTO products (name, attributes)
VALUES ('노트북', '{"brand": "Samsung", "ram": 16, "storage": "512GB"}');

-- JSON 필드 조회
SELECT name, attributes->>'$.brand' AS brand
FROM products
WHERE attributes->>'$.ram' = '16';

-- MySQL 8.0+: Multi-Valued Index
CREATE INDEX idx_tags ON products ((CAST(attributes->'$.tags' AS CHAR(50) ARRAY)));

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(255),
    attributes JSONB
);

INSERT INTO products (name, attributes)
VALUES ('노트북', '{"brand": "Samsung", "ram": 16, "tags": ["ultrabook", "2024"]}');

-- JSONB 연산자
SELECT name, attributes->>'brand' AS brand
FROM products
WHERE attributes @> '{"ram": 16}';    -- 포함 연산자

-- GIN 인덱스로 JSONB 전체 필드 인덱싱
CREATE INDEX idx_attributes ON products USING GIN (attributes);

-- JSONB 배열 요소 검색
SELECT * FROM products
WHERE attributes->'tags' ? 'ultrabook';

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE articles (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    title VARCHAR(255),
    body TEXT,
    FULLTEXT INDEX ft_idx (title, body)
) ENGINE=InnoDB;

-- 자연어 검색
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('Redis 캐시 전략' IN NATURAL LANGUAGE MODE);

-- 불린 모드
SELECT * FROM articles
WHERE MATCH(title, body) AGAINST('+Redis -Memcached' IN BOOLEAN MODE);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE TABLE articles (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(255),
    body TEXT,
    search_vector TSVECTOR
);

-- tsvector 컬럼 자동 업데이트 트리거
CREATE FUNCTION update_search_vector() RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector := to_tsvector('simple', COALESCE(NEW.title, '') || ' ' || COALESCE(NEW.body, ''));
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_search_vector
    BEFORE INSERT OR UPDATE ON articles
    FOR EACH ROW EXECUTE FUNCTION update_search_vector();

-- GIN 인덱스
CREATE INDEX idx_search ON articles USING GIN (search_vector);

-- 검색
SELECT title, ts_rank(search_vector, query) AS rank
FROM articles, to_tsquery('simple', 'Redis & 캐시') query
WHERE search_vector @@ query
ORDER BY rank DESC;

1
2
3
4
5
6
7
8
9
-- MySQL 기본 격리 수준: REPEATABLE READ
SHOW VARIABLES LIKE 'transaction_isolation';
-- 'REPEATABLE-READ'

-- InnoDB는 REPEATABLE READ에서도 Phantom Read를 방지한다 (Gap Lock)
START TRANSACTION;
SELECT * FROM orders WHERE amount > 1000 FOR UPDATE;
-- Gap Lock으로 범위 내 INSERT 차단
COMMIT;

1
2
3
4
5
6
7
8
9
10
-- PostgreSQL 기본 격리 수준: READ COMMITTED
SHOW default_transaction_isolation;
-- 'read committed'

-- SERIALIZABLE 격리 수준 사용
BEGIN TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT SUM(balance) FROM accounts WHERE branch = 'gangnam';
UPDATE accounts SET balance = balance - 1000 WHERE id = 1;
COMMIT;
-- 직렬화 충돌 시 에러 발생 → 애플리케이션에서 재시도 필요

1
2
3
-- MySQL: 기본 키 조회는 매우 빠르다
SELECT * FROM users WHERE id = 42;
-- Clustered Index 구조로 PK 조회 = 데이터 직접 접근

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
-- PostgreSQL: CTE + 윈도우 함수 조합
WITH monthly_sales AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        category,
        SUM(amount) AS total
    FROM orders
    GROUP BY 1, 2
)
SELECT
    month,
    category,
    total,
    LAG(total) OVER (PARTITION BY category ORDER BY month) AS prev_month,
    ROUND((total - LAG(total) OVER (PARTITION BY category ORDER BY month))
        / LAG(total) OVER (PARTITION BY category ORDER BY month) * 100, 2) AS growth_pct
FROM monthly_sales
ORDER BY category, month;