LIKE문 사용하기LIKE및 인덱스- 데이터베이스 열 신뢰성 있게 참조하기
- ID 추출
- ApplicationRecord에서 상속
- UNION 사용
- 생성일로 정렬하기
WHERE EXISTS를 사용하고WHERE IN은 피하기.exists?쿼리의 쿼리 계획 전환 문제-
.find_or_create_by는 원자적이지 않음 -
대안 1:
UPSERT - 프로덕션에서 SQL 쿼리 모니터링
- 공통 테이블 식 사용 시점
SQL 쿼리 지침
이 문서에서는 ActiveRecord/Arel 또는 원시 SQL 쿼리를 사용할 때 SQL 쿼리를 작성할 때 따라야 할 다양한 지침을 설명합니다.
LIKE 문 사용하기
데이터를 검색하는 가장 일반적인 방법은 LIKE 문을 사용하는 것입니다. 예를 들어, 제목이 “Draft:”로 시작하는 모든 문제를 가져오려면 다음 쿼리를 작성합니다:
SELECT *
FROM issues
WHERE title LIKE 'Draft:%';
PostgreSQL에서는 LIKE 문이 대소문자를 구분합니다. 대소문자를 구분하지 않는 LIKE를 수행하려면 대신 ILIKE를 사용해야 합니다.
이를 자동으로 처리하려면 원시 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은 해결책을 제공합니다: 삼중 일반화 역 인덱스(GIN)입니다. 이러한 인덱스는 다음과 같이 생성할 수 있습니다:
CREATE INDEX [CONCURRENTLY] index_name_here
ON table_name
USING GIN(column_name gin_trgm_ops);
여기서 핵심은 GIN(column_name gin_trgm_ops) 부분입니다. 이는 GIN 인덱스를 생성하며, 연산자 클래스가 gin_trgm_ops로 설정됩니다. 이러한 인덱스는 ILIKE / LIKE에 사용될 수 있으며 성능 개선을 가져올 수 있습니다. 이러한 인덱스의 한 가지 단점은 인덱싱된 데이터의 양에 따라 쉽게 매우 커질 수 있다는 점입니다.
이러한 인덱스의 명명을 일관되게 유지하기 위해 다음 명명 패턴을 사용합니다:
index_TABLE_on_COLUMN_trigram
예를 들어, issues.title에 대한 GIN/삼중 인덱스는 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(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" ...
# 또는
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을 사용해야 할 유일한 경우는 실제로 Ruby에서 값을 작업해야 하는 경우입니다(예를 들어, 파일에 쓰는 경우). 다른 경우에는 “서브 쿼리를 사용하지 않을 수 있나?”라고 자문해 보세요.
우리의 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 사용
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이 흔히 사용되지만, 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
쿼리는 다음과 같은 오류 메시지를 발생시킵니다:
각 UNION 쿼리는 동일한 열 수를 가져야 합니다.
이 문제는 분명하며 개발 중 쉽게 수정할 수 있습니다. 하나의 엣지 케이스는 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에 인덱스를 추가할 필요가 없음을 의미합니다. 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.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을 추가합니다.
복잡한 ActiveRecord 범위, 예를 들어 IN 쿼리가 포함된 경우, 데이터베이스 쿼리 계획 동작을 부정적으로 변경할 수 있습니다.
실행 계획:
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 인덱스에서 400,000개 이상의 행을 읽는다고 계획자가 추정한 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
이 메서드는 다음과 같은 작업을 수행합니다:
-
고유 열로 모델을 조회합니다.
-
레코드가 없으면 새 레코드를 만듭니다.
-
레코드를 저장합니다.
조회 쿼리와 영속화 쿼리 사이에 짧은 경쟁 조건이 있어 다른 프로세스가 레코드를 삽입할 수 있으며, 이로 인해 ActiveRecord::RecordInvalid 예외가 발생할 수 있습니다.
코드는 이 특정 예외를 처리하고 작업을 재시도합니다. 두 번째 실행에서 레코드는 성공적으로 찾아질 것입니다. 예를 들어 이 코드 블록을 확인하세요.
프로덕션에서 SQL 쿼리 모니터링
GitLab 팀원들은 PostgreSQL 로그를 사용하여 GitLab.com에서 느리거나 취소된 쿼리를 모니터링할 수 있으며, 이러한 로그는 Elasticsearch에 색인화되어 있고 Kibana를 사용하여 검색할 수 있습니다.
자세한 내용은 런북을 참조하세요.
공통 테이블 식 사용 시점
더 복잡한 쿼리 내에서 임시 결과 집합을 생성하기 위해 공통 테이블 식(CTEs)을 사용할 수 있습니다. 또한 재귀 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"
그러나 CTE는 #update_all과 함께 사용할 때 제거됩니다. 결과적으로, 이 메서드는 전체 테이블을 업데이트합니다:
> 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)
도움말