이 문서는 ActiveRecord/Arel 또는 raw SQL 쿼리로 SQL 쿼리를 작성할 때 따라야 하는 여러 가이드라인을 설명합니다.

LIKE 구문 사용#

데이터를 검색하는 가장 일반적인 방법은 LIKE 구문을 사용하는 것입니다. 예를 들어 제목이 Draft:로 시작하는 모든 이슈를 가져오려면 다음 쿼리를 작성합니다:

SELECT *
FROM issues
WHERE title LIKE 'Draft:%';

PostgreSQL에서 LIKE 구문은 대소문자를 구분합니다. 대소문자를 구분하지 않는 LIKE를 수행하려면 ILIKE를 대신 사용해야 합니다.

이를 자동으로 처리하려면 raw SQL 프래그먼트 대신 Arel로 LIKE 쿼리를 작성해야 합니다. Arel은 PostgreSQL에서 자동으로 ILIKE를 사용합니다.

Issue.where('title LIKE ?', 'Draft:%')

대신 다음과 같이 작성합니다:

Issue.where(Issue.arel_table[:title].matches('Draft:%'))

여기서 matches는 사용 중인 데이터베이스에 따라 올바른 LIKE / ILIKE 구문을 생성합니다.

여러 OR 조건을 연결해야 한다면 Arel로도 다음과 같이 할 수 있습니다:

table = Issue.arel_table

Issue.where(table[:title].matches('Draft:%').or(table[:foo].matches('Draft:%')))

PostgreSQL에서는 다음을 생성합니다:

SELECT *
FROM issues
WHERE (title ILIKE 'Draft:%' OR foo ILIKE 'Draft:%')

LIKE와 인덱스#

PostgreSQL은 와일드카드가 앞에 오는 LIKE / ILIKE를 사용할 때 인덱스를 사용하지 않습니다. 예를 들어 다음 쿼리는 인덱스를 사용하지 않습니다:

SELECT *
FROM issues
WHERE title ILIKE '%Draft:%';

ILIKE의 값이 와일드카드로 시작하기 때문에 데이터베이스는 인덱스 스캔을 어디서 시작해야 할지 알 수 없어 인덱스를 사용할 수 없습니다.

다행히 PostgreSQL은 해결책을 제공합니다. trigram Generalized Inverted Index(GIN) 인덱스입니다. 이러한 인덱스는 다음과 같이 생성할 수 있습니다:

CREATE INDEX [CONCURRENTLY] index_name_here
ON table_name
USING GIN(column_name gin_trgm_ops);

여기서 핵심은 GIN(column_name gin_trgm_ops) 부분입니다. 이는 연산자 클래스가 gin_trgm_ops로 설정된 GIN 인덱스를 생성합니다. 이러한 인덱스는 ILIKE / LIKE에서 사용할 수 있으며 성능을 크게 향상시킬 수 있습니다. 이러한 인덱스의 단점 하나는 (인덱싱되는 데이터의 양에 따라) 크기가 상당히 커질 수 있다는 점입니다.

이러한 인덱스의 이름을 일관되게 유지하려면 다음 명명 패턴을 사용합니다:

index_TABLE_on_COLUMN_trigram

예를 들어 issues.title에 대한 GIN/trigram 인덱스의 이름은 index_issues_on_title_trigram이 됩니다.

이러한 인덱스는 빌드하는 데 상당한 시간이 걸리므로 동시에 빌드해야 합니다. CREATE INDEX 대신 CREATE INDEX CONCURRENTLY를 사용하면 됩니다. 동시 인덱스는 트랜잭션 안에서 생성할 수 없습니다. 마이그레이션의 트랜잭션은 다음 패턴으로 비활성화할 수 있습니다:

class MigrationName < Gitlab::Database::Migration[2.1]
  disable_ddl_transaction!
end

예를 들어 다음과 같습니다:

class AddUsersLowerUsernameEmailIndexes < Gitlab::Database::Migration[2.1]
  disable_ddl_transaction!

  def up
    execute 'CREATE INDEX CONCURRENTLY index_on_users_lower_username ON users (LOWER(username));'
    execute 'CREATE INDEX CONCURRENTLY index_on_users_lower_email ON users (LOWER(email));'
  end

  def down
    remove_index :users, :index_on_users_lower_username
    remove_index :users, :index_on_users_lower_email
  end
end

데이터베이스 칼럼을 안정적으로 참조하기#

ActiveRecord는 기본적으로 쿼리한 데이터베이스 테이블의 모든 칼럼을 반환합니다. 경우에 따라 반환되는 행을 사용자 지정해야 할 수 있습니다. 예를 들어 다음과 같습니다:

• 데이터베이스에서 반환되는 데이터 양을 줄이기 위해 일부 칼럼만 지정합니다.
• JOIN 관계의 칼럼을 포함합니다.
• 계산을 수행합니다(SUM, COUNT).

