통신사 B2B SaaS 어드민

백오피스 대시보드·통계·가입자 조회 화면에서 200만 회원 규모의 데이터를 처리하는 과정에 심각한 성능 저하가 발생했습니다. CROSS JOIN + 상관 서브쿼리 구조, 인덱스 부재, 순차 쿼리 실행 등 복합적인 원인을 EXPLAIN ANALYZE로 특정하고 쿼리 구조를 재설계하여 가입자 통계 API 6.6분 → 4초, 대시보드 조회 API 1.9분 → 1초, 가입자 조회 API 11초 → 0.2초로 개선했습니다.

1. 가입자 통계 API 성능 개선

블로그 — 3일 동안 쿼리 튜닝만 한 스토리 (1)

문제 상황

가입자 통계 화면 진입 시 6.6분 무응답 발생 (200만 회원 기준)

원인 분석

CROSS JOIN + 상관 서브쿼리 구조로 3,700만 회 서브쿼리 실행
- 전체 유저(200만) × 날짜(2일) CROSS JOIN 후, 각 행마다 상관 서브쿼리 2개 실행
- EXPLAIN ANALYZE 결과: loops=7,963,720 (43만 유저 기준)
날짜별 순차 루프로 DB 쿼리 약 40회 순차 실행 (화면 진입 시 API 2회 호출 × 날짜당 ~20회)
DATE(created_at + INTERVAL '9 hours') 함수 적용으로 인덱스 무력화 → 풀스캔 4회
항상 빈 결과를 반환하는 죽은 CTE 5개

해결 방식

스냅샷 + LATERAL: 기준일 총이용자를 1회 스냅샷으로 확보 → 이후는 변경 유저만 LATERAL로 조회
- O(전체유저 × 날짜) → O(변경유저 × 날짜) 로 스케일링 특성 변경
- 상관 서브쿼리 3,700만 회 → 0회
날짜 범위 조건 전환: DATE(컬럼) → created_at >= ... AND created_at < ... 으로 인덱스 활용
연속 날짜 그룹화: 누락 날짜를 연속 구간별로 묶어 SQL 호출 최소화
Batch INSERT: 날짜별 개별 INSERT → multi-row INSERT ... ON CONFLICT 1회

결과

기존: 6.6분 (200만 회원)
개선: 4초
향상률: 99% 개선

2. 대시보드 Summary API 성능 개선

블로그 — 3일 동안 쿼리 튜닝만 한 스토리 (2)

문제 상황

백오피스 대시보드 진입 시 1.9분 무응답 발생 (200만 회원 기준)

원인 분석

267줄 단일 CTE 쿼리에서 전체 유저 200만 명 대상 상관 서브쿼리 수백만 회 실행
- user_status_histories 전체 이력을 뒤져 현재 가입 상태를 계산 → users.join_type에 이미 저장된 값
- 30일 유지 고객 계산도 동일하게 이력에서 재계산 → users.updated_at으로 대체 가능
서로 의존성 없는 5개 쿼리를 순차 await로 실행 (총 응답시간 = 각 쿼리 시간의 합)
월별 누적 통계를 매번 전체 유저 × 5개월 CROSS JOIN으로 계산 → join_statistics_daily 캐시 테이블에 이미 데이터 존재

해결 방식

users 테이블 직접 조회: 현재 상태(join_type), 30일 유지(updated_at) → 상관 서브쿼리 수백만 회 → 0회
오늘 범위만 스캔: 오늘 상태 변경 이벤트는 user_status_histories의 오늘 범위(~12,000건)만 조회
캐시 테이블 활용: 월별 통계 → join_statistics_daily 캐시 조회로 전환
Promise.all 2레벨 병렬화: 5개 쿼리 순차 → 최대 9개 동시 실행 (커넥션 풀 크기 확인 후 적용)

결과

기존: 1.9분 (200만 회원 기준)
개선: 1초
향상률: 99% 개선

3. 가입자 조회 API 성능 개선

블로그 — 3일 동안 쿼리 튜닝만 한 스토리 (3)

문제 상황

가입자 관리 화면(페이지당 10건) 진입 시 11초 소요 (200만 회원 기준)

원인 분석

UNION ALL 전체를 서브쿼리로 감싼 뒤 외부에서 LIMIT 적용 → PostgreSQL이 LIMIT을 안쪽으로 push-down 불가, 전체 집계 후 10건만 반환
데이터 쿼리 + COUNT 쿼리를 동일한 UNION ALL + GROUP BY로 순차 2회 실행
(user_id, after_status, created_at) 복합 인덱스 부재 → LEFT JOIN + EXISTS 서브쿼리마다 이력 전체 스캔
LEFT JOIN + GROUP BY + MAX(CASE WHEN) 패턴으로 이력 전체가 JOIN 후 팽창 → GROUP BY로 축소

해결 방식

복합 인덱스 추가: (user_id, after_status, created_at DESC) — CONCURRENTLY 옵션으로 서비스 중단 없이 적용
Promise.all 병렬화: 데이터 쿼리 + COUNT 쿼리 동시 실행
LATERAL JOIN 전환: LEFT JOIN + GROUP BY → LEFT JOIN LATERAL로 유저당 필요한 값만 직접 조회 (행 팽창 제거)

결과

기존: 11초 (200만 회원 기준)
개선: 264ms
향상률: 97% 개선