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" = $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

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

4. 쓰기 성능 영향 분석

일반적으로 인덱스 추가는 쓰기 성능을 저하시킬 것으로 예상되나, 이 경우 오히려 성능이 개선되었습니다. 이는 PostgreSQL이 외래 키(FK) 제약조건을 검사할 때 인덱스를 활용하기 때문입니다.

모든 INSERT 작업에서 PostgreSQL은 session_id가 Session 테이블의 유효한 id를 참조하는지, 그리고 create_user_token이 UserSessionToken 테이블의 유효한 token을 참조하는지 확인해야 합니다. 인덱스가 없을 경우 이 검사를 위해 전체 테이블 스캔이 필요할 수 있으나, 인덱스가 있으면 빠르게 검증이 가능합니다.

인덱스 적용 전:

"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

두 번째 줄의 "Trigger for constraint"는 PostgreSQL이 FK 제약조건을 검사하는 내부 트리거의 실행 정보를 보여줍니다. time=31.544는 이 FK 검사에 31.544 밀리초가 걸렸다는 의미이고, calls=1은 이 검사가 한 번 실행되었다는 의미입니다. 인덱스 적용 후에는 이 검사 시간이 0.232ms로 크게 감소했음을 확인할 수 있습니다.

📈 결과 및 성과

성능 개선 결과:

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

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

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

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