인덱스가 없는 컬럼이 어떻게 우리 데이터베이스를 마비시켰나
단 하나의 누락된 인덱스가 8ms짜리 API를 8초짜리 악몽으로 만들었습니다.
주요 릴리스를 앞두고 부하 테스트를 진행했습니다. 로컬 개발 환경에서는 100개의 레코드로 문제없이 작동했습니다. 그 후, 운영 환경 규모의 데이터인 150만 개의 행을 대상으로 500명의 사용자를 시뮬레이션했습니다.
결과는 처참했습니다:
- API 응답 시간이 45ms에서 8,000ms로 급증했습니다.
- 데이터베이스 CPU 점유율이 100%에 도달했습니다.
- 커넥션 풀(Connection pool)이 고갈되어 타임아웃 에러가 발생했습니다.
슬로우 쿼리 로그(slow-query log)를 확인한 결과, 사용자 ID와 상태(status)별로 주문 내역을 가져오는 엔드포인트를 발견했습니다.
PostgreSQL에서 EXPLAIN ANALYZE를 실행해 보니 Sequential Scan이 나타났습니다. user_id에 인덱스가 없었기 때문에 엔진은 매 요청마다 150만 개의 행을 모두 읽어야 했습니다.
100개의 동시 요청이 들어오자, 한꺼번에 1억 5천만 개의 행을 스캔하게 되었습니다.
해결하는 데는 5분밖에 걸리지 않았습니다.
user_id에 단순 인덱스를 생성하는 대신, (user_id, status, created_at DESC)에 복합 인덱스(composite index)를 생성했습니다. 이를 통해 데이터베이스는 다음과 같은 작업을 수행할 수 있게 되었습니다:
user_id로 필터링.status로 필터링.- 가장 최신 행을 즉시 반환.
- 불필요한 정렬 단계 생략.
작업 중에 테이블이 잠기지 않도록 CREATE INDEX CONCURRENTLY를 사용했습니다.
수정 후:
- 쿼리 시간이 8,150ms에서 0.14ms로 단축되었습니다.
- API 지연 시간(latency)이 8초에서 12ms로 줄어들었습니다.
- CPU 사용률이 100%에서 8% 미만으로 떨어졌습니다.
배운 점:
- 로컬 테스트는 오해를 불러일 수 있습니다. 100개의 행은 100만 개를 대변하지 못합니다.
- 외래 키(foreign key)에 인덱스를 생성하세요. 대부분의 ORM은 이 과정을 생략합니다.
- 하드웨어를 추가로 구매하기 전에
EXPLAIN ANALYZE를 실행해 보세요. - 실제로 실행되는 쿼리를 중심으로 인덱스를 설계하세요.
서버를 먼저 확장하지 마세요. 쿼리를 최적화하세요.