다음 예시에서는 칼럼은 지정하지만 그 테이블은 지정하지 않습니다:

• projects 테이블의 path
• merge_requests 테이블의 user_id

쿼리는 다음과 같습니다:

# bad, avoid
Project.select("path, user_id").joins(:merge_requests) # SELECT path, user_id FROM "projects" ...

이후 새 기능이 projects 테이블에 user_id 칼럼을 추가합니다. 배포 중에는 데이터베이스 마이그레이션은 이미 실행되었지만 새 버전의 애플리케이션 코드는 아직 배포되지 않은 짧은 시간대가 있을 수 있습니다. 이 기간에 위 쿼리가 실행되면 쿼리는 다음 오류 메시지와 함께 실패합니다: PG::AmbiguousColumn: ERROR: column reference "user_id" is ambiguous

이 문제는 데이터베이스에서 속성을 선택하는 방식 때문에 발생합니다. user_id 칼럼은 users 테이블과 merge_requests 테이블에 모두 있습니다. 쿼리 플래너는 user_id 칼럼을 조회할 때 어느 테이블을 사용해야 할지 결정할 수 없습니다.

사용자 지정 SELECT 구문을 작성할 때는 테이블 이름과 함께 칼럼을 명시적으로 지정하는 것이 좋습니다.

올바른 방법 (권장)#

Project.select(:path, 'merge_requests.user_id').joins(:merge_requests)

# SELECT "projects"."path", merge_requests.user_id as user_id FROM "projects" ...

Project.select(:path, :'merge_requests.user_id').joins(:merge_requests)

# SELECT "projects"."path", "merge_requests"."id" as user_id FROM "projects" ...

Arel(arel_table)을 사용하는 예시입니다:

Project.select(:path, MergeRequest.arel_table[:user_id]).joins(:merge_requests)

# SELECT "projects"."path", "merge_requests"."user_id" FROM "projects" ...

raw SQL 쿼리를 작성할 때는 다음과 같습니다:

SELECT projects.path, merge_requests.user_id FROM "projects"...

raw SQL 쿼리를 파라미터화하는 경우(이스케이프가 필요한 경우)는 다음과 같습니다:

"""
SELECT
  #{Gitlab::Database.quote_table_name('projects')}.#{Gitlab::Database.quote_column_name('path')},
  #{Gitlab::Database.quote_table_name('merge_requests')}.#{Gitlab::Database.quote_column_name('user_id')}
FROM ...
"""

잘못된 방법 (피해야 함)#

Project.select('id, path, user_id').joins(:merge_requests).to_sql

# SELECT id, path, user_id FROM "projects" ...

Project.select("path", "user_id").joins(:merge_requests)
# SELECT "projects"."path", "user_id" FROM "projects" ...

# or

Project.select(:path, :user_id).joins(:merge_requests)
# SELECT "projects"."path", "user_id" FROM "projects" ...

칼럼 목록이 주어지면 ActiveRecord는 인자를 projects 테이블에 정의된 칼럼과 일치시키고 테이블 이름을 자동으로 앞에 붙입니다. 이 경우 id 칼럼은 문제가 되지 않지만 user_id 칼럼은 예상치 못한 데이터를 반환할 수 있습니다:

Project.select(:id, :user_id).joins(:merge_requests)

# Before deployment (user_id is taken from the merge_requests table):
# SELECT "projects"."id", "user_id" FROM "projects" ...

# After deployment (user_id is taken from the projects table):
# SELECT "projects"."id", "projects"."user_id" FROM "projects" ...

pluck으로 ID 가져오기#

ActiveRecord의 pluck으로 값 집합을 메모리에 로드해 다른 쿼리의 인자로만 사용하는 것은 매우 주의해야 합니다. 일반적으로 쿼리 로직을 PostgreSQL 밖의 Ruby로 옮기는 것은 해롭습니다. PostgreSQL의 쿼리 최적화 도구는 원하는 작업에 대한 컨텍스트를 상대적으로 더 많이 가질 때 더 잘 동작하기 때문입니다.

어떤 이유로 pluck한 결과를 단일 쿼리에서 사용해야 한다면 대부분의 경우 materialized CTE가 더 나은 선택입니다:

WITH ids AS MATERIALIZED (
  SELECT id FROM table...
)
SELECT * FROM projects
WHERE id IN (SELECT id FROM ids);

이렇게 하면 PostgreSQL이 값을 내부 배열로 pluck합니다.

피해야 할 pluck 관련 실수는 다음과 같습니다:

