PostgreSQL JOIN 성능 이슈 파이썬에서 해결하기

배경

학습 종료라는 이벤트를 정의할 수 없는 문제에 따라 발생했던 수많은 중복 이벤트, 그리고 그 이벤트들을 어떻게든 처리하기 위해 ksql을 적용했었다.

 

2024.02.20 - [데이터 엔지니어링] - ksqlDB를 이용해 실시간 이벤트 스트리밍 최적화하기

 

하지만 결국 지속적인 비용문제와 대시보드의 사용성이 보통 주간 상담에 사용되기 때문에 실시간 처리의 효율성이 낮다고 판단했다. 이에 따라 실시간으로 학습상황을 집계하던 파이프라인은 에어플로우 주간 배치로 옮겨가게 되었다.

 

그러나 배치 작업으로 전환한 뒤에도 문제가 발생했다. 한번에 모아서 처리하기 때문에 더 이상 중복 문제는 발생하지 않았지만 일주일치를 처리하는데 시간이 매우 오래걸렸다. 특히 기존에 문제가 되었던 데이터베이스 Read 성능이 커리큘럼이 클수록, 학습 기록이 많을수록 극도로 저하되는 현상을 보였다. 주간 배치에 36시간이 걸리게 되었으며, 일부 사용자는 데이터를 조회하는데만 길면 2분 이상이 걸리는 경우도 발견했다.

 

실시간 처리에서는 RDS 스펙 적당히 조절하고, 이벤트가 밀리더라도 놔두면 트래픽이 없는 밤에 처리되고 해소되는 형태였는데, 배치 작업에서는 리소스를 혼자 너무 많이 점유해서 에어플로우에 있던 다른 DAG 까지 영향을 받기 시작했다. 작업시간이 24시간이 넘어가니까 다음날 데일리 배치 작업과 맞물리며 OOM 에러가 계속 났고, 그에 따른 DAG 실패로 대시보드가 제대로 업데이트 되지 않아 이슈 제보가 들어오기 시작했다. 그래서 근본적인 DB Read 문제를 파악하기 위해 삽질했던 경험기를 적어본다.

---

문제 파악

문제가 되었던 쿼리를 먼저 살펴보자.

SELECT
  학습테이블.curriculum_id,
  학습테이블.user_id,
  ...
FROM 학습테이블
INNER JOIN 경로테이블
  on 학습테이블.activity_id=경로테이블.activity_id
  and 학습테이블.unit_id = 경로테이블.unit_id
  and 학습테이블.module_id = 경로테이블.module_id
  and 학습테이블.curriculum_id = 경로테이블.curriculum_id
WHERE 학습테이블.curriculum_id = '{curriculum_id}'
  AND 학습테이블.user_id = {user_id}

경로테이블에는 학습한 내용의 풀 경로가 들어있는 테이블이고, 학습테이블은 학습을 진행할 때마다 지속적으로 업데이트 되는 테이블이다. 정확한 경로를 위해 이 두 테이블을 join 할때 반드시 저렇게 4개의 id key로 join 을 해줘야 한다. 여기서 사용된 테이블은 다음과 같은 규모를 가지고 있다.

• 학습테이블: 약 2억행, 290GB
• 경로테이블: 약 8억행, 385GB

각 유저별로 걸린 시간을 로그로 남겨 특별히 시간이 오래 걸리는 경우를 뽑아 왜 오래 걸리는지 체크해보았다. 기본적으로 각 테이블마다 결과값이 천개가 넘어가는 순간부터 30초 정도로 급속도로 느려졌고, 1분 이상 매우 느리게 걸리는 경우는 각 테이블 값이 3~4천개가 넘어가는 경우였다.

---

해결 선택지 검토

1. 캐싱 & 파티셔닝

 

제일 쉽게 생각할 수 있는 선택지. 하지만 우리의 경로테이블은 고정이 되어있지 않아 적용할 수 없었다. 실제로 수시로 업데이트가 일어나는 테이블로, 업데이트가 일어나면 기존 경로는 전부 아카이빙 되고 새로운 경로로 모든 값이 새로 생기게 되는 구조로 캐싱도 파티셔닝도 적용하기가 애매했다.

 

2. 서비스 DB(MySQL)에 있는 원본 경로 직접 사용

 

서비스 DB에 부하를 주고, 여기서도 마찬가지로 데이터 개수가 많을수록 조회에 몇십초씩 걸려서 안된다고 판단했다.

 

3. 쿼리 튜닝(feat: 복합키 인덱싱)

 

각각의 테이블 조회하면 빠른데, 복합키 조인이 들어가는 순간 급격히 느려졌다. 특히 수천건 수준에서 조회 성능이 이렇제 저하 되는게 아무리 생각해도 비정상적이라고 판단되어 쿼리 튜닝을 시도하기로 결정했다.

 

4. Spark 도입해 분산처리

 

이것도 고려해봤지만 데이터가 수십 수백만도 아니고 몇천건 수준이라서, 보다 근본적인 수준에서 해결해야지 분산처리 환경을 구성하는 것은 오버 엔지니어링이라는 생각이 들었다.

---

해결시도 - 복합 인덱스 생성

기존 인덱스 구성은 다음과 같다. 

• 단일 인덱스: curriculum_id
• 복합 인덱스: (activity_id, unit_id, module_id)

이를 기반으로 오래 걸리는 경우의 쿼리 분석을 보면 다음과 같다. 

쿼리 분석을 보니 다음과 같은 문제를 발견했다.

• 학습테이블: bitmap index scan을 통해 (curriculum_id, user_id) 조건을 걸어, 수천~수만 건을 가져옴.
• Nested Loop: 가져온 학습 레코드를 기준으로, 경로테이블에 있는 (activity_id, unit_id, module_id) 복합 인덱스를 매번 스캔.
• 문제: 이때 curriculum_id = '…'가 Filter로만 적용되고 인덱스 조건(Index Cond)에는 들어가지 않음.

