Codingstairs 검색을 ILIKE·pg_trgm·마이그레이션으로 최적화하기

현재 Codingstairs 검색은 제목·설명·slug·lesson 본문에서 ILIKE '%검색어%'를 사용한다. 검색어를 escape하고 길이를 제한하는 것은 안전성 계약이고, wildcard 앞뒤 때문에 일반 B-tree 인덱스는 이 쿼리를 충분히 돕지 못한다.

실제 조회에 맞춘 인덱스

frontend/admin/sql/codingstairs/003_create_posts.sql과 007_create_lessons.sql은 pg_trgm extension과 GIN 인덱스를 최종 스키마로 선언한다.

• posts: title, description, 관리자 검색용 slug
• lessons: 발행된 행의 title, content_md
• 기존 운영 DB: codingstairs-migrate.ts가 같은 extension·index를 멱등적으로 보강

published, language, content_kind, series_slug를 먼저 좁히는 composite/partial 인덱스는 목록·상세 조회를 위한 별도 계약이다. trigram 인덱스가 이를 대신하지 않는다.

tsvector를 무조건 되살리지 않는 이유

한국어 설정이 없는 기본 PostgreSQL 이미지에서 to_tsvector('korean', ...)를 사용하면 schema 초기화가 통째로 rollback될 수 있다. 제품 검색 계약이 ILIKE인 상태에서 존재하지 않는 text-search configuration을 가정하는 것은 빠른 검색보다 더 큰 장애를 만든다. 전문검색으로 바꾸려면 analyzer·언어·ranking·재색인 정책을 먼저 제품 계약으로 정해야 한다.

검증 순서

staging 데이터에서 대표 검색어에 대해 다음을 비교한다.

EXPLAIN (ANALYZE, BUFFERS)
SELECT ...
WHERE published = TRUE AND language = 'ko'
  AND title ILIKE '%docker%';

실제 계획이 항상 Bitmap/GIN이 되는 것은 아니다. 행이 아주 적거나 검색어가 두 글자뿐이면 순차 스캔이 더 쌀 수 있다. 적용 뒤에는 쓰기 시간, vacuum, 백업 크기와 함께 확인해야 하며 인덱스가 있다는 사실만으로 성능 완료를 선언하지 않는다.

완료 기준

• SQL SSOT와 기존 DB migration이 같은 인덱스 이름·연산자를 가리킨다.
• 검색 입력 escape·길이 제한·429 계약이 유지된다.
• staging의 대표 query plan과 write/backup 비용을 기록한다.
• 검색 결과가 빨라져도 발행되지 않은 콘텐츠가 노출되지 않는다.