제 검색 속도는 안녕합니다.(fear. PostgreSQL GIN + pg_trgm)

제 검색 속도는 안녕합니다 (feat. PostgreSQL GIN + pg_trgm)

MySQL FULLTEXT와는 무엇이 다른가

이전 글에서 MySQL의 LIKE '%WildCard%' 문제와, FULLTEXT index + ngram parser로 우회하는 방법을 다뤘다.

2026.05.13 - [DB/MySQL] - 당신의 검색 속도는 안녕하십니까?(feat. MySQL Index)

핵심은 이거였다. B+Tree index는 leading character 기준으로 정렬된 자료구조이기 때문에, leading wildcard가 붙은 substring search는 sargable하지 않다. 그래서 인덱스가 있어도 full index scan으로 떨어진다.

MySQL에서는 FULLTEXT index에 ngram parser를 붙여 토큰화된 검색을 가능하게 만들 수 있지만, 그러려면 쿼리 문법 자체를 MATCH ... AGAINST로 바꿔야 했다. 인덱스를 추가하는 것만으로 끝나지 않는다.

CORE그런데 PostgreSQL에서는 같은 문제를 훨씬 깔끔하게 해결할 수 있다. pg_trgm extension과 GIN index를 조합하면, 쿼리 문법을 LIKE로 그대로 두면서도 substring search를 인덱스로 가속할 수 있다. 심지어 ILIKE, regular expression(~, ~*), 유사도 검색(% 연산자)까지 같은 인덱스로 처리된다.

이번 글은 그 방법에 대한 이야기다. 어떻게 설정하는지, 내부적으로 어떻게 동작하는지, 그리고 MySQL FULLTEXT와는 무엇이 본질적으로 다른지 살펴본다.

먼저, GIN은 무엇인가

GIN은 Generalized Inverted Index의 약자다. 이름 그대로 inverted index 자료구조이며, MySQL FULLTEXT가 사용하는 것과 같은 계열이다. 하나의 row가 여러 indexed entry에 분산되어 등장할 수 있는 데이터, 즉 "row 하나 안에 여러 key가 들어 있는 데이터"에 적합하다.

대표적인 사용처는 다음과 같다.

• tsvector (full-text search): document를 lexeme 단위로 쪼개서 indexing
• jsonb: JSON document의 key/value를 indexing
• 배열 컬럼: 배열 element 각각을 indexing
• pg_trgm: 문자열을 trigram 단위로 쪼개서 indexing

KEY IDEA

GIN의 핵심은 "value의 구성요소(component)를 key로 삼는다"는 발상이다. B+Tree가 column value 전체를 하나의 key로 다루는 것과 정반대다.

pg_trgm: 문자열을 trigram으로 쪼갠다

pg_trgm은 PostgreSQL 표준 contrib extension이다. 문자열을 trigram, 즉 연속된 3글자 단위로 쪼개서 다룬다.

CREATE EXTENSION pg_trgm;

extension을 설치하면 show_trgm() 함수로 trigram 분해를 직접 확인할 수 있다.

SELECT show_trgm('postgres');
-- {"  p"," po","gre","ost","pos","res","stg","tgr"}

여기서 두 가지 주목할 점이 있다.

첫째, padding이 있다. 문자열 앞에 공백 2개, 뒤에 공백 1개가 붙는다. postgres는 내부적으로 " postgres "로 다뤄지고, 거기서 3글자씩 sliding window로 추출된다. " p", " po", "pos", "ost", ... 같은 식이다. padding은 단어 경계(word boundary)를 trigram에 인코딩하려는 의도이고, "postgres로 시작한다"는 정보가 " p", " po"라는 trigram으로 표현된다.

둘째, 결과는 set이다. 중복은 제거되고 순서도 정렬된다. 위치 정보는 보존되지 않는다.

이 trigram set을 GIN index로 indexing하는 게 핵심이다.

GIN + pg_trgm 인덱스 만들기

다음과 같이 인덱스를 생성한다.

CREATE INDEX idx_member_name_trgm
ON member
USING GIN (name gin_trgm_ops);

여기서 gin_trgm_ops는 operator class다. "이 컬럼을 GIN으로 인덱싱하되, pg_trgm의 trigram 추출 방식을 사용해라"는 지시다.

인덱스 내부 구조는 개념적으로 다음과 같다.

trigram -> posting list (이 trigram을 포함하는 row의 ctid 목록)