• 쿼리에 너무 많은 정수를 전달합니다. 명시적인 제한은 없지만 PostgreSQL에는 수천 개의 ID라는 실질적인 인자 개수 한도가 있습니다. 이 한도에 부딪히는 상황은 피해야 합니다.
• 로깅 인프라에 문제를 일으킬 수 있는 거대한 쿼리 텍스트를 생성합니다.
• 실수로 테이블 전체를 스캔합니다. 예를 들어 다음 코드는 불필요한 데이터베이스 쿼리를 추가로 실행하고 불필요한 데이터를 많이 메모리에 로드합니다:

projects = Project.all.pluck(:id)

MergeRequest.where(source_project_id: projects)

대신 성능이 훨씬 나은 서브쿼리를 사용할 수 있습니다:

MergeRequest.where(source_project_id: Project.all.select(:id))

pluck을 선택할 만한 구체적인 이유는 다음과 같습니다:

• 실제로 Ruby 자체에서 값을 다루어야 합니다. 예를 들어 값을 파일에 쓰는 경우입니다.
• 값이 여러 관련 쿼리에서 재사용되도록 캐시되거나 메모이제이션됩니다.

CodeReuse/ActiveRecord cop에 맞추어 pluck(:id)나 pluck(:user_id) 같은 형태는 모델 코드 안에서만 사용해야 합니다. 앞의 경우에는 ApplicationRecord가 제공하는 .pluck_primary_key 헬퍼 메서드를 대신 사용할 수 있습니다. 뒤의 경우에는 해당 모델에 작은 헬퍼 메서드를 추가해야 합니다.

pluck을 사용할 강한 이유가 있다면 pluck하는 레코드 수를 제한하는 것이 합리적일 수 있습니다. MAX_PLUCK의 기본값은 ApplicationRecord에서 1_000입니다. 어떤 경우에도 서브쿼리 사용을 먼저 검토하고 pluck을 사용하는 것이 확실히 더 나은 선택인지 확인해야 합니다.

ApplicationRecord에서 상속#

GitLab 코드베이스의 대부분 모델은 ActiveRecord::Base가 아니라 ApplicationRecord 또는 Ci::ApplicationRecord에서 상속해야 합니다. 이렇게 하면 헬퍼 메서드를 쉽게 추가할 수 있습니다.

데이터베이스 마이그레이션에서 만든 모델은 이 규칙의 예외입니다. 이러한 모델은 애플리케이션 코드와 분리되어야 하므로 마이그레이션 컨텍스트에서만 사용할 수 있는 MigrationRecord를 계속 상속해야 합니다.

UNION 사용#

UNION은 대부분의 Rails 애플리케이션에서 그다지 흔히 쓰이지 않지만 매우 강력하고 유용합니다. 쿼리는 관련 데이터나 특정 기준에 따른 데이터를 얻기 위해 JOIN을 많이 사용하는 경향이 있지만, JOIN 성능은 다루는 데이터가 늘어날수록 빠르게 나빠질 수 있습니다.

예를 들어 이름에 특정 값이 포함된 프로젝트나 네임스페이스 이름에 특정 값이 포함된 프로젝트의 목록을 얻으려 할 때 대부분은 다음 쿼리를 작성합니다:

SELECT *
FROM projects
JOIN namespaces ON namespaces.id = projects.namespace_id
WHERE projects.name ILIKE '%gitlab%'
OR namespaces.name ILIKE '%gitlab%';

큰 데이터베이스에서는 이 쿼리를 실행하는 데 800밀리초 정도가 쉽게 걸립니다. UNION을 사용하면 대신 다음과 같이 작성합니다:

SELECT projects.*
FROM projects
WHERE projects.name ILIKE '%gitlab%'

UNION

SELECT projects.*
FROM projects
JOIN namespaces ON namespaces.id = projects.namespace_id
WHERE namespaces.name ILIKE '%gitlab%';

이 쿼리는 완전히 같은 레코드를 반환하면서도 완료까지 15밀리초 정도만 걸립니다.

그렇다고 모든 곳에서 UNION을 사용해야 한다는 뜻은 아닙니다. 다만 쿼리에서 JOIN을 많이 사용하고 조인된 데이터를 기준으로 레코드를 걸러 낼 때 고려할 사항입니다.

GitLab에는 여러 ActiveRecord::Relation 객체의 UNION을 만들 수 있는 Gitlab::SQL::Union 클래스가 있습니다. 이 클래스는 다음과 같이 사용할 수 있습니다:

union = Gitlab::SQL::Union.new([projects, more_projects, ...])

Project.from("(#{union.to_sql}) projects")

FromUnion 모델 concern은 위와 같은 결과를 만드는 더 편리한 메서드를 제공합니다:

class Project
  include FromUnion
  ...
end

Project.from_union(projects, more_projects, ...)

UNION은 코드베이스 전반에서 흔히 쓰이지만, 다른 SQL 집합 연산자인 EXCEPT와 INTERSECT도 사용할 수 있습니다:

