커서 페이징 성능 트러블슈팅 - KimGyuBek/Threadly GitHub Wiki

부하 테스트를 위해 대량의 테스트 데이터를 적재한 환경에서 피드 조회 API GET /api/posts?limit=10을 호출했을 때 응답 시간이 과도하게 지연되었다.

k6를 이용한 측정 결과 p95, p99가 목표(5s, 10s) 대비 크게 초과하여, 커서 기반 피드 조회 쿼리에서 조회 성능 병목이 발생하고 있음을 확인했다.

[
  {
    "user_count": 20001,
    "post_count": 500000,
    "comment_count": 2000000,
    "post_like_count": 2997152
  }
]

{
  "api": "GET /api/posts?limit=10",
  "vus": 10,
  "duration": "10m",
  "metrics": {
    "p90": 5664.9186,
    "p95": 6135.550799999998,
    "p99": 7000.2734,
    "success_rate": 0.9983700081499592,
    "total_requests": 1227
  }
}

-- PostJpaRepository
select
--     생략
from posts p
         join users u on p.user_id = u.user_id
         join user_profile up on u.user_id = up.user_id
         left join user_profile_images upi on up.user_id = upi.user_id and upi.status = 'CONFIRMED'
         left join(select post_id, count(*) as like_count
                   from post_likes
                   group by post_id) pl_count on p.post_id = pl_count.post_id
         left join(select post_id, count(*) as comment_count
                   from post_comments
                   where status = 'ACTIVE'
                   group by post_id) pc_count on p.post_id = pc_count.post_id
         left join(select post_id,
                          true as liked
                   from post_likes
                   where user_id = :userId) pl_liked on p.post_id = pl_liked.post_id
where p.status = 'ACTIVE'
  and (
    cast(:cursorPostedAt as timestamp) is null
        or
    p.modified_at < :cursorPostedAt
        or (
        p.modified_at = :cursorPostedAt and p.post_id < :cursorPostId
        )
    )
order by p.modified_at DESC, p.post_id desc limit :limit;

EXPLAIN ANALYZE 결과를 보니, 최종적으로는 limit만큼만 반환하면서도 그 이전 단계에서 post_likes, post_comments에 대한 전체 스캔/집계가 선행된 뒤 마지막에 limit 만큼만 잘라내는 실행 계획이 나왔다.

즉, "상위 N건을 먼저좁히지 못한 채 집계 조인을 먼저 수행하는 구조라서" 대량의 데이터가 있는 환경에서는 느릴 수밖에 없었다.

기능 구현 당시에는 조회 결과가 나오기만 하면 된다는 관점으로 작성해, 대규모 데이터에서의 실행 계획과 비용을 고려하지 못한 점이 근본 원인이었다.

특히 아래 두 구간이 병목으로 잡혔다.

Seq Scan on post_likes post_likes_1  (cost=0.00..54948.52 rows=2997152 width=16) (actual time=0.264..235.268 rows=2997152 loops=3)
HashAggregate  (cost=69934.28..71479.96 rows=154568 width=24) (actual time=1136.231..1184.058 rows=411043 loops=3) Group Key: post_likes_1.post_id

Seq Scan on post_comments  (cost=0.00..63451.07 rows=2000006 width=16) (actual time=0.196..211.628 rows=2000000 loops=3)
HashAggregate  (cost=73451.10..74972.76 rows=152166 width=24) (actual time=669.959..693.071 rows=353479 loops=3) Group Key: post_comments.post_id

posts에서 limit 만큼 자르기 전에

LEFT JOIN 과정에서 post_likes(약 300만), post_comments(약 200만) 전체를 매 요청마다 순차 스캔(Seq Scan)하고 GROUP BY로 집계 테이블(pl_count, pc_count)를 통째로 생성한 뒤 다시 posts와 조인한다는 의미다.

결과적으로 limit 만큼의 피드를 조회하는 요청임에도 아래 작업이 요청마다 반복 수행되었다.

• 게시글 좋아요 전체 스캔 및 집계
• 게시글 댓글 전체 스캔 및 집계

테스트 데이터가 적을 떄는 체감이 없었지만, 데이터가 커지자 집계 비용이 폭발하면서 p95, p99 응답 지연으로 이어졌다.

정리하면, "상위 N건을 먼저 제한하지 못하고 전체 집계를 선행하는 쿼리 구조" 가 조회 성능 병목의 직접 원인이었다.