" po"  -> [1, 5, 17, 42, ...]
"pos"  -> [1, 5, 17, ...]
"ost"  -> [1, 5, 17, 99, ...]
"stg"  -> [1, 5, ...]
...

여기까지만 보면 MySQL FULLTEXT의 inverted index와 구조적으로 비슷하다. 차이는 쿼리 시점에 발생한다.

LIKE 쿼리를 trigram set 비교로 변환한다

이게 PostgreSQL의 좋은 점이다.

WHERE name LIKE '%윤혁%'라는 쿼리가 들어오면, planner는 다음과 같이 동작한다.

1검색 패턴 '%윤혁%'에서 와일드카드를 제거하고 고정된 substring을 추출한다. 여기서는 윤혁이다.

2이 substring에서 trigram을 추출한다. 윤혁은 padding을 적용해 " 윤", " 윤혁"... 같은 trigram이 만들어지지만, 좀 더 직관적인 영문 예시로 '%search%'라면 "sea", "ear", "arc", "rch"가 추출된다.

3이 trigram들이 모두 포함된 row의 후보를 GIN index에서 lookup한다. 각 trigram의 posting list를 가져와 intersection을 구하는 방식이다.

"sea" -> [1, 7, 12, 33, 88]
"ear" -> [1, 7, 33, 56]
"arc" -> [1, 7, 33]
"rch" -> [1, 33]

intersection -> [1, 33]

4후보 row의 실제 값에 대해 LIKE '%search%' pattern을 다시 한 번 검증한다 (rechecking).

NOTE왜 recheck가 필요할까? GIN index는 trigram이 모두 포함되는지만 보지, 그것들이 연속해서 나타나는지까지는 보지 않는다. 예를 들어 "sea", "arc"가 모두 나오는 row가 있더라도 그게 search로 붙어 있다는 보장은 없다. 그래서 후보 row를 추린 다음, heap에서 실제 값을 읽어 LIKE pattern으로 다시 검증한다.

이 동작이 execution plan에서 Bitmap Index Scan + Bitmap Heap Scan + Recheck Cond로 나타난다.

Bitmap Heap Scan on member  (cost=...)
  Recheck Cond: (name ~~ '%search%'::text)
  -> Bitmap Index Scan on idx_member_name_trgm
       Index Cond: (name ~~ '%search%'::text)

요점은 이렇다. GIN + pg_trgm은 정확한 답을 인덱스만으로 내지 않는다. 후보 집합을 좁히는 filter 역할을 하고, 최종 매칭은 heap에서 다시 확인한다. 하지만 500만 row 중 후보 100개로 좁힐 수 있다면, 그 100개에 대한 LIKE 매칭은 거의 무시 가능한 비용이다.

MySQL FULLTEXT와는 어떻게 다른가

여기서 본질적인 차이가 드러난다. 두 접근 모두 inverted index를 사용하지만, 쿼리 인터페이스와 token 추출 시점이 다르다.

MySQL FULLTEXT의 경우

MySQL FULLTEXT는 인덱스를 전용 쿼리 인터페이스(MATCH ... AGAINST)를 통해서만 사용할 수 있다.

-- 인덱스를 탄다
WHERE MATCH(name) AGAINST('윤혁' IN BOOLEAN MODE)

-- 인덱스를 안 탄다
WHERE name LIKE '%윤혁%'

토큰화는 indexing 시점에 parser가 한 번 수행한다. ngram parser면 모든 row를 미리 n글자 단위로 쪼개 인덱스에 저장한다. 쿼리 시점에는 검색어를 같은 parser로 토큰화해서 inverted index를 lookup한다.

장점은 한글처럼 공백이 없는 언어에 ngram이 잘 맞으면 효과적이라는 점이다. 단점은 두 가지다. 첫째, 기존 쿼리(LIKE, regex 등)를 그대로 가속할 수 없다. 쿼리를 rewriting해야 한다. 둘째, 토큰화 방식이 indexing 시점에 고정된다. ngram_token_size = 2로 인덱스를 만들었다면 1글자 검색은 불가능하다.

PostgreSQL pg_trgm + GIN의 경우

PostgreSQL은 일반 LIKE, ILIKE, regular expression 쿼리에서도 같은 인덱스를 자동으로 사용한다.

-- 모두 같은 GIN + pg_trgm 인덱스를 사용
WHERE name LIKE '%search%'
WHERE name ILIKE '%SeArCh%'
WHERE name ~ '^[A-Z]earch'
WHERE name % 'serch'        -- 유사도 검색 (오타 허용)

