From 0cfe5ebf0da7cc93ac5e6b1bb048ec83576ffe89 Mon Sep 17 00:00:00 2001
From: Marius Bobin <mbobin@gitlab.com>
Date: Mon, 6 Feb 2023 13:46:20 +0200
Subject: [PATCH] Add partitioned index and FK for ci_job_artifacts and
 ci_builds

Changelog: added
---
 ...ob_artifacts_on_partition_id_and_job_id.rb | 17 +++++++++
 ...ob_artifacts_on_partition_id_and_job_id.rb | 37 +++++++++++++++++++
 db/schema_migrations/20230127152727           |  1 +
 db/schema_migrations/20230127152728           |  1 +
 db/structure.sql                              |  5 +++
 5 files changed, 61 insertions(+)
 create mode 100644 db/post_migrate/20230127152727_add_fk_index_to_ci_job_artifacts_on_partition_id_and_job_id.rb
 create mode 100644 db/post_migrate/20230127152728_add_fk_to_ci_job_artifacts_on_partition_id_and_job_id.rb
 create mode 100644 db/schema_migrations/20230127152727
 create mode 100644 db/schema_migrations/20230127152728

diff --git a/db/post_migrate/20230127152727_add_fk_index_to_ci_job_artifacts_on_partition_id_and_job_id.rb b/db/post_migrate/20230127152727_add_fk_index_to_ci_job_artifacts_on_partition_id_and_job_id.rb
new file mode 100644
index 00000000000000..66933fa600ec70
--- /dev/null
+++ b/db/post_migrate/20230127152727_add_fk_index_to_ci_job_artifacts_on_partition_id_and_job_id.rb
@@ -0,0 +1,17 @@
+# frozen_string_literal: true
+
+class AddFkIndexToCiJobArtifactsOnPartitionIdAndJobId < Gitlab::Database::Migration[2.1]
+  disable_ddl_transaction!
+
+  INDEX_NAME = :index_ci_job_artifacts_on_partition_id_job_id
+  TABLE_NAME = :ci_job_artifacts
+  COLUMNS = [:partition_id, :job_id]
+
+  def up
+    add_concurrent_index(TABLE_NAME, COLUMNS, name: INDEX_NAME)
+  end
+
+  def down
+    remove_concurrent_index_by_name(TABLE_NAME, INDEX_NAME)
+  end
+end
diff --git a/db/post_migrate/20230127152728_add_fk_to_ci_job_artifacts_on_partition_id_and_job_id.rb b/db/post_migrate/20230127152728_add_fk_to_ci_job_artifacts_on_partition_id_and_job_id.rb
new file mode 100644
index 00000000000000..7c7cf7861ea2b5
--- /dev/null
+++ b/db/post_migrate/20230127152728_add_fk_to_ci_job_artifacts_on_partition_id_and_job_id.rb
@@ -0,0 +1,37 @@
+# frozen_string_literal: true
+
+class AddFkToCiJobArtifactsOnPartitionIdAndJobId < Gitlab::Database::Migration[2.1]
+  disable_ddl_transaction!
+
+  SOURCE_TABLE_NAME = :ci_job_artifacts
+  TARGET_TABLE_NAME = :ci_builds
+  COLUMN = :job_id
+  TARGET_COLUMN = :id
+  FK_NAME = :fk_rails_c5137cb2c1_p
+  PARTITION_COLUMN = :partition_id
+
+  def up
+    add_concurrent_foreign_key(
+      SOURCE_TABLE_NAME,
+      TARGET_TABLE_NAME,
+      column: [PARTITION_COLUMN, COLUMN],
+      target_column: [PARTITION_COLUMN, TARGET_COLUMN],
+      validate: false,
+      reverse_lock_order: true,
+      on_update: :cascade,
+      on_delete: :cascade,
+      name: FK_NAME
+    )
+  end
+
+  def down
+    with_lock_retries do
+      remove_foreign_key_if_exists(
+        SOURCE_TABLE_NAME,
+        TARGET_TABLE_NAME,
+        name: FK_NAME,
+        reverse_lock_order: true
+      )
+    end
+  end
+end
diff --git a/db/schema_migrations/20230127152727 b/db/schema_migrations/20230127152727
new file mode 100644
index 00000000000000..e29d1af05aca32
--- /dev/null
+++ b/db/schema_migrations/20230127152727
@@ -0,0 +1 @@
+03377600dfb6df35b3541c4a6a1fdf19d29179c55ed101141289442322086651
\ No newline at end of file
diff --git a/db/schema_migrations/20230127152728 b/db/schema_migrations/20230127152728
new file mode 100644
index 00000000000000..e2fa412b5aafe1
--- /dev/null
+++ b/db/schema_migrations/20230127152728
@@ -0,0 +1 @@
+50e5a64558a8253e13514d158cc265e463dcfc92d3615a1f207d18b94778ec68
\ No newline at end of file
diff --git a/db/structure.sql b/db/structure.sql
index 9f4100a0d20d40..12c172f829b653 100644
--- a/db/structure.sql
+++ b/db/structure.sql
@@ -29240,6 +29240,8 @@ CREATE INDEX index_ci_job_artifacts_on_id_project_id_and_file_type ON ci_job_art
 
 CREATE UNIQUE INDEX index_ci_job_artifacts_on_job_id_and_file_type ON ci_job_artifacts USING btree (job_id, file_type);
 
+CREATE INDEX index_ci_job_artifacts_on_partition_id_job_id ON ci_job_artifacts USING btree (partition_id, job_id);
+
 CREATE INDEX index_ci_job_artifacts_on_project_id ON ci_job_artifacts USING btree (project_id);
 
 CREATE INDEX index_ci_job_artifacts_on_project_id_and_id ON ci_job_artifacts USING btree (project_id, id);
@@ -35931,6 +35933,9 @@ ALTER TABLE ONLY boards_epic_board_recent_visits
 ALTER TABLE ONLY ci_job_artifacts
     ADD CONSTRAINT fk_rails_c5137cb2c1 FOREIGN KEY (job_id) REFERENCES ci_builds(id) ON DELETE CASCADE;
 
+ALTER TABLE ONLY ci_job_artifacts
+    ADD CONSTRAINT fk_rails_c5137cb2c1_p FOREIGN KEY (partition_id, job_id) REFERENCES ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE NOT VALID;
+
 ALTER TABLE ONLY packages_events
     ADD CONSTRAINT fk_rails_c6c20d0094 FOREIGN KEY (package_id) REFERENCES packages_packages(id) ON DELETE SET NULL;
 
-- 
GitLab