PostgreSQL 전역 검색: 전체 텍스트, 트라이그램, 부분 인덱스

내부 툴에서 “검색 전역(search everywhere)”이 실제로 의미하는 것

내부 화면에서 “검색 전역”은 보통 이렇게 해석됩니다: “내가 생각하는 정확한 레코드를 빠르게 찾아줘, 내가 정확히 기억하지 못해도.” 사람들은 탐색하는 게 아니라 특정 고객, 티켓, 송장, 장치로 바로 점프하려 합니다.

그래서 느린 검색은 느린 페이지보다 더 나쁘게 느껴집니다. 페이지 로드는 한 번 일어나지만 검색은 연속으로 여러 번 일어납니다. 통화 중이거나 트리아지를 하는 동안이라면 특히 그렇습니다. 결과가 2–3초 걸리면 사용자는 쿼리를 바꾸고, 백스페이스를 누르고, 다른 용어를 시도합니다. 그 결과 부하와 좌절감이 커집니다.

하나의 검색 상자에 여러 동작이 기대됩니다: 부분 일치("alex"가 "Alexander"를 찾음), 작은 오타에 대한 관용("microsfot"가 여전히 "Microsoft"를 찾음), 합리적인 “최고 결과” 정렬(정확한 ID나 이메일이 상단에), 약간의 최신성 편향, 기본으로 적용되는 필터(열린 티켓, 활성 고객) 등입니다.

문제는 하나의 입력이 여러 의도를 숨기는 경우입니다. 담당자가 티켓 번호를 붙여넣거나, 이름 조각을 입력하거나, 이메일을 검색하거나, 전화번호를 입력할 수 있습니다. 각 의도는 다른 전략, 다른 인덱스, 때로는 다른 순위 규칙을 원합니다.

그래서 인덱스부터 시작하지 마세요. 먼저 사용자가 실제로 가진 몇 가지 검색 의도를 나열하고, 정체성 필드(ID, 이메일)와 퍼지 필드(이름, 제목), 긴 텍스트(노트)를 분리하세요.

데이터와 검색 동작에 이름 붙이기부터 시작하세요

인덱스를 고르기 전에 사람들이 실제로 무엇을 입력하는지 적어보세요. “PostgreSQL 검색 전역”은 하나의 기능처럼 들리지만, 보통 매우 다른 검색들이 섞여 있습니다.

내부 도구는 “강한” 식별자(주문 ID, 티켓 번호, 송장 코드)와 “약한” 텍스트(고객 이름, 이메일, 노트, 태그)를 섞어 사용합니다. 이 그룹들은 PostgreSQL에서 다르게 동작하므로 같은 방식으로 처리하면 느린 쿼리로 가기 쉽습니다.

다음으로 동작을 분리하세요:

• 정확 조회: TCK-104883 같은 값을 검색하면 한 건의 정확한 결과를 기대합니다.
• 퍼지 조회: john smth을 입력하면 이름(때로는 이메일 포함)에 관대하게 매칭되어 짧은 목록을 스캔합니다.
• 필터 중심 검색: “상태 = Open”과 “담당자 = 나”를 선택하는 경우 텍스트 상자는 보조적입니다.

결과를 정렬(가장 적합한 항목 우선)해야 하는지, 아니면 단순히 필터링만 하면 되는지 조기에 결정하세요. 긴 노트나 설명은 정렬이 중요합니다. ID와 이메일은 정렬이 무작위처럼 느껴지고 비용만 늘릴 때가 많습니다.

짧은 체크리스트가 있으면 충분합니다:

• 어떤 필드를 매일 검색하나요?
• 어떤 입력이 정확한가요(ID, 코드), 퍼지인가요(이름), 긴 텍스트인가요(노트)?
• 거의 모든 검색에 어떤 필터가 적용되나요?
• “최적 일치” 정렬이 필요한가요, 아니면 어느 매칭이나 괜찮은가요?
• 테이블은 얼마나 빨리 커질까요: 수천, 수십만, 수백만?

이 결정을 미리 내리면 나중에 인덱스 선택이 추측이 아니게 됩니다.

기본기: 정확 매칭과 ILIKE가 문제일 때

먼저 쉬운 승리를 확보하세요. 많은 내부 화면에서는 B-tree 인덱스만으로도 ID, 주문 번호, 이메일, 외부 참조 같은 정확 매칭에 대해 즉각적인 결과를 제공합니다.

