• Tie-breaker 열
• 조인된 테이블 열로 정렬하기
• 필터링
  • 프로젝트별
  • 그룹별
    • 동일한 데이터베이스 테이블의 열
    • 특수 케이스: 기밀 플래그
    • 다른 데이터베이스 테이블의 열

페이지네이션 성능 가이드라인

다음 설명서는 페이지네이션(정렬) 성능을 향상시키는 몇 가지 아이디어를 제공합니다. 이것은 offset 및 keyset 페이지네이션 모두에 적용됩니다.

Tie-breaker 열

열을 정렬할 때에는 중복되지 않는 열로 정렬하는 것이 좋습니다. 다음 예를 고려해보세요:

만약 created_at으로 정렬한다면, 결과는 레코드가 디스크 상에 어떻게 위치하는지에 따라 달라질 수 있습니다.

데이터가 잘 정의된 인터페이스를 통해 노출되고 API와 같은 자동화된 프로세스에 의해 사용된다면 tie-breaker 열을 사용하는 것이 좋습니다. tie-breaker 열이 없다면 레코드의 순서가 변경될 수 있어서 디버그하기 어려운 문제를 야기할 수 있습니다:

• 행을 비교하여 변경사항을 결정하는 통합이 중단됨.
• E-tag 캐시 값이 변경되어 완전한 재다운로드를 필요로 함.

SELECT issues.* FROM issues ORDER BY created_at;

ORDER BY에 두 번째 열을 추가하여 이 문제를 해결할 수 있습니다:

SELECT issues.* FROM issues ORDER BY created_at, id;

이 변경으로 순서가 중복되지 않아 “안정적인” 정렬을 얻을 수 있습니다.

참고: 쿼리를 효율적으로 만들기 위해 두 열을 모두 포함하는 인덱스 (created_at, id)가 필요합니다. 열의 순서는 ORDER BY 절의 열과 일치해야 합니다.

조인된 테이블 열로 정렬하기

자주 발생하는 상황은 조인된 데이터베이스 테이블의 열을 기준으로 데이터를 정렬할 경우입니다. 다음 예시는 issues 레코드를 issue_metrics 테이블의 first_mentioned_in_commit_at 메트릭 열에 따라 정렬합니다:

SELECT issues.* FROM issues
INNER JOIN issue_metrics on issue_metrics.issue_id=issues.id
WHERE issues.project_id = 2
ORDER BY issue_metrics.first_mentioned_in_commit_at DESC, issues.id DESC
LIMIT 20
OFFSET 0

PostgreSQL 11 버전에서 플래너는 먼저 project_id 필터와 일치하는 모든 issues를 찾은 후 모든 issue_metrics 행을 조인합니다. 행의 정렬은 메모리에서 발생합니다. 조인된 관계가 항상 존재하는 경우(1:1 관계), 데이터베이스는 project_id 필터와 일치하는 행 수(총 행 수의 2배인 N * 2)를 읽습니다.

성능상의 이유로 ORDER BY 절에서 다른 테이블의 열을 섞는 것을 피해야 합니다.

특히 이러한 경우에는 쿼리를 개선하기 위한 간단한 방법(예: 인덱스 생성)이 없습니다. 일부는 issues.id 열을 issue_metrics.issue_id로 변경하면 쿼리가 성능이 떨어질 것으로 생각하지만, 이로 인해 데이터베이스가 issue_metrics 테이블의 모든 행을 처리해야 할 수 있기 때문에 오히려 더 나쁠 수 있습니다.

이 문제에 대한 한 가지 해결책은 정규화를 해제하는 것입니다. issue_metrics 테이블에 project_id 열을 추가하면 필터링 및 정렬을 효율적으로 수행할 수 있습니다:

SELECT issues.* FROM issues
INNER JOIN issue_metrics on issue_metrics.issue_id=issues.id
WHERE issue_metrics.project_id = 2
ORDER BY issue_metrics.first_mentioned_in_commit_at DESC, issue_metrics.issue_id DESC
LIMIT 20
OFFSET 0

참고: 이 쿼리는 다음 열 구성에 대한 issue_metrics 테이블의 인덱스를 필요로 합니다: (project_id, first_mentioned_in_commit_at DESC, issue_id DESC).

필터링

프로젝트별

프로젝트별로 필터링하는 것은 프로젝트 수준에서 많은 기능이 있는 매우 흔한 사용 사례입니다. 예시: 병합 요청, 이슈, 보드, 반복.

