[뉴젯] 유저 지표에서 시작된 쿼리 성능 최적화

 

SW 마에스트로 과정 종료 이후, 뉴젯 서비스를 지속적으로 운영하며 그동안 쌓였던 기술적 부채들을 하나씩 해소하며 서비스를 개선하고 있습니다.

 

이번 글에서는 데이터이스 성능 최적화를 진행했던 과정을 기록하였습니다. 고객이 가장 많이 이탈하는 지점에서 쿼리 성능을 측정하고, 이를 다양한 실험을 통해 최적의 방식을 도입하여 문제를 해결한 경험에 대해 자세히 소개합니다.

 

---

문제 상황: 쿼리 성능 문제로 인한 고객 이탈

뉴젯 서비스를 운영하며 고객 행동 데이터를 분석하기 위해 GA를 활용해 사용자 데이터를 추적하고 있습니다. 특히 고객 이탈율을 낮추기 위해 GA 보고서를 활용해 사용자 이탈 지점을 찾아 원인을 분석하여 이를 해결하고자 하였습니다.

분석 결과 아래와 같이 사용자들은 앱이 로딩되는 시점인 스플래시 화면에서 약 13%의 유저가 이탈하는 것을 알 수 있었습니다.

 

현재 스플래시 화면은 아래와 같이 구성되어 있고, 해당 시점에 화면 탭 구성에 필요한 모든 데이터를 조회하기 때문에 평균적으로 1초 ~ 2초 정도의 시간이 소모되었습니다.

 

Supabase에서 제공하는 Query Performance Advisor를 통해 쿼리별 실행 시간을 확인할 수 있다는 내용을 듣고, 해당 지표를 확인한 결과..!

스플래시 화면에서 호출하는 캘린더 아티클 목록 조회 쿼리('get_monthly_article_with_image')가 현재까지 소요된 전체 쿼리 실행시간의 약 40%를 차지한다는 것을 알 수 있었습니다. 앱에 접속한 모든 유저가 해당 쿼리를 호출하기 때문에 실행 횟수가 많고, 쿼리의 긴 수행 시간이 스플래시 로딩 시간에도 큰 영향을 끼칠 것이라고 판단하여, 해당 쿼리 성능 최적화를 진행하였습니다.

 

---

문제 정의: 쿼리 병목 지점 분석

'get_monthly_article_with_image' 함수는 아래와 같이 사용자의 userId와 년도, 월을 입력받아서 사용자가 해당 월에 받은 뉴스레터 전체 목록을 정렬하여 반환하는 형태로 쿼리가 작성되어 있습니다.

SELECT 
    a.id,
    a.from_name,
    a.title,
    a.is_read,
    a.created_at,
    n.image_url
  FROM 
    article a
  LEFT OUTER JOIN 
    newsletter n 
  ON
    (a.mailling_list IS NOT NULL AND a.mailling_list = n.mailling_list)
    OR (a.from_domain = n.domain)
  WHERE 
    a.to_user_id = uid
    AND EXTRACT(YEAR FROM a.created_at) = year
    AND EXTRACT(MONTH FROM a.created_at) = month
  ORDER BY
    a.created_at DESC;

 

article 테이블에는 현재 17만개의 데이터가 저장되어 있습니다. 하지만 매월 50,000개가 넘는 데이터들이 추가로 쌓이고 있기 때문에 쿼리의 성능은 시간이 지날수록 점점 떨어질 것이라고 예상하였고, article 데이터가 100만개가 되었을 시 쿼리 성능을 분석하여 병목 지점을 찾고자 하였습니다.

 

아를 위해 테스트용 데이터베이스에 article 데이터 100만개를 추가하고, PostgreSQL의 EXPLAIN ANALYZE 키워드를 활용해 해당 쿼리의 성능을 측정하는 실험을 진행하였습니다.