사용자가 정확한 값을 붙여넣는다면 쿼리가 실제로 정확한지 확인하세요. WHERE id = ... 또는 WHERE email = ...는 일반 인덱스로 매우 빠를 수 있습니다. 이메일에 유니크 인덱스를 두면 속도와 데이터 품질 두 가지 혜택을 얻습니다.

문제는 “검색 전역”이 조용히 ILIKE로 변할 때 시작됩니다. name ILIKE '%ann%' 같은 쿼리는 앞쪽 와일드카드 때문에 일반 B-tree 인덱스를 사용할 수 없습니다. 많은 행을 확인하게 되고 테이블이 커질수록 느려집니다.

접두사 검색은 작동할 수 있지만 패턴이 시작에서 고정될 때만 가능합니다: name ILIKE 'ann%'. 그럼에도 세부 사항(정렬(collation), 대소문자 처리, 쿼리할 때와 동일한 표현을 인덱스했는지 여부)이 중요합니다. UI가 대소문자 구분 없는 동작을 요구하면 보통 lower(name)을 쿼리하고 동일한 표현으로 인덱스를 생성하는 방법을 씁니다.

또한 “빠르다”의 기준을 합의하면 도움이 됩니다:

• 따뜻한 캐시에서 DB 작업은 약 200ms 이하
• 네트워크와 렌더링 포함하여 엔드투엔드는 1초 미만
• 흔한 검색에 대해 눈에 띄는 로딩 상태 없음

이 목표가 있으면 정확 및 접두사 매칭으로 유지할지, 전체 텍스트나 트라이그램 인덱스가 필요한지 결정하기가 쉬워집니다.

전체 텍스트 검색이 적절한 경우

사람들이 자연어를 입력하고 시스템이 관련 항목을 찾아주길 기대할 때 전체 텍스트 검색이 적합합니다. 티켓 메시지, 내부 노트, 긴 설명, 지식 기반 문서, 통화 로그 등이 해당합니다.

큰 장점은 순위 매김입니다. 관련성으로 정렬할 수 있어 최적의 결과가 묻히지 않습니다. 내부 도구에서는 몇 초 안에 답을 얻는 것이 중요하므로 이는 큰 차이를 만듭니다.

높은 수준에서 전체 텍스트 검색은 세 부분으로 이루어집니다:

• tsvector(검색 가능한 텍스트, 저장하거나 생성)
• tsquery(사용자가 입력한 것을 쿼리로 변환한 것)
• 언어 구성(단어를 정규화하는 방법)

언어 구성에서 동작의 차이가 드러납니다. PostgreSQL은 일반적인 불용어(예: "the", "and")를 제거하고 어간 처리(stemming)를 적용해 "pay", "paid", "payment"가 매치되게 합니다. 이는 노트나 메시지에는 유용하지만, 짧은 흔한 단어를 검색할 때 아무 결과가 나오지 않아 사용자가 놀랄 수 있습니다.

동의어도 결정해야 할 사항입니다. 회사에서 같은 것을 다르게 부를 때(예: "refund" vs "chargeback") 도움이 되지만 시간이 지나면 관리가 필요합니다. 동의어 목록은 짧고 지원팀이나 운영팀이 실제로 사용하는 용어를 기반으로 유지하세요.

실용적 예: “can’t login after reset”을 검색하면 메시지에 "cannot log in after password reset"이라고 써 있어도 티켓을 찾아야 합니다. 이런 ‘관련 항목 찾기’ 동작이 전체 텍스트 검색의 핵심이며, ILIKE로 검색 엔진처럼 만들려 애쓰는 것보다 보통 더 낫습니다.

트라이그램 인덱스가 이기는 경우

트라이그램은 사용자가 단편을 입력하거나 오타를 내거나 "비슷한 것"만 기억할 때 강력합니다. 전체 텍스트가 너무 엄격한 짧은 필드에 특히 유용합니다: 사람 이름, 회사 이름, 티켓 제목, SKU, 주문 번호, 제품 코드 등.

트라이그램은 3글자 단위의 청크입니다. PostgreSQL은 두 문자열이 공유하는 트라이그램 수로 유사도를 계산합니다. 그래서 "Jon Smth"을 "John Smith"와 매치하거나 "ACM"을 "ACME"와 매치할 수 있고, 쿼리가 단어 중간에 있어도 결과를 찾습니다.

이 접근법은 “적절한 행을 찾아라”가 목적일 때 풀텍스트보다 관대하고 빠른 해결책인 경우가 많습니다.

전체 텍스트보다 더 나은 경우

