개요
지난 2025 ProfitLab 해커톤에 참여한 뒤로 팀원들과 계속 소통하면서 앱 개발을 하고 있다.
우리의 목표는 실제로 앱을 출시하고 수익을 내는거기 때문이다.
그러던 중 사용자가 원하는 구독 서비스를 찾고자 할 때, 어떻게 하면 쉽고 빠르게 찾을 수 있는지에 대하여 조사하게 되었다.
그 결과 PostgreSQL을 사용하면 pg_trgm extension과 GIN 인덱스를 사용하여 목표를 달성할 수 있다는 사실을 알게되었다.
물론 Elasticsearch 같은 것을 사용하여 형태소 분석, 오타 교정 등을 할 수 있지만 우리 서비스에는 오버엔지니어링이라고 판단했다.
사용자가 타이핑을 하면 어떤 순서로 검색될까?
1. Prefix Match
`LIKE '검색어%'` → 인덱스 탐
2. In-fix Match
`ILIKE '%검색어%'` → pg_trgm 인덱스 덕분에 %가 앞에 있어도 빠름
3. Fuzzy Match
검색 결과가 아예 없을 때, “혹시 이걸 찾으시나요?”를 위한 오타 교정용
준비
-- pg_trgm 생성
CREATE EXTENSION pg_trgm;
-- GIN 인덱스 생성
CREATE INDEX idx_services_search_trgm ON subscription_services
USING gin (name gin_trgm_ops, english_name gin_trgm_ops);
GIN 인덱스란?
GIN(Generalized Inverted Index)은 ‘일반화된 역색인’이라고 부르며, 주로 배열(Array), JSONB, 그리고 Full-Text Search(텍스트 검색)에 특화된 인덱스이다.
1. GIN의 핵심 개념: 역색인(Inverted Index)
`netflix`라는 단어가 들어간 페이지를 다 가져와라 → 키워드 목록에서 netflix를 찾고, 그것이 포함된 모든 행의 ID(포인터)를 한꺼번에 반환한다.
2. GIN 인덱스의 내부 동작 (Trigram 기준)
pg_trgm과 GIN이 만나면 다음과 같이 동작한다.
예를 들어, name 컬럼에 `netflix`, `net`이라는 두 데이터가 있다고 가정해 보자.
- 분해: 각 단어를 Trigram 조각으로 쪼갠다.
- `netflix` → { n, new, net, etf, tfl, … }
- `net` → { n, ne, net, et, t }
- 인덱스 생성: 각 조각이 어느 행에 있는지 기록한다.
- `n`: [Row 1, Row 2]
- `etf`: [Row 1]
- `et`: [Row 2]
- 검색: 사용자가 `net`을 검색하면, GIN 인덱스는 { n, ne, net } 조각을 가진 행들의 리스트를 가져와서 교집합을 구한다.
3. B-Tree vs GIN
| 특징 | B-Tree | GIN |
| 주 용도 | 일치(=), 범위(>, <), 전방 일치(LIKE 'A%') | 포함(@>), 요소 검색, 중간 일치(LIKE '%A%'), 유사도 |
| 검색 속도 | 매우 빠름 | B-Tree보다는 느리지만 Full Scan보다 압도적으로 빠름 |
| 삽입/수정 속도 | 빠름 | 느림 (데이터 하나 추가 시 수십 개의 조각을 인덱스에 업데이트해야 함) |
| 인덱스 크기 | 작음 | 큼 (조각을 많이 생성할수록 커짐) |
4. 주의사항
- 쓰기 성능 저하: 데이터가 `INSERT`될 때마다 수많은 Trigram 조각을 인덱스에 넣어야 하므로 쓰기 작업이 무겁다. 하지만 조회는 많고 수정은 적은 테이블에는 최적의 선택이다.
- `gin_pending_list_limit`: GIN은 쓰기 성능을 보완하기 위해 임시 리스트에 모았다가 한꺼번에 인덱스에 반영한다. 대량의 데이터를 넣을 때는 이 설정을 튜닝하기도 한다.
사용할 수 있는 쿼리
SELECT *,
CASE
-- 1순위: 정확히 일치하거나 접두사가 일치 (가장 높은 점수)
WHEN name LIKE '냇플%' THEN 3
-- 2순위: 중간에 포함됨 (pg_trgm 인덱스 활용)
WHEN name ILIKE '%냇플%' THEN 2
-- 3순위: 오타가 있거나 유사함 (유사도 기반)
WHEN name % '냇플' THEN 1
ELSE 0
END AS search_priority
FROM SUBSCRIPTIONS
WHERE
name ILIKE '%넷플%' -- 인덱스 활용을 위한 조건
OR name % '넷플' -- 오타 대응용 조건
ORDER BY search_priority DESC, sort_order ASC
LIMIT 10;