Reference

Opening reference

Preparing the article, table of contents, and metadata. 본문, 목차, 메타데이터를 준비하고 있습니다.

Loading category cards

Preparing references and filters for this topic. 이 주제의 레퍼런스와 필터를 준비하고 있습니다.

PostgreSQL인덱스와 성능

전문 검색 (Full-Text Search)

tsvector/tsquery의 동작 원리, GIN 인덱스로 전문 검색 성능을 확보하는 방법, LIKE 검색과의 성능 차이를 정리합니다.

마지막 수정 2026년 4월 4일

핵심 정리

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;

전문 검색과 LIKE 검색은 찾는 단위가 다르다

전문 검색은 단어와 어휘소 단위 검색에 맞고, LIKE '%foo%'는 부분 문자열 검색에 맞습니다. "의미 있는 단어 검색"이 목적이면 전문 검색이 맞고, 중간 문자열 일부를 찾는 UI면 pg_trgm 쪽이 더 맞을 수 있습니다.

sql

-- 단어 검색
WHERE search_vec @@ to_tsquery('english', 'postgresql & index')

-- 부분 문자열 검색
WHERE title LIKE '%postgre%'

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_search

GIN은 쓰기 비용이 높지만 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%';

전문 검색과 LIKE는 찾는 단위가 달라서 대체 관계가 아니다

전문 검색은 단어와 어휘소를, LIKE는 문자열 일부를 찾습니다. "postgresql index" 같은 개념 검색이면 FTS가 맞고, SKU 일부나 이메일 조각처럼 부분 문자열 검색이면 LIKE나 pg_trgm이 더 맞습니다. 둘을 섞어 쓰기보다 검색 UX가 무엇을 요구하는지 먼저 정하는 편이 좋습니다.

sql

-- 단어 의미 검색
WHERE search_vec @@ websearch_to_tsquery('english', 'postgresql index')

-- 부분 문자열 검색
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;

sql

SELECT id FROM articles
WHERE body LIKE '%postgresql index%';

전문 검색을 도입해 놓고도 이런 패턴을 계속 쓰면 GIN 인덱스 이점을 거의 못 얻습니다. 검색 요구가 "단어 검색"인지 "부분 문자열 검색"인지 먼저 고정해야 설계가 흔들리지 않습니다.

sql

SELECT id, title
FROM articles
WHERE search_vec @@ to_tsquery('english', user_input);

사용자 입력을 그대로 to_tsquery에 넣으면 연산자 문법 오류나 의도치 않은 검색식이 생기기 쉽습니다. 자유 입력 검색창은 보통 websearch_to_tsquery 쪽이 더 안전하고 UX도 자연스럽습니다.

Reference

Opening reference

Preparing the article, table of contents, and metadata. 본문, 목차, 메타데이터를 준비하고 있습니다.

PostgreSQL인덱스와 성능

전문 검색 (Full-Text Search)

tsvector/tsquery의 동작 원리, GIN 인덱스로 전문 검색 성능을 확보하는 방법, LIKE 검색과의 성능 차이를 정리합니다.

마지막 수정 2026년 4월 4일

핵심 정리

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`는 검색 조건 트리다

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;

전문 검색과 LIKE 검색은 찾는 단위가 다르다

sql

-- 단어 검색
WHERE search_vec @@ to_tsquery('english', 'postgresql & index')

-- 부분 문자열 검색
WHERE title LIKE '%postgre%'

GIN 인덱스가 전문 검색을 빠르게 만드는 이유

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_search

`ts_rank`로 관련성 점수를 매기는 원리

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%';

전문 검색과 LIKE는 찾는 단위가 달라서 대체 관계가 아니다

sql

-- 단어 의미 검색
WHERE search_vec @@ websearch_to_tsquery('english', 'postgresql index')

-- 부분 문자열 검색
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` 또는 외부 파서

주의할 점

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;

sql

SELECT id FROM articles
WHERE body LIKE '%postgresql index%';

sql

SELECT id, title
FROM articles
WHERE search_vec @@ to_tsquery('english', user_input);

Opening reference

Loading category cards

전문 검색 (Full-Text Search)

핵심 정리

문법

tsvector는 정규화된 어휘소 목록이고 tsquery는 검색 조건 트리다

전문 검색과 LIKE 검색은 찾는 단위가 다르다

GIN 인덱스가 전문 검색을 빠르게 만드는 이유

ts_rank로 관련성 점수를 매기는 원리

LIKE vs 전문 검색 성능 차이

전문 검색과 LIKE는 찾는 단위가 달라서 대체 관계가 아니다

선택 기준

주의할 점

Opening reference

전문 검색 (Full-Text Search)

핵심 정리

문법

tsvector는 정규화된 어휘소 목록이고 tsquery는 검색 조건 트리다

전문 검색과 LIKE 검색은 찾는 단위가 다르다

GIN 인덱스가 전문 검색을 빠르게 만드는 이유

ts_rank로 관련성 점수를 매기는 원리

LIKE vs 전문 검색 성능 차이

전문 검색과 LIKE는 찾는 단위가 달라서 대체 관계가 아니다

선택 기준

주의할 점

`tsvector`는 정규화된 어휘소 목록이고 `tsquery`는 검색 조건 트리다

`ts_rank`로 관련성 점수를 매기는 원리

`tsvector`는 정규화된 어휘소 목록이고 `tsquery`는 검색 조건 트리다

`ts_rank`로 관련성 점수를 매기는 원리