class Project
  include FromIntersect
  include FromExcept
  ...
end

intersected = Project.from_intersect(all_projects, project_set_1, project_set_2)
excepted = Project.from_except(all_projects, project_set_1, project_set_2)

UNION 서브쿼리의 불균등한 칼럼#

UNION 쿼리의 SELECT 절에 칼럼 수가 서로 다르면 데이터베이스는 오류를 반환합니다. 다음 UNION 쿼리를 살펴봅니다:

SELECT id FROM users WHERE id = 1
UNION
SELECT id, name FROM users WHERE id = 2
end

이 쿼리는 다음 오류 메시지를 반환합니다:

each UNION query must have the same number of columns

이 문제는 눈에 잘 드러나고 개발 중에 쉽게 고칠 수 있습니다. 한 가지 예외 사례는 UNION 쿼리가 ActiveRecord 스키마 캐시에서 가져온 목록으로 칼럼을 명시적으로 나열하는 방식과 결합되는 경우입니다.

예시(나쁨, 피해야 함):

scope1 = User.select(User.column_names).where(id: [1, 2, 3]) # selects the columns explicitly
scope2 = User.where(id: [10, 11, 12]) # uses SELECT users.*

User.connection.execute(Gitlab::SQL::Union.new([scope1, scope2]).to_sql)

이 코드를 배포해도 즉시 문제가 생기지는 않습니다. 다른 개발자가 users 테이블에 새 데이터베이스 칼럼을 추가하면 이 쿼리는 프로덕션에서 깨지고 다운타임을 일으킬 수 있습니다. 두 번째 쿼리(SELECT users.*)에는 새로 추가된 칼럼이 포함되지만 첫 번째 쿼리에는 포함되지 않습니다. column_names 메서드는 오래된 값을 반환합니다(새 칼럼이 빠져 있습니다). 값이 ActiveRecord 스키마 캐시에 캐시되어 있기 때문입니다. 이 값은 보통 애플리케이션이 부팅할 때 채워집니다.

이 시점에서 유일한 해결책은 스키마 캐시가 갱신되도록 애플리케이션을 완전히 재시작하는 것입니다. 다만 스키마 캐시는 자동으로 재설정되어 이후 쿼리는 성공합니다. 이 재설정은 ops 기능 플래그 reset_column_information_on_statement_invalid를 비활성화해 끌 수 있습니다.

항상 SELECT users.*를 사용하거나 항상 칼럼을 명시적으로 정의하면 이 문제를 피할 수 있습니다.

SELECT users.*를 사용하는 경우:

# Bad, avoid it
scope1 = User.select(User.column_names).where(id: [1, 2, 3])
scope2 = User.where(id: [10, 11, 12])

# Good, both queries generate SELECT users.*
scope1 = User.where(id: [1, 2, 3])
scope2 = User.where(id: [10, 11, 12])

User.connection.execute(Gitlab::SQL::Union.new([scope1, scope2]).to_sql)

칼럼 목록을 명시적으로 정의하는 경우:

# Good, the SELECT columns are consistent
columns = User.cached_column_list # The helper returns fully qualified (table.column) column names (Arel)
scope1 = User.select(*columns).where(id: [1, 2, 3]) # selects the columns explicitly
scope2 = User.select(*columns).where(id: [10, 11, 12]) # uses SELECT users.*

User.connection.execute(Gitlab::SQL::Union.new([scope1, scope2]).to_sql)

생성일(created_at) 기준 정렬#

요약하면, 기능에 문제가 생긴다고 확신하지 않는 한 ORDER BY created_at보다 ORDER BY id를 우선해야 합니다.

created_at 기준으로 정렬된 데이터를 제공하려는 요구는 사용자 쪽에서 흔히 나옵니다. 페이지네이션된 표 보기와 페이지네이션된 API에서는 최신 항목을 먼저 (또는 가장 오래된 항목을 먼저) 보려는 경우가 많습니다. 그래서 보통 쿼리에 ORDER BY created_at DESC LIMIT 20 같은 절을 추가하고 싶어집니다. 이 쿼리를 추가하면 created_at에 인덱스(또는 다른 필터링 요건에 따라 복합 인덱스)를 추가해야 합니다. 인덱스를 추가하면 비용이 따릅니다. 게다가 created_at은 보통 고유 칼럼이 아니므로 이를 기준으로 정렬하고 페이지네이션하면 불안정하며, 여기에도 적절한 인덱스를 갖춘 정렬 동점 처리 칼럼 (예를 들어 ORDER BY created_at, id)을 추가해야 합니다.