기존 실행 계획에서 post_likes, post_comments에 대한 Seq Scan과 HashAggregate 비용이 컸고, 커서 기반 정렬/필터가 효율적으로 동작하지 못했다.

따라서 조회 패턴에 맞춘 인덱스를 먼저 추가한 뒤, 인덱스 적용만으로만 성능이 얼마나 개선되는지 측정했다.

--- 게시글 목록 조회 최적화
create index if not exists idx_posts_active_modified_id
    on posts(modified_at desc, post_id desc) where status = 'ACTIVE';

-- 게시글별 좋아요 수 조회 최적화
create index if not exists idx_post_likes_post_id
    on post_likes(post_id);

-- 특정 사용자의 좋아요 유무 확인 최적화
create index if not exists idx_post_likes_user_post
    on post_likes(user_id, post_id);

-- 게시글별 댓글 수 집계 최적화
create index if not exists idx_post_comments_active_post
    on post_comments(post_id) where status = 'ACTIVE';

-- 통계 업데이트
analyze
posts, post_likes, post_comments;

{
  "api": "GET /api/posts?limit=10",
  "vus": 10,
  "duration": "10m",
  "metrics": {
    "p90": 5477.3666,
    "p95": 5937.704,
    "p99": 7115.244559999999,
    "success_rate": 1,
    "total_requests": 1269
  }
}

베이스라인에 비해 p95가 소폭 개선되었으나, p99는 유의미한 개선이 없었다.

원인은 인덱스의 부재가 아니라 쿼리 구조 자체이기 때문이다.

이는 쿼리가 limit을 적용하기 전에 post_likes, post_comments를 전체 범위로 먼저 스캔 및 집계한 결과를 만들어 둔 뒤 마지막에 posts 와 조인하고 limit 을 적용하는 흐름이라, 인덱스를 추가해도 병목이 줄지 않았다.

따라서

단순 인덱스 추가만으로는 성능 지표에 유의미한 변화가 없음을 확인했고, 근본 해결은 쿼리 재구성이 필요했다.

인덱스 추가만으로는 p95, p99 지표가 유의미하게 개선되지 않아, 쿼리 구조 자체를 튜닝했다.

핵심은 WITH page로 상위 N개의 posts를 먼저 확정한 뒤, 그 N건에 대해서만 좋아요/댓글 집계와 좋아요 여부 검증을 수행하도록 조회 흐름을 재구성하는 것이다.

기존 쿼리는 posts에서 limit 만큼만 가져오는 요청임에도 post_likes, post_comments 전체를 먼저 집계한 뒤 마지막에 limit을 적용하는 구조였다.

이 때문에 데이터가 커질수록 매 요청이 "전체 테이블을 조회"하는 작업이 되었고, p95, p99가 폭발했다.

이를 해결하기 위해 CTE에서 커서 조건 + 정렬 + limit을 먼저 적용해 상위 N개 posts만 확정했다.

• 요청이 limit=10이면, 이 단계에서 이미 10개만 추려진 상태로 다음 조인이 시작된다.
• 즉, 이후 모든 집계 및 조인은 "전체 posts가 아니라 page에 포함된 N건만" 대상으로 수행된다.

CTE로 N개를 확정한 뒤, 각 post에 대해 좋아요/댓글 집계를 행 단위로 필요한 만큼만 실행하도록 LEFT JOIN LATERAL을 사용했다.

• LATERAL JOIN은 외부 row의 컬럼 내부 서브쿼리에서 참조할 수 있는 조인이다.
• 따라서 pl.post_id = p.post_id 같은 조건을 서브 쿼리 내부에 직접 걸어 특정 post에 대해서만 집계할 수 있다.
• limit=N 이면 LATERAL 서브 쿼리는 N번 수행되지만, N은 10~20 수준이라, 기존처럼 수백만 건을 한 번에 스캔/집계하는 비용과는 비교가 되지 않는다.

기존: post_likes, post_comments 전체를 스캔 -> group by로 전체 집계 테이블 생성 -> posts와 조인 -> 마지막에 limit

개선: posts에서 커서 조건 + 정렬 + limit으로 N건 확정 -> 확정된 N건 각가에 대해서만 조건 집계 -> 결과 완성

기존에는

• 좋아요 수: pl_count 서브 쿼리(전체 GROUP BY)
• 좋아요 유무: pl_liked 서브 쿼리(특정 사용자 기준 조회)