전체 텍스트는 긴 텍스트와 의미 기반 정렬에 강하지만, 짧은 필드의 부분 문자열이나 작은 오타 처리는 본질적으로 잘하지 못합니다. 트라이그램 검색은 그런 유형의 퍼지성에 맞춰 설계되었습니다.

쓰기 비용을 합리적으로 유지하기

트라이그램 인덱스는 크기가 크고 쓰기 시 오버헤드가 생기므로 신중하게 적용하세요. 사람들 이 실제로 검색 상자에 입력하는 열만 인덱싱하세요:

• 이름, 이메일, 회사, 사용자명
• 짧은 식별자(SKU, 코드, 참조)
• 간결한 제목 필드(큰 노트/코멘트 필드는 아님)

사용자가 검색 상자에 입력하는 정확한 필드를 알 수 있다면 트라이그램 인덱스를 작고 빠르게 유지할 수 있습니다.

사람들이 실제로 쓰는 필터에 대한 부분 인덱스

검색 상자에는 보통 숨겨진 기본값이 있습니다. 워크스페이스 내부, 활성 항목, 삭제 제외 등 필터를 기본으로 적용하는 경우가 많습니다. 이런 필터가 거의 모든 요청에 포함된다면 해당 행만 인덱싱해 일반 경로를 빠르게 만드세요.

부분 인덱스는 WHERE 절이 있는 일반 인덱스입니다. PostgreSQL은 관심 있는 행만 저장하므로 인덱스가 작아집니다. 그 결과 읽어야 할 페이지가 줄고 캐시 적중률이 좋아집니다.

일반적인 부분 인덱스 대상은 활성 행(status = 'active'), 소프트 삭제(deleted_at IS NULL), 테넌트 스코핑, 최근 윈도우(예: 지난 90일) 등입니다.

핵심은 UI와 일치시키는 것입니다. 화면이 항상 삭제된 행을 숨긴다면 쿼리도 항상 deleted_at IS NULL을 포함해야 하고, 부분 인덱스도 같은 조건을 사용해야 합니다. 예를 들어 한 곳에서 is_deleted = false를 쓰고 다른 곳에서 deleted_at IS NULL을 쓰면 플래너가 인덱스를 사용하지 않을 수 있습니다.

부분 인덱스는 전체 텍스트나 트라이그램과 함께 작동합니다. 예를 들어 비삭제 행에 대해서만 텍스트 검색 인덱스를 만들면 인덱스 크기를 통제할 수 있습니다.

단점: 부분 인덱스는 드문 쿼리에는 덜 유용합니다. 누군가 가끔 삭제된 레코드를 검색하면 PostgreSQL은 더 느린 계획을 택할 수 있습니다. 그런 경우는 관리자 전용 경로로 다루거나 드물게 발생하는 쿼리가 빈번해지면 별도의 인덱스를 추가하세요.

검색을 혼합하되 미스터리로 만들지 않기

대부분 팀은 한 검색 상자가 여러 의도를 처리해야 하므로 기술을 섞게 됩니다. 목표는 작업 순서가 명확해 결과가 예측 가능하게 느껴지도록 하는 것입니다.

간단한 우선순위 규칙을 두면 좋습니다. 별도 쿼리로 구현하든 CASE 논리를 사용하는 한 쿼리로 구현하든 간에 일관된 순서가 필요합니다.

예측 가능한 우선순위 사다리

엄격한 것부터 느슨한 것 순으로 시작하세요:

• 정확 매칭 우선(IDs, 이메일, 티켓 번호, SKU) — B-tree 인덱스 사용
• 접두사 매칭(가능한 경우)
• 이름과 제목의 오타·조각용으로 트라이그램 매칭
• 긴 노트와 자유 형식 콘텐츠에는 전체 텍스트 검색

같은 사다리를 유지하면 사용자는 검색 상자의 의미를 학습합니다. 예를 들어 "12345"는 티켓을 즉시 찾지만 "refund policy"는 더 긴 텍스트를 검색한다는 걸 이해하게 됩니다.

필터를 먼저 적용한 뒤 퍼지를 적용하세요

퍼지 검색은 전체 테이블을 고려하면 비용이 커집니다. 사용자들이 실제로 사용하는 필터(상태, 담당 팀, 날짜 범위, 계정)로 후보 집합을 좁힌 뒤 트라이그램이나 전체 텍스트를 실행하세요. 빠른 트라이그램 인덱스라도 수백만 행을 점수 매기게 하면 느리게 느껴질 수 있습니다.