그러나 대다수 기능에서 사용자는 ORDER BY id가 자신에게 필요한 것을 충분히 대체한다고 느낍니다. id 기준 정렬이 created_at 기준 정렬과 정확히 같다는 것이 기술적으로 항상 참은 아니지만, 충분히 가깝습니다. 그리고 created_at은 사용자가 직접 제어하는 경우가 거의 없다는 점 (즉 내부 구현 세부 사항이라는 점)을 고려하면, 사용자가 이 두 칼럼의 차이를 실제로 신경 쓰는 경우는 거의 없습니다.

따라서 id 기준 정렬에는 최소한 3가지 이점이 있습니다:

1. 기본 키이므로 이미 인덱싱되어 있으며, 다른 필터링이나 정렬 파라미터가 없는 단순한 쿼리에는 이것으로 충분할 수 있습니다.
2. 복합 인덱스가 필요한 경우 btree (namespace_id, id) 같은 인덱스가 btree (namespace_id, created_at, id)보다 작습니다.
3. 고유하므로 정렬과 페이지네이션에 안정적입니다.

WHERE IN 대신 WHERE EXISTS 사용#

WHERE IN과 WHERE EXISTS는 같은 데이터를 만들 수 있지만 가능하면 WHERE EXISTS를 사용하는 것이 권장됩니다. 많은 경우 PostgreSQL이 WHERE IN을 상당히 잘 최적화하지만, WHERE EXISTS가 (훨씬) 더 나은 성능을 내는 경우도 많습니다.

Rails에서는 SQL 프래그먼트를 만들어 사용해야 합니다:

Project.where('EXISTS (?)', User.select(1).where('projects.creator_id = users.id AND users.foo = X'))

그러면 다음과 같은 쿼리가 생성됩니다:

SELECT *
FROM projects
WHERE EXISTS (
    SELECT 1
    FROM users
    WHERE projects.creator_id = users.id
    AND users.foo = X
)

.exists? 쿼리의 쿼리 플랜 뒤집기 문제#

Rails에서 ActiveRecord 스코프에 .exists?를 호출하면 쿼리 플랜 뒤집기 문제가 발생해 데이터베이스 명령문 시간 초과로 이어질 수 있습니다. 검토용 쿼리 플랜을 준비할 때는 ActiveRecord 스코프가 만드는 기반 쿼리의 모든 변형을 확인하는 것이 좋습니다.

예시: 그룹과 그 하위 그룹에 에픽이 있는지 확인합니다.

# Similar queries, but they might behave differently (different query execution plan)

Epic.where(group_id: group.first.self_and_descendant_ids).order(:id).limit(20) # for pagination
Epic.where(group_id: group.first.self_and_descendant_ids).count # for providing total count
Epic.where(group_id: group.first.self_and_descendant_ids).exists? # for checking if there is at least one epic present

.exists? 메서드를 호출하면 Rails는 액티브 레코드 스코프를 다음과 같이 수정합니다:

• select 칼럼을 SELECT 1로 바꿉니다.
• 쿼리에 LIMIT 1을 추가합니다.

IN 쿼리가 포함된 것처럼 복잡한 ActiveRecord 스코프를 호출하면 데이터베이스 쿼리 계획 동작이 부정적으로 바뀔 수 있습니다.

실행 계획:

Epic.where(group_id: group.first.self_and_descendant_ids).exists?

Limit  (cost=126.86..591.11 rows=1 width=4)
  ->  Nested Loop Semi Join  (cost=126.86..3255965.65 rows=7013 width=4)
        Join Filter: (epics.group_id = namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)])
        ->  Index Only Scan using index_epics_on_group_id_and_iid on epics  (cost=0.42..8846.02 rows=426445 width=4)
        ->  Materialize  (cost=126.43..808.15 rows=435 width=28)
              ->  Bitmap Heap Scan on namespaces  (cost=126.43..805.98 rows=435 width=28)
                    Recheck Cond: ((traversal_ids @> '{9970}'::integer[]) AND ((type)::text = 'Group'::text))
                    ->  Bitmap Index Scan on index_namespaces_on_traversal_ids_for_groups  (cost=0.00..126.32 rows=435 width=0)
                          Index Cond: (traversal_ids @> '{9970}'::integer[])

플래너가 40만 행이 넘는 읽기를 예상하는 index_epics_on_group_id_and_iid 인덱스의 Index Only Scan에 주목합니다.

exists? 없이 쿼리를 실행하면 다른 실행 계획이 나옵니다:

Epic.where(group_id: Group.first.self_and_descendant_ids).to_a

실행 계획:

Nested Loop  (cost=807.49..11198.57 rows=7013 width=1287)
  ->  HashAggregate  (cost=807.06..811.41 rows=435 width=28)
        Group Key: namespaces.traversal_ids[array_length(namespaces.traversal_ids, 1)]
        ->  Bitmap Heap Scan on namespaces  (cost=126.43..805.98 rows=435 width=28)
              Recheck Cond: ((traversal_ids @> '{9970}'::integer[]) AND ((type)::text = 'Group'::text))
              ->  Bitmap Index Scan on index_namespaces_on_traversal_ids_for_groups  (cost=0.00..126.32 rows=435 width=0)
                    Index Cond: (traversal_ids @> '{9970}'::integer[])
  ->  Index Scan using index_epics_on_group_id_and_iid on epics  (cost=0.42..23.72 rows=16 width=1287)
        Index Cond: (group_id = (namespaces.traversal_ids)[array_length(namespaces.traversal_ids, 1)])

이 쿼리 플랜에는 MATERIALIZE 노드가 없고, 그룹 계층을 먼저 로드해 더 효율적인 액세스 방법을 사용합니다.

쿼리 플랜 뒤집기는 아주 작은 쿼리 변경으로도 실수로 발생할 수 있습니다. 그룹 ID 데이터베이스 칼럼을 다르게 선택하는 .exists? 쿼리를 다시 살펴봅니다:

Epic.where(group_id: group.first.select(:id)).exists?

Limit  (cost=126.86..672.26 rows=1 width=4)
  ->  Nested Loop  (cost=126.86..1763.07 rows=3 width=4)
        ->  Bitmap Heap Scan on namespaces  (cost=126.43..805.98 rows=435 width=4)
              Recheck Cond: ((traversal_ids @> '{9970}'::integer[]) AND ((type)::text = 'Group'::text))
              ->  Bitmap Index Scan on index_namespaces_on_traversal_ids_for_groups  (cost=0.00..126.32 rows=435 width=0)
                    Index Cond: (traversal_ids @> '{9970}'::integer[])
        ->  Index Only Scan using index_epics_on_group_id_and_iid on epics  (cost=0.42..2.04 rows=16 width=4)
              Index Cond: (group_id = namespaces.id)

여기서 다시 더 나은 실행 계획을 볼 수 있습니다. 쿼리를 조금만 변경하면 다시 뒤집힙니다:

Epic.where(group_id: group.first.self_and_descendants.select('id + 0')).exists?

Limit  (cost=126.86..591.11 rows=1 width=4)
  ->  Nested Loop Semi Join  (cost=126.86..3255965.65 rows=7013 width=4)
        Join Filter: (epics.group_id = (namespaces.id + 0))
        ->  Index Only Scan using index_epics_on_group_id_and_iid on epics  (cost=0.42..8846.02 rows=426445 width=4)
        ->  Materialize  (cost=126.43..808.15 rows=435 width=4)
              ->  Bitmap Heap Scan on namespaces  (cost=126.43..805.98 rows=435 width=4)
                    Recheck Cond: ((traversal_ids @> '{9970}'::integer[]) AND ((type)::text = 'Group'::text))
                    ->  Bitmap Index Scan on index_namespaces_on_traversal_ids_for_groups  (cost=0.00..126.32 rows=435 width=0)
                          Index Cond: (traversal_ids @> '{9970}'::integer[])

IN 서브쿼리를 CTE로 옮기면 실행 계획을 강제할 수 있습니다:

cte = Gitlab::SQL::CTE.new(:group_ids, Group.first.self_and_descendant_ids)
Epic.where('epics.id IN (SELECT id FROM group_ids)').with(cte.to_arel).exists?

Limit  (cost=817.27..818.12 rows=1 width=4)
  CTE group_ids
    ->  Bitmap Heap Scan on namespaces  (cost=126.43..807.06 rows=435 width=4)
          Recheck Cond: ((traversal_ids @> '{9970}'::integer[]) AND ((type)::text = 'Group'::text))
          ->  Bitmap Index Scan on index_namespaces_on_traversal_ids_for_groups  (cost=0.00..126.32 rows=435 width=0)
                Index Cond: (traversal_ids @> '{9970}'::integer[])
  ->  Nested Loop  (cost=10.21..380.29 rows=435 width=4)
        ->  HashAggregate  (cost=9.79..11.79 rows=200 width=4)
              Group Key: group_ids.id
              ->  CTE Scan on group_ids  (cost=0.00..8.70 rows=435 width=4)
        ->  Index Only Scan using epics_pkey on epics  (cost=0.42..1.84 rows=1 width=4)
              Index Cond: (id = group_ids.id)

.find_or_create_by는 원자적이지 않음#

.find_or_create_by나 .first_or_create 같은 메서드가 가진 본질적인 패턴은 원자적이지 않다는 점입니다. 즉 먼저 SELECT를 실행하고 결과가 없으면 INSERT를 수행합니다. 동시 프로세스를 고려하면 비슷한 레코드 두 개를 삽입하려 하는 경쟁 조건이 존재합니다. 이는 원하는 동작이 아닐 수 있고, 예를 들어 제약 조건 위반으로 쿼리 하나가 실패하는 원인이 될 수도 있습니다.

