많은 REST API 엔드포인트에서는 page URL 파라미터를 사용하여 결과를 페이지네이션하는 오프셋 기반 페이지네이션을 사용합니다. 오프셋 페이지네이션은 선형으로 확장되기 때문에, 페이지 번호가 높을수록 데이터베이스 쿼리가 느려집니다. 이는 페이지 번호가 클 경우 데이터베이스 쿼리가 타임아웃될 수 있음을 의미합니다. 이 문제는 주로 서드파티 통합 및 스크립트가 시스템과 상호작용할 때 발생하며, 사용자가 의도적으로 높은 페이지 번호를 방문하는 경우는 드뭅니다.

오프셋 페이지네이션과 관련된 확장성 문제를 해결하는 가장 이상적인 방법은 키셋 페이지네이션으로 전환하는 것입니다. 그러나 이는 API의 호환성을 깨는 변경을 의미합니다. 임시적인 중간 조치로 Gitlab::Pagination::Offset::PaginationWithIndexOnlyScan 클래스를 사용할 수 있습니다. 이 최적화는 높은 OFFSET 값이 존재할 때 오프셋 페이지네이션 쿼리의 성능을 개선하는 데 특정 상황에서 도움이 될 수 있습니다. 성능 개선이란 쿼리가 향상된 쿼리 타이밍으로 선형 확장을 유지한다는 것을 의미하며, 타임아웃이 발생하더라도 훨씬 더 높은 page 번호에서 발생하게 됩니다.

최적화 사용 요구 사항#

이 최적화는 ORDER BY, OFFSET, LIMIT 절을 기반으로 레코드를 결정할 때 SELECT *를 호출하지 않고, 데이터베이스 I/O를 줄이기 위해 인덱스 온리 스캔을 사용하려고 시도합니다. 최적화를 사용하려면 키셋 페이지네이션과 동일한 요구 사항을 충족해야 합니다:

• ORDER BY 절이 있어야 합니다.

• ORDER BY 절이 하나의 데이터베이스 칼럼을 고유하게 식별해야 합니다.

올바른 예시, 기본 키 사용: ORDER BY id

• 잘못된 예시, created_at은 고유하지 않음: ORDER BY created_at

• 올바른 예시, 타이 브레이커가 있음: ORDER BY created_at, id

• 쿼리가 데이터베이스 인덱스로 잘 커버되어야 합니다.

최적화 클래스 사용 방법#

최적화 클래스는 ActiveRecord::Relation 객체와 함께 사용할 수 있으며, 결과로 최적화된 kaminari 페이지네이션 ActiveRecord::Relation 객체를 반환합니다. 최적화를 적용할 수 없는 경우에는 원래의 ActiveRecord::Relation 객체가 페이지네이션에 사용됩니다.

기본 사용법:

scope = Issue.where(project_id: 1).order(:id)
records = Gitlab::Pagination::Offset::PaginationWithIndexOnlyScan.new(scope: scope, page: 5, per_page: 100).paginate_with_kaminari
puts records.to_a

최적화는 항상 피처 플래그와 함께 배포해야 하며, 특정 조건이 충족될 때 최적화 사용을 타깃팅할 수도 있습니다.

# - Only apply optimization for large page number lookups
# - When label_names filter parameter is given, the optimization will not have effect (complex JOIN).
if params[:page] > 100 && params[:label_names].blank? && Feature.enabled?(:my_optimized_offet_query)
  Gitlab::Pagination::Offset::PaginationWithIndexOnlyScan.new(scope: scope, page: params[:page], per_page: params[:per_page]).paginate_with_kaminari
else
  scope.page(params[:page]).per(params[:per_page])
end

최적화 동작 방식#

최적화는 전달된 ActiveRecord::Relation 객체를 받아 CTE(Common Table Expression)로 이동시킵니다. CTE 내에서 원래 쿼리는 ORDER BY 칼럼만 선택하도록 변경됩니다. 이를 통해 데이터베이스가 인덱스 온리 스캔을 사용할 수 있게 됩니다.

쿼리가 실행되면, CTE 내의 쿼리가 먼저 평가되며 CTE는 선택된 칼럼이 포함된 LIMIT 수만큼의 행을 포함하게 됩니다. ORDER BY 값을 사용하여 LATERAL 쿼리가 전체 행을 하나씩 찾습니다. LATERAL 쿼리는 중첩 루프를 강제하기 위해 여기서 사용됩니다: CTE의 각 행에 대해 테이블에서 전체 행을 조회합니다.