Sort  (cost=33833.93..33833.93 rows=1 width=151) (actual time=2451.309..2520.075 rows=608 loops=1)
  Sort Key: a.created_at DESC
  Sort Method: quicksort  Memory: 167kB
  ->  Nested Loop Left Join  (cost=1000.00..33833.92 rows=1 width=151) (actual time=2413.205..2515.094 rows=608 loops=1)
    Join Filter: (((a.mailling_list IS NOT NULL) AND (a.mailling_list = n.mailling_list)) OR (a.from_domain = n.domain))
    Rows Removed by Join Filter: 87553"
    ->  Gather  (cost=1000.00..33817.29 rows=1 width=87) (actual time=2408.838..2477.677 rows=608 loops=1)
      Workers Planned: 1
      Workers Launched: 1
      ->  Parallel Seq Scan on article a  (cost=0.00..32817.19 rows=1 width=87) (actual time=2398.993..2400.435 rows=304 loops=2)
        Filter: ((to_user_id = '333e3c1c-b28a-4836-a42a-d02c846d2c10'::uuid) AND (EXTRACT(year FROM created_at) = '2024'::numeric) AND (EXTRACT(month FROM created_at) = '11'::numeric))
        Rows Removed by Filter: 500351
      ->  Seq Scan on newsletter n  (cost=0.00..14.45 rows=145 width=144) (actual time=0.003..0.044 rows=145 loops=608)
Planning Time: 8.260 ms
Execution Time: 2520.251 ms

 

실행 결과 총 2.5초의 시간이 소요되었고, 아래와 같은 순서로 SQL문이 실행된다는 것을 파악할 수 있었습니다.

 

1. article 테이블 병렬 스캔으로 필터링 조건(userId, 년, 달) 적용

2. newsletter 테이블 전체 스캔

3. article의 필터링된 결과와 newsletter 테이블은 nested Loop left join 실행

4. article.created_at 필드를 기준으로 내림차순 정렬

 

이 중 가장 많은 시간이 소요된 지점은 1번 article 테이블을 병렬 스캔하는 부분으로 이 쿼리에서만 약 2.4초의 시간이 소모된다는 것을 알 수 있었습니다. 현재는 year, month 조건으로 데이터를 필터링하기 위해 모든 데이터를 순회하며 값을 비교하고 있습니다. 따라서 이를 쿼리 수정 및 Index 사용을 통해 성능을 향상시키고자 하였습니다.

문제 정의: 많은 데이터를 가진 article 테이블에서 데이터 필터링 조건 적용 시, index를 사용할 수 없어 쿼리 응답 속도가 느리다.

 

---

문제 해결

1. EXTRACT 대신 make_date 범위 조건을 사용하도록 쿼리 변경

가장 먼저 article 테이블에 range scan을 적용하기 위하여 extract 대신 make_date를 사용해 범위 조건으로 데이터를 필터링 하도록 하였습니다.

  WHERE 
    a.to_user_id = '333e3c1c-b28a-4836-a42a-d02c846d2c10'
    AND created_at >= make_date(2024, 11, 1)
    AND created_at < make_date(2024, 11, 1) + INTERVAL '1 month'

 

2. user_id, created_at 필드를 활용한 복합 인덱스 생성

이후 article 테이블에서 to_user_id, created_at 필드의 복합 인덱스를 생성하여 range scan이 가능하도록 하였습니다.

CREATE INDEX idx_article_to_user_id_created_at
ON article (to_user_id, created_at desc);

 

쿼리 실행 분석 결과 아래와 같이 Index scan을 활용한 것을 확인하였습니다.

Nested Loop Left Join  (cost=0.42..19.27 rows=1 width=151) (actual time=0.122..31.097 rows=608 loops=1)
 ...
  ->  Index Scan using idx_article_to_user_id_created_at on article a  (cost=0.42..2.65 rows=1 width=87) (actual time=0.066..0.377 rows=608 loops=1)
        Index Cond: ((to_user_id = '333e3c1c-b28a-4836-a42a-d02c846d2c10'::uuid) AND (created_at >= '2024-11-01'::date) AND (created_at < '2024-12-01 00:00:00'::timestamp without time zone))
  ->  Seq Scan on newsletter n  (cost=0.00..14.45 rows=145 width=144) (actual time=0.001..0.033 rows=145 loops=608)

Execution Time: 31.189 ms

 

한 사람이 한달에 받을 수 있는 뉴스레터의 개수는 평균적으로 100개입니다. 가장 많은 뉴스레터를 구독한 사람의 경우에도 한달에 받는 뉴스레터의 총 개수는 608개로 데이터의 전체 크기에 비해 매우 작습니다. 이로 인해 페이지 이동으로 인한 Disk I/O 비용이 상대적으로 작아, Index Scan을 사용하여도 실행 시간에 큰 무리가 없습니다. 또한 데이터의 순서가 created_at 필드로 내림차순 정렬된 것이 보장되기 때문에 sorting 로직도 사라진 것을 확인할 수 있었습니다.

 

 