이러한 기능들은 기본 쿼리에 project_id에 대한 필터를 가지고 있습니다. 프로젝트의 이슈를 로딩하는 예시:

project = Project.find(5)

# 내부 ID로 정렬
issues = project.issues.order(:iid).page(1).per(20)

기본 쿼리를 효율적으로 만들기 위해 보통 project_id 열을 포함하는 데이터베이스 인덱스가 있습니다. 이는 데이터베이스가 스캔해야 하는 행 수를 크게 줄여줍니다. 인덱스가 없다면 데이터베이스는 (풀 테이블 스캔으로) 전체 issues 테이블을 읽게 됩니다.

project_id가 외래 키이므로 아마도 다음과 같은 인덱스가 사용 가능할 것입니다:

"index_issues_on_project_id" btree (project_id)

GitLab 13.11은 issues 테이블에 다음과 같은 인덱스 정의를 갖고 있습니다:

"index_issues_on_project_id_and_iid" UNIQUE, btree (project_id, iid)

이 인덱스는 데이터베이스 쿼리와 페이지네이션을 완전히 커버합니다.

그룹별

안타깝게도, 그룹 수준에서 정렬하고 페이지네이션하는 효율적인 방법은 없습니다. 데이터베이스 쿼리 실행 시간은 그룹 내 레코드 수에 따라 증가합니다.

실제로 그룹 수준이란 그룹과 하위 그룹을 모두 의미하는 경우 상황은 더 나빠집니다. 첫 번째 페이지를로드하려면 데이터베이스가 그룹 계층 구조를 찾아 모든 프로젝트를 찾고 그런 다음 모든 이슈를 조회합니다.

그룹 수준의 비효율적인 쿼리의 주된 이유는 데이터베이스 스키마가 설계된 방식입니다. 핵심 도메인 모델은 프로젝트와 관련되어 있으며, 프로젝트는 그룹과 관련됩니다. 이는 데이터베이스 구조가 나쁘다는 것을 의미하는 것이 아니라 효율적인 그룹 수준 쿼리에 최적화되지 않은 잘 정규화된 형태입니다. 장기적으로는 정규화를 해제할 필요성이 있을 수 있습니다.

예시: 그룹의 이슈 나열

group = Group.find(9970)

Issue.where(project_id: group.projects).order(:iid).page(1).per(20)

생성된 SQL 쿼리:

SELECT "issues".*
FROM "issues"
WHERE "issues"."project_id" IN
    (SELECT "projects"."id"
     FROM "projects"
     WHERE "projects"."namespace_id" = 5)
ORDER BY "issues"."iid" ASC
LIMIT 20
OFFSET 0

실행 계획을 보면 요청된 행(20)보다 훨씬 많은 행을 읽고 행이 메모리내에서 정렬되어 있는 것을 확인할 수 있습니다:

 Limit  (cost=10716.87..10716.92 rows=20 width=1300) (actual time=1472.305..1472.308 rows=20 loops=1)
   ->  Sort  (cost=10716.87..10717.03 rows=61 width=1300) (actual time=1472.303..1472.305 rows=20 loops=1)
         Sort Key: issues.iid
         Sort Method: top-N heapsort  Memory: 41kB
         ->  Nested Loop  (cost=1.00..10715.25 rows=61 width=1300) (actual time=0.215..1331.647 rows=177267 loops=1)
               ->  Index Only Scan using index_projects_on_namespace_id_and_id on projects  (cost=0.44..3.77 rows=19 width=4) (actual time=0.077..1.057 rows=270 loops=1)
                     Index Cond: (namespace_id = 9970)
                     Heap Fetches: 25
               ->  Index Scan using index_issues_on_project_id_and_iid on issues  (cost=0.56..559.28 rows=448 width=1300) (actual time=0.101..4.781 rows=657 loops=270)
                     Index Cond: (project_id = projects.id)
 Planning Time: 12.281 ms
 Execution Time: 1472.391 ms
(12 rows)

동일한 데이터베이스 테이블의 열

동일한 데이터베이스 테이블에 있는 열을 기준으로 필터링할 때는 인덱스를 추가하여 성능을 향상시킬 수 있습니다. 예를 들어, state_id 열에 의한 필터링을 지원하고 싶다면 다음과 같은 인덱스를 추가할 수 있습니다:

