대용량 텍스트 검색: PostgreSQL LIKE부터 역인덱스, 그리고 ripgrep까지

PostgreSQL의 LIKE 검색이 내부적으로 어떻게 동작하는지, 인덱스 유무에 따른 차이, Elasticsearch 같은 역인덱스 엔진의 원리, 그리고 VS Code 검색(ripgrep)이 인덱스 없이도 빠른 이유를 정리.

들어가며

개발하다 보면 한 번쯤은 맞닥뜨리는 상황이 있다. DB에 텍스트가 잔뜩 쌓여 있고, 그 안에서 특정 값을 찾아야 하는 순간. 처음엔 LIKE로 해결되다가, 어느 시점부터 쿼리가 수십 초씩 걸리기 시작한다.

이 글은 그 문제를 직접 겪으면서 정리한 내용이다. 6천만 건의 SOAP 로그에서 LIKE '%R137C00123%'를 날렸을 때 무슨 일이 벌어지는지, 인덱스를 걸면 왜 해결이 안 되는지, 그렇다면 Elasticsearch는 어떤 원리로 빠른지를 내부 동작 수준에서 파고든다.

"그냥 Elasticsearch 쓰면 되는 거 아냐?"라는 답은 반은 맞고 반은 틀리다. 도구를 선택하려면 각각이 어떤 트레이드오프를 안고 있는지 알아야 한다. 이 글이 그 판단에 도움이 되길 바란다.

배경

soap.soap_logs 테이블에 XML 원문이 text 컬럼으로 저장되어 있고 (약 6천만건), 특정 인터페이스 + 특정 값으로 검색할 필요가 있었다.

SELECT id, "startedAt", "requestData"
FROM soap.soap_logs
WHERE interface = 'IF_EVCP_ECC_RFQ_INFORMATION'
  AND "requestData" LIKE '%R137C00123%';

1. 인덱스 없는 LIKE: Sequential Scan + 슬라이딩 윈도우

인덱스가 없으면 PostgreSQL은 모든 행을 순서대로 읽으면서 문자열을 비교한다.

동작 과정

1. 힙 페이지 순차 읽기 — 8KB 페이지 단위로 디스크에서 읽음
2. TOAST 디컴프레스 — text가 약 2KB 이상이면 별도 TOAST 테이블에 압축 저장됨. 매 행마다 꺼내서 압축 해제
3. 슬라이딩 윈도우 문자 비교 — %keyword% 패턴의 경우:

텍스트: <RFQ><VENDOR>R137C00123</VENDOR>...
패턴:   R137C00123

위치 0: '<' vs 'R' → 불일치, 다음
위치 1: 'R' vs 'R' → 일치, 계속 비교
위치 2: 'F' vs '1' → 불일치, 다음
...
위치 N: 'R137C00123' 전체 일치 → 매치!

• 시간 복잡도: O(n × m) (n=텍스트 길이, m=패턴 길이)
• Boyer-Moore 같은 최적화는 적용되지 않음 (PostgreSQL 내부 MatchText 함수)

숨은 비용: TOAST

긴 text 컬럼의 실제 병목은 문자 비교가 아니라 TOAST일 수 있다:

• TOAST 데이터는 본 테이블과 물리적으로 다른 위치에 저장됨
• shared_buffers에 잘 안 남아서 반복 쿼리도 디스크 히트 가능
• 압축 해제 CPU 비용

2. B-tree 인덱스: 양쪽 와일드카드에는 무력

B-tree는 값의 좌측부터 정렬되어 있다.

B-tree 리프 (정렬 순서):
  "ABC..." → "ABD..." → "BAA..." → "CAA..."

requestData 컬럼에 인덱스를 걸어봐야 %keyword% 패턴에는 아무 효과가 없다. 인덱스 크기만 늘어날 뿐이다.

3. pg_trgm GIN 인덱스: 트라이그램 역인덱스

pg_trgm은 텍스트를 연속 3글자(trigram) 조합으로 분해하여 GIN 역인덱스를 만든다.

인덱싱

'R137C00123' → 트라이그램 분해:
  'R13', '137', '37C', '7C0', 'C00', '001', '012', '123'

GIN 인덱스 (역인덱스 구조):
  'R13' → [row 42, row 1587, row 30012, ...]
  '137' → [row 42, row 1587, row 8821, ...]
  '37C' → [row 42, row 1587, ...]
  ...

