From 0cf52ec93a877dafa04aad77ddc6e3f6450325a9 Mon Sep 17 00:00:00 2001 From: Tomasz Skorupa Date: Wed, 10 Dec 2025 13:12:40 -0500 Subject: [PATCH 1/4] Add sharding key columns to web_hook_logs_daily Addresses https://gitlab.com/gitlab-org/gitlab/-/work_items/524820 This MR precedes https://gitlab.com/gitlab-org/gitlab/-/merge_requests/215236 in order to add the sharding key columns and prepare their async indexes (exclusively on .com) because the time to create those was very large. Changelog: other --- ..._organization_id_to_web_hook_logs_daily.rb | 11 ++++++++++ ...842_add_group_id_to_web_hook_logs_daily.rb | 11 ++++++++++ ...3_add_project_id_to_web_hook_logs_daily.rb | 11 ++++++++++ ..._web_hook_logs_daily_on_organization_id.rb | 20 +++++++++++++++++++ ...c_index_web_hook_logs_daily_on_group_id.rb | 20 +++++++++++++++++++ ...index_web_hook_logs_daily_on_project_id.rb | 20 +++++++++++++++++++ db/schema_migrations/20251210175841 | 1 + db/schema_migrations/20251210175842 | 1 + db/schema_migrations/20251210175843 | 1 + db/schema_migrations/20251210180245 | 1 + db/schema_migrations/20251210180531 | 1 + db/schema_migrations/20251210180643 | 1 + db/structure.sql | 3 +++ 13 files changed, 102 insertions(+) create mode 100644 db/migrate/20251210175841_add_organization_id_to_web_hook_logs_daily.rb create mode 100644 db/migrate/20251210175842_add_group_id_to_web_hook_logs_daily.rb create mode 100644 db/migrate/20251210175843_add_project_id_to_web_hook_logs_daily.rb create mode 100644 db/post_migrate/20251210180245_prepare_async_index_web_hook_logs_daily_on_organization_id.rb create mode 100644 db/post_migrate/20251210180531_prepare_async_index_web_hook_logs_daily_on_group_id.rb create mode 100644 db/post_migrate/20251210180643_prepare_async_index_web_hook_logs_daily_on_project_id.rb create mode 100644 db/schema_migrations/20251210175841 create mode 100644 db/schema_migrations/20251210175842 create mode 100644 db/schema_migrations/20251210175843 create mode 100644 db/schema_migrations/20251210180245 create mode 100644 db/schema_migrations/20251210180531 create mode 100644 db/schema_migrations/20251210180643 diff --git a/db/migrate/20251210175841_add_organization_id_to_web_hook_logs_daily.rb b/db/migrate/20251210175841_add_organization_id_to_web_hook_logs_daily.rb new file mode 100644 index 00000000000000..184278eda29045 --- /dev/null +++ b/db/migrate/20251210175841_add_organization_id_to_web_hook_logs_daily.rb @@ -0,0 +1,11 @@ +# frozen_string_literal: true + +class AddOrganizationIdToWebHookLogsDaily < Gitlab::Database::Migration[2.3] + milestone '18.7' + + def change + # rubocop:disable Migration/PreventAddingColumns -- required for sharding + add_column :web_hook_logs_daily, :organization_id, :bigint, if_not_exists: true + # rubocop:enable Migration/PreventAddingColumns + end +end diff --git a/db/migrate/20251210175842_add_group_id_to_web_hook_logs_daily.rb b/db/migrate/20251210175842_add_group_id_to_web_hook_logs_daily.rb new file mode 100644 index 00000000000000..eb41d36bdd9acc --- /dev/null +++ b/db/migrate/20251210175842_add_group_id_to_web_hook_logs_daily.rb @@ -0,0 +1,11 @@ +# frozen_string_literal: true + +class AddGroupIdToWebHookLogsDaily < Gitlab::Database::Migration[2.3] + milestone '18.7' + + def change + # rubocop:disable Migration/PreventAddingColumns -- required for sharding + add_column :web_hook_logs_daily, :group_id, :bigint, if_not_exists: true + # rubocop:enable Migration/PreventAddingColumns + end +end diff --git a/db/migrate/20251210175843_add_project_id_to_web_hook_logs_daily.rb b/db/migrate/20251210175843_add_project_id_to_web_hook_logs_daily.rb new file mode 100644 index 00000000000000..205ba7e4f5563b --- /dev/null +++ b/db/migrate/20251210175843_add_project_id_to_web_hook_logs_daily.rb @@ -0,0 +1,11 @@ +# frozen_string_literal: true + +class AddProjectIdToWebHookLogsDaily < Gitlab::Database::Migration[2.3] + milestone '18.7' + + def change + # rubocop:disable Migration/PreventAddingColumns -- required for sharding + add_column :web_hook_logs_daily, :project_id, :bigint, if_not_exists: true + # rubocop:enable Migration/PreventAddingColumns + end +end diff --git a/db/post_migrate/20251210180245_prepare_async_index_web_hook_logs_daily_on_organization_id.rb b/db/post_migrate/20251210180245_prepare_async_index_web_hook_logs_daily_on_organization_id.rb new file mode 100644 index 00000000000000..9118923a9d1548 --- /dev/null +++ b/db/post_migrate/20251210180245_prepare_async_index_web_hook_logs_daily_on_organization_id.rb @@ -0,0 +1,20 @@ +# frozen_string_literal: true + +class PrepareAsyncIndexWebHookLogsDailyOnOrganizationId < Gitlab::Database::Migration[2.3] + include Gitlab::Database::PartitioningMigrationHelpers + + milestone '18.7' + disable_ddl_transaction! + + INDEX_NAME = 'index_web_hook_logs_daily_on_organization_id' + + def up + # rubocop:disable Migration/PreventIndexCreation -- required for sharding + prepare_partitioned_async_index :web_hook_logs_daily, :organization_id, name: INDEX_NAME + # rubocop:enable Migration/PreventIndexCreation + end + + def down + unprepare_partitioned_async_index :web_hook_logs_daily, INDEX_NAME + end +end diff --git a/db/post_migrate/20251210180531_prepare_async_index_web_hook_logs_daily_on_group_id.rb b/db/post_migrate/20251210180531_prepare_async_index_web_hook_logs_daily_on_group_id.rb new file mode 100644 index 00000000000000..6d59ea08b154b2 --- /dev/null +++ b/db/post_migrate/20251210180531_prepare_async_index_web_hook_logs_daily_on_group_id.rb @@ -0,0 +1,20 @@ +# frozen_string_literal: true + +class PrepareAsyncIndexWebHookLogsDailyOnGroupId < Gitlab::Database::Migration[2.3] + include Gitlab::Database::PartitioningMigrationHelpers + + milestone '18.7' + disable_ddl_transaction! + + INDEX_NAME = 'index_web_hook_logs_daily_on_group_id' + + def up + # rubocop:disable Migration/PreventIndexCreation -- required for sharding + prepare_partitioned_async_index :web_hook_logs_daily, :group_id, name: INDEX_NAME + # rubocop:enable Migration/PreventIndexCreation + end + + def down + unprepare_partitioned_async_index :web_hook_logs_daily, INDEX_NAME + end +end diff --git a/db/post_migrate/20251210180643_prepare_async_index_web_hook_logs_daily_on_project_id.rb b/db/post_migrate/20251210180643_prepare_async_index_web_hook_logs_daily_on_project_id.rb new file mode 100644 index 00000000000000..a327b52aace170 --- /dev/null +++ b/db/post_migrate/20251210180643_prepare_async_index_web_hook_logs_daily_on_project_id.rb @@ -0,0 +1,20 @@ +# frozen_string_literal: true + +class PrepareAsyncIndexWebHookLogsDailyOnProjectId < Gitlab::Database::Migration[2.3] + include Gitlab::Database::PartitioningMigrationHelpers + + milestone '18.7' + disable_ddl_transaction! + + INDEX_NAME = 'index_web_hook_logs_daily_on_project_id' + + def up + # rubocop:disable Migration/PreventIndexCreation -- required for sharding + prepare_partitioned_async_index :web_hook_logs_daily, :project_id, name: INDEX_NAME + # rubocop:enable Migration/PreventIndexCreation + end + + def down + unprepare_partitioned_async_index :web_hook_logs_daily, INDEX_NAME + end +end diff --git a/db/schema_migrations/20251210175841 b/db/schema_migrations/20251210175841 new file mode 100644 index 00000000000000..309957465297ec --- /dev/null +++ b/db/schema_migrations/20251210175841 @@ -0,0 +1 @@ +b3462a58962530ec9d45480031d887260cd122393b80554f9a14505381db71d0 \ No newline at end of file diff --git a/db/schema_migrations/20251210175842 b/db/schema_migrations/20251210175842 new file mode 100644 index 00000000000000..02f07d06a40750 --- /dev/null +++ b/db/schema_migrations/20251210175842 @@ -0,0 +1 @@ +b4de00e7bcc13ce2e568a5195483b301a59215bb7dedf94064a145ad630d4ab7 \ No newline at end of file diff --git a/db/schema_migrations/20251210175843 b/db/schema_migrations/20251210175843 new file mode 100644 index 00000000000000..33280a65ba7d67 --- /dev/null +++ b/db/schema_migrations/20251210175843 @@ -0,0 +1 @@ +9a001c47565d6c3968e0627fcad98e378a09a0a42c8063d7b613fb7e2a56094b \ No newline at end of file diff --git a/db/schema_migrations/20251210180245 b/db/schema_migrations/20251210180245 new file mode 100644 index 00000000000000..afe8104cd50358 --- /dev/null +++ b/db/schema_migrations/20251210180245 @@ -0,0 +1 @@ +87e7602a6e4f866b0d1db747ac63e5410263911d938ab91322286fe93071f8ee \ No newline at end of file diff --git a/db/schema_migrations/20251210180531 b/db/schema_migrations/20251210180531 new file mode 100644 index 00000000000000..7260b0ccd522a3 --- /dev/null +++ b/db/schema_migrations/20251210180531 @@ -0,0 +1 @@ +9cdf916ae5d5c34e9e4900182bcda7c4ae413f914f559579f7f80787afc774c4 \ No newline at end of file diff --git a/db/schema_migrations/20251210180643 b/db/schema_migrations/20251210180643 new file mode 100644 index 00000000000000..320bf02b75d839 --- /dev/null +++ b/db/schema_migrations/20251210180643 @@ -0,0 +1 @@ +94e0ffa878280d0d4f0ced81ca0851d851fc1eced3f3bbdd061d30a8d490ba38 \ No newline at end of file diff --git a/db/structure.sql b/db/structure.sql index 7809a7aedda1f6..73b534f6f706e6 100644 --- a/db/structure.sql +++ b/db/structure.sql @@ -6347,6 +6347,9 @@ CREATE TABLE web_hook_logs_daily ( updated_at timestamp without time zone NOT NULL, created_at timestamp without time zone NOT NULL, url_hash text, + organization_id bigint, + group_id bigint, + project_id bigint, CONSTRAINT check_df72cb58f5 CHECK ((char_length(url_hash) <= 44)) ) PARTITION BY RANGE (created_at); -- GitLab From 13267a63958821b7d6b423704a030c8e679c7256 Mon Sep 17 00:00:00 2001 From: Tomasz Skorupa Date: Wed, 10 Dec 2025 14:12:35 -0500 Subject: [PATCH 2/4] fixup! Add sharding key columns to web_hook_logs_daily --- spec/db/schema_spec.rb | 2 +- 1 file changed, 1 insertion(+), 1 deletion(-) diff --git a/spec/db/schema_spec.rb b/spec/db/schema_spec.rb index f004d5a92d7689..1131df8c5e031f 100644 --- a/spec/db/schema_spec.rb +++ b/spec/db/schema_spec.rb @@ -247,7 +247,7 @@ # See: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/87584 # Fixes performance issues with the deletion of web-hooks with many log entries web_hook_logs: %w[web_hook_id], - web_hook_logs_daily: %w[web_hook_id], + web_hook_logs_daily: %w[web_hook_id organization_id group_id project_id], ml_candidates: %w[internal_id], value_stream_dashboard_counts: %w[namespace_id], vulnerability_export_parts: %w[start_id end_id], -- GitLab From a1d88242ba8f084b08ddf9e940c52bc7b7250369 Mon Sep 17 00:00:00 2001 From: Tomasz Skorupa Date: Fri, 12 Dec 2025 10:34:28 -0500 Subject: [PATCH 3/4] fixup! fixup! Add sharding key columns to web_hook_logs_daily --- ...re_async_index_web_hook_logs_daily_on_organization_id.rb | 6 +++--- ...1_prepare_async_index_web_hook_logs_daily_on_group_id.rb | 6 +++--- ...prepare_async_index_web_hook_logs_daily_on_project_id.rb | 6 +++--- spec/lib/gitlab/organizations/sharding_key_spec.rb | 1 + 4 files changed, 10 insertions(+), 9 deletions(-) diff --git a/db/post_migrate/20251210180245_prepare_async_index_web_hook_logs_daily_on_organization_id.rb b/db/post_migrate/20251210180245_prepare_async_index_web_hook_logs_daily_on_organization_id.rb index 9118923a9d1548..e3a3ace1fad56c 100644 --- a/db/post_migrate/20251210180245_prepare_async_index_web_hook_logs_daily_on_organization_id.rb +++ b/db/post_migrate/20251210180245_prepare_async_index_web_hook_logs_daily_on_organization_id.rb @@ -6,15 +6,15 @@ class PrepareAsyncIndexWebHookLogsDailyOnOrganizationId < Gitlab::Database::Migr milestone '18.7' disable_ddl_transaction! - INDEX_NAME = 'index_web_hook_logs_daily_on_organization_id' + PARTITIONED_INDEX_NAME = 'index_web_hook_logs_daily_on_organization_id' def up # rubocop:disable Migration/PreventIndexCreation -- required for sharding - prepare_partitioned_async_index :web_hook_logs_daily, :organization_id, name: INDEX_NAME + prepare_partitioned_async_index :web_hook_logs_daily, :organization_id, name: PARTITIONED_INDEX_NAME # rubocop:enable Migration/PreventIndexCreation end def down - unprepare_partitioned_async_index :web_hook_logs_daily, INDEX_NAME + unprepare_partitioned_async_index :web_hook_logs_daily, PARTITIONED_INDEX_NAME end end diff --git a/db/post_migrate/20251210180531_prepare_async_index_web_hook_logs_daily_on_group_id.rb b/db/post_migrate/20251210180531_prepare_async_index_web_hook_logs_daily_on_group_id.rb index 6d59ea08b154b2..c13fecb3454e44 100644 --- a/db/post_migrate/20251210180531_prepare_async_index_web_hook_logs_daily_on_group_id.rb +++ b/db/post_migrate/20251210180531_prepare_async_index_web_hook_logs_daily_on_group_id.rb @@ -6,15 +6,15 @@ class PrepareAsyncIndexWebHookLogsDailyOnGroupId < Gitlab::Database::Migration[2 milestone '18.7' disable_ddl_transaction! - INDEX_NAME = 'index_web_hook_logs_daily_on_group_id' + PARTITIONED_INDEX_NAME = 'index_web_hook_logs_daily_on_group_id' def up # rubocop:disable Migration/PreventIndexCreation -- required for sharding - prepare_partitioned_async_index :web_hook_logs_daily, :group_id, name: INDEX_NAME + prepare_partitioned_async_index :web_hook_logs_daily, :group_id, name: PARTITIONED_INDEX_NAME # rubocop:enable Migration/PreventIndexCreation end def down - unprepare_partitioned_async_index :web_hook_logs_daily, INDEX_NAME + unprepare_partitioned_async_index :web_hook_logs_daily, PARTITIONED_INDEX_NAME end end diff --git a/db/post_migrate/20251210180643_prepare_async_index_web_hook_logs_daily_on_project_id.rb b/db/post_migrate/20251210180643_prepare_async_index_web_hook_logs_daily_on_project_id.rb index a327b52aace170..581f72e74a4b0d 100644 --- a/db/post_migrate/20251210180643_prepare_async_index_web_hook_logs_daily_on_project_id.rb +++ b/db/post_migrate/20251210180643_prepare_async_index_web_hook_logs_daily_on_project_id.rb @@ -6,15 +6,15 @@ class PrepareAsyncIndexWebHookLogsDailyOnProjectId < Gitlab::Database::Migration milestone '18.7' disable_ddl_transaction! - INDEX_NAME = 'index_web_hook_logs_daily_on_project_id' + PARTITIONED_INDEX_NAME = 'index_web_hook_logs_daily_on_project_id' def up # rubocop:disable Migration/PreventIndexCreation -- required for sharding - prepare_partitioned_async_index :web_hook_logs_daily, :project_id, name: INDEX_NAME + prepare_partitioned_async_index :web_hook_logs_daily, :project_id, name: PARTITIONED_INDEX_NAME # rubocop:enable Migration/PreventIndexCreation end def down - unprepare_partitioned_async_index :web_hook_logs_daily, INDEX_NAME + unprepare_partitioned_async_index :web_hook_logs_daily, PARTITIONED_INDEX_NAME end end diff --git a/spec/lib/gitlab/organizations/sharding_key_spec.rb b/spec/lib/gitlab/organizations/sharding_key_spec.rb index 9841713eb42e28..02dbc6d7e1a26a 100644 --- a/spec/lib/gitlab/organizations/sharding_key_spec.rb +++ b/spec/lib/gitlab/organizations/sharding_key_spec.rb @@ -257,6 +257,7 @@ "fork_networks" => "https://gitlab.com/gitlab-org/gitlab/-/issues/522958", "bulk_import_configurations" => "https://gitlab.com/gitlab-org/gitlab/-/issues/536521", "pool_repositories" => "https://gitlab.com/gitlab-org/gitlab/-/issues/490484", + "web_hook_logs_daily" => "https://gitlab.com/gitlab-org/gitlab/-/work_items/524820", # All the tables below related to uploads are part of the same work to # add sharding key to the table "admin_roles" => "https://gitlab.com/gitlab-org/gitlab/-/issues/553437", -- GitLab From 42f00a3ab2796656d6e8ab86dab744d09795b320 Mon Sep 17 00:00:00 2001 From: Tomasz Skorupa Date: Mon, 15 Dec 2025 11:03:56 -0500 Subject: [PATCH 4/4] Update milestone to 18.8 --- ...20251210175841_add_organization_id_to_web_hook_logs_daily.rb | 2 +- .../20251210175842_add_group_id_to_web_hook_logs_daily.rb | 2 +- .../20251210175843_add_project_id_to_web_hook_logs_daily.rb | 2 +- ...repare_async_index_web_hook_logs_daily_on_organization_id.rb | 2 +- ...80531_prepare_async_index_web_hook_logs_daily_on_group_id.rb | 2 +- ...643_prepare_async_index_web_hook_logs_daily_on_project_id.rb | 2 +- 6 files changed, 6 insertions(+), 6 deletions(-) diff --git a/db/migrate/20251210175841_add_organization_id_to_web_hook_logs_daily.rb b/db/migrate/20251210175841_add_organization_id_to_web_hook_logs_daily.rb index 184278eda29045..54e37213ba3260 100644 --- a/db/migrate/20251210175841_add_organization_id_to_web_hook_logs_daily.rb +++ b/db/migrate/20251210175841_add_organization_id_to_web_hook_logs_daily.rb @@ -1,7 +1,7 @@ # frozen_string_literal: true class AddOrganizationIdToWebHookLogsDaily < Gitlab::Database::Migration[2.3] - milestone '18.7' + milestone '18.8' def change # rubocop:disable Migration/PreventAddingColumns -- required for sharding diff --git a/db/migrate/20251210175842_add_group_id_to_web_hook_logs_daily.rb b/db/migrate/20251210175842_add_group_id_to_web_hook_logs_daily.rb index eb41d36bdd9acc..b044a83c7a2c10 100644 --- a/db/migrate/20251210175842_add_group_id_to_web_hook_logs_daily.rb +++ b/db/migrate/20251210175842_add_group_id_to_web_hook_logs_daily.rb @@ -1,7 +1,7 @@ # frozen_string_literal: true class AddGroupIdToWebHookLogsDaily < Gitlab::Database::Migration[2.3] - milestone '18.7' + milestone '18.8' def change # rubocop:disable Migration/PreventAddingColumns -- required for sharding diff --git a/db/migrate/20251210175843_add_project_id_to_web_hook_logs_daily.rb b/db/migrate/20251210175843_add_project_id_to_web_hook_logs_daily.rb index 205ba7e4f5563b..dc13859e35abd1 100644 --- a/db/migrate/20251210175843_add_project_id_to_web_hook_logs_daily.rb +++ b/db/migrate/20251210175843_add_project_id_to_web_hook_logs_daily.rb @@ -1,7 +1,7 @@ # frozen_string_literal: true class AddProjectIdToWebHookLogsDaily < Gitlab::Database::Migration[2.3] - milestone '18.7' + milestone '18.8' def change # rubocop:disable Migration/PreventAddingColumns -- required for sharding diff --git a/db/post_migrate/20251210180245_prepare_async_index_web_hook_logs_daily_on_organization_id.rb b/db/post_migrate/20251210180245_prepare_async_index_web_hook_logs_daily_on_organization_id.rb index e3a3ace1fad56c..3f96c01a3d1393 100644 --- a/db/post_migrate/20251210180245_prepare_async_index_web_hook_logs_daily_on_organization_id.rb +++ b/db/post_migrate/20251210180245_prepare_async_index_web_hook_logs_daily_on_organization_id.rb @@ -3,7 +3,7 @@ class PrepareAsyncIndexWebHookLogsDailyOnOrganizationId < Gitlab::Database::Migration[2.3] include Gitlab::Database::PartitioningMigrationHelpers - milestone '18.7' + milestone '18.8' disable_ddl_transaction! PARTITIONED_INDEX_NAME = 'index_web_hook_logs_daily_on_organization_id' diff --git a/db/post_migrate/20251210180531_prepare_async_index_web_hook_logs_daily_on_group_id.rb b/db/post_migrate/20251210180531_prepare_async_index_web_hook_logs_daily_on_group_id.rb index c13fecb3454e44..0d870e0e774c43 100644 --- a/db/post_migrate/20251210180531_prepare_async_index_web_hook_logs_daily_on_group_id.rb +++ b/db/post_migrate/20251210180531_prepare_async_index_web_hook_logs_daily_on_group_id.rb @@ -3,7 +3,7 @@ class PrepareAsyncIndexWebHookLogsDailyOnGroupId < Gitlab::Database::Migration[2.3] include Gitlab::Database::PartitioningMigrationHelpers - milestone '18.7' + milestone '18.8' disable_ddl_transaction! PARTITIONED_INDEX_NAME = 'index_web_hook_logs_daily_on_group_id' diff --git a/db/post_migrate/20251210180643_prepare_async_index_web_hook_logs_daily_on_project_id.rb b/db/post_migrate/20251210180643_prepare_async_index_web_hook_logs_daily_on_project_id.rb index 581f72e74a4b0d..533238b67ae583 100644 --- a/db/post_migrate/20251210180643_prepare_async_index_web_hook_logs_daily_on_project_id.rb +++ b/db/post_migrate/20251210180643_prepare_async_index_web_hook_logs_daily_on_project_id.rb @@ -3,7 +3,7 @@ class PrepareAsyncIndexWebHookLogsDailyOnProjectId < Gitlab::Database::Migration[2.3] include Gitlab::Database::PartitioningMigrationHelpers - milestone '18.7' + milestone '18.8' disable_ddl_transaction! PARTITIONED_INDEX_NAME = 'index_web_hook_logs_daily_on_project_id' -- GitLab