"index_issues_on_project_id_and_state_id_and_iid" UNIQUE, btree (project_id, state_id, iid)

Rails에서의 쿼리 예시:

project = Project.find(5)

# 내부 ID 순으로 정렬
issues = project.issues.opened.order(:iid).page(1).per(20)

SQL 쿼리:

SELECT "issues".*
FROM "issues"
WHERE
  "issues"."project_id" = 5
  AND ("issues"."state_id" IN (1))
ORDER BY "issues"."iid" ASC
LIMIT 20
OFFSET 0

위의 인덱스는 다음과 같은 프로젝트 레벨 질의를 지원하지 않습니다:

SELECT "issues".*
FROM "issues"
WHERE "issues"."project_id" = 5
ORDER BY "issues"."iid" ASC
LIMIT 20
OFFSET 0

특수 케이스: 기밀 플래그

issues 테이블에서는 이슈의 기밀 여부를 나타내는 부울 필드인 confidential가 있습니다. 이로 인해 기밀 처리된 이슈는 비회원 사용자에게는 보이지 않습니다.

예시 SQL 쿼리:

SELECT "issues".*
FROM "issues"
WHERE "issues"."project_id" = 5
AND "issues"."confidential" = FALSE
ORDER BY "issues"."iid" ASC
LIMIT 20
OFFSET 0

일반적으로 project_id, confidential, iid에 대한 인덱스를 추가하여 데이터베이스 질의를 개선하는 것이 유용하다고 생각할 수 있지만, 이 경우에는 아마도 그렇지 않습니다. 테이블의 데이터 분포를 기반으로 하면, 기밀 처리된 이슈는 드물기 때문에 그들을 필터링하는 것이 데이터베이스 질의를 크게 느리게 만들지 않을 수 있습니다. 데이터베이스는 약간의 추가 행을 읽을 수 있지만, 성능 차이는 최종 사용자에게 조차 눈에 띄지 않을 수도 있습니다.

반면, 기밀 처리된 이슈만 표시하는 특수 필터를 구현했다면, 인덱스가 필요합니다. 20개의 기밀 처리된 이슈를 찾는 것은 데이터베이스가 수백 개의 행을 스캔하거나, 최악의 경우에는 프로젝트의 모든 이슈를 스캔해야 할 수도 있습니다.

참고: 새로운 데이터베이스 인덱스를 도입할 때에는 데이터 분포와 테이블 접근 패턴(기능 작동 방식)을 고려해야 합니다. 올바른 결정을 내리기 위해 프로덕션 데이터를 샘플링하는 것이 필요할 수 있습니다.

다른 데이터베이스 테이블의 열

예시: 담당자별 이슈 필터링

project = Project.find(5)

project
  .issues
  .joins(:issue_assignees)
  .where(issue_assignees: { user_id: 10 })
  .order(:iid)
  .page(1)
  .per(20)

SELECT "issues".*
FROM "issues"
INNER JOIN "issue_assignees" ON "issue_assignees"."issue_id" = "issues"."id"
WHERE "issues"."project_id" = 5
  AND "issue_assignees"."user_id" = 10
ORDER BY "issues"."iid" ASC
LIMIT 20
OFFSET 0

예시 데이터베이스 (과도하게 간소화된) 실행 계획:

1. 데이터베이스는 SQL 쿼리를 구문 분석하고 JOIN을 감지합니다.
2. 데이터베이스는 쿼리를 두 개의 하위 쿼리로 분할합니다.
   • SELECT "issue_assignees".* FROM "issue_assignees" WHERE "issue_assignees"."user_id" = 10
   • SELECT "issues".* FROM "issues" WHERE "issues"."project_id" = 5
3. 데이터베이스는 각 쿼리를 실행하는 데 필요한 행 수와 비용을 추정합니다.
4. 데이터베이스는 먼저 가장 저렴한 쿼리를 실행합니다.
5. 쿼리 결과를 사용하여 다른 테이블의 행을 로드하고 JOIN 열을 사용하여 행을 더 필터링합니다.

특히 이 예제에서는 issue_assignees 쿼리가 먼저 실행될 것입니다.