검색

LIKE '%R137C00123%'

1. 검색어를 트라이그램으로 분해
2. 각 트라이그램의 행 목록을 GIN에서 조회
3. 모든 목록의 교집합(AND) → 후보 행
4. 후보 행만 실제 LIKE로 재검증 (false positive 제거)

트레이드오프

• 장점: %keyword% 검색이 인덱스를 탐
• 단점: 인덱스 크기가 원본의 2~3배. XML처럼 긴 텍스트면 디스크/메모리 부담 큼

4. Elasticsearch: 단어 단위 역인덱스

Elasticsearch의 핵심은 Inverted Index(역인덱스) — 책 뒤의 "찾아보기"와 같은 원리.

일반 인덱스 vs 역인덱스

일반 인덱스 (B-tree):  문서 → 내용
  doc_1 → "<RFQ><VENDOR>R137C00123</VENDOR><AMOUNT>5000</AMOUNT>"

역인덱스:              단어 → 문서 목록
  "rfq"        → [doc_1, doc_2]
  "r137c00123" → [doc_1]
  "5000"       → [doc_1]

색인 과정 (Indexing)

원본: "<RFQ><VENDOR>R137C00123</VENDOR>"
         │
         ▼
      Analyzer (분석기)
         │
         ├── Tokenizer: 텍스트를 토큰으로 분리
         │     → ["RFQ", "VENDOR", "R137C00123"]
         │
         ├── Token Filter: 소문자화, 불용어 제거 등
         │     → ["rfq", "vendor", "r137c00123"]
         │
         ▼
      역인덱스에 등록

검색 과정

검색어: "R137C00123"
  → 같은 Analyzer로 분석 → "r137c00123"
  → 역인덱스에서 키 조회 (O(1)~O(log n))
  → 결과 즉시 반환

문서 본문을 한 글자도 스캔하지 않는다. 이것이 LIKE와의 근본적 차이.

Analyzer가 전부다

같은 원본이라도 Analyzer에 따라 토큰이 달라진다:

Standard Analyzer (기본):
  "<VENDOR>R137C00123</VENDOR>" → ["vendor", "r137c00123", "vendor"]

Keyword Analyzer (통째로):
  "<VENDOR>R137C00123</VENDOR>" → ["<VENDOR>R137C00123</VENDOR>"]

Custom Analyzer (XML 태그 제거):
  "<VENDOR>R137C00123</VENDOR>" → ["R137C00123"]

XML 원문을 그대로 색인하면 태그명도 토큰으로 들어가서 검색 노이즈가 생긴다. 커스텀 Analyzer로 태그를 제거하고 값만 추출하는 전처리가 실무에서 중요한 이유다.

5. 역인덱스의 근본적 한계: 토큰 경계

원본:  "ABC DEF"
토큰:  ["abc", "def"]

검색 "C D" → 어떤 토큰과도 일치하지 않음 → 매칭 실패

토큰 경계를 넘는 임의 부분 문자열 검색은 기본 역인덱스로 불가능하다.

6. RUM Conjecture: 인덱스 설계의 삼각 트레이드오프

        Read 성능
           /\
          /  \
         /    \
        /  Pick \
       /   Two   \
      /____________\
 Update 성능    Memory 비용

실무에서 이 트레이드오프를 어떻게 쓰느냐

이 이론이 실용적인 이유는, 검색 요구사항이 정해지면 나머지 비용이 자동으로 따라온다는 걸 명확히 해주기 때문이다.

• "빈번한 %keyword% 검색이 필요하다" → Read를 개선해야 한다 → 반드시 인덱스(pg_trgm 또는 ES)가 필요하다 → Update 속도와 디스크를 포기할 준비를 해야 한다
• "데이터가 계속 쌓이고, 가끔만 검색한다" → Update를 지키는 게 우선 → 인덱스 없이 LIKE + 선행 필터로 범위를 좁히는 전략이 현실적이다
• "검색 대상 필드가 특정 값으로 한정된다" → 해당 값을 별도 컬럼으로 추출해 B-tree 인덱스를 걸면, Read와 Memory 모두 잡을 수 있다 (트라이그램 인덱스 없이)

도구 선택은 이 트레이드오프 어디에 무게를 두느냐의 문제다. "Elasticsearch 쓰면 다 해결된다"는 말은 반만 맞다 — Read는 해결되지만, 운영 비용, 색인 지연, 인프라 복잡도를 새로 안는다.