또한 기술 비전문가가 이해할 수 있는 한 문단 규칙을 작성해두면 좋습니다. 예: “우리는 티켓 번호를 정확히 매치하고, 고객 이름은 오타 허용으로 매치하며, 그다음 노트를 검색합니다.” 이런 정의가 있으면 왜 특정 행이 뜨는지에 대한 논쟁을 줄일 수 있습니다.

단계별: 접근법을 선택하고 안전하게 구현하기

빠른 검색 상자는 작은 결정들의 집합입니다. 먼저 정리하면 DB 작업이 더 쉬워집니다.

1. 정의: 입력은 하나의 상자만인가, 아니면 필터(상태, 소유자, 날짜 범위)도 같이 있는가?
2. 필드별 매칭 유형 선택: ID/코드는 정확 매칭, 이름/이메일은 접두사나 퍼지, 긴 노트/설명은 자연어 검색이 더 낫습니다.
3. 적절한 인덱스 추가 후 사용 여부 확인: 인덱스를 만든 뒤 실제 쿼리에서 EXPLAIN (ANALYZE, BUFFERS)로 확인하세요.
4. 의도에 맞는 정렬/순위 추가: 사용자가 "invoice 1042"를 입력하면 정확 매칭이 위로 오게 하고, 오타가 있는 이름에는 유사도 기반 순위를 적용하세요.
5. 실제 쿼리로 테스트: 오타, 아주 짧은 용어(예: "al"), 긴 붙여넣기 텍스트, 빈 입력, “필터만” 모드를 시도하세요.

안전하게 배포하려면 한 번에 하나씩 변경하고 롤백을 쉽게 하세요. 대형 테이블의 새 인덱스는 쓰기를 차단하지 않도록 CREATE INDEX CONCURRENTLY를 선호합니다. 가능하면 기능 플래그 뒤에 숨겨서 대기 시간 변화를 비교하세요.

실용적 패턴: 정확 매칭 우선(빠르고 정확), 사람 필드에는 트라이그램, 긴 텍스트에는 전체 텍스트를 사용하세요.

현실적인 예: 지원 관리자 패널의 하나의 검색 상자

지원 관리자 패널을 상상해보세요. 팀은 하나의 검색 상자를 쓰지만 고객, 티켓, 노트까지 찾아주길 기대합니다. 이것이 바로 “하나의 입력, 여러 의미” 문제입니다.

첫 번째 개선은 의도를 마찰 없이 드러내는 것입니다. 쿼리가 이메일이나 전화번호처럼 보이면 고객 조회로 처리하세요. 티켓 ID처럼 보이면(예: "TKT-10482") 바로 티켓으로 라우팅하세요. 그 외는 티켓 제목과 노트 전반을 텍스트 검색으로 폴백합니다.

고객 조회는 보통 트라이그램 인덱스가 가장 적절합니다. 이름과 회사 문자열은 엉망이고 사용자는 조각을 입력하므로 트라이그램이 "jon smi"나 "acm" 같은 검색을 빠르고 관대하게 만들어줍니다.

티켓 노트는 전체 텍스트 검색을 사용하세요. 노트는 문장 단위이며 관련성 있는 매칭이 필요합니다. 같은 키워드를 언급한 수십 개의 티켓이 있을 때 순위가 도움이 됩니다.

필터는 대부분의 팀이 생각하는 것보다 훨씬 중요합니다. 에이전트가 “열린 티켓” 상태에 머문다면 열린 행만 커버하는 부분 인덱스를 추가하세요. 활성 고객에도 동일하게 적용하면 인덱스가 작아지고 일반 경로가 빨라집니다.

매우 짧은 쿼리는 규칙을 두세요. 그렇지 않으면 DB가 값 없는 노이즈 작업을 하게 됩니다:

• 1–2자: 최근 열린 티켓과 최근 업데이트된 고객을 보여줌
• 3자 이상: 고객 필드는 트라이그램, 티켓 텍스트는 전체 텍스트 실행
• 명확한 의도가 없을 때: 혼합 목록을 보여주되 각 그룹을 제한(예: 고객 10개, 티켓 10개)

검색을 느리거나 혼란스럽게 만드는 흔한 실수

대부분의 “검색이 느린 이유” 버그는 스스로 만든 경우가 많습니다. 목표는 모든 것을 인덱싱하는 것이 아니라 사람들이 실제로 하는 것을 인덱싱하는 것입니다.

