PostgreSQL vs MySQL 인덱싱 비교

PostgreSQL은 부분·표현식·GIN 인덱스로 복잡한 쿼리에 강합니다. MySQL은 B-Tree 기반 단순 쿼리 처리에 최적화돼 있습니다.

데이터베이스 인덱스는 테이블 전체를 스캔하지 않고 원하는 행을 빠르게 찾기 위한 자료구조입니다. PostgreSQL과 MySQL은 지원하는 인덱스 타입과 내부 구현이 다릅니다. 이 차이는 쿼리 성능과 활용 전략에 직접 영향을 줍니다.

flowchart TD
    Q[쿼리 조건] --> E{동등·범위 조회}
    E -->|예| B[B-Tree 검토]
    E -->|아니요| T{텍스트·배열·JSON}
    T -->|PostgreSQL| G[GIN·GiST·표현식 인덱스]
    T -->|MySQL| F[FULLTEXT·함수 인덱스]

지원 인덱스 타입 비교

PostgreSQL

MySQL (InnoDB)

PostgreSQL은 B-Tree 외에 GIN, GiST, BRIN처럼 용도가 다른 인덱스를 기본 제공합니다. MySQL(InnoDB)은 주로 B-Tree를 사용하며, 텍스트 검색용 FULLTEXT와 공간 검색용 R-Tree도 제공합니다. 특수 워크로드일수록 두 DBMS의 인덱스 선택지 차이가 커집니다.

부분 인덱스 & 표현식 인덱스

PostgreSQL Partial Index

일부 데이터만 인덱싱하여 디스크 사용량과 유지 비용을 줄입니다.

-- status='ACTIVE'인 행만 인덱스
CREATE INDEX idx_active_users ON users(email)
WHERE status = 'ACTIVE';

PostgreSQL Expression Index

함수나 표현식의 결과를 기반으로 인덱스를 생성합니다.

-- 대소문자 구분 없이 검색
CREATE INDEX idx_lower_name ON users((LOWER(name)));
 
-- JSON 특정 키만 인덱싱
CREATE INDEX idx_json_email ON users((data->>'email'));

MySQL의 한계

• 부분 인덱스: 조건 기반 부분 인덱스 미지원 (접두사 인덱스만 가능)
• 표현식 인덱스: 8.0.13+ 함수 기반 인덱스 지원

-- MySQL 접두사 인덱스
CREATE INDEX idx_name ON users(name(10));
 
-- MySQL 8.0+ 함수 기반 인덱스
CREATE INDEX idx_lower ON users((LOWER(name)));

MySQL은 8.0.13부터 함수 기반 인덱스를 지원합니다. 내부적으로는 숨김 가상 생성 컬럼으로 구현합니다. 인덱스 식에는 서브쿼리·비결정적 함수를 쓸 수 없습니다. PostgreSQL 표현식 인덱스는 임의 함수 결과를 더 폭넓게 허용합니다.

Full Text Search & JSON 인덱싱

PostgreSQL

GIN 인덱스로 고급 텍스트 검색과 JSONB 인덱싱을 지원합니다.

-- Full Text Search
CREATE INDEX idx_fts ON articles USING GIN(to_tsvector('english', content));
 
-- JSONB 인덱싱
CREATE INDEX idx_jsonb ON users USING GIN(metadata jsonb_path_ops);

• 언어별 토큰화·불용어 처리 내장
• 한국어 검색은 기본 설정만으로 충분하지 않아 별도 형태소 분석 확장이나 사용자 정의 설정 필요
• JSONB 전체 구조 역색인화 가능

MySQL

-- FULLTEXT 인덱스
CREATE FULLTEXT INDEX idx_content ON articles(content);
 
-- JSON 경로 인덱스 (8.0+)
CREATE INDEX idx_json ON users((CAST(data->>'$.email' AS CHAR(255))));

• 자연어 검색, Boolean 모드 지원
• JSON 전체 구조 인덱싱은 제한적

동시성 및 인덱스 관리

PostgreSQL

-- 서비스 중단 없이 인덱스 생성
CREATE INDEX CONCURRENTLY idx_name ON users(name);
 
-- 인덱스 재구성
REINDEX INDEX idx_name;

CONCURRENTLY는 쓰기 잠금 없이 인덱스를 만들어 운영 중 테이블에도 적용할 수 있습니다. 대신 테이블을 두 번 스캔하므로 인덱스 생성이 더 느립니다. 도중에 실패하면 사용 불가능한 인덱스가 남습니다. 이 인덱스는 수동으로 제거해야 합니다.

MySQL

-- 온라인 DDL (일부 작업 제한)
ALTER TABLE users ADD INDEX idx_name(name), ALGORITHM=INPLACE, LOCK=NONE;

ALGORITHM=INPLACE는 테이블 전체 복사를 피하고, LOCK=NONE은 인덱스 생성 중에도 읽기·쓰기를 허용합니다. 다만 작업 종류에 따라 LOCK=NONE이 거부되어 더 강한 잠금으로 폴백될 수 있습니다.

성능 최적화 포인트

부분·표현식 인덱스와 텍스트·JSON 검색에서는 PostgreSQL의 선택지가 더 넓습니다. MySQL은 B-Tree 범위·동등 쿼리에서 예측하기 쉬운 실행 계획을 제공합니다. 인덱스 개수보다 실제 워크로드가 요구하는 연산을 기준으로 선택해야 합니다.

정리

PostgreSQL은 부분·표현식·GIN 인덱스로 텍스트와 JSONB 검색을 지원합니다. MySQL은 B-Tree 중심이며 부분 인덱스와 표현식 사용 범위에는 제약이 있습니다. 두 DBMS 모두 실제 쿼리 계획으로 인덱스 사용 여부를 확인해야 합니다.

운영 중 인덱스 생성 방식도 다릅니다. PostgreSQL의 CONCURRENTLY와 MySQL 온라인 DDL은 잠금 조건과 실패 처리가 같지 않습니다. 데이터 모델, 검색 연산, 배포 중 허용 가능한 잠금을 기준으로 인덱스 전략을 선택합니다.