트랜잭션을 사용해도 이 문제는 해결되지 않습니다.

이를 해결하기 위해 ApplicationRecord.safe_find_or_create_by를 추가했습니다.

이 메서드는 find_or_create_by와 같은 방식으로 사용할 수 있지만, 호출을 새 트랜잭션(또는 서브트랜잭션)으로 감싸고 ActiveRecord::RecordNotUnique 오류로 실패하는 경우 재시도합니다.

이 메서드를 사용하려면 이를 사용할 모델이 ApplicationRecord를 상속하는지 확인합니다.

Rails 6 이상에는 .create_or_find_by 메서드가 있습니다. 이 메서드는 먼저 INSERT를 수행하고 그 호출이 실패할 때만 SELECT 명령을 수행하므로 GitLab의 .safe_find_or_create_by 메서드와 다릅니다.

INSERT가 실패하면 데드 튜플이 남고 기본 키 시퀀스가 있다면 값이 증가하며, 그 밖에도 다른 단점이 있습니다.

레코드 하나를 처음 만든 뒤 재사용하는 것이 일반적인 경로라면 .safe_find_or_create_by를 선호합니다. 그러나 새 레코드를 만드는 것이 더 일반적인 경로이고, 예외 사례(예를 들어 job 재시도)에서 중복 레코드가 삽입되는 것만 막고 싶다면 .create_or_find_by가 SELECT를 한 번 줄여 줍니다.

두 메서드는 기존 트랜잭션 컨텍스트 안에서 실행되면 내부적으로 서브트랜잭션을 사용합니다. 이는 전체 성능에 큰 영향을 줄 수 있으며, 특히 단일 트랜잭션 안에서 활성 서브트랜잭션이 64개를 넘을 때 그렇습니다.

.safe_find_or_create_by 사용 가능 여부#

코드가 전반적으로 분리되어 있고(예를 들어 워커에서만 실행되고) 다른 트랜잭션으로 감싸여 있지 않다면 .safe_find_or_create_by를 사용할 수 있습니다. 다만 다른 사람이 트랜잭션 안에서 해당 코드를 호출하는 경우를 잡아내는 도구는 없습니다. .safe_find_or_create_by를 사용하면 현재로서는 완전히 제거할 수 없는 위험이 분명히 따릅니다.

또한 .safe_find_or_create_by 사용을 막는 RuboCop 규칙 Performance/ActiveRecordSubtransactionMethods가 있습니다. 이 규칙은 # rubocop:disable Performance/ActiveRecordSubtransactionMethods로 사례별로 비활성화할 수 있습니다.

.find_or_create_by의 대안#

대안 1: UPSERT#

테이블이 고유 인덱스로 뒷받침되는 경우 .upsert 메서드가 대안이 될 수 있습니다.

.upsert 메서드의 간단한 사용법입니다:

BuildTrace.upsert(
  {
    build_id: build_id,
    title: title
  },
  unique_by: :build_id
)

주의할 점은 다음과 같습니다:

• 레코드가 업데이트만 된 경우에도 기본 키 시퀀스가 증가합니다.
• 생성된 레코드는 반환되지 않습니다. returning 옵션은 INSERT가 발생할 때(새 레코드일 때)만 데이터를 반환합니다.
• ActiveRecord 검증은 실행되지 않습니다.

검증과 레코드 로딩을 포함한 .upsert 메서드 예시입니다:

params = {
  build_id: build_id,
  title: title
}

build_trace = BuildTrace.new(params)

unless build_trace.valid?
  raise 'notify the user here'
end

BuildTrace.upsert(params, unique_by: :build_id)

build_trace = BuildTrace.find_by!(build_id: build_id)

# do something with build_trace here

.upsert를 호출하기 전에 검증을 실행하므로, build_id 칼럼에 모델 수준의 고유성 검증이 있으면 위 코드 스니펫은 제대로 동작하지 않습니다.

이를 우회하는 방법은 두 가지입니다:

• ActiveRecord 모델에서 고유성 검증을 제거합니다.
• on 키워드를 사용해 컨텍스트별 검증을 구현합니다.

대안 2: 존재 확인 후 rescue#

같은 레코드를 동시에 생성할 가능성이 매우 낮다면 더 단순한 방법을 사용할 수 있습니다:

def my_create_method
  params = {
    build_id: build_id,
    title: title
  }

  build_trace = BuildTrace
    .where(build_id: params[:build_id])
    .first

  build_trace = BuildTrace.new(params) if build_trace.blank?

  build_trace.update!(params)

rescue ActiveRecord::RecordInvalid => invalid
  retry if invalid.record&.errors&.of_kind?(:build_id, :taken)
end

이 메서드는 다음을 수행합니다:

