느린 쿼리를 "해결"하려고 인덱스 20개를 추가했더니 — 데이터베이스가 3배 더 느려졌다

6개월 전, 저는 운영 트래픽을 감당하지 못해 허덕이는 PostgreSQL 데이터베이스를 물려받았습니다. API 응답 시간은 8초에 달했습니다. 사용자들은 타임아웃(timeout)을 겪고 있었고, 운영 팀은 새벽 2시에 호출을 받고 있었습니다.

그래서 저는 소위 "경험 있는" 개발자라면 누구나 할 법한 행동을 했습니다. 바로 인덱스(indexes)를 추가하는 것이었습니다. 아주 많이 말이죠.

12개의 테이블에 걸쳐 20개의 인덱스를 추가했습니다. 문제가 해결되었을까요?

아니요. 데이터베이스는 오히려 더 느려졌습니다. 쓰기 작업(write operations)은 기어갔고, 디스크 사용량(disk usage)은 급증했습니다. 그리고 제가 최적화하려고 했던 쿼리들은? 여전히 느렸습니다.

여기서 제가 인덱스 튜닝(index tuning)에 대해 혹독하게 배운 점과, 현재 실제로 효과를 보고 있는 프로세스를 공유합니다.

모든 사람이 저지르는 실수

인덱스에 대한 가장 큰 오해는 이것입니다: 인덱스가 많을수록 = 쿼리가 빨라진다.

PostgreSQL은 모든 쓰기 작업 시마다 모든 인덱스를 유지 관리해야 합니다. 인덱스를 추가하면 모든 INSERT, UPDATE, DELETE 작업이 더 무거워집니다. 20개의 추가 인덱스로 인해, 우리의 쓰기 집약적인(write-heavy) 분석 테이블은 데이터를 저장하는 시간보다 인덱스를 업데이트하는 데 더 많은 시간을 소비하게 되었습니다.

하지만 진짜 치명적이었던 것은 제가 예상치 못한 것이었습니다: 바로 **인덱스 비대화 (index bloat)**였습니다.

실제로 무엇이 잘못되었나

1. 낮은 카디널리티 (Low-Cardinality) 컬럼에 인덱스를 생성함

저는 pending, active, suspended, deleted라는 단 4가지 값만 가질 수 있는 status 컬럼에 인덱스를 걸었습니다.

PostgreSQL의 쿼리 플래너(query planner)는 해당 인덱스를 검토한 후, 각 값이 행(rows)의 약 25%와 일치한다는 것을 확인하고 전체 테이블 스캔(full table scan)이 더 저렴하다고 판단했습니다. 그 인덱스는 디스크 공간과 쓰기 성능만 낭비할 뿐, 읽기 측면에서는 아무런 이득을 주지 못하는 무거운 짐이었습니다.

2. 중복된 인덱스를 생성함

저는 다음과 같은 인덱스들을 가지고 있었습니다:

• CREATE INDEX idx_user_email ON users(email)
• CREATE INDEX idx_user_email_name ON users(email, name)

두 번째 인덱스는 이미 email 단독 쿼리를 포함(cover)하고 있습니다. 첫 번째 인덱스는 순전히 중복이었습니다. PostgreSQL은 본질적으로 동일한 조회를 위해 두 개의 인덱스를 유지 관리하고 있었습니다.

3. 부분 인덱스 (Partial Indexes)를 무시함

우리의 orders 테이블에는 수백만 개의 행이 있었지만, 90%는 completed 또는 cancelled 상태였습니다. 느린 쿼리들은 모두 status = 'pending'을 찾고 있었습니다. 다음과 같은 부분 인덱스 (Partial Index)를 사용했다면 어땠을까요:

CREATE INDEX idx_orders_pending 
ON orders(created_at, customer_id) 
WHERE status = 'pending';

이 작은 인덱스(테이블의 10% 규모)는 전체 테이블 인덱스보다 5배 더 뛰어난 성능을 보였습니다.

해결책: 체계적인 인덱스 감사 (Index Audit)

피해를 복구하고 실제로 최적화하기 위해 제가 따랐던 과정은 다음과 같습니다:

1단계: 사용되지 않는 인덱스 찾기

SELECT 
  schemaname,
  tablename,
...

이를 통해 마지막 통계 (stats) 초기화 이후 한 번도 사용되지 않은 14개의 인덱스를 찾아냈습니다. 저는 즉시 그것들을 삭제했습니다.

2단계: 진짜 느린 쿼리 찾기

추측하는 대신, pg_stat_statements를 사용했습니다:

SELECT 
  query,
  calls,
...

이를 통해 어떤 쿼리가 실제로 CPU 시간을 소모하고 있는지 확인할 수 있었습니다. 제가 느리다고 가정했던 쿼리가 아니라, 실제로 느린 쿼리들을 찾아낸 것입니다.

3단계: EXPLAIN ANALYZE 사용하기

모든 느린 쿼리에 대해, 실제 실행 계획 (execution plan)을 확인하기 위해 EXPLAIN ANALYZE를 실행했습니다. EXPLAIN이 아니라 EXPLAIN ANALYZE입니다. 차이점은 EXPLAIN ANALYZE가 실제로 쿼리를 실행하고 실제 타이밍 데이터를 보여준다는 점입니다.

제가 발견한 사실은 다음과 같습니다: PostgreSQL은 인덱스가 있는 테이블임에도 불구하고 순차 스캔 (sequential scan)을 수행하고 있었는데, 이는 제 쿼리 조건이 인덱스 컬럼 순서와 일치하지 않았기 때문이었습니다.

4단계: 적절한 크기의 인덱스 구축하기

실제로 차이를 만들어낸 세 개의 인덱스는 다음과 같습니다:

-- 실제 WHERE + ORDER BY 패턴과 일치하는 복합 인덱스 (Composite index)
CREATE INDEX idx_analytics_date_type 
ON analytics(event_date, event_type) 
...

결과

지표	감사 전	감사 후
총 인덱스 수	47	18
...

제가 지금 따르는 규칙

먼저 EXPLAIN ANALYZE를 실행하지 않고는 절대로 인덱스를 추가하지 마세요.

모든 인덱스는 그것이 가속화하도록 설계된 특정 쿼리가 있어야 합니다. 특정 쿼리를 지목하여 실행 전후의 실행 계획을 보여줄 수 없다면, 인덱스를 생성하지 마세요.

인덱스 (Indexes)는 "혹시 모르니까" 만드는 것이 아닙니다. 그것은 정교한 수술 도구입니다. 도구답게 사용하십시오.

최적화를 시도하다가 오히려 데이터베이스를 더 느리게 만든 적이 있나요? 당신의 경각심을 깨운 계기는 무엇이었나요?