SQL Query Guidelines

이 문서는 SQL 쿼리를 작성할 때 따를 여러 지침에 대해 설명합니다. ActiveRecord/Arel 또는 원시 SQL 쿼리를 사용하는 경우입니다.

LIKE 문 사용

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

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

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

자동으로 이를 처리하려면 PostgreSQL에서는 ILIKE를 사용하는 대신 Arel을 사용하여 LIKE 쿼리를 사용해야 합니다. 

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" ...

원시 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" ...

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

# 배포 전 (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 cop에 따라서, 모델 코드 내에서 pluck(:id)나 pluck(:user_id)와 같은 형태만 사용해야 합니다. 전자의 경우, ApplicationRecord에서 제공하는 .pluck_primary_key 도우미 메서드를 대신 사용할 수 있습니다. 후자의 경우에는 해당 모델에 작은 도우미 메서드를 추가해야 합니다.

pluck를 사용할 강력한 이유가 있다면, 뽑아낸 레코드 수를 제한하는 것이 합리적일 수 있습니다. MAX_PLUCK의 기본값은 1,000입니다.

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 모델 콘서은 위와 같은 결과를 보다 편리하게 생성하는 데 도움이 되는 더 편리한 메서드를 제공합니다. 

class Project
  include FromUnion
  ...
end

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

UNION은 코드베이스 전반에 흔히 사용되지만, UNION 서브 쿼리에 각각의 SELECT 절에 불균형한 열이 있을 때 발생하는 문제도 있습니다. 

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

위와 같은 UNION 쿼리의 경우, 데이터베이스는 다음과 같은 오류 메시지를 반환합니다. 

each UNION query must have the same number of columns

이 문제는 명확하게 드러나며 개발 중에 쉽게 수정할 수 있습니다. UNION 쿼리를 ActiveRecord 스키마 캐시에서 원본 목록을 사용하는 경우 엣지 케이스가 됩니다.

The Why And The How Of Magic Columns

모든 열을 명시적으로 정의하거나 항상 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 # 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으로 정렬하는 대신 id 열로 정렬할 수 있습니다. ID는 항상 고유하며 행이 생성된 순서대로 증가하기 때문에 정확히 동일한 결과를 얻을 수 있습니다. 이는 created_at에 인덱스를 추가할 필요가 없음을 의미하며, id가 기본적으로 인덱싱되어 있기 때문에 일관된 성능을 보장할 필요가 없다는 것을 의미합니다.

WHERE EXISTS 대신 WHERE IN 사용하기

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이 있는지 확인합니다. 

# 유사한 쿼리지만 동작이 다를 수 있음 (다른 쿼리 실행 계획)

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? # 적어도 하나의 epic이 있는지 확인용

.exists? 메서드가 호출되면 Rails는 다음과 같이 active record 스코프를 수정합니다:

  • 선택 열을 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 메서드가 있습니다. 이 메서드는 우리의 .safe_find_or_create_by 메서드와 다르며 INSERT를 수행한 다음 해당 호출이 실패할 경우에만 SELECT 명령을 실행합니다.

INSERT가 실패하면 데드 튜플이 남고, 주 키 시퀀스가 증가하며 다른 단점들이 있습니다.

우리는 첫 번째 레코드가 작성된 후에 재사용되는 단일 레코드가 일반적인 경우에는 .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를 통해 사례별로 비활성화할 수 있습니다.

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

BuildTrace.upsert(params, unique_by: :build_id)

build_trace = BuildTrace.find_by!(build_id: build_id)

# 여기서 build_trace를 사용하세요

위의 코드 조각은 build_id 열에 모델 수준의 고유성 검증이 있으면 잘 작동하지 않습니다. 왜냐하면 .upsert를 호출하기 전에 검증을 호출하기 때문입니다.

이를 해결하기 위해 두 가지 옵션이 있습니다:

  • 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

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

  1. 고유한 열로 모델을 조회합니다.
  2. 레코드를 찾을 수 없는 경우 새로운 레코드를 만듭니다.
  3. 레코드를 지속시킵니다.

조회 쿼리와 지속 쿼리 사이에 짧은 경합 상태가 있으며 또 다른 프로세스가 레코드를 삽입하고 ActiveRecord::RecordInvalid 예외를 발생시킬 수 있습니다.

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

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

GitLab 팀원은 GitLab.com에서 PostgreSQL 로그를 사용하여 느린 또는 취소된 쿼리를 모니터링할 수 있으며 Elasticsearch에 인덱싱되어 Kibana에서 검색할 수 있습니다.

더 많은 세부 정보는 런북를 참조하세요.

공통 테이블 표현식(CTE)을 사용하는 시기

보다 복잡한 쿼리 내에서 임시 결과 집합을 생성하기 위해 공통 테이블 표현식(CTE)을 사용할 수 있습니다. 또한 재귀 CTE를 사용하여 쿼리 내에서 CTE의 결과 집합을 참조할 수도 있습니다. 다음 예제는 이전_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)

참고: 무제한 데이터의 대량 업데이트를 피하세요. 데이터에 application limits가 없거나 데이터 볼륨에 대해 확실하지 않은 경우, 데이터를 일괄적으로 업데이트하세요.