Skip to content

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

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)

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