이 문서는 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" ...

ID Plucking#

ActiveRecord의 pluck을 사용하여 값 집합을 메모리에 로드한 다음 다른 쿼리의 인수로 사용하는 것은 매우 주의해야 합니다. 일반적으로 쿼리 로직을 PostgreSQL에서 Ruby로 이동시키는 것은 좋지 않습니다. PostgreSQL에는 원하는 작업에 대해 더 많은 컨텍스트를 가질수록 더 잘 수행하는 쿼리 옵티마이저가 있기 때문입니다.

어떤 이유로 pluck이 필요하고 결과를 단일 쿼리에서 사용해야 한다면, 대부분의 경우 구체화된 CTE가 더 나은 선택입니다:

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

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

피해야 할 pluck 관련 실수들:

• 쿼리에 너무 많은 정수를 전달하는 것. 명시적으로 제한되어 있지는 않지만, PostgreSQL은 실제로 수천 개의 ID라는 실용적인 arity 제한이 있습니다. 이 제한에 부딪히고 싶지 않습니다.

• 로깅 인프라에 문제를 일으킬 수 있는 거대한 쿼리 텍스트를 생성하는 것.

• 실수로 전체 테이블을 스캔하는 것. 예를 들어, 다음은 불필요한 추가 데이터베이스 쿼리를 실행하고 불필요한 데이터를 많이 메모리에 로드합니다:

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은 코드베이스 전반에 걸쳐 일반적이지만, EXCEPT 및 INTERSECT와 같은 다른 SQL 집합 연산자를 사용하는 것도 가능합니다:

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 스키마 캐시 내에서 캐시되기 때문에 오래된 값을 반환합니다(새 칼럼이 누락됨). 이 값들은 일반적으로 애플리케이션이 시작될 때 채워집니다.

이 시점에서 유일한 수정 방법은 스키마 캐시가 업데이트되도록 전체 애플리케이션을 재시작하는 것입니다. GitLab 16.1부터 스키마 캐시는 자동으로 재설정되어 이후 쿼리가 성공합니다. 이 재설정은 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) 기준 정렬#

Cells 아키텍처에서 id로 정렬하는 것은 더 이상 생성 순서를 안정적으로 반영하지 않습니다. 각 Cell에는 할당된 데이터베이스 시퀀스 범위가 있습니다. 데이터가 Cell 간에 이동할 때 레코드는 소스 Cell의 원래 ID를 유지합니다.

생성 날짜를 기준으로 정확하게 정렬하려면 적절한 인덱싱과 함께 ORDER BY created_at, id를 사용하세요.

간단히 말해서, 기능에 문제가 발생할 것이 확실하지 않는 한 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이 사용자에 의해 직접 제어되는 경우가 거의 없다는 점을 고려하면(즉, 내부 구현 세부 사항), 사용자가 실제로 이 2개의 칼럼 차이에 신경 쓰는 경우는 거의 없습니다.

따라서 id로 정렬하는 데는 최소 3가지 장점이 있습니다:

• 기본 키로서 이미 인덱싱되어 있으므로, 다른 필터링이나 정렬 파라미터가 없는 간단한 쿼리에는 충분할 수 있습니다.

• 복합 인덱스가 필요한 경우, btree (namespace_id, id)와 같은 인덱스는 btree (namespace_id, created_at, id)보다 작습니다.

• 고유하므로 정렬 및 페이지네이션에 안정적입니다.

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는 ActiveRecord 스코프를 수정합니다:

• 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[])

플래너가 400,000개 이상의 행을 읽을 것으로 추정하는 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)

복잡성으로 인해 CTE 사용은 최후의 수단으로만 사용해야 합니다. 더 간단한 쿼리 변경으로 유리한 실행 플랜이 생성되지 않는 경우에만 CTE를 사용하세요.

.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 명령을 수행하기 때문에 .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

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

• 고유 칼럼으로 모델을 조회합니다.

• 레코드를 찾지 못하면 새 레코드를 빌드합니다.

• 레코드를 저장합니다.

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

코드는 이 특정 예외를 rescue하고 작업을 재시도합니다. 두 번째 실행에서는 레코드가 성공적으로 위치합니다. 예를 들어 PreventApprovalByAuthorService의 이 코드 블록을 확인하세요.

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

GitLab 팀원은 Elasticsearch에 인덱싱되고 Kibana를 사용하여 검색할 수 있는 PostgreSQL 로그를 통해 GitLab.com의 느리거나 취소된 쿼리를 모니터링할 수 있습니다.

자세한 내용은 런북을 참조하세요.

공통 테이블 표현식을 사용할 때#

공통 테이블 표현식(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 구문 내에서 사용할 수 있습니다. UPDATE 또는 DELETE와 함께 CTE를 사용하면 예상치 못한 동작이 발생할 수 있습니다:

다음 메서드를 고려해보세요:

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

이 동작을 해결하려면:

• 레코드의 id를 쿼리합니다:

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

• 이 배열을 사용하여 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)

경계가 없는 대량의 데이터를 업데이트하는 것은 피하세요. 데이터에 애플리케이션 제한이 없거나 데이터 볼륨이 불확실한 경우 배치로 데이터를 업데이트해야 합니다.