를 서로 다른 서브쿼리로 따로 조인해서 가져왔다.

이를 아래처럼 하나의 LATERAL 서브 쿼리로 통합했다.

left join lateral (
    select count(*)                      as like_count, -- 좋아요 수
           bool_or(pl.user_id = :userId) as liked       -- 특정 사용자의 좋아요 유무
    from post_likes pl
    where pl.post_id = p.post_id
) pl on true

• where pl.post_id = p.post_id로 집계 범위를 현재 post 1건으로 제한한다.
• bool_or(pl.user_id = :userId)로 좋아요 여부를 집계 단계에서 같이 계산해, 별도 liked 조인이 사라졌다.

결과적으로

좋아요 관련 계산이 단인 한 번의 스캔으로 끝나는 구조가 됐다.

{
  "api": "GET /api/posts?limit=10",
  "vus": 10,
  "duration": "10m",
  "metrics": {
    "p90": 1009.2488000000001,
    "p95": 1050.9261,
    "p99": 1168.78883,
    "success_rate": 0.996742671009772,
    "total_requests": 3070
  }
}

• p95: 6135ms -> 1051ms
• p99: 7000ms -> 1169ms

동일한 테스트 조건에서 로 약 6배 향상 되었다.

이는 상위 N건(limit 범위)을 먼저 확정한 뒤, 그 범위에 대해서만 좋아요, 댓글을 집계하도록 조회 흐름을 재구성해 요청당 처리해야 할 데이터량과 집계 비용이 급감한 결과다.

쿼리 구조를 WITH page + LEFT JOIN LATERAL + bool_or로 재구성한 결과, 대용량 데이터 환경에서 피드 조회 성능이 크게 개선되었다.

동일 조건에서 p95, p99가 약 6배 향상, 요청당 불필요한 전체 집계 비용을 제거한 효과를 확인했다.

추가로 동시 요청 환경에서도 개선 효과가 유지되는지 확인하기 위해 VU를 증가시켜 부하 테스트를 수행했다.

{
  "api": "GET /api/posts?limit=10",
  "vus": 30,
  "duration": "5m",
  "metrics": {
    "p95": 2516.25535,
    "p99": 2845.45667,
    "success_rate": 1,
    "total_requests": 3150
  }
}

• VU=30 환경에서도 p95, p99가 안정적으로 유지되었다.
• 테스트를 실행한 맥북의 CPU 사용률이 한계(100%)에 도달해, 더 높은 VU는 측정 환경 특성상 진행하지 않았다.

즉, 이 쿼리 튜닝은

상위 N건을 먼저 확정하고 필요한 범위만 집계하는 조회 흐름으로 병목을 제거해,

대용량 데이터와 동시 요청 상황 모두에서 유의미한 성능 개선을 달성했다.

with page as (select p.post_id,
                     p.user_id,
                     p.content,
                     p.view_count,
                     p.modified_at
              from posts p
              where p.status = 'ACTIVE'
                and (
                  cast(:cursorPostedAt as timestamp) is null
                      or p.modified_at < :cursorPostedAt
                      or (p.modified_at = :cursorPostedAt
                      and p.post_id < :cursorPostId))
              order by p.modified_at desc, p.post_id desc
    limit :limit)
select p.post_id                     as postId,
       u.user_id                     as userId,
       upi.image_url                 as userProfileImageUrl,
       up.nickname                   as userNickname,
       p.content                     as content,
       p.view_count                  as viewCount,
       upi.image_url                 as userProfileImageUrl,
       coalesce(pl.like_count, 0)    as likeCount,
       coalesce(pc.comment_count, 0) as commentCount,
       coalesce(pl.liked, false)     as liked
from page p
         join users u on p.user_id = u.user_id
         join user_profile up on u.user_id = up.user_id
         left join user_profile_images upi on u.user_id = upi.user_id and upi.status = 'CONFIRMED'
         left join lateral (
    select count(*)                      as like_count,
           bool_or(pl.user_id = :userId) as liked
    from post_likes pl
    where pl.post_id = p.post_id
        ) pl on true
         left join lateral (
    select count(*) as comment_count
    from post_comments pc
    where pc.post_id = p.post_id
      and pc.status = 'ACTIVE'
        ) pc on true
order by p.modified_at desc, p.post_id desc;