[260402 TIL] 검색 쿼리 최적화 트러블슈팅

텍스트 검색 쿼리 10초(??) → 1초 최적화 트러블슈팅

TL;DR

Supabase에서 대략 8만 행 풀 대상 해시태그+캡션 텍스트 검색 쿼리가 10초(????)+ 걸리던 것을,
View → Materialized View + 인덱스 → pg_trgm GIN 인덱스 적용으로 1초까지 줄였다.
과정에서 중복 집계 버그도 발견·수정.

---

개요

회사에서 스크래핑 어쩌고 프로젝트를 진행 중이다.
대충 인스타 트렌딩 릴스 스크래핑 어쩌구인데.. 검색 기능이 필요하게 되었다.
이 검색 기능의 성능 문제를 해결해나간 과정을 정리한다.

테이블 구조

• reels: 최초 수집 데이터 (릴스 기본 정보, 캡션 등)
• reel_metrics: 서드파티를 통해 +1일 후 수집한 메트릭 데이터. diff를 보기 위해 릴스당 2개의 행이 존재

검색 시 두 테이블을 JOIN해야 하는 구조였고,
이를 기반으로 reel_growth_rate라는 View를 만들어 사용하고 있었다.

---

삽질기: 10초 → 1초까지의 여정

1단계: 문제 인식

초기 필터 조건은 카테고리 = "OO" & 나머지 조건 없음.
약 4만 행의 풀에서 800개 정도가 매칭되는데, 쿼리 시간이 8초 이상 걸렸다.
거기에 더해 간헐적으로 Supabase가 에러를 뱉었다.
양이 많고 복잡해서인지, 타임아웃인지 정확한 원인은 파악하지 못했다.

2단계: View + RPC 적용 (8초 → 5초)

"막연히 RPC면 빨라지겠지"라는 생각으로 기존 View 위에 RPC를 걸었다.
쿼리 시간은 5초 정도로 줄었고, 간헐적 에러도 줄어 안정성이 올라갔다.

하지만 여전히 느렸다. 그래도... 일단 돌아가니까 놔뒀다.

3단계: 풀 증가로 재폭발

보관 기한이 1달인데, 1달이 다가오자 풀이 약 8만 행으로 늘어났다.
쿼리 시간은 10초 가까이 늘어났고, 더 이상 방치할 수 없었다.

다시 들여다보니 View와 RPC 사용 모두가 문제였다:

• View의 한계: 행 수가 많은 상황에서 일반 View는 매 쿼리마다 내부 JOIN을 실행한다. 인덱스도 걸 수 없다.
• RPC의 실체: 내부적으로 View를 참조하고 있었으므로, 결국 매번 JOIN이 돌아가고 있던 것이다.

4단계: Materialized View + 인덱스 (10초 → 1.5초, 그러나...)

"그냥 MV를 만드는 게 낫겠다"는 생각이 (이제서야) 들었다.
MV로 전환하고 인덱스를 걸자 RPC도 필요 없어졌고, 쿼리 시간이 1.5초로 단축되었다.

그런데 여기서 문제가 발생했다.

기본 필터 쿼리 결과가 800개에서 300개 수준으로 급감한 것이다.

풀은 오히려 늘었는데 결과는 줄었다.

원인을 파보니, View와 MV를 만들 때 원 테이블을 그대로 JOIN하고 있었다.
reel_metrics는 릴스당 2개의 행이 있으므로(diff를 보기 위해 2회 수집),
중복 제거를 하지 않으면 하나의 릴스가 2번 카운트된다.

맨 처음 일반 View를 만들었을 때부터 잘못하고 있었던 것이다.

기존에 800~1000개로 보고 있던 수치의 절반은 중복이었다.

5단계: 1/3로 줄었다? 절반이 아니고?

그런데 다시 생각해보니, 중복 제거를 하면 절반으로 줄어야 하는데 왜 1/3 수준이 된 걸까?

다시 살펴보니... 맨 처음에는 해시태그 + 캡션 둘 다 검색이었는데,
MV를 만드는 과정에서 돌았는지 해시태그만 ILIKE 하고 캡션은 빠져 있었다.

캡션 검색을 다시 추가하자 카운트는 정상(기존의 딱 절반)으로 돌아왔다.
그런데 쿼리 시간이 4초 이상으로 다시 늘어났다.

6단계: pg_trgm GIN 인덱스 (4초 → 1초)

ILIKE 검색은 결국 풀스캔이라고 한다..
두 컬럼에 ILIKE를 걸면 그만큼 느려질 수밖에 없다.

 

pg_trgm 확장이라는 것을 찾았고,
MV에 해시태그+캡션을 결합한 검색용 컬럼을 하나 추가한 뒤 GIN 인덱스를 적용했다.

 

최종 결과: 카운트 정확(기존 대비 정확히 절반) + 쿼리 시간 약 1초.

---

교훈

View vs Materialized View 선택 기준

일반 View는 매 쿼리마다 내부 쿼리를 실행하므로, 행 수가 많고 JOIN이 포함된 경우 성능이 나오지 않는다.
읽기 빈도가 높고 실시간성이 덜 중요하다면 MV가 맞다. MV는 인덱스도 걸 수 있다.(용량은 뭐...)

JOIN 결과의 행 수를 항상 검증할 것

JOIN 시 1:N 관계에서 중복 행이 발생하는 건 기본 중의 기본이지만, 처음 접하면 놓치기 쉽다.
View나 MV를 만든 후에는 반드시 원본 대비 행 수를 검증해야 한다.

ILIKE의 성능 한계와 pg_trgm

ILIKE는 인덱스를 타지 못하고 풀스캔을 한다.
텍스트 검색이 핵심 기능이라면 pg_trgm 확장 + GIN 인덱스를 처음부터 고려하는 것이 좋다.

• B-tree: LIKE 'keyword%'만 인덱스 사용 가능 (앞쪽 고정)
• GIN trigram: ILIKE '%keyword%'도 인덱스 사용 가능

"일단 돌아가니까"의 대가

대충 5초 정도였을 때 제대로 파봤으면,
풀이 늘어나서 10초가 되기 전에 해결할 수 있었다.
기술 부채는 데이터가 늘어나면 이자가 붙는다.

LLM과 페어 프로그래밍할 때, "OK" 버튼을 누르기 전에

요즘 LLM 선생님들이 이미 사람보다 훨씬 나은 면이 많다 보니(어떤 면에서는 시니어 개발자보다도??),
추천해주는 방향대로 무조건 OK 하게 되는 경향이 있다.
이번에 View + RPC 조합을 선택했던 것도 그런 과정이었다.

나는 "View + RPC면 막연히 빠르겠지"라는 생각이 있었고, 그대로 LLM에게 물어봤다.
선생님들도 그게 좋겠다고 해서 그대로 진행했다.

 

그러나 여기서 빠진 건 구체적인 컨텍스트였다.
LLM은 내가 "View"를 이야기했을 때 그 View 아래로 수만 행의 JOIN 결과가 매번 만들어질지는 몰랐을 것이다.
내가 그 정보를 주지 않았으니까.

 

LLM이 아무리 똑똑해도, 내 상황의 구체적인 맥락은 내가 제공해야 한다.
"이 방향이 맞나요?"가 아니라 "4만 행이 JOIN되는 상황에서 이 방향이 맞나요?"라고 물어야 했다.

좋은 답을 얻으려면 좋은 질문을 해야 한다는...
사람들은 하네스가 어쩌고 저쩌고 하는데 아직 이러고 있다...

참조

• PostgreSQL pg_trgm 공식 문서
• PostgreSQL GIN 인덱스 문서
• Supabase - Materialized Views