이게 가능한 이유는, PostgreSQL planner가 쿼리 패턴을 분석해서 trigram set으로 자동 변환하기 때문이다. 검색어가 '%search%'이든 '%sea%'이든, 패턴에서 추출 가능한 trigram을 뽑아 인덱스를 사용한다.

구분	MySQL FULLTEXT	PostgreSQL GIN + pg_trgm
쿼리 문법	MATCH ... AGAINST 전용	LIKE, ILIKE, regex 그대로 사용
토큰화 시점	indexing 시점에 고정	indexing + 쿼리 시점 모두
유사도 검색	native하게 미지원	% 연산자로 즉시 지원
도입 비용	쿼리 rewriting 필요	인덱스 추가만으로 끝

한 줄 요약

MySQL FULLTEXT는 "검색용 전용 인덱스"이고, GIN + pg_trgm은 "범용 substring/유사도 가속 인덱스"에 가깝다.

왜 PostgreSQL은 이게 가능했을까: 확장성

구조적인 차이가 하나 더 있다. PostgreSQL은 처음부터 확장 가능한(extensible) 데이터베이스로 설계됐다. 사용자 정의 타입, 사용자 정의 연산자, 사용자 정의 인덱스 access method를 모두 지원한다.

GIN 자체는 PostgreSQL 코어가 제공하는 일반화된 inverted index 프레임워크다. 개념적으로 "value를 여러 component로 쪼개 indexing한다"는 추상화만 제공할 뿐, 어떻게 쪼갤지는 operator class가 결정한다.

-- 같은 GIN을 다른 operator class로 사용

-- trigram 기반 (substring search)
USING GIN (name gin_trgm_ops)

-- jsonb의 key/value 기반
USING GIN (data jsonb_ops)

-- 배열 element 기반
USING GIN (tags)

-- tsvector의 lexeme 기반 (full-text search)
USING GIN (to_tsvector('english', body))

즉 pg_trgm은 GIN이라는 인덱스 프레임워크 위에 trigram 추출 방식을 plugin처럼 끼워 넣은 extension이다. MySQL은 이런 식의 확장 모델이 없기 때문에, full-text search를 위해서는 별도의 전용 인덱스 타입(FULLTEXT)을 만들고 거기에 parser를 plugin으로 연결하는 구조가 됐다. 그 결과 일반 쿼리 문법으로는 사용할 수 없는 별개의 시스템이 되어 버렸다.

이 설계 차이가 "쿼리 문법은 그대로 두고 인덱스만 추가하면 빨라진다"는 PostgreSQL의 사용성 차이로 이어진다.

검색 속도를 빠르게 하는 단계

이전 글의 회원 검색 시나리오를 PostgreSQL로 옮겨보자. 단계는 다음과 같다.

-- 1. extension 활성화 (한 번만)
CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 2. GIN 인덱스 생성
CREATE INDEX idx_member_name_trgm
ON member
USING GIN (name gin_trgm_ops);

-- 3. 기존 쿼리 그대로 사용
SELECT id, name, phone
FROM member
WHERE name LIKE '%윤혁%'
LIMIT 20;

DONE이게 전부다. 쿼리는 한 줄도 바꾸지 않았고, 인덱스만 추가했을 뿐이다.

EXPLAIN ANALYZE 결과는 대략 이런 식으로 나온다.

Limit  (cost=... rows=20)
  -> Bitmap Heap Scan on member  (cost=...)
       Recheck Cond: (name ~~ '%윤혁%'::text)
       Heap Blocks: exact=...
       -> Bitmap Index Scan on idx_member_name_trgm
            Index Cond: (name ~~ '%윤혁%'::text)

해석하면 다음과 같다.

• Bitmap Index Scan: GIN 인덱스에서 윤혁의 trigram들의 posting list를 가져와 intersection으로 후보 ctid bitmap을 만든다.
• Bitmap Heap Scan: 그 bitmap을 따라 heap에서 실제 row를 읽는다.
• Recheck Cond: 읽어온 row에 대해 LIKE '%윤혁%' 조건을 재확인한다 (trigram intersection은 후보일 뿐이므로).

핵심은 500만 row를 전부 읽는 sequential scan이 아니라, 후보 수십~수백 row만 heap에서 확인한다는 점이다.

유사도 검색까지 같은 인덱스로

같은 GIN + pg_trgm 인덱스로 유사도(fuzzy) 검색도 가능하다. 사용자가 "윤혁"을 "운혁"으로 잘못 입력해도 매칭이 된다.

