pg_trgm 통합 검색과 콘텐츠 모델 설계 — PostgreSQL (1)

PostgreSQL pg_trgm trigram GIN 인덱스로 게시판·장비·연구성과 세 도메인을 한 검색창에서 찾는 통합 검색 구현. 도메인별 콘텐츠 모델 분리, 짧은 라벨은 영어 필드 병기·정적 문구는 메시지 파일로 가른 다국어 전략, Promise.all 병렬 조회, SQLite에서 PostgreSQL로 옮기며 챙긴 타입 엄격화와 cuid 키 전략까지 정리한다.

PostgreSQL의 pg_trgm으로 통합 검색을 만들고 콘텐츠 모델을 설계한 과정을 정리한다.

 

시리즈를 시작하며

연구기관의 공식 웹사이트를 만들었다. 연구 역량과 성과를 보여주고, 게시판·장비·뉴스 등을 통합 검색하고, 다국어로 제공하는 사이트다. Next.js + TypeScript 기반이고, 데이터는 PostgreSQL을 쓴다.

세 편으로 나눠 기록한다.

1. (이 글) 콘텐츠 모델과 pg_trgm 통합 검색
2. 국제화(i18n)·SEO·접근성·성능
3. 에디터(TipTap)·인증·E2E 테스트

특정 기관명은 모두 제외하고 구현만 다룬다.

콘텐츠 도메인 나누기

기관 사이트는 보여줄 콘텐츠 종류가 많다. 연구 역량, 연구 성과(ResearchOutput), 게시판(Board), 장비(Equipment), 뉴스 등이다. 각각을 별도 도메인으로 모델링하되, 공통적으로 "검색되고, 다국어로 보이고, 관리자가 CRUD"하는 성격을 공유한다.

연구 역량 페이지는 API + 목록 + 상세(리다이렉트 처리)로 구현하고, 다국어(i18n)를 처음부터 염두에 뒀다(2편에서 자세히).

핵심: 통합 검색 (pg_trgm)

이 사이트에서 가장 공들인 기능이 통합 검색이다. 사용자가 한 검색창에서 게시판·장비·연구성과를 한꺼번에 찾는다. 단순 LIKE '%키워드%' 검색은 두 가지 문제가 있다.

• 부분 오타·유사어를 못 잡는다.
• 양쪽 와일드카드(%키워드%)는 인덱스를 못 타서 느리다.

그래서 PostgreSQL의 pg_trgm(trigram) 확장을 도입했다. 문자열을 3글자 단위(trigram)로 쪼개 유사도를 계산하는 방식이라, 부분 일치와 유사 검색에 강하고 GIN 인덱스로 빠르다.

CREATE EXTENSION IF NOT EXISTS pg_trgm;

-- 검색 대상 컬럼에 trigram GIN 인덱스
CREATE INDEX idx_board_title_trgm ON board USING gin (title gin_trgm_ops);
CREATE INDEX idx_equipment_name_trgm ON equipment USING gin (name gin_trgm_ops);
CREATE INDEX idx_research_title_trgm ON research_output USING gin (title gin_trgm_ops);

-- 유사도 기반 검색
SELECT *, similarity(title, :q) AS score
FROM board
WHERE title % :q          -- % = trigram 유사 연산자
ORDER BY score DESC;

게시판·장비·연구성과 세 테이블에 trigram 인덱스를 걸고, 통합 검색이 이 인덱스를 타도록 했다. 프론트엔드에서는 글로벌 내비게이션(GNB)에 검색을 연동하고, 결과를 카테고리 탭으로 나눠 보여줬다.

SQLite → PostgreSQL 마이그레이션

처음엔 개발 편의로 SQLite로 시작했다. 그런데 pg_trgm 같은 본격 검색과 운영을 생각하면 PostgreSQL이 맞았다. 그래서 DB를 SQLite에서 PostgreSQL로 마이그레이션했다.

마이그레이션에서 신경 쓴 점:

• 타입 차이: SQLite는 타입이 느슨하고(동적 타입), PostgreSQL은 엄격하다. 컬럼 타입을 명확히 잡아야 했다.
• 자동 증가 키: SQLite의 AUTOINCREMENT와 PostgreSQL의 SERIAL/IDENTITY 차이.
• 확장 기능: pg_trgm처럼 PostgreSQL에서만 되는 기능을 마이그레이션 후 활성화.

이 전환 덕에 검색 성능과 운영 안정성을 한 번에 확보했다. "개발은 가볍게(SQLite) → 운영은 제대로(PostgreSQL)"의 전환을 깔끔히 한 셈이다.

정리하며

• 콘텐츠를 도메인별로 나누되, 검색·다국어·CRUD라는 공통 성격으로 묶음.
• 통합 검색은 pg_trgm으로 — 부분/유사 검색 + GIN 인덱스로 빠르게.
• SQLite → PostgreSQL 마이그레이션으로 검색 기능과 운영 안정성 확보.

다음 2편에서는 기관 사이트의 필수 요소, 국제화(i18n)·SEO·접근성·성능을 다룬다.