postgreSQL 인덱스 전략

📄 PostgreSQL에서의 인덱스 전략을 통한 채팅 조회 성능 개선

• 채팅 조회 API의 성능 개선 과정에서 발견한 인덱스 전략과 그 효과를 정리했습니다.
• 예상과 달리 읽기와 쓰기 성능 모두에서 개선이 이루어진 과정을 분석했습니다.

🧩 배경 및 필요성

채팅 애플리케이션의 메시지 조회 API에서 성능 이슈가 발견되었습니다. 주요 조회 쿼리는 다음과 같았습니다:

SELECT *
FROM "Chatting"
WHERE "session_id" = $1
ORDER BY "chatting_id" DESC
LIMIT 20
OFFSET 10;

🗺️ 문제 해결 과정

1. 초기 상태 분석

300만 건의 테스트 데이터로 성능을 측정했을 때, Full Table Scan으로 인해 상당한 지연이 발생했습니다:

Limit  (cost=1000.45..63640.71 rows=20 width=100)
Execution Time: 5708.944 ms

2. 단일 인덱스 적용

먼저 조회 조건인 session_id에 단일 인덱스를 적용했습니다:

@@index([sessionId])

실행 계획은 다음과 같이 변경되었습니다:

Limit (cost=143.24..143.29 rows=20 width=100)
  -> Sort (cost=143.24..143.32 rows=35 width=100)
    -> Bitmap Heap Scan on "Chatting"
      -> Bitmap Index Scan on "Chatting_session_id_idx"
Planning: Buffers: shared hit=98 read=24
Planning Time: 23.577 ms
Execution Time: 2.029 ms

3. 복합 인덱스 도입

정렬 작업을 최적화하기 위해 복합 인덱스를 추가했습니다:

@@index([sessionId, chattingId(Sort.Desc)])

실행 계획이 단순화되었습니다:

Limit (cost=0.56..83.19 rows=20 width=100)
  -> Index Scan Backward using "Chatting_session_id_chatting_id_idx"
Planning Time: 9.548 ms
Execution Time: 2.418 ms

4. 쓰기 성능 영향 분석

일반적으로 인덱스 추가는 쓰기 성능을 저하시킬 것으로 예상되나, FK 제약조건 검사에 인덱스가 활용되어 오히려 성능이 개선되었습니다:

인덱스 적용 전:

"Insert on ""Chatting"" (actual time=9.861..9.864 rows=1 loops=1)
Trigger for constraint Chatting_session_id_fkey: time=31.544 calls=1
Planning Time: 0.764 ms
Execution Time: 46.132 ms

인덱스 적용 후:

"Insert on ""Chatting"" (actual time=0.131..0.132 rows=1 loops=1)
Trigger for constraint Chatting_session_id_fkey: time=0.232 calls=1
Planning Time: 0.027 ms
Execution Time: 3.416 ms

📈 결과 및 성과

성능 개선 결과:

1. 읽기 성능: 5708ms → 2ms로 개선
2. 쓰기 성능: 46ms → 3ms로 개선
3. 실행 계획: 4단계에서 2단계로 단순화

이러한 결과를 바탕으로 다음과 같은 인덱스 전략을 수립했습니다:

1. @@index([sessionId]): 기본 세션 조회용 인덱스
2. @@index([sessionId, chattingId(Sort.Desc)]): 정렬된 페이지네이션을 위한 최적화 인덱스

이번 성능 개선 과정을 통해 실제 측정을 통한 검증의 중요성을 재확인할 수 있었습니다. 특히 인덱스가 FK 제약조건 검사 성능에도 긍정적인 영향을 미친다는 점은 주목할 만한 발견이었습니다.