오프셋 페이지네이션 최적화

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

오프셋 페이지네이션과 관련된 확장성 문제를 처리하는 이상적인 방법은 키셋 페이지네이션으로 전환하는 것이지만, 이는 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

최적화는 항상 기능 플래그와 함께 롤아웃해야 하며, 특정 조건이 충족되었을 때 최적화를 사용할 수 있습니다.

# - 큰 페이지 번호 조회에 대해서만 최적화를 적용합니다.
# - label_names 필터 매개변수가 주어지면, 최적화는 효과가 없습니다(복잡한 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로 이동시킵니다. CTE 내에서 원래 쿼리는 ORDER BY 열만 선택하도록 변경됩니다. 이를 통해 데이터베이스가 인덱스 전용 스캔을 사용할 수 있게 됩니다.

쿼리가 실행되면 CTE 내의 쿼리가 먼저 평가되며, CTE는 선택된 열로 LIMIT 수의 행을 포함하게 됩니다.

ORDER BY 값을 사용하여 LATERAL 쿼리는 전체 행을 하나씩 찾습니다. LATERAL 쿼리는 CTE의 각 행에 대해 테이블에서 전체 행을 조회하도록 강제하기 위해 사용됩니다.

원래 쿼리:

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

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

최적화된 쿼리:

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

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

최적화가 도움이 되는지 확인하기

높은 (100,000) OFFSET 값을 가진 데이터베이스 쿼리에 대해 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