Swap MergeRequest ID columns (Step 2)

Timeline of bigint column migration on merge_requests table

We split the Step 2 of bigint migration into 3 stages to avoid possible issues related to WAL rate caused by creating two many indexes all at once. Please see #507695 (comment 2595156011) for more information about how we're splitting these up.

We will append MRs as we created to each steps below for tracking purposes.

Stage 1

We add two MRs as users reporting existing mr indexes are missing from old instances, causing the bigint swap migration failure. For next two stages, we will also add these kind of MRs as well.

assignee_id, merge_user_id, updated_by_id, milestone_id, source_project_id related

"index_merge_requests_on_assignee_id" btree (assignee_id)
"index_merge_requests_on_merge_user_id" btree (merge_user_id) WHERE merge_user_id IS NOT NULL
"index_merge_requests_on_updated_by_id" btree (updated_by_id) WHERE updated_by_id IS NOT NULL
"index_merge_requests_on_milestone_id" btree (milestone_id)
"idx_merge_requests_on_source_project_and_branch_tate_opened" btree (source_project_id, source_branch) WHERE state_id = 1
"index_merge_requests_on_source_project_id_and_source_branch" btree (source_project_id, source_branch)
ALTER TABLE ONLY merge_requests
    ADD CONSTRAINT fk_6149611a04 FOREIGN KEY (assignee_id) REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE ONLY merge_requests
    ADD CONSTRAINT fk_641731faff FOREIGN KEY (updated_by_id) REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE ONLY merge_requests
    ADD CONSTRAINT fk_6a5165a692 FOREIGN KEY (milestone_id) REFERENCES milestones(id) ON DELETE SET NULL;
ALTER TABLE ONLY merge_requests
    ADD CONSTRAINT fk_ad525e1f87 FOREIGN KEY (merge_user_id) REFERENCES users(id) ON DELETE SET NULL;
ALTER TABLE ONLY merge_requests
    ADD CONSTRAINT fk_source_project FOREIGN KEY (source_project_id) REFERENCES projects(id) ON DELETE SET NULL;

Stage 2

target_project_id and latest_merge_request_diff_id and last_edited_by_id related

"index_merge_requests_on_target_project_id_and_iid" UNIQUE, btree (target_project_id, iid)
"index_merge_requests_on_target_project_id_and_merged_commit_sha" btree (target_project_id, merged_commit_sha)
"index_merge_requests_on_target_project_id_and_source_branch" btree (target_project_id, source_branch)
"index_merge_requests_on_target_project_id_and_squash_commit_sha" btree (target_project_id, squash_commit_sha)
"index_merge_requests_on_target_project_id_and_target_branch" btree (target_project_id, target_branch) WHERE state_id = 1 AND merge_when_pipeline_succeeds = true
"index_merge_requests_on_latest_merge_request_diff_id" btree (latest_merge_request_diff_id)
"index_merge_requests_for_latest_diffs_with_state_merged" btree (latest_merge_request_diff_id, target_project_id) WHERE state_id = 3
-- Following indexes are recreated due to other column migration
"idx_mrs_on_target_id_and_created_at_and_state_id" btree (target_project_id, state_id, created_at, id)
"index_merge_requests_on_target_project_id_and_created_at_and_id" btree (target_project_id, created_at, id)
"index_merge_requests_on_target_project_id_and_updated_at_and_id" btree (target_project_id, updated_at, id)
"index_merge_requests_on_tp_id_and_merge_commit_sha_and_id" btree (target_project_id, merge_commit_sha, id)
"index_merge_requests_on_author_id_and_target_project_id" btree (author_id, target_project_id)
"index_on_merge_requests_for_latest_diffs" btree (target_project_id) INCLUDE (id, latest_merge_request_diff_id)
ALTER TABLE ONLY merge_requests
    ADD CONSTRAINT fk_a6963e8447 FOREIGN KEY (target_project_id) REFERENCES projects(id) ON DELETE CASCADE;

ALTER TABLE p_generated_ref_commits
    ADD CONSTRAINT fk_generated_ref_commits_merge_request_id FOREIGN KEY (project_id, merge_request_iid) REFERENCES merge_requests(target_project_id, iid) ON DELETE CASCADE;

ALTER TABLE ONLY merge_requests
    ADD CONSTRAINT fk_06067f5644 FOREIGN KEY (latest_merge_request_diff_id) REFERENCES merge_request_diffs(id) ON DELETE SET NULL;

Stage 3

  • Step 1: Async index creation (wait 1 weekend for execution)
  • Step 2: Async FK creation / validation (wait 1 weekend for execution)
  • Step 3: Swap columns, index & FK removal

id and author_id related

"merge_requests_pkey" PRIMARY KEY, btree (id)
"idx_merge_requests_on_id_and_merge_jid" btree (id, merge_jid) WHERE merge_jid IS NOT NULL AND state_id = 4
"idx_merge_requests_on_merged_state" btree (id) WHERE state_id = 3
"idx_merge_requests_on_unmerged_state_id" btree (id) WHERE state_id <> 3
"index_merge_requests_id_created_at_prepared_at" btree (created_at, id) WHERE prepared_at IS NULL -- currently being removed via https://gitlab.com/gitlab-org/gitlab/-/merge_requests/197599
"index_merge_requests_on_author_id_and_id" btree (author_id, id)
"index_merge_requests_on_author_id_and_created_at" btree (author_id, created_at)
-- Following indexes are recreated due to other column migration
"idx_mrs_on_target_id_and_created_at_and_state_id" btree (target_project_id, state_id, created_at, id)
"index_merge_requests_on_target_project_id_and_created_at_and_id" btree (target_project_id, created_at, id)
"index_merge_requests_on_target_project_id_and_updated_at_and_id" btree (target_project_id, updated_at, id)
"index_merge_requests_on_tp_id_and_merge_commit_sha_and_id" btree (target_project_id, merge_commit_sha, id)
"index_on_merge_requests_for_latest_diffs" btree (target_project_id) INCLUDE (id, latest_merge_request_diff_id)
"index_merge_requests_on_author_id_and_target_project_id" btree (author_id, target_project_id)

NOT TOUCHED 

"index_merge_requests_on_sprint_id" btree (sprint_id)
"index_merge_requests_on_created_at" btree (created_at)
"index_merge_requests_on_source_branch" btree (source_branch)
"index_merge_requests_on_title_trigram" gin (title gin_trgm_ops) WITH (fastupdate='false')
"index_merge_requests_on_description_trigram" gin (description gin_trgm_ops) WITH (fastupdate='false')
"index_merge_requests_on_head_pipeline_id" btree (head_pipeline_id)
"index_merge_requests_on_target_branch" btree (target_branch)

Next Step

After above stages in Step 2 are all finished, we would move on to step 3 and 4 in follow up issues.

Edited by zli