7. 공통 구조 정리

pg_trgm, PostgreSQL tsvector, Elasticsearch 모두 동일한 역인덱스 원리를 공유한다. 차이는 토큰을 어떻게 자르느냐 뿐이다.

8. VS Code 검색 = ripgrep: 인덱스 없이 빠른 이유

VS Code의 파일 내 검색(Ctrl+Shift+F)은 내부적으로 ripgrep(rg)을 임베딩하여 사용한다. 인덱스를 전혀 만들지 않으면서도 프로젝트 규모(수천~수만 파일)에서 체감상 즉시 결과를 보여준다.

핵심 최적화 4가지

1) 메모리 맵 I/O (mmap)

일반 read():
  디스크 → 커널 버퍼 → 유저 버퍼 → 검색
  (복사 2번)

mmap():
  디스크 → 커널 버퍼 ← 유저가 직접 참조
  (복사 0번, 필요한 페이지만 로드)

파일 전체를 미리 읽지 않고, 실제 접근하는 페이지만 OS가 알아서 올려준다.

2) DFA 기반 정규식 엔진: 항상 O(n)

패턴: "R137C"

일반 백트래킹 regex (PCRE 등):
  매칭 실패 시 되돌아가서 다시 시도 → 최악 O(2^n)

DFA (ripgrep):
  상태 머신으로 변환, 문자당 정확히 1번 상태 전이 → 항상 O(n)

  S0 --'R'--> S1 --'1'--> S2 --'3'--> S3 --'7'--> S4 --'C'--> MATCH
   \           \           \           \           \
    다른문자     다른문자     다른문자     다른문자     다른문자
     → S0        → S0        → S0        → S0        → S0

텍스트를 딱 한 번만 훑으면서, 매 바이트마다 상태 테이블을 참조. PostgreSQL LIKE의 슬라이딩 윈도우(O(n×m))보다 빠르다.

3) SIMD 리터럴 최적화 (Teddy)

패턴에 고정 문자열이 포함되면 DFA조차 건너뛰고 SIMD 명령어로 바이트 단위 병렬 비교:

일반 비교:     한 번에 1바이트씩 비교
SIMD (AVX2):  한 번에 32바이트를 동시에 비교

텍스트: [<RFQ><VENDOR>R137C00123</VENDOR><AM...]
         ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
         32바이트를 한 CPU 명령으로 스캔

리터럴 검색이면 이론상 일반 비교의 16~32배 처리량.

4) 병렬 처리 + 스마트 필터링

검색 시작
  │
  ├── .gitignore / .ignore 파일 파싱 → 무시할 경로 제외
  ├── node_modules, .git 등 자동 스킵
  ├── 바이너리 파일 감지 → 스킵
  │
  └── 남은 파일들을 스레드 풀에서 병렬 처리
        Thread 1: src/lib/*.ts
        Thread 2: src/app/*.tsx
        Thread 3: src/components/*.tsx
        ...

grep vs ripgrep

역인덱스와의 근본적 차이

• Elasticsearch: "미리 인덱스를 만들어서" 빠름 — 쓰기 비용 선불
• ripgrep: "인덱스 없이 원본을 훑되, 훑는 속도 자체를 극한까지 최적화" — DFA + SIMD + mmap + 병렬

프로젝트 규모(수천~수만 파일)에서는 ripgrep 방식이 충분히 빠르다. 하지만 6천만건 DB 규모로 가면 결국 역인덱스가 필요해진다. 두 방식이 경쟁하는 게 아니라, 데이터 규모와 쓰기/읽기 패턴에 따라 적용 영역이 다르다.

실무 선택 가이드

마치며

같은 "텍스트 검색"이라도 데이터 규모, 쓰기 빈도, 검색 패턴에 따라 최적의 도구가 완전히 달라진다. B-tree는 왼쪽 고정 패턴에만 쓸 수 있고, pg_trgm은 디스크를 두 배로 먹고, Elasticsearch는 별도 인프라를 요구하고, ripgrep은 수억 건 DB엔 통하지 않는다.

어떤 도구를 쓸지 결정하기 전에, 검색이 얼마나 자주 일어나는지, 데이터가 얼마나 빠르게 쌓이는지, 그리고 RUM 트레이드오프에서 무엇을 포기할 수 있는지를 먼저 따져보는 게 순서다.