원래 쿼리:

• 인덱스에서 OFFSET + LIMIT 수만큼의 항목을 읽습니다.

• 테이블에서 OFFSET + LIMIT 수만큼의 행을 읽습니다.

최적화된 쿼리:

• 인덱스에서 OFFSET + LIMIT 수만큼의 항목을 읽습니다.

• 테이블에서 LIMIT 수만큼의 행만 읽습니다.

최적화 효과 확인 방법#

높은 OFFSET 값(100_000)으로 데이터베이스 쿼리에 EXPLAIN (buffers, analyze)를 실행하면 최적화가 효과적인지 명확히 확인할 수 있습니다.

다음 사항을 확인하세요:

• 최적화된 쿼리 계획에는 인덱스 온리 스캔 노드가 있어야 합니다.

• 캐시된 버퍼 수와 타이밍을 비교했을 때 더 낮아야 합니다.

동일한 쿼리를 2~3회 실행하여 확인할 수 있습니다.

다음 쿼리를 고려해 보세요:

SELECT issues.*
FROM issues
ORDER BY id
OFFSET 100000
LIMIT 100

이 쿼리는 인덱스 스캔을 사용하는 실행 계획을 생성합니다:

 Limit  (cost=27800.96..27828.77 rows=100 width=1491) (actual time=138.305..138.470 rows=100 loops=1)
   Buffers: shared hit=73212
   I/O Timings: read=0.000 write=0.000
   ->  Index Scan using issues_pkey on public.issues  (cost=0.57..26077453.90 rows=93802448 width=1491) (actual time=0.063..133.688 rows=100100 loops=1)
         Buffers: shared hit=73212
         I/O Timings: read=0.000 write=0.000

Time: 143.779 ms
  - planning: 5.222 ms
  - execution: 138.557 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 73212 (~572.00 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

최적화된 쿼리:

WITH index_only_scan_pagination_cte AS MATERIALIZED
  (SELECT id
   FROM issues
   ORDER BY id ASC
   LIMIT 100
   OFFSET 100000)
SELECT issues.*
FROM
  (SELECT id
   FROM index_only_scan_pagination_cte) index_only_scan_subquery,
     LATERAL
  (SELECT issues.*
   FROM issues
   WHERE issues.id = index_only_scan_subquery.id
   LIMIT 1) issues

실행 계획:

 Nested Loop  (cost=2453.51..2815.44 rows=100 width=1491) (actual time=23.614..23.973 rows=100 loops=1)
   Buffers: shared hit=56167
   I/O Timings: read=0.000 write=0.000
   CTE index_only_scan_pagination_cte
     ->  Limit  (cost=2450.49..2452.94 rows=100 width=4) (actual time=23.590..23.621 rows=100 loops=1)
           Buffers: shared hit=55667
           I/O Timings: read=0.000 write=0.000
           ->  Index Only Scan using issues_pkey on public.issues issues_1  (cost=0.57..2298090.72 rows=93802448 width=4) (actual time=0.070..20.412 rows=100100 loops=1)
                 Heap Fetches: 1063
                 Buffers: shared hit=55667
                 I/O Timings: read=0.000 write=0.000
   ->  CTE Scan on index_only_scan_pagination_cte  (cost=0.00..2.00 rows=100 width=4) (actual time=23.593..23.641 rows=100 loops=1)
         Buffers: shared hit=55667
         I/O Timings: read=0.000 write=0.000
   ->  Limit  (cost=0.57..3.58 rows=1 width=1491) (actual time=0.003..0.003 rows=1 loops=100)
         Buffers: shared hit=500
         I/O Timings: read=0.000 write=0.000
         ->  Index Scan using issues_pkey on public.issues  (cost=0.57..3.58 rows=1 width=1491) (actual time=0.003..0.003 rows=1 loops=100)
               Index Cond: (issues.id = index_only_scan_pagination_cte.id)
               Buffers: shared hit=500
               I/O Timings: read=0.000 write=0.000

Time: 29.562 ms
  - planning: 5.506 ms
  - execution: 24.056 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 56167 (~438.80 MiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0