-- '%' 연산자: 두 문자열의 trigram 유사도가 threshold 이상이면 true
SELECT name, similarity(name, '운혁') AS sml
FROM member
WHERE name % '운혁'
ORDER BY sml DESC
LIMIT 20;

pg_trgm.similarity_threshold (기본값 0.3) 위로 유사한 row를 가져온다. similarity는 두 문자열의 trigram set 교집합 비율로 계산된다.

TIPMySQL FULLTEXT만으로는 native하게 지원하지 않는 기능이다. 검색창의 오타 허용, "혹시 이걸 찾으셨나요?" 기능 등을 별도 검색 엔진 없이 PostgreSQL만으로 구현할 수 있다.

물론 한계도 있다

GIN + pg_trgm도 만능 열쇠는 아니다. 도입 전에 알아두면 좋은 한계가 몇 가지 있다.

인덱스가 크고, 쓰기 비용이 비싸다

row 하나가 trigram 여러 개로 분해되어 인덱스에 등록되기 때문에, 인덱스 크기 자체가 B-tree보다 훨씬 크다. INSERT/UPDATE/DELETE 비용도 더 든다. GIN은 fastupdate로 쓰기 비용을 어느 정도 분산시키지만, 그래도 B-tree보다는 무겁다는 사실은 변하지 않는다. 자주 갱신되는 컬럼이라면 GiST 기반(gist_trgm_ops)도 고려할 수 있다.

2글자 미만 검색어는 비효율적이다

패턴에서 trigram을 하나도 뽑을 수 없으면 인덱스가 무력화된다. 예를 들어 LIKE '%a%'처럼 검색어가 1글자라면, 추출할 trigram이 없거나 너무 적어서 GIN이 도움이 안 된다.

CAUTIONPostgreSQL 공식 문서에도 명시되어 있다: "패턴에서 trigram을 추출할 수 없으면 full index scan으로 떨어진다." 검색 UI에서 최소 글자 수 제한을 두는 게 안전하다.

한글에 대한 trigram의 동작

pg_trgm은 trigram을 추출할 때 "문자(character)" 단위로 동작하며, UTF-8 기준으로 한글 한 글자도 하나의 character로 다룬다. 그래서 "김윤혁"도 padding을 거쳐 " 김", " 김윤", "김윤혁", "윤혁 " 같은 trigram으로 분해된다. 이 동작 자체는 한글에서도 잘 맞는다. 다만 "non-word character는 무시한다"는 규칙이 있어 단어 경계 처리에서 영문과 미묘하게 다른 결과가 나올 수 있다. 실제 데이터로 show_trgm()을 돌려보고 검증하는 게 좋다.

한국어의 형태소 분석은 별개 문제다

trigram은 어디까지나 문자열의 substring 매칭이다. "달리다"와 "달려서"를 같은 의미로 인식하지는 못한다. 형태소 분석, 어간 추출, 동의어 처리 같은 고급 검색 요구사항이 있다면 PostgreSQL Full-Text Search(tsvector, to_tsvector)와 한국어 dictionary를 조합하거나, Elasticsearch + Nori 같은 전용 검색 엔진을 고려해야 한다.

같은 inverted index, 다른 통합 방식

MySQL FULLTEXT와 PostgreSQL GIN + pg_trgm은 둘 다 inverted index를 사용한다. 그러나 시스템에 통합되는 방식이 다르다.

MySQL은 FULLTEXT를 별도의 인덱스 타입으로 만들고, 전용 쿼리 문법(MATCH ... AGAINST)을 통해서만 사용하도록 했다. 토큰화 방식은 parser plugin으로 추상화했지만, 인덱스 자체가 일반 SQL 연산자와 분리되어 있다.

PostgreSQL은 GIN이라는 일반화된 inverted index framework을 코어에 두고, trigram이라는 분해 방식을 extension으로 plugin했다. 그 결과 planner는 일반 LIKE, ILIKE, regex 쿼리를 trigram set 비교 + recheck로 변환해서 같은 인덱스를 활용할 수 있다.

이 차이가 사용자 입장에서 "쿼리 그대로 두고 인덱스만 추가하면 빨라진다" vs "쿼리를 다시 작성해야 한다"라는 경험의 차이로 이어진다.

결론

PostgreSQL을 쓰고 있고 LIKE '%keyword%'로 인한 느린 검색을 겪고 있다면, 가장 먼저 시도할 옵션은 명확하다. pg_trgm extension을 활성화하고, GIN 인덱스를 추가하라. 쿼리는 한 줄도 바꾸지 않아도 된다.