피드로 돌아가기
Dev.toDatabase
원문 읽기
Postgres 인덱스 최적화로 API p95 응답 속도 400ms에서 40ms로 10배 개선
The 7 Postgres Indexes That Took My API From 400ms to 40ms
AI 요약
Context
800만 건의 데이터가 14개 테이블에 분산된 분석 대시보드 환경에서 p95 기준 400ms 이상의 지연 및 리포트 페이지 Timeout 발생. SQL 구문의 결함보다 대량의 데이터에 대한 Sequential Scan으로 인한 I/O 병목이 핵심 원인으로 분석됨.
Technical Solution
- pg_stat_statements를 통한 쿼리별 실행 시간 분석으로 전체 부하의 78%를 차지하는 4개 핵심 쿼리 식별
- Composite Index(user_id, created_at DESC) 적용으로 데이터 필터링 후 발생하는 정렬 부하 제거
- read_at IS NULL 조건의 Partial Index 설계를 통해 인덱스 크기를 94% 줄이며 캐시 효율 극대화
- INCLUDE 절을 활용한 Covering Index 구축으로 Heap Fetch를 제거하고 Index Only Scan 유도
- CREATE INDEX CONCURRENTLY 옵션 사용으로 서비스 중단 없는 인덱스 생성 및 쓰기 락 방지
- pg_stat_user_indexes 기반의 Index Hit Ratio 및 Bloat 모니터링 체계 구축을 통한 지속적 최적화
실천 포인트
1. pg_stat_statements로 전체 실행 시간의 높은 비중을 차지하는 'Hot Query' 우선 식별
2. EXPLAIN (ANALYZE, BUFFERS)로 Sequential Scan 및 Heap Fetch 발생 여부 검증
3. 빈번한 필터 조건이 특정 값(예: NULL)에 집중될 경우 Partial Index 검토
4. 조회 컬럼이 적고 빈도가 높은 쿼리는 INCLUDE 기반 Covering Index 적용
5. 주 단위로 Index Hit Ratio를 체크하여 Working Set의 메모리 적재 여부 확인