PostgreSQL Full-Text Search 설정

Introduction

PostgreSQL의 전문 검색(Full-Text Search, FTS)은 텍스트 데이터를 효율적으로 검색할 수 있는 내장 기능입니다. 별도의 외부 검색 엔진 없이도 강력한 텍스트 검색 기능을 구현할 수 있으며,GIN/GiST 인덱스를 활용하여 높은 검색 성능을 제공합니다. 이 글에서는 FTS 설정부터 최적화까지 전체 프로세스를 다루겠습니다.

Environment

-- PostgreSQL 버전 확인
SELECT version();

-- FTS 관련 설정 확인
SHOW default_text_search_config;
-- "english" (기본값)

# psql 연결
psql -U postgres -d mydb

-- 테스트 테이블 생성
CREATE TABLE articles (
    id SERIAL PRIMARY KEY,
    title VARCHAR(200),
    content TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- 샘플 데이터 삽입
INSERT INTO articles (title, content) VALUES
('PostgreSQL 성능 최적화', '이 글에서는 PostgreSQL 데이터베이스의 성능을 최적화하는 방법을 다룹니다.'),
('Redis 캐싱 전략', 'Redis를 활용한 효율적인 캐싱 전략과 구현 방법을 설명합니다.'),
('MongoDB 파티셔닝', 'MongoDB 대용량 데이터 처리를 위한 파티셔닝 전략을 소개합니다.');

Problem

기존 텍스트 검색의 한계:

-- LIKE 기반 검색 - 매우 느림
SELECT * FROM articles 
WHERE content LIKE '%PostgreSQL%';
-- Seq Scan on articles  (cost=0.00..1234.56 rows=3)
--   Filter: (content ~~ '%PostgreSQL%')
-- 실행 시간: 156ms

-- 정규식 기반 검색 - 더 느림
SELECT * FROM articles 
WHERE content ~* 'postgresql|database';
-- 정규식 검색은 인덱스를 사용할 수 없음
-- 실행 시간: 234ms

-- 단순 텍스트 매칭의 한계
-- 1. 어근 처리 불가 (running, runs, ran 등)
-- 2. 불용어 처리 없음
-- 3. 관련성 순위 매기기 어려움

# 텍스트 검색 성능 측정
psql -U postgres -d mydb -c "
EXPLAIN ANALYZE
SELECT * FROM articles 
WHERE content LIKE '%PostgreSQL%';
"

Analysis

PostgreSQL FTS의 내부 동작을 분석했습니다:

-- tsvector 변환 확인
SELECT to_tsvector('english', 'PostgreSQL is a powerful database system');
-- 'database':5 'power':3 'postgresql':1 'system':6

-- tsquery 생성 확인
SELECT to_tsquery('english', 'PostgreSQL & database');
-- 'postgresql' & 'database'

-- tsvector와 tsquery 비교
SELECT 
    to_tsvector('english', 'The running man runs quickly') as vector,
    to_tsquery('english', 'running') as query;
-- 'man':2 'quickli':4 'run':1,3
-- 'running'

-- 불용어 처리 확인
SELECT to_tsvector('english', 'The quick brown fox jumps over the lazy dog');
-- 'brown':3 'dog':9 'fox':4 'jump':6 'lazi':8 'quick':2
-- 'the'는 불용어로 제외됨

-- FTS 인덱스 상태 확인
SELECT 
    indexname,
    indexdef
FROM pg_indexes
WHERE tablename = 'articles';

-- 인덱스가 없으면 전체 테이블 스캔 발생

Solution

1단계: tsvector 컬럼 및 인덱스 생성

-- 검색용 tsvector 컬럼 추가
ALTER TABLE articles ADD COLUMN search_vector tsvector;

-- 기존 데이터로 search_vector 업데이트
UPDATE articles SET search_vector = 
    setweight(to_tsvector('english', coalesce(title, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(content, '')), 'B');

-- GIN 인덱스 생성
CREATE INDEX idx_articles_search ON articles USING GIN(search_vector);

-- 자동 업데이트 트리거 생성
CREATE OR REPLACE FUNCTION update_search_vector()
RETURNS TRIGGER AS $$
BEGIN
    NEW.search_vector := 
        setweight(to_tsvector('english', coalesce(NEW.title, '')), 'A') ||
        setweight(to_tsvector('english', coalesce(NEW.content, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_update_search_vector
    BEFORE INSERT OR UPDATE ON articles
    FOR EACH ROW
    EXECUTE FUNCTION update_search_vector();

2단계: 검색 쿼리 작성

-- 기본 FTS 검색
SELECT id, title, 
       ts_rank(search_vector, query) as rank
FROM articles, 
     to_tsquery('english', 'PostgreSQL & performance') as query
WHERE search_vector @@ query
ORDER BY rank DESC;

-- 검색 결과 하이라이팅
SELECT id, title,
       ts_headline('english', content, 
                   to_tsquery('english', 'PostgreSQL & performance'),
                   'StartSel=, StopSel=, MaxWords=50, MinWords=20') as highlighted
FROM articles
WHERE search_vector @@ to_tsquery('english', 'PostgreSQL & performance');

-- 복합 검색 쿼리
SELECT id, title, 
       ts_rank_cd(search_vector, query, 32) as rank
FROM articles,
     to_tsquery('english', 'PostgreSQL | Redis') as query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 10;

3단계: 한국어 FTS 설정

# 한국어 사전 설정 (需安装)
# sudo apt install postgresql-14-text-search-intdict
# 또는 
# sudo apt install postgresql-14-textsearch-ko

# mecab을 사용한 형태소 분석
# CREATE EXTENSION IF NOT EXISTS mecab;

-- 한국어 FTS 설정
CREATE TEXT SEARCH CONFIGURATION korean (COPY = simple);

-- 형태소 분석기 설정
ALTER TEXT SEARCH CONFIGURATION korean
    ALTER MAPPING FOR hword, hword_part, word
    WITH simple;

-- 한국어 검색 테스트
SELECT to_tsvector('korean', 'PostgreSQL는 강력한 데이터베이스 시스템입니다');

-- 한국어 불용어 사전 생성
CREATE TEXT SEARCH DICTIONARY korean_stop (
    TEMPLATE = simple,
    STOPWORDS = korean
);

-- 불용어 사전 적용
ALTER TEXT SEARCH CONFIGURATION korean
    ALTER MAPPING FOR hword, hword_part, word
    WITH korean_stop;

Lessons Learned

GIN vs GiST: GIN 인덱스가 일반적으로 GiST보다 빠르지만, 업데이트가 더 느립니다
가중치 설정: setweight로 제목, 본문 등에 가중치를 부여하여 검색 관련성을 향상시킬 수 있습니다
불용어 처리: 불용어 사전을 활용하여 검색 정확도를 높여야 합니다
한국어 지원: 한국어 형태소 분석을 위해서는 추가 확장이 필요합니다
tsvector 미리 계산: INSERT/UPDATE 시 tsvector를 미리 계산하여 검색 성능을 최적화해야 합니다

이 블로그는 외부 스폰서십, 제휴 마케팅 또는 광고 수익을 받지 않습니다.

PostgreSQL Full-Text Search 설정: 고성능 텍스트 검색 구현