From db4efc6dd1e8cb7d73b8ed20e54e691c8d9876e5 Mon Sep 17 00:00:00 2001 From: David Kim Date: Tue, 1 Jul 2025 14:52:09 +1000 Subject: [PATCH 1/5] Prepare indexes for merge_requests bigint conversion It only includes following columns only to minimize db WAL rate impact. - assignee_id_convert_to_bigint - merge_user_id_convert_to_bigint - updated_by_id_convert_to_bigint - milestone_id_convert_to_bigint - source_project_id_convert_to_bigint Changelog: added --- ...es_for_merge_requests_bigint_conversion.rb | 80 +++++++++++++++++++ db/schema_migrations/20250701035405 | 1 + 2 files changed, 81 insertions(+) create mode 100644 db/post_migrate/20250701035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb create mode 100644 db/schema_migrations/20250701035405 diff --git a/db/post_migrate/20250701035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb b/db/post_migrate/20250701035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb new file mode 100644 index 00000000000000..5d12fb82c55f48 --- /dev/null +++ b/db/post_migrate/20250701035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb @@ -0,0 +1,80 @@ +# frozen_string_literal: true + +class PrepareAssigneeIdIndexesForMergeRequestsBigintConversion < Gitlab::Database::Migration[2.3] + include Gitlab::Database::MigrationHelpers::ConvertToBigint + + disable_ddl_transaction! + milestone '18.2' + + TABLE_NAME = 'merge_requests' + BIGINT_COLUMNS = [ + :assignee_id_convert_to_bigint, + :merge_user_id_convert_to_bigint, + :updated_by_id_convert_to_bigint, + :milestone_id_convert_to_bigint, + :source_project_id_convert_to_bigint + ].freeze + + INDEXES = [ + { + name: 'index_merge_requests_on_assignee_id', + columns: [:assignee_id_convert_to_bigint] + }, + { + name: 'index_merge_requests_on_merge_user_id', + columns: [:merge_user_id_convert_to_bigint], + options: { where: "merge_user_id_convert_to_bigint IS NOT NULL" } + }, + { + name: 'index_merge_requests_on_updated_by_id', + columns: [:updated_by_id_convert_to_bigint], + options: { where: "updated_by_id_convert_to_bigint IS NOT NULL" } + }, + { + name: 'index_merge_requests_on_milestone_id', + columns: [:milestone_id_convert_to_bigint] + }, + { + name: 'idx_merge_requests_on_source_project_and_branch_state_opened', + columns: [:source_project_id_convert_to_bigint, :source_branch], + options: { where: "state_id = 1" } + }, + { + name: 'index_merge_requests_on_source_project_id_and_source_branch', + columns: [:source_project_id_convert_to_bigint, :source_branch] + } + ].freeze + + def up + return if skip_migration? + + INDEXES.each do |index| + options = index[:options] || {} + prepare_async_index(TABLE_NAME, index[:columns], name: bigint_index_name(index[:name]), **options) + end + end + + def down + return if skip_migration? + + INDEXES.each do |index| + options = index[:options] || {} + unprepare_async_index(TABLE_NAME, index[:columns], name: bigint_index_name(index[:name]), **options) + end + end + + private + + def skip_migration? + unless conversion_columns_exist? + say "No conversion columns found - migration skipped" + return true + end + + false + end + + def conversion_columns_exist? + BIGINT_COLUMNS.all? { |column| column_exists?(TABLE_NAME, column) } + end +end diff --git a/db/schema_migrations/20250701035405 b/db/schema_migrations/20250701035405 new file mode 100644 index 00000000000000..f48478b3655cf0 --- /dev/null +++ b/db/schema_migrations/20250701035405 @@ -0,0 +1 @@ +11b5f75296f5ff8e58757116001d94a95ea08a2ee7b200a6308fd7da9146aef2 \ No newline at end of file -- GitLab From e72d8806ee5fe917142fdeec53c084ce139990d1 Mon Sep 17 00:00:00 2001 From: "Sincheol (David) Kim" Date: Wed, 20 Aug 2025 09:51:26 +1000 Subject: [PATCH 2/5] Apply 1 suggestion(s) to 1 file(s) --- ..._assignee_id_indexes_for_merge_requests_bigint_conversion.rb | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/db/post_migrate/20250701035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb b/db/post_migrate/20250701035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb index 5d12fb82c55f48..c27b079682186f 100644 --- a/db/post_migrate/20250701035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb +++ b/db/post_migrate/20250701035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb @@ -4,7 +4,7 @@ class PrepareAssigneeIdIndexesForMergeRequestsBigintConversion < Gitlab::Databas include Gitlab::Database::MigrationHelpers::ConvertToBigint disable_ddl_transaction! - milestone '18.2' + milestone '18.4' TABLE_NAME = 'merge_requests' BIGINT_COLUMNS = [ -- GitLab From 06f5c44414d76d4c0c2a7e5e64061a2f8e11e5c7 Mon Sep 17 00:00:00 2001 From: David Kim Date: Wed, 20 Aug 2025 14:14:44 +1000 Subject: [PATCH 3/5] Bump up migration timestamp --- ..._assignee_id_indexes_for_merge_requests_bigint_conversion.rb} | 0 db/schema_migrations/20250701035405 | 1 - db/schema_migrations/20250820035405 | 1 + 3 files changed, 1 insertion(+), 1 deletion(-) rename db/post_migrate/{20250701035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb => 20250820035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb} (100%) delete mode 100644 db/schema_migrations/20250701035405 create mode 100644 db/schema_migrations/20250820035405 diff --git a/db/post_migrate/20250701035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb b/db/post_migrate/20250820035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb similarity index 100% rename from db/post_migrate/20250701035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb rename to db/post_migrate/20250820035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb diff --git a/db/schema_migrations/20250701035405 b/db/schema_migrations/20250701035405 deleted file mode 100644 index f48478b3655cf0..00000000000000 --- a/db/schema_migrations/20250701035405 +++ /dev/null @@ -1 +0,0 @@ -11b5f75296f5ff8e58757116001d94a95ea08a2ee7b200a6308fd7da9146aef2 \ No newline at end of file diff --git a/db/schema_migrations/20250820035405 b/db/schema_migrations/20250820035405 new file mode 100644 index 00000000000000..48f77f95b033a8 --- /dev/null +++ b/db/schema_migrations/20250820035405 @@ -0,0 +1 @@ +7bc886716ae3670543764248e7f5d0fb67ebac2a5907ca2133a88f29e7c6ea7f \ No newline at end of file -- GitLab From 57e793122efdca7448a07fcc1ce1a669acb11bba Mon Sep 17 00:00:00 2001 From: "Sincheol (David) Kim" Date: Wed, 20 Aug 2025 14:49:27 +1000 Subject: [PATCH 4/5] Apply 1 suggestion(s) to 1 file(s) --- ..._assignee_id_indexes_for_merge_requests_bigint_conversion.rb | 2 ++ 1 file changed, 2 insertions(+) diff --git a/db/post_migrate/20250820035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb b/db/post_migrate/20250820035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb index c27b079682186f..3f1e4467ed6f50 100644 --- a/db/post_migrate/20250820035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb +++ b/db/post_migrate/20250820035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb @@ -48,10 +48,12 @@ class PrepareAssigneeIdIndexesForMergeRequestsBigintConversion < Gitlab::Databas def up return if skip_migration? +# rubocop:disable Migration/PreventIndexCreation -- Bigint migration INDEXES.each do |index| options = index[:options] || {} prepare_async_index(TABLE_NAME, index[:columns], name: bigint_index_name(index[:name]), **options) end +# rubocop:enable Migration/PreventIndexCreation end def down -- GitLab From 91d9603ba741c1415e0f8ab86c2ae639b0486a0a Mon Sep 17 00:00:00 2001 From: "Sincheol (David) Kim" Date: Wed, 20 Aug 2025 14:50:47 +1000 Subject: [PATCH 5/5] Apply 2 suggestion(s) to 1 file(s) --- ...ssignee_id_indexes_for_merge_requests_bigint_conversion.rb | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) diff --git a/db/post_migrate/20250820035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb b/db/post_migrate/20250820035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb index 3f1e4467ed6f50..9c7454a152bf2e 100644 --- a/db/post_migrate/20250820035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb +++ b/db/post_migrate/20250820035405_prepare_assignee_id_indexes_for_merge_requests_bigint_conversion.rb @@ -48,12 +48,12 @@ class PrepareAssigneeIdIndexesForMergeRequestsBigintConversion < Gitlab::Databas def up return if skip_migration? -# rubocop:disable Migration/PreventIndexCreation -- Bigint migration + # rubocop:disable Migration/PreventIndexCreation -- Bigint migration INDEXES.each do |index| options = index[:options] || {} prepare_async_index(TABLE_NAME, index[:columns], name: bigint_index_name(index[:name]), **options) end -# rubocop:enable Migration/PreventIndexCreation + # rubocop:enable Migration/PreventIndexCreation end def down -- GitLab