PostgreSQL 쿼리 하나로 응답속도 10배 개선

슬랙에 "이거 타임아웃 나요"가 올라온 날

금요일 오후 3시쯤이었다. 프론트엔드 팀 채널에 메시지가 떴다. 대시보드 API가 타임아웃을 뿜고 있다고. 처음엔 "서버 문제인가?" 싶어서 가볍게 들어갔는데, 아니, 쿼리가 3.2초를 찍고 있었다.

이 API는 사용자 활동 내역을 조회하는 건데, 서비스 초기엔 문제없었다. 데이터가 10만 건일 때는. 근데 1년이 지나니까 800만 건이 됐고, 그때부터 슬슬 느려지기 시작한 걸 아무도 안 잡은 거다.

문제의 쿼리를 복사해서 EXPLAIN ANALYZE를 돌렸다. 전형적인 분석 쿼리였다. 사용자 테이블이랑 활동 로그를 JOIN하고, 날짜 범위 필터 걸고, GROUP BY로 집계하는 구조.

결과를 보니까 Seq Scan이 두 군데서 터지고 있었다. 800만 건 테이블을 풀스캔. 예상 비용이 15만을 넘기고 있었다. 이건 뭐 느린 게 당연하다.

created_at에 B-tree 인덱스는 걸어뒀었다. 근데 쿼리를 자세히 보니까 DATE(created_at) 이렇게 함수로 감싸서 비교하고 있었다. 함수로 감싼 순간 인덱스를 안 탄다. 이론으로는 알고 있었는데 실제 코드에서 이걸 놓치기가 너무 쉽다.

WHERE created_at >= '2026-01-01' AND created_at < '2026-02-01'로 바꿨더니 바로 Index Scan으로 전환됐다. 이것만으로 1.2초가 빠졌다.

(이걸 왜 처음부터 안 잡았는지는 나도 모르겠다.)

JOIN 쪽을 봤더니 user_id랑 action_type으로 동시에 필터링하는데, 각각 단일 인덱스만 있었다. 복합 인덱스를 걸었다.

CREATE INDEX idx_activity_user_action ON activity_log (user_id, action_type, created_at)

쿼리 플래너가 Index Only Scan을 선택했고, 이 순간 800ms로 떨어졌다. 복합 인덱스에서 컬럼 순서가 중요하다는 걸 머리가 아니라 몸으로 느꼈다. WHERE 절에서 등호 조건이 먼저, 범위 조건이 뒤에 와야 한다.

원래 쿼리에 서브쿼리가 중첩되어 있었다. CTE로 바꾸면서 MATERIALIZED 힌트를 줬다. 800만 건에서 날짜 필터로 먼저 20만 건으로 줄이고, 그 결과에 대해 집계하는 구조로 재설계했다.

800ms에서 400ms로 또 줄었다.

여기서 하나 더. ANALYZE를 수동으로 실행하니까 50ms가 더 줄었다. PostgreSQL 쿼리 플래너는 테이블 통계를 기반으로 실행 계획을 세우는데, 통계가 오래되면 비효율적인 계획을 선택한다.

운영 환경의 autovacuum 설정이 기본값 그대로였다. 800만 건짜리 테이블에 autovacuum_analyze_scale_factor를 0.01로 낮춰서 통계 갱신 주기를 높였다.

(이건 공식 문서에서도 잘 안 다루는 부분이다.)

함수 기반 조건을 범위 조건으로 바꾸고, 복합 인덱스를 추가하고, 쿼리 구조를 개선하고, 통계를 갱신했다. 전부 기본적인 것들이다. 화려한 기술 같은 거 하나도 안 썼다.

사실 쿼리 튜닝의 80%는 EXPLAIN ANALYZE를 제대로 읽는 것이고, 나머지는 인덱스를 올바르게 거는 것이다.

데이터가 적을 때는 모든 쿼리가 빠르다. 문제는 서비스가 성장하면서 드러난다. 이 경험 이후로 새 테이블을 설계할 때 예상 데이터 양을 먼저 생각하게 됐다. 100만 건일 때도 괜찮은가, 1,000만 건이면 어떤가. 1년 뒤에도 이 쿼리가 버틸 수 있을까.