postgreSQL 인덱스 전략

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

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

🧩 배경 및 필요성

채팅 애플리케이션의 메시지 조회 API에서 성능 이슈가 발견되었습니다.

테이블 구조

채팅 메시지를 저장하는 "Chatting" 테이블의 구조는 다음과 같습니다:

CREATE TABLE "Chatting" (
    chatting_id INTEGER PRIMARY KEY,
    create_user_token TEXT NOT NULL,
    body TEXT NOT NULL,
    created_at TIMESTAMP(3) NOT NULL DEFAULT CURRENT_TIMESTAMP,
    session_id TEXT NOT NULL,

    FOREIGN KEY (create_user_token) REFERENCES "UserSessionToken"(token),
    FOREIGN KEY (session_id) REFERENCES "Session"(session_id)
);

이 테이블에서 session_id는 세션을 참조하는 외래 키(FK)로, 각 채팅 메시지가 어떤 세션에 속하는지를 나타냅니다.

조회 쿼리

주요 조회 쿼리는 다음과 같았습니다:

SELECT *
FROM "Chatting"
WHERE "session_id" = 'abcdefg' AND "chatting_id" < 100
ORDER BY "chatting_id" DESC
LIMIT 20;

🗺️ 문제 해결 과정

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 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

이전의 Bitmap Index Scan 방식은 조건에 맞는 레코드들의 위치를 비트맵으로 메모리에 저장한 후 이를 다시 정렬하는 과정이 필요했습니다. 반면 복합 인덱스를 통한 Index Scan Backward는 이미 정렬된 인덱스를 역순으로 직접 스캔하면서 필요한 레코드만 즉시 가져올 수 있어 추가적인 정렬 작업이 필요 없습니다. 이는 특히 LIMIT과 OFFSET이 있는 페이지네이션 쿼리에서 큰 성능 향상을 가져옵니다.

4. 쓰기 성능 영향 분석

일반적으로 인덱스 추가는 쓰기 성능의 저하를 일으킬 것으로 예상됩니다. 그러나 이 경우 쓰기 성능에 악영향을 크게 주지 않음을 확인했습니다.

다음과 같은 쿼리문을 토대로 인덱스가 없는 경우와 있는 경우에 대한 쓰기 성능을 비교했습니다.

EXPLAIN ANALYZE 
INSERT INTO "Chatting" (create_user_token, body, session_id)
VALUES (
    '17b1e554-3621-426f-bc73-04543ebb375c',
    'Hello, this is a test message',
    '7f6316be-e44a-4d2e-b004-862c3b3c5951'
);

#인덱스 없음
 Insert on "Chatting"  (cost=0.00..0.02 rows=0 width=0) (actual time=2.277..2.277 rows=0 loops=1)
   ->  Result  (cost=0.00..0.02 rows=1 width=108) (actual time=0.764..0.764 rows=1 loops=1)
 Planning Time: 1.746 ms
 Trigger for constraint Chatting_session_id_fkey: time=10.179 calls=1
 Trigger for constraint Chatting_create_user_token_fkey: time=2.048 calls=1
 Execution Time: 14.587 ms
(6 rows)

#복합 인덱스
 Insert on "Chatting"  (cost=0.00..0.02 rows=0 width=0) (actual time=3.773..3.773 rows=0 loops=1)
   ->  Result  (cost=0.00..0.02 rows=1 width=108) (actual time=0.483..0.483 rows=1 loops=1)
 Planning Time: 1.262 ms
 Trigger for constraint Chatting_session_id_fkey: time=6.868 calls=1
 Trigger for constraint Chatting_create_user_token_fkey: time=2.303 calls=1
 Execution Time: 12.999 ms
(6 rows)

인덱스가 없는 경우 약 14ms, 있는 경우는 약 13ms로 유의미한 차이가 발생하지 않았습니다.

이를 통해 인덱스가 쓰기 성능에 악영향을 주고있지 않다고 판단하였습니다.

📈 결과 및 성과

성능 개선 결과:

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

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

@@index([sessionId, chattingId(Sort.Desc)]): 정렬된 페이지네이션을 위한 최적화 인덱스

이번 성능 개선 과정을 통해 실제 측정을 통한 검증의 중요성을 재확인할 수 있었습니다.