인덱스 없는 컬럼이 어떻게 우리 데이터베이스를 망가뜨렸나
단 하나의 누락된 인덱스가 8ms짜리 API를 8초짜리 악몽으로 만들었습니다.
주요 릴리스 전에 부하 테스트를 진행했습니다. 로컬 개발 환경에서는 100개의 행(row)을 처리하는 데 아무런 문제가 없었습니다. 스테이징 환경에서는 150만 개의 행을 대상으로 500명의 사용자를 시뮬레이션했습니다.
모든 것이 무너졌습니다.
- API 응답 시간이 45ms에서 8,000ms로 급증했습니다.
- 데이터베이스 CPU 점유율이 100%에 도달했습니다.
- 커넥션 풀(Connection pools)이 고갈되었습니다.
- 요청이 타임아웃되었습니다.
원인은 user_id와 status로 주문 내역을 가져오는 단순한 쿼리였습니다.
PostgreSQL에서 EXPLAIN ANALYZE를 실행해 보니 순차 스캔(Sequential Scan)이 발생하고 있었습니다. user_id에 인덱스가 없었기 때문에, 엔진은 매 요청마다 150만 개의 행을 모두 읽어야 했습니다.
100개의 동시 요청이 들어오자 데이터베이스는 한 번에 1억 5천만 개의 행을 스캔했습니다.
해결하는 데는 5분밖에 걸리지 않았습니다. 테이블이 온라인 상태를 유지할 수 있도록 CREATE INDEX CONCURRENTLY를 사용하여 (user_id, status, created_at DESC)에 복합 인덱스를 생성했습니다.
결과:
- 쿼리 시간이 8,150ms에서 0.14ms로 단축되었습니다.
- API 지연 시간(latency)이 8초에서 12ms로 줄어들었습니다.
- CPU 사용률이 100%에서 8% 미만으로 떨어졌습니다.
교훈:
- 로컬 테스트는 오해를 불러일으킬 수 있습니다. 100개의 행은 수백만 개의 행을 대변할 수 없습니다.
- 외래 키(Foreign keys)에 인덱스를 생성하세요. 대부분의 ORM은 이 과정을 생략합니다.
EXPLAIN ANALYZE를 실행하세요. 데이터베이스가 문제 지점을 알려줍니다.- 하드웨어를 더 구매하기 전에 쿼리를 최적화하세요.
서버를 먼저 확장하지 마세요. 쿼리부터 최적화하세요.