기본 패턴
sql
-- tsvector 컬럼 생성 + GIN 인덱스
ALTER TABLE articles ADD COLUMN search_vec tsvector
GENERATED ALWAYS AS (
to_tsvector('english', coalesce(title, '') || ' ' || coalesce(body, ''))
) STORED;
CREATE INDEX idx_articles_fts ON articles USING GIN (search_vec);
-- 검색 + 관련성 정렬
SELECT id, title,
ts_rank(search_vec, query) AS rank
FROM articles,
to_tsquery('english', 'postgresql & index') query
WHERE search_vec @@ query
ORDER BY rank DESC
LIMIT 20;설명
tsvector는 정규화된 어휘소 목록이고 tsquery는 검색 조건 트리다
to_tsvector는 텍스트를 받아 어휘소(lexeme) 목록으로 변환한다. 이 과정에서 불용어(stopword)를 제거하고 어간 추출(stemming)을 적용한다. 예를 들어 "running", "runs"는 모두 "run"으로 정규화된다. 각 어휘소에는 등장 위치 정보가 함께 저장되어 근접 검색(<->)과 가중치 계산에 활용된다.
to_tsquery는 검색 표현식을 파싱해 조건 트리로 만든다. websearch_to_tsquery는 Google 스타일 검색어("phrase search", -exclude)를 자동으로 파싱하므로 사용자 입력을 직접 받을 때 더 안전하다.
sql
-- tsvector 변환 결과 확인
SELECT to_tsvector('english', 'The quick brown foxes are jumping over lazy dogs');
-- 'brown':3 'dog':9 'fox':4 'jump':6 'lazi':8 'quick':2
-- "The", "are", "over" 등 불용어 제거, 복수형·진행형 어간 추출
-- 검색 연산자
SELECT to_tsquery('english', 'postgresql & (index | table)');
-- 'postgresql' & ( 'index' | 'tabl' )
-- websearch_to_tsquery: 사용자 입력 직접 사용 가능
SELECT websearch_to_tsquery('english', 'postgresql index -table');
-- 'postgresql' & 'index' & !'tabl'
-- 구문 검색 (단어 순서 보장)
SELECT to_tsquery('english', 'full <-> text <-> search');
-- 매칭 여부 확인
SELECT 'quick brown fox'::tsvector @@ 'fox'::tsquery; -- true
-- 텍스트를 직접 매칭 (인덱스 미사용)
SELECT to_tsvector('english', 'PostgreSQL full-text search')
@@ to_tsquery('english', 'search & postgresql'); -- true복수 컬럼 결합 시 가중치(A~D)를 부여해 제목이 본문보다 검색 순위에 더 큰 영향을 주게 할 수 있다.
sql
-- 제목(가중치 A)과 본문(가중치 B) 결합
SELECT
setweight(to_tsvector('english', title), 'A') ||
setweight(to_tsvector('english', body), 'B') AS search_vec
FROM articles;GIN 인덱스가 전문 검색을 빠르게 만드는 이유
GIN(Generalized Inverted Index)은 역 인덱스(inverted index) 구조다. 어휘소를 키로, 해당 어휘소가 등장하는 행의 목록을 값으로 저장한다. @@ tsquery 연산 시 조건의 어휘소로 직접 행 목록을 조회하므로, 테이블 전체를 스캔하지 않아도 된다.
sql
-- 표현식 인덱스: 컬럼이 아닌 함수 결과에 인덱스
CREATE INDEX idx_articles_fts
ON articles
USING GIN (to_tsvector('english', title || ' ' || body));
-- GENERATED STORED 컬럼 방식 (INSERT/UPDATE 시 자동 갱신)
ALTER TABLE articles
ADD COLUMN search_vec tsvector
GENERATED ALWAYS AS (
setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
setweight(to_tsvector('english', coalesce(body, '')), 'B')
) STORED;
CREATE INDEX idx_articles_search ON articles USING GIN (search_vec);
-- 인덱스 사용 여부 확인
EXPLAIN (ANALYZE, BUFFERS)
SELECT id FROM articles
WHERE search_vec @@ to_tsquery('english', 'postgresql');
-- Bitmap Index Scan on idx_articles_searchGIN은 쓰기 비용이 높지만 gin_pending_list_limit 파라미터로 pending list를 활용해 쓰기 부하를 완충할 수 있다. 읽기가 압도적으로 많은 검색 워크로드에 최적화되어 있다.
ts_rank로 관련성 점수를 매기는 원리
ts_rank는 term frequency(TF) 와 어휘소의 위치 정보를 결합해 점수를 계산한다. ts_rank_cd는 cover density 를 추가로 반영해 검색어 단어들이 문서 내에서 가까이 붙어 있을수록 높은 점수를 부여한다.
sql
-- ts_rank: 기본 빈도 기반
SELECT
id, title,
ts_rank(search_vec, query) AS rank_basic,
ts_rank_cd(search_vec, query) AS rank_cover
FROM articles,
to_tsquery('english', 'full & text & search') query
WHERE search_vec @@ query
ORDER BY rank_cover DESC
LIMIT 10;
-- normalization 파라미터로 점수 정규화
-- 0: 정규화 없음 (기본값)
-- 1: 어휘소 수로 나눔
-- 2: 문서 길이 로그로 나눔
-- 4: 고유 어휘소 수로 나눔 (중복 어휘소 페널티)
SELECT
id,
ts_rank(search_vec, query, 1) AS rank_normalized
FROM articles,
to_tsquery('english', 'index') query
WHERE search_vec @@ query
ORDER BY rank_normalized DESC;
-- 하이라이트 (일치 부분 강조)
SELECT
ts_headline('english', body,
to_tsquery('english', 'postgresql & index'),
'MaxWords=50, MinWords=20, StartSel=<b>, StopSel=</b>'
) AS excerpt
FROM articles
WHERE search_vec @@ to_tsquery('english', 'postgresql & index');LIKE vs 전문 검색 성능 차이
LIKE '%keyword%'는 인덱스를 활용할 수 없어 테이블 전체를 순차 스캔한다. 전문 검색은 GIN 인덱스 룩업으로 O(log n) 수준의 복잡도를 달성한다.
sql
-- LIKE: 순차 스캔, 100만 행에서 수 초 소요
EXPLAIN ANALYZE
SELECT id FROM articles WHERE body LIKE '%postgresql index%';
-- Seq Scan on articles (cost=0.00..28456.00 rows=100 ...)
-- Execution Time: 3200 ms
-- 전문 검색: GIN 인덱스 룩업
EXPLAIN ANALYZE
SELECT id FROM articles
WHERE search_vec @@ to_tsquery('english', 'postgresql & index');
-- Bitmap Index Scan on idx_articles_search (cost=0.00..12.34 rows=50 ...)
-- Execution Time: 8 ms
-- pg_trgm + GIN으로 LIKE 패턴 검색을 빠르게 (부분 문자열 매칭 필요 시)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_articles_trgm ON articles USING GIN (title gin_trgm_ops);
-- 트라이그램 인덱스를 사용하는 LIKE (단, FTS보다 인덱스 크기 큼)
SELECT id, title FROM articles WHERE title LIKE '%postgre%';빠른 정리
| 상황 | 적합한 선택 |
|---|---|
| 단어 단위 전문 검색이 필요하다 | tsvector + GIN 인덱스 |
| 사용자 자유 입력 검색어를 받는다 | websearch_to_tsquery |
| 제목을 본문보다 우선 순위에 올리고 싶다 | setweight + 가중치 (A~D) |
| 검색 결과에 관련성 순위가 필요하다 | ts_rank / ts_rank_cd |
| 검색어 일치 부분을 강조하고 싶다 | ts_headline |
부분 문자열 매칭 (LIKE %foo%)이 필요하다 | pg_trgm + GIN |
| 한국어 형태소 분석이 필요하다 | pg_bigm 또는 외부 파서 |
주의할 점
한국어는 기본 pg_catalog.simple 또는 english 설정 사용 시 형태소 분석이 되지 않아 공백 단위 분리만 된다. "데이터베이스"를 검색할 때 "데이터"로 조회되지 않는다.
sql
-- ❌ 한국어에 english 설정 사용 — 어간 추출 없음
SELECT to_tsvector('english', '데이터베이스 인덱스 최적화');
-- '데이터베이스':1 '인덱스':2 '최적화':3
-- 단어 단위 분리만 됨, "데이터"로 검색하면 히트 안 됨
-- ✅ 한국어 대응 옵션 1: pg_bigm (바이그램 기반, 부분 매칭 지원)
-- CREATE EXTENSION pg_bigm;
-- CREATE INDEX ON articles USING GIN (body gin_bigm_ops);
-- ✅ 한국어 대응 옵션 2: simple 설정 + 완전 일치 위주 운용
SELECT to_tsvector('pg_catalog.simple', '데이터베이스 인덱스 최적화');
-- ✅ 애플리케이션 레이어에서 형태소 분석 후 tsvector 직접 구성
UPDATE articles
SET search_vec = to_tsvector('pg_catalog.simple', analyzed_tokens)
WHERE id = 1;