LIKE 문 사용
LIKE 및 인덱스
데이터베이스 열을 신뢰성 있게 참조하기
- 좋음 (권장)
- 나쁨 (피하세요)
ID 집어넣기
ApplicationRecord에서 상속
UNION 사용
- UNION 하위 쿼리의 열이 불균형한 경우
생성 날짜에 따라 정렬
WHERE IN 대신 WHERE EXISTS 사용
.exists? 쿼리에서 쿼리 계획 뒤바꿈 문제
- 실행 계획:
- 대안 2: 존재 여부 확인 및 회피
프로덕션 환경에서 SQL 쿼리 모니터링
공통 테이블 표현식 사용 시기

SQL 쿼리 가이드라인

이 문서는 SQL 쿼리를 작성할 때 따를 여러 지침을 설명합니다. ActiveRecord/Arel 또는 원시 SQL 쿼리를 사용하는 방법에 관한 내용입니다.

`LIKE` 문 사용

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

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

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

이 작업을 자동으로 처리하려면 PostgreSQL에서 ILIKE를 자동으로 사용하는 Arel을 사용해야 합니다.

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 일반 역 인덱스 (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

쿼리:

# 나쁨, 피하세요
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을 사용하는 예제:

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

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

원시 SQL 쿼리를 작성할 때:

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

원시 SQL 쿼리에 매개변수를 사용하는 경우(이스케이핑이 필요한 경우):

include ActiveRecord::ConnectionAdapters::Quoting

"""
SELECT
  #{quote_table_name('projects')}.#{quote_column_name('path')},
  #{quote_table_name('merge_requests')}.#{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" ...

# 또는

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)

# 배포 전(user_id가 merge_requests 테이블에서 가져오기 때문):
# SELECT "projects"."id", "user_id" FROM "projects" ...

# 배포 후(user_id가 projects 테이블에서 가져오기 때문):
# SELECT "projects"."id", "projects"."user_id" FROM "projects" ...

ID 집어넣기

메모리로 값을 집어넣은 후에 다른 쿼리에 인수로 사용하는 것을 위해 ActiveRecord의 pluck를 사용해서는 안 됩니다. 예를 들어, 이것은 불필요한 데이터베이스 쿼리를 실행하고 많은 불필요한 데이터를 메모리에 로드합니다.

projects = Project.all.pluck(:id)

MergeRequest.where(source_project_id: projects)

그 대신 성능이 훨씬 더 좋은 서브쿼리를 사용할 수 있습니다.

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

실제로 값들에 대해 조작해야 하는 경우에만 pluck를 사용해야 합니다(예: 파일에 쓰기). 거의 모든 경우에 “서브쿼리를 사용할 수 없을까?”라고 스스로 물어보아야 합니다.

CodeReuse/ActiveRecord 규칙을 준수하여, 당신은 모델 코드 내에서만 pluck(:id)나 pluck(:user_id)와 같은 형태를 사용해야 합니다. 전자의 경우, ApplicationRecord이 제공하는 .pluck_primary_key 도우미 메서드를 사용할 수 있습니다. 후자의 경우, 해당 모델에 작은 도우미 메서드를 추가해야 합니다.

pluck를 사용해야 하는 강력한 이유가 있다면, 검색할 레코드 수를 제한하는 것이 합리적일 수 있습니다. ApplicationRecord에서 MAX_PLUCK의 기본값은 1,000입니다.

ApplicationRecord에서 상속

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

이 규칙의 예외 사항은 데이터베이스 마이그레이션에서 생성된 모델입니다. 애플리케이션 코드에서 격리되어야 하기 때문에, 이러한 모델은 마이그레이션 컨텍스트에서만 사용 가능한 MigrationRecord에서 계속하여 하위 클래스화 해야 합니다.

UNION 사용

대부분의 Rails 애플리케이션에서 UNION은 흔히 사용되지는 않지만, 매우 강력하고 유용합니다. 쿼리는 관련 데이터나 특정 기준에 따라 데이터를 가져오기 위해 많은 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 모델 관심사는 위와 같은 결과를 더 간편하게 생성할 수 있는 메서드를 제공합니다.

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]) # 열을 명시적으로 선택합니다
scope2 = User.where(id: [10, 11, 12]) # 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.* 사용:

# 나쁨, 피하세요
scope1 = User.select(User.column_names).where(id: [1, 2, 3])
scope2 = User.where(id: [10, 11, 12])

# 좋음, 두 쿼리 모두 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)

열을 명시적으로 정의:

# 좋음, SELECT 열이 일관되게 정의됩니다
columns = User.cached_column_list # 도우미는 (테이블.열) 형식으로 완전히 지정된 열 이름 (Arel)을 반환합니다
scope1 = User.select(*columns).where(id: [1, 2, 3]) # 열을 명시적으로 선택합니다
scope2 = User.select(*columns).where(id: [10, 11, 12]) # SELECT users.*를 사용합니다

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

생성 날짜에 따라 정렬

생성된 시간을 기준으로 레코드를 정렬할 때 created_at 대신 id 열을 기준으로 정렬할 수 있습니다. ID는 항상 고유하며 행이 생성된 순서대로 증가하기 때문에 동일한 결과가 생성됩니다. 이는 created_at에 인덱스를 추가하여 일관된 성능을 보장할 필요가 없다는 것을 의미합니다.

`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 스코프의 모든 변형을 확인하는 것이 좋습니다.

예: 그룹 및 하위 그룹의 에픽이 있는지 확인하세요.

# 유사한 쿼리이지만 다른 쿼리 실행 계획을 가질 수 있습니다

Epic.where(group_id: group.first.self_and_descendant_ids).order(:id).limit(20) # 페이지네이션용
Epic.where(group_id: group.first.self_and_descendant_ids).count # 총 수량 제공용
Epic.where(group_id: group.first.self_and_descendant_ids).exists? # 적어도 하나의 에픽이 있는지 확인용

.exists? 메서드가 호출되면 Rails는 활성 레코드 스코프를 수정합니다.

선택한 열을 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[])

index_epics_on_group_id_and_iid 인덱스에 대한 Index Only Scan(인덱스만 스캔) 에서 40만 개 이상의 행을 읽을 것으로 계획합니다.

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

## `.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`](https://api.rubyonrails.org/classes/ActiveRecord/Relation.html#method-i-create_or_find_by) 메서드가 있습니다. 이 메서드는 우리의 `.safe_find_or_create_by` 메서드와 달리 `INSERT`를 수행한 후 그 호출이 실패하는 경우에만 `SELECT` 명령을 수행합니다.

`INSERT`가 실패하면 죽은 튜플이 남아 있고(primary key sequence가 있는 경우) [기타 단점들](https://api.rubyonrails.org/classes/ActiveRecord/Relation.html#method-i-create_or_find_by)이 발생합니다.

일반적인 경로가 처음에 생성된 후 다시 사용되는 단일 레코드인 경우에는 우리는 `.safe_find_or_create_by`를 선호합니다. 그러나 더 일반적인 경로가 새 레코드를 만들고, 예외적인 경우(예: 작업 재시도)에만 중복 레코드가 삽입되는 것을 피하려는 경우에는 `.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`를 통해 case by case basis로 비활성화될 수 있습니다.

## 대안 1: `UPSERT`

테이블이 고유 인덱스로 지원되는 경우 [`.upsert`](https://api.rubyonrails.org/v7.0.5/classes/ActiveRecord/Persistence/ClassMethods.html#method-i-upsert) 메서드는 대안 솔루션일 수 있습니다.

`.upsert` 메서드의 간단한 사용법:

```ruby
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 '여기에서 사용자에게 알림'
end

BuildTrace.upsert(params, unique_by: :build_id)

build_trace = BuildTrace.find_by!(build_id: build_id)

# build_trace로 무언가를 수행하세요

위의 코드 조각은 build_id 열에 모델 수준의 고유성 유효성 검사가 있는 경우 잘 작동하지 않을 것입니다. 이를 해결하기 위해 두 가지 옵션이 있습니다:

ActiveRecord 모델에서 고유성 유효성 검사를 제거합니다.
on 키워드를 사용하고 콘텍스트별 유효성 검사를 구현합니다.

대안 2: 존재 여부 확인 및 회피

동시에 동일한 레코드를 생성할 가능성이 매우 낮은 경우 더 간단한 접근 방식을 사용할 수 있습니다:

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 예외를 발생시킬 수 있습니다.

이 코드는 특정 예외를 처리하고 작업을 재시도합니다. 두 번째 실행에서 레코드는 성공적으로 찾을 수 있습니다. 예를 들어 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 문 내에서 사용할 수 있습니다. 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

이 동작을 해결하려면:

레코드의 ids를 쿼리합니다:

> 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)

적용 제한이 없는 경우 데이터의 큰 양을 업데이트하지 마십시오. 데이터 볼륨에 대한 애플리케이션 한도가 없거나 데이터 볼륨에 대해 확신이 없는 경우 데이터를 일괄적으로 업데이트해야 합니다.

SQL 쿼리 가이드라인

`LIKE` 문 사용

`LIKE` 및 인덱스

데이터베이스 열을 신뢰성 있게 참조하기

좋음 (권장)

나쁨 (피하세요)

ID 집어넣기

ApplicationRecord에서 상속

UNION 사용

`UNION` 하위 쿼리의 열이 불균형한 경우

생성 날짜에 따라 정렬

`WHERE IN` 대신 `WHERE EXISTS` 사용

`.exists?` 쿼리에서 쿼리 계획 뒤바꿈 문제

실행 계획:

대안 2: 존재 여부 확인 및 회피

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

공통 테이블 표현식 사용 시기

도움말

기능 사용 가능성과 제품 평가판

도움받기

SQL 쿼리 가이드라인

LIKE 문 사용

LIKE 및 인덱스

데이터베이스 열을 신뢰성 있게 참조하기

좋음 (권장)

나쁨 (피하세요)

ID 집어넣기

ApplicationRecord에서 상속

UNION 사용

UNION 하위 쿼리의 열이 불균형한 경우

생성 날짜에 따라 정렬

WHERE IN 대신 WHERE EXISTS 사용

.exists? 쿼리에서 쿼리 계획 뒤바꿈 문제

실행 계획:

대안 2: 존재 여부 확인 및 회피

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

공통 테이블 표현식 사용 시기

도움말

기능 사용 가능성과 제품 평가판

도움받기

`LIKE` 문 사용

`LIKE` 및 인덱스

`UNION` 하위 쿼리의 열이 불균형한 경우

`WHERE IN` 대신 `WHERE EXISTS` 사용

`.exists?` 쿼리에서 쿼리 계획 뒤바꿈 문제