흔한 함정은 “혹시 몰라” 하며 많은 열에 인덱스를 추가하는 것입니다. 읽기 성능은 좋아질 수 있지만 모든 삽입과 업데이트에 추가 작업이 생깁니다. 티켓, 주문, 사용자처럼 하루 종일 레코드가 바뀌는 내부 툴에서는 쓰기 속도가 중요합니다.

또 다른 실수는 실제로는 이름이나 이메일의 오타 허용 조회가 필요한데 전체 텍스트를 사용하는 것입니다. 전체 텍스트는 문서와 설명에 탁월하지만 "Jon" 대 "John"이나 "gmail.con" 대 "gmail.com" 같은 문제를 자동으로 해결해주진 않습니다. 그런 경우는 보통 트라이그램 영역입니다.

필터도 계획을 조용히 깨뜨릴 수 있습니다. 대부분의 검색이 고정 필터(status = 'open' 또는 org_id = 42)와 함께 일어난다면 부분 인덱스가 최적입니다. 이를 잊으면 PostgreSQL이 기대보다 훨씬 많은 행을 스캔할 수 있습니다.

반복되는 몇 가지 실수:

• 쓰기 비용을 측정하지 않고 많은 인덱스를 추가함
• 전체 텍스트가 자동으로 오타 허용 자동완성을 대신해줄 거라 기대함
• 흔한 필터가 인덱스 사용 여부를 어떻게 바꾸는지 무시함
• 작고 깨끗한 데이터에서만 테스트하고 실제 용어 빈도를 고려하지 않음
• 정렬하는 컬럼에 인덱스가 없어 느린 정렬을 강제함

예: 티켓을 제목, 고객 이름, 티켓 번호로 검색한 뒤 최신 활동 기준으로 정렬한다면, 필터된 집합(예: 열린 티켓)에 대해 latest_activity_at에 인덱스가 없다면 검색 인덱스로 얻은 속도가 정렬 때문에 날아갈 수 있습니다.

출시 전 빠른 점검 목록

“검색 전역” 기능을 끝냈다고 선언하기 전에 약속하는 동작을 구체화하세요.

• 사람들이 정확 식별자(티켓 번호, 이메일)로 레코드를 찾으려 하나요?
• 오타에 대한 퍼지 매칭을 기대하나요?
• 긴 노트와 설명에서 순위 기반 결과를 원하나요?

모드를 섞는다면 충돌할 때 어떤 모드가 우선하는지 결정하세요.

그다음 2–3개 주요 필드를 확인하세요. 예: 검색의 80%가 이메일, 이름, 티켓 ID로 이루어진다면 그 세 가지를 먼저 최적화하고 나머지는 보조로 처리하세요.

사전 출시 체크리스트:

• 필드별 주요 매칭 모드(정확 조회, 퍼지 매칭, 순위 텍스트)를 확인하세요
• 사용자가 일상적으로 적용하는 필터 조합을 나열하고 인덱스가 그 조합을 지원하는지 확인하세요
• 아주 짧은/빈 쿼리는 어떻게 처리할지 결정하세요(예: 퍼지 검색은 2–3자 이상 필요; 빈 쿼리는 “최근” 표시)
• 정렬 기준을 설명 가능하게 만드세요: 최신, 텍스트 매칭 우선, 또는 단순 결합 규칙

마지막으로 정확성 뿐 아니라 현실적인 데이터 크기와 타이밍으로 테스트하세요. 1,000행에서는 즉각적인 쿼리가 1,000,000행에서는 느려질 수 있습니다.

다음 단계: 계획을 빠른 내부 검색 화면으로 전환하기

검색 상자는 팀이 무엇을 하길 원하는지 합의할 때 빠르게 유지됩니다. 일치의 의미(정확, 접두사, 오타 허용), 어떤 필드를 검색하는지, 필터가 결과 집합을 어떻게 바꾸는지 평이한 언어로 규칙을 적으세요.

실제 검색들을 모아 작은 테스트 세트로 유지하고 회귀 테스트처럼 다루세요. 흔하게 쓰이는 이름 몇 개, 부분 이메일, 오타, 긴 노트 조각, 그리고 "결과 없음" 케이스 하나면 보통 충분합니다. 변경 전후에 실행해 성능과 관련성이 조용히 깨지지 않도록 하세요.

내부 툴을 AppMaster(appmaster.io)로 만드는 경우 검색 규칙을 데이터 모델과 비즈니스 로직과 함께 정의하면 UI 동작과 DB 선택이 요구사항 변화에 따라 흩어지지 않습니다.