GitLab 프로젝트에서 이 쿼리를 실행하면 다음과 같은 실행 계획이 생성됩니다:

 Limit  (cost=411.20..411.21 rows=1 width=1300) (actual time=24.071..24.077 rows=20 loops=1)
   ->  Sort  (cost=411.20..411.21 rows=1 width=1300) (actual time=24.070..24.073 rows=20 loops=1)
         Sort Key: issues.iid
         Sort Method: top-N heapsort  Memory: 91kB
         ->  Nested Loop  (cost=1.00..411.19 rows=1 width=1300) (actual time=0.826..23.705 rows=190 loops=1)
               ->  Index Scan using index_issue_assignees_on_user_id on issue_assignees  (cost=0.44..81.37 rows=92 width=4) (actual time=0.741..13.202 rows=215 loops=1)
                     Index Cond: (user_id = 4156052)
               ->  Index Scan using issues_pkey on issues  (cost=0.56..3.58 rows=1 width=1300) (actual time=0.048..0.048 rows=1 loops=215)
                     Index Cond: (id = issue_assignees.issue_id)
                     Filter: (project_id = 278964)
                     Rows Removed by Filter: 0
 Planning Time: 1.141 ms
 Execution Time: 24.170 ms
(13 rows)

이 쿼리는 먼저 assignees를 조회하여 user_id (user_id = 4156052)로 필터링하고 215개의 행을 찾습니다. 이 215개의 행을 사용하여 데이터베이스는 기본 키로 연결된 215개의 이슈 행을 찾습니다. project_id 열의 필터링은 인덱스로 지원되지 않음에 유의하세요.

대부분의 경우에는 운이 좋아서 조인된 관계가 많은 행을 반환하지 않기 때문에 비교적 효율적인 데이터베이스 쿼리가 되는 경우가 많습니다. 그러나 데이터베이스가 커지면 이러한 쿼리는 다르게 동작할 수 있습니다. 특정 사용자의 issue_assignees 레코드 수가 수백만 개인 경우를 가정해 보겠습니다. 이 조인 쿼리는 성능이 좋지 않을 뿐만 아니라 시간 초과될 가능성이 높습니다.

이와 유사한 문제가 두 번의 조인 쿼리로 발생하는 경우도 있을 수 있습니다. 예를 들면 Issue -> LabelLink -> Label(name=bug)과 같은 경우입니다.

이러한 문제를 해결하는 쉬운 방법은 없습니다. 데이터의 비정규화는 중요한 도움이 될 수 있지만, 부정적인 영향도 가지고 있습니다(데이터 중복 및 데이터 유지 관리).

issue_assignees 필터를 개선하기 위한 아이디어:

• issue_assignees 테이블에 project_id 열을 추가하여 JOIN 수행 시 추가 project_id 필터가 행을 추가로 필터링하도록 합니다. 정렬은 아마도 메모리에서 이루어집니다:

  SELECT "issues".*
  FROM "issues"
  INNER JOIN "issue_assignees" ON "issue_assignees"."issue_id" = "issues"."id"
  WHERE "issues"."project_id" = 5
    AND "issue_assignees"."user_id" = 10
    AND "issue_assignees"."project_id" = 5
  ORDER BY "issues"."iid" ASC
  LIMIT 20
  OFFSET 0
  

• issue_assignees 테이블에 iid 열을 추가합니다. 여기서 ORDER BY 열이 다르고, issues 테이블에서 project_id 필터가 없어졌음을 유의하세요:

  SELECT "issues".*
  FROM "issues"
  INNER JOIN "issue_assignees" ON "issue_assignees"."issue_id" = "issues"."id"
  WHERE "issue_assignees"."user_id" = 10
    AND "issue_assignees"."project_id" = 5
  ORDER BY "issue_assignees"."iid" ASC
  LIMIT 20
  OFFSET 0
  

이제 이 쿼리는 issue_assignees 레코드 수에 관계없이 잘 수행되지만, 그것에는 매우 높은 대가를 치르게 됩니다:

• 두 개의 열이 중복되어 데이터베이스 크기가 증가합니다.
• 두 열을 동기화 유지해야 합니다.
• 쿼리를 지원하기 위해 issue_assignees 테이블에 더 많은 인덱스가 필요합니다.
• 새로운 데이터베이스 쿼리는 담당자 검색에 매우 특정적이며 복잡한 백엔드 코드가 필요합니다.
  • 담당자가 사용자로 필터링되고 다른 열로 정렬되며, project_id 필터를 제거하는 등의 경우 등

참고: 현재 GitLab에서는 이러한 종류의 비정규화를 수행하지 않습니다.