PostgreSQL vs MySQL 인덱싱 비교
인덱스 타입, 부분 인덱스, Full Text Search, JSON 인덱싱 차이
Contents
PostgreSQL은 부분·표현식·GIN 인덱스로 복잡한 쿼리에 강하고, MySQL은 B-Tree 기반 단순 쿼리 처리에 최적화돼 있다.
데이터베이스 인덱스는 테이블 전체를 스캔하지 않고 원하는 행을 빠르게 찾기 위한 자료구조이다. PostgreSQL과 MySQL은 지원하는 인덱스 타입과 내부 구현이 다르며, 이 차이가 쿼리 성능과 활용 전략에 직접 영향을 준다.
지원 인덱스 타입 비교
PostgreSQL
| 인덱스 | 용도 |
|---|---|
| B-Tree | 기본 인덱스, 비교 연산 최적화 |
| Hash | 동등 연산(=) 빠른 비교 |
| GIN (Generalized Inverted Index) | Full Text Search, JSONB, 배열 검색 |
| GiST (Generalized Search Tree) | 지리 정보(PostGIS), 범위 타입 |
| BRIN (Block Range Index) | 대용량 시계열/순차 데이터 |
| SP-GiST | 트라이 기반 공간 분할 |
MySQL (InnoDB)
| 인덱스 | 용도 |
|---|---|
| B-Tree | 기본 인덱스, 대부분의 경우 |
| Hash | Adaptive Hash Index (내부 최적화용) |
| FULLTEXT | 텍스트 검색 |
| R-Tree | GIS 기능 (8.0+) |
PostgreSQL은 B-Tree 외에 GIN, GiST, BRIN처럼 용도가 분화된 인덱스를 기본 제공한다. MySQL(InnoDB)은 사실상 B-Tree 하나에 텍스트 검색용 FULLTEXT와 공간 검색용 R-Tree를 더한 구성이라, 특수 워크로드일수록 선택지 차이가 커진다.
부분 인덱스 & 표현식 인덱스
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이 거부되어 더 강한 잠금으로 폴백될 수 있다.
성능 최적화 포인트
| 항목 | PostgreSQL | MySQL |
|---|---|---|
| B-Tree | 기본 지원 | 기본 지원 |
| 부분 인덱스 | 조건 기반 가능 | 미지원 |
| 표현식 인덱스 | 자유로움 | 8.0+ 제한적 |
| 텍스트 검색 | GIN + 고급 기능 | FULLTEXT 기본 |
| JSON | GIN 역색인 | 경로 기반 제한적 |
부분·표현식 인덱스와 텍스트·JSON 검색에서 PostgreSQL이 더 넓은 선택지를 갖고, MySQL은 B-Tree 범위/동등 쿼리에서 단순하고 예측 가능한 성능을 낸다. 따라서 인덱스 다양성 자체보다 워크로드가 어느 쪽에 치우치는지가 선택을 가른다.
정리
PostgreSQL은 부분·표현식·GIN 인덱스를 기본 제공해 텍스트 검색이나 JSONB처럼 복잡한 데이터 모델에서 유리하다. MySQL은 B-Tree 중심이라 범위·동등 조건 쿼리를 단순하고 빠르게 처리하지만, 조건 기반 부분 인덱스나 폭넓은 표현식 인덱스에서는 제약이 있다. 무중단 인덱싱은 PostgreSQL의 CONCURRENTLY와 MySQL의 온라인 DDL이 각각 다른 방식으로 지원하므로, 운영 중 적용 가능 여부도 미리 확인해야 한다. 데이터 모델의 복잡도, 텍스트·JSON 검색 비중, 무중단 인덱싱 필요 여부를 기준으로 DBMS와 인덱스 전략을 선택한다.