1. 고유 칼럼으로 모델을 조회합니다.
2. 레코드를 찾지 못하면 새로 만듭니다.
3. 레코드를 저장합니다.

조회 쿼리와 저장 쿼리 사이에는 다른 프로세스가 레코드를 삽입해 ActiveRecord::RecordInvalid 예외를 일으킬 수 있는 짧은 경쟁 조건이 있습니다.

이 코드는 해당 예외를 rescue하고 작업을 재시도합니다. 두 번째 실행에서는 레코드를 성공적으로 찾습니다. 예시는 PreventApprovalByAuthorService의 이 코드 블록을 참고합니다.

프로덕션에서 SQL 쿼리 모니터링#

GitLab 팀 멤버는 PostgreSQL 로그를 사용해 GitLab.com에서 느린 쿼리나 취소된 쿼리를 모니터링할 수 있습니다. 이 로그는 Elasticsearch에 인덱싱되어 있고 Kibana로 검색할 수 있습니다.

자세한 내용은 런북을 참고합니다.

공통 테이블 표현식 사용 시점#

공통 테이블 표현식(CTE)을 사용하면 더 복잡한 쿼리 안에 임시 결과 집합을 만들 수 있습니다. 재귀 CTE를 사용하면 쿼리 자체에서 CTE의 결과 집합을 참조할 수도 있습니다. 다음 예시는 previous_personal_access_token_id 칼럼에서 서로를 참조하는 personal access tokens 체인을 조회합니다.

WITH RECURSIVE "personal_access_tokens_cte" AS (
(
    SELECT
      "personal_access_tokens".*
    FROM
      "personal_access_tokens"
    WHERE
      "personal_access_tokens"."previous_personal_access_token_id" = 15)
  UNION (
    SELECT
      "personal_access_tokens".*
    FROM
      "personal_access_tokens",
      "personal_access_tokens_cte"
    WHERE
      "personal_access_tokens"."previous_personal_access_token_id" = "personal_access_tokens_cte"."id"))
SELECT
  "personal_access_tokens".*
FROM
  "personal_access_tokens_cte" AS "personal_access_tokens"

 id | previous_personal_access_token_id
----+-----------------------------------
 16 |                                15
 17 |                                16
 18 |                                17
 19 |                                18
 20 |                                19
 21 |                                20
(6 rows)

CTE는 임시 결과 집합이므로 다른 SELECT 구문 안에서 사용할 수 있습니다. CTE를 UPDATE나 DELETE와 함께 사용하면 예상치 못한 동작이 발생할 수 있습니다:

다음 메서드를 살펴봅니다:

def personal_access_token_chain(token)
  cte = Gitlab::SQL::RecursiveCTE.new(:personal_access_tokens_cte)
  personal_access_token_table = Arel::Table.new(:personal_access_tokens)

  cte << PersonalAccessToken
           .where(personal_access_token_table[:previous_personal_access_token_id].eq(token.id))
  cte << PersonalAccessToken
           .from([personal_access_token_table, cte.table])
           .where(personal_access_token_table[:previous_personal_access_token_id].eq(cte.table[:id]))
  PersonalAccessToken.with.recursive(cte.to_arel).from(cte.alias_to(personal_access_token_table))
end

데이터를 조회하는 데 사용하면 예상대로 동작합니다:

> personal_access_token_chain(token)

WITH RECURSIVE "personal_access_tokens_cte" AS (
(
    SELECT
      "personal_access_tokens".*
    FROM
      "personal_access_tokens"
    WHERE
      "personal_access_tokens"."previous_personal_access_token_id" = 11)
  UNION (
    SELECT
      "personal_access_tokens".*
    FROM
      "personal_access_tokens",
      "personal_access_tokens_cte"
    WHERE
      "personal_access_tokens"."previous_personal_access_token_id" = "personal_access_tokens_cte"."id"))
SELECT
    "personal_access_tokens".*
FROM
    "personal_access_tokens_cte" AS "personal_access_tokens"

그러나 #update_all과 함께 사용하면 CTE가 사라집니다. 그 결과 이 메서드는 테이블 전체를 업데이트합니다:

> personal_access_token_chain(token).update_all(revoked: true)

UPDATE
    "personal_access_tokens"
SET
    "revoked" = TRUE

이 동작을 우회하려면 다음을 수행합니다:

1. 레코드의 ids를 조회합니다:

   > token_ids = personal_access_token_chain(token).pluck_primary_key
   => [16, 17, 18, 19, 20, 21]
   

2. 이 배열을 사용해 PersonalAccessTokens의 범위를 지정합니다:

   PersonalAccessToken.where(id: token_ids).update_all(revoked: true)
   

또는 이 두 단계를 하나로 합칩니다:

PersonalAccessToken
  .where(id: personal_access_token_chain(token).pluck_primary_key)
  .update_all(revoked: true)