반면 created_at 필드로만 index를 생성하였을 때는 sort 로직도 사라지지 않고, Bitmap Index Scan과 Bitmap Heap Scan이 발생한 것을 알 수 있습니다. 

Sort ...
->  Bitmap Heap Scan on article a  (cost=1084.91..22091.92 rows=1 width=87) (actual time=71.924..72.151 rows=608 loops=1)
    Recheck Cond: ((created_at >= '2024-11-01'::date) AND (created_at < '2024-12-01 00:00:00'::timestamp without time zone))
    Filter: (to_user_id = '333e3c1c-b28a-4836-a42a-d02c846d2c10'::uuid)
    Rows Removed by Filter: 82587
    Heap Blocks: exact=19320
    ->  Bitmap Index Scan on idx_article_created_at  (cost=0.00..1084.90 rows=83258 width=0) (actual time=17.221..17.221 rows=83195 loops=1)
        Index Cond: ((created_at >= '2024-11-01'::date) AND (created_at < '2024-12-01 00:00:00'::timestamp without time zone))
        
Execution Time: 1383.335 ms

 

article 테이블의 pk는 random_uuid가 설정되어 있기 때문에 기본적으로 시간순으로 정렬되어 있지 않습니다. 따라서 TID로 조회한 데이터에 직접 접근하기 위해서 페이지를 이동할 가능성이 매우 높습니다.

 

이때, index scan을 하게 되면 날짜 필터링 이후 조회된 83195개의 데이터 모두에 대해 user_id 일치 여부를 확인해야 합니다. 하지만 index의 정렬 방식과 실제 데이터의 정렬이 일치하지 않기 때문에 많은 페이지를 조회하며 이동해야 하고, 중복된 페이지에 접근해야 하는 경우도 많아져 시간이 오래 걸린다는 문제가 발생합니다.

 

반면 Bitmap Scan 과 Bitmap Heap Scan을 진행하면 중복된 페이지 접근 없어 Disk I/O 시간을 절약할 수 있습니다. 따라서 Optimizer에서 Index Scan이 아닌 Bitmap Scan & Bitmap Heap Scan 방식을 활용해 잦은 페이지 이동으로 인한 I/O 시간을 줄이는 방식을 택하였습니다.

Index Scan
Index가 설정된 데이터를 조회할 때 조건과 일치하는 데이터의 TID를 찾아서 하나하나 조회하는 방식입니다.

Bitmap Index Scan
Index를 통해 조건과 일치하는 데이터의 TID를 Bitmap에 기록합니다.

Bitmap Heap Scan
Bitmap을 기반으로 가져올 데이터의 페이지를 읽고 해당 페이지에서 가져올 데이터를 한번에 가져옵니다.

 

하지만 index를 사용하게 되면 테이블 쓰기 쿼리 성능이 낮아지기 때문에 데이터 1,000개를 article 테이블에 동시 추가 하는 실험을 진행하여 쓰기 성능을 측정하였습니다.

 

	복합 인덱스	단일 인덱스	인덱스 없음 (기존)
조회 시간	31ms	1383ms	2520ms
쓰기 시간	1770ms	1840ms	533ms

 

측정 결과 위와 같이 user_id 와 created_at 복합키를 사용하였을 때 기존 방식 대비 읽기성능은 약 81배 향상, 쓰기 성능은 약 3배 저하 되는 것을 확인하였습니다.

 

뉴젯 서비스의 특성상 데이터베이스 쓰기 작업에 비해 유저에게 보여지는 읽기 작업은 빠르게 실행하여 유저 경험 개선이 필요하였습니다. 또한 비슷한 데이터가 한번에 쓰여지는 경우가 많기 때문에 쓰기 작업은 PostgreSQL의 Shared Buffer 메모리 캐시를 통해 성능이 점차 개선되는 것을 확인하였기 때문에 복합 인덱스를 적용하는 방식을 선택하였습니다. 지속적인 모니터링을 통해 쓰기 작업 지연 문제가 커져 메일 수신 속도가 느려진다면 application 단의 비동기 처리나 파티셔닝과 같은 방식을 적용하여 응답 시간을 개선하는 작업을 수행할 계획입니다.

 

---

참고자료

https://ksh-coding.tistory.com/159