Draft: PoC for removing partitioned data using foreign keys

What does this MR do and why?

Not to be merged

Related to #358943 (closed)

This partitions all of the tables described in https://docs.gitlab.com/ee/architecture/blueprints/ci_data_decay/pipeline_partitioning.html#why-do-we-need-to-partition-cicd-data and creates 5 partitions for each. It also adds some horrible hacks to allow the execution of Ci::CreatePipelineService for data insertion:

# Configure which partition to use for data storage: 100..105
[1] pry(main)> Thread.current[:current_partition_id] = 103
=> 103
[2] pry(main)> Ci::CreatePipelineService.new(Project.find(75), User.first, { ref: 'main', variables_attributes: [ {key: 'GIT_STRATEGY', value: 'none' } ] }).execute!(:web, {})
=> #<ServiceResponse:0x00007ff272967960
 @http_status=:ok,
 @message=nil,
 @payload=
  #<Ci::Pipeline:0x00007ff286766300
   id: 544,
   ref: "main",
   sha: "bc47a984eb5f8b5712a420ac1c043396e05f973f",
   before_sha: "0000000000000000000000000000000000000000",
   created_at: Thu, 14 Jul 2022 12:44:39.769820000 UTC +00:00,
   updated_at: Thu, 14 Jul 2022 12:44:39.769820000 UTC +00:00,
   tag: false,
   yaml_errors: nil,
   committed_at: nil,
   project_id: 75,
   status: "created",
   started_at: nil,
   finished_at: nil,
   duration: nil,
   user_id: 1,
   lock_version: 0,
   auto_canceled_by_id: nil,
   pipeline_schedule_id: nil,
   source: "web",
   config_source: "repository_source",
   protected: true,
   failure_reason: nil,
   iid: 57,
   merge_request_id: nil,
   source_sha: nil,
   target_sha: nil,
   external_pull_request_id: nil,
   ci_ref_id: 73,
   locked: "artifacts_locked",
   partition_id: 103>,
 @status=:success>

Data is inserted in the right partition:

gitlabhq_development_ci=# select id, iid, partition_id, project_id from p_ci_pipelines_103;
 id  | iid | partition_id | project_id
-----+-----+--------------+------------
 520 |  33 |          103 |         75
 521 |  34 |          103 |         75
 522 |  35 |          103 |         75
 544 |  57 |          103 |         75
(4 rows)

gitlabhq_development_ci=# select id, name, commit_id, partition_id from p_ci_builds_103;
  id   |  name   | commit_id | partition_id
-------+---------+-----------+--------------
 26751 | build1  |       520 |          103
 26752 | test1   |       520 |          103
 26753 | test2   |       520 |          103
 26754 | deploy1 |       520 |          103
 26755 | build1  |       521 |          103
 26756 | test1   |       521 |          103
 26757 | test2   |       521 |          103
 26758 | deploy1 |       521 |          103
 26759 | build1  |       522 |          103
 26760 | test1   |       522 |          103
 26761 | test2   |       522 |          103
 26762 | deploy1 |       522 |          103
 26847 | build1  |       544 |          103
 26848 | test1   |       544 |          103
 26849 | test2   |       544 |          103
 26850 | deploy1 |       544 |          103
(16 rows)

Screenshots or screen recordings

These are strongly recommended to assist reviewers and reduce the time to merge your change.

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.

db/migrate/20220714083150_partition_ci_builds.rb

@@ -35,6 +35,12 @@ def up
        REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT
        EXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records();
     SQL
+
+    execute <<~SQL
+      ALTER TABLE p_ci_builds
+        ADD CONSTRAINT fk_d3130c9a7f FOREIGN KEY (commit_id, partition_id)
+          REFERENCES p_ci_pipelines(id, partition_id) ON DELETE CASCADE;
+    SQL
   end
 
   def down