이 말은, 경로테이블에서 activity_id, unit_id, module_id만으로 검색한 후, 매 건마다 replica_curriculum_id = '…'를 테이블 레벨에서 필터로 확인한다는 뜻이다. 만약 curriculum_id가 다른 레코드가 많다면, 필터로 제거되는 행(Rows Removed by Filter)이 수천~수만 건까지 늘어나, 조인 처리 시간이 길어지게 된다.

 

즉 학습테이블에서 수천건이 나왔을때 각 건마다 경로테이블 인덱스 스캔 → curriculum_id가 맞는지 필터로 하나하나 확인 → 필터로 버려지는 레코드가 많아질수록 불필요한 레코드를 반복적으로 읽게되서 디스크 I/O와 CPU 소모가 쌓여 시간 폭증

 

이를 해결하기 위해 모든 조인 조건을 포함하는 복합 인덱스 생성해보았다.

CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_join_all
ON public.경로테이블 (
    curriculum_id,
    activity_id,
    unit_id,
    module_id
)

하지만 이 새로운 인덱스는 크기가 매우커 PostgreSQL 플래너가 이를 활용하지 않고 기존의 인덱스를 계속 사용했다. 실제 크기를 조회해보니 테이블 크기가 385GB인데 4컬럼 복합 인덱스의 크기가 144GB로 인덱스가 테이블 크기의 1/3 차지했다.

 

PostgreSQL 에서는 직접적인 인덱스 힌트가 없어서 ANALYZE, 쿼리 변형, random_page_cost 같은 플래너 파라미터 조정 등 이것저것 시도해봤지만 (아마) 너무 큰 인덱스 탓에 생성한 복합 인덱스를 타지 않고 기존과 동일하게 작동했다. 심지어 커다란 인덱스를 만들었더니 insert가 체감되는 수준으로 느려지는게 느껴졌다. 그렇게 복합인덱스 적용은 사용하지 못하고 폐기했다.

---

최종 해결 방법 - 애플리케이션 레벨에서의 In-Memory Join

그때 떠오른 생각. DB레벨에서 최적화가 어렵다면 그냥 파이썬에서 대신 조인 작업을 하면 안될까?

 

메모리가 걱정되었지만 실시간이 아니고 배치 작업이어서 user당 한번만 실행하고 넘어가면 되고, 행 개수가 많아봐야 몇천 건 정도이기 때문에 메모리를 크게 잡아먹진 않을 것으로 판단했다. 그래서 기존 조인 쿼리를 학습/경로 테이블에서 각각 가져온 후 해시로 복합키 걸러내 값을 합치는 로직을 반영하고 테스트해보았다.

 

결과는 대성공.

 

메모리도 많이 먹지 않고, 101초 걸리던 시간이 0.3초로 미친듯이 단축되었다. 이로 인해 주간 업데이트가 너무 늦다는 운영팀의 일 업데이트 요구사항도 들어줄 수 있게 되었다. 주간 배치를 일간 배치로 변경하고도 1시간 이내로 종료되는 결과를 얻었다. 또한 자연스레 Airflow 상의 DAG 실행 및 리소스 문제가 해결되었다.

 

배치 로그에서 오래 걸리는 케이스를 추출해 비교한 표

	경로 조회	학습 조회	파이썬 join 시간	메모리(MB)	기존 로직 총 시간	파이썬 로직 총 시간
case 1	0.09초 1400개	0.08초 522개	0.0016초	100	32초	0.18초
case 2	0.19초 1586개	0.14초 1028개	0.029초	100	33초	0.35초
case 3	0.12초 3994개	0.2초 3754개	0.0098초	115	38초	0.44초
case 4	0.13초 3996개	0.15초 3996개	0.9초	116	101초	0.3초
case 5	0.13초 3996개	0.09초 4556개	0.0095초	117	97초	0.3초
case 6	0.17초 3996개	0.5초 4296개	0.016초	114	96초	0.7초

 

실행시간 로그 분석 비교 표

	기존	변경
총 실행 user 개수	3723개	3723개
평균 실행 시간	6.7252초	0.2482초
중앙값 실행 시간	2.7145초	0.1936초
최대 실행 시간	97.9127초	2.5295초
최소 실행 시간	0.0089초	0.0140초

 

 

속이 시원한 에어플로우 실행 시간

 

---

느낀점 

이 문제를 꼭 해결하고 싶어서 퇴근하고 맨날 따로 찾아보고 혼자 고민했는데 생각보다 간단(?)하게 처리되어서 약간 허무하면서도 실행시간을 보면 속이 시원하기도 하다. 실시간 작업 이후로 다른 프로젝트로 바빠서 약 일년 정도후에 해결을 하게 됐는데, 그래도 1년이란 시간을 보냈기 때문에 이렇게라도 해결을 할 수 있지 않았을까 싶다.

 

특히 인덱스를 적당히 만들어보기만 했지, 실제로 얼마나 스캔되고 있는지 pg_stat 을 조회해서 검색해 볼 수 있다는 것도 알게되었고, (이를 통해 사용하지 않는 인덱스를 제거하는 기회도 있었음) 또한 쿼리 플랜도 아직은 어렵지만 좀 더 자세하게 보는 방법에 대해 알 수 있었다. 

 

비슷한 문제를 겪고 있는 분들에게 도움이 되기를 바라며, DB 문제라고 DB 레벨에서만 해결할 생각을 하는게 아니라 데이터 규모 및 특성에 따라 다양한 해결방법이 있을수 있다는 교훈을 얻었다.