Prepare indexes for bigint conversion
What does this MR do and why?
This MR prepares indexes for bigint conversion on some of the columns in merge_requets table. It only includes following columns only to minimize db WAL rate impact.
- assignee_id_convert_to_bigint
- merge_user_id_convert_to_bigint
- updated_by_id_convert_to_bigint
- milestone_id_convert_to_bigint
- source_project_id_convert_to_bigint
We'd also need to add FKs once these indexes are fully created in a follow up MR before we can swap these columns.
- Follow-up of Initialize conversion of MergeRequest ID column... (#507693 - closed)
- Guide: https://docs.gitlab.com/development/database/avoiding_downtime_in_migrations/#swap-the-columns-release-n--1
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.
- Stage 1
- Step 2: Async index creation (wait 1 weekend for execution) - THIS MR
- Step 2: Async FK creation / validation (wait 1 weekend for execution)
- Step 2: Swap columns, index & FK removal
- Stage 2
- Step 2: Async index creation (wait 1 weekend for execution)
- Step 2: Async FK creation / validation (wait 1 weekend for execution)
- Step 2: Swap columns, index & FK removal
- Stage 3
- Step 2: Async index creation (wait 1 weekend for execution)
- Step 2: Async FK creation / validation (wait 1 weekend for execution)
- Step 2: Swap columns, index & FK removal
- Step 3: Remove the trigger and all old integer columns
- Step 4: Remove ignore rules
Current table status(via postgres.ai)
merge_requests table
Table "public.merge_requests"
Column | Type | Collation | Nullable | Default | Storage | Compression | Stats target | Description
------------------------------------------------+-----------------------------+-----------+----------+--------------------------------------------+----------+-------------+--------------+-------------
id | integer | | not null | nextval('merge_requests_id_seq'::regclass) | plain | | |
target_branch | character varying(510) | | not null | | extended | | |
source_branch | character varying(510) | | not null | | extended | | |
source_project_id | integer | | | | plain | | |
author_id | integer | | | | plain | | |
assignee_id | integer | | | | plain | | |
title | character varying(510) | | | NULL::character varying | extended | | |
created_at | timestamp with time zone | | not null | | plain | | |
updated_at | timestamp with time zone | | not null | | plain | | |
milestone_id | integer | | | | plain | | |
merge_status | character varying(510) | | not null | 'unchecked'::character varying | extended | | |
target_project_id | integer | | not null | | plain | | |
iid | integer | | | | plain | | |
description | text | | | | extended | | |
updated_by_id | integer | | | | plain | | |
merge_error | text | | | | extended | | |
merge_params | text | | | | extended | | |
merge_when_pipeline_succeeds | boolean | | not null | false | plain | | |
merge_user_id | integer | | | | plain | | |
merge_commit_sha | character varying | | | | extended | | |
approvals_before_merge | integer | | | | plain | | |
rebase_commit_sha | character varying | | | | extended | | |
in_progress_merge_commit_sha | character varying | | | | extended | | |
lock_version | integer | | | 0 | plain | | |
title_html | text | | | | extended | | |
description_html | text | | | | extended | | |
time_estimate | integer | | | 0 | plain | | |
squash | boolean | | not null | false | plain | | |
cached_markdown_version | integer | | | | plain | | |
last_edited_at | timestamp without time zone | | | | plain | | |
last_edited_by_id | integer | | | | plain | | |
merge_jid | character varying | | | | extended | | |
discussion_locked | boolean | | | | plain | | |
latest_merge_request_diff_id | integer | | | | plain | | |
allow_maintainer_to_push | boolean | | | true | plain | | |
state_id | smallint | | not null | 1 | plain | | |
rebase_jid | character varying | | | | extended | | |
squash_commit_sha | bytea | | | | extended | | |
sprint_id | bigint | | | | plain | | |
merge_ref_sha | bytea | | | | extended | | |
draft | boolean | | not null | false | plain | | |
prepared_at | timestamp with time zone | | | | plain | | |
merged_commit_sha | bytea | | | | extended | | |
override_requested_changes | boolean | | not null | false | plain | | |
head_pipeline_id | bigint | | | | plain | | |
imported_from | smallint | | not null | 0 | plain | | |
retargeted | boolean | | not null | false | plain | | |
id_convert_to_bigint | bigint | | not null | 0 | plain | | |
source_project_id_convert_to_bigint | bigint | | | | plain | | |
author_id_convert_to_bigint | bigint | | | | plain | | |
assignee_id_convert_to_bigint | bigint | | | | plain | | |
milestone_id_convert_to_bigint | bigint | | | | plain | | |
target_project_id_convert_to_bigint | bigint | | not null | 0 | plain | | |
updated_by_id_convert_to_bigint | bigint | | | | plain | | |
merge_user_id_convert_to_bigint | bigint | | | | plain | | |
last_edited_by_id_convert_to_bigint | bigint | | | | plain | | |
latest_merge_request_diff_id_convert_to_bigint | bigint | | | | plain | | |
Indexes:
"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_source_project_and_branch_state_opened" btree (source_project_id, source_branch) WHERE state_id = 1
"idx_merge_requests_on_unmerged_state_id" btree (id) WHERE state_id <> 3
"idx_mrs_on_target_id_and_created_at_and_state_id" btree (target_project_id, state_id, created_at, id)
"index_merge_requests_for_latest_diffs_with_state_merged" btree (latest_merge_request_diff_id, target_project_id) WHERE state_id = 3
"index_merge_requests_id_created_at_prepared_at" btree (created_at, id) WHERE prepared_at IS NULL
"index_merge_requests_on_assignee_id" btree (assignee_id)
"index_merge_requests_on_author_id_and_created_at" btree (author_id, created_at)
"index_merge_requests_on_author_id_and_id" btree (author_id, id)
"index_merge_requests_on_author_id_and_target_project_id" btree (author_id, target_project_id)
"index_merge_requests_on_created_at" btree (created_at)
"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_latest_merge_request_diff_id" btree (latest_merge_request_diff_id)
"index_merge_requests_on_merge_user_id" btree (merge_user_id) WHERE merge_user_id IS NOT NULL
"index_merge_requests_on_milestone_id" btree (milestone_id)
"index_merge_requests_on_source_branch" btree (source_branch)
"index_merge_requests_on_source_project_id_and_source_branch" btree (source_project_id, source_branch)
"index_merge_requests_on_sprint_id" btree (sprint_id)
"index_merge_requests_on_target_branch" btree (target_branch)
"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_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_target_project_id_and_updated_at_and_id" btree (target_project_id, updated_at, id)
"index_merge_requests_on_title_trigram" gin (title gin_trgm_ops) WITH (fastupdate='false')
"index_merge_requests_on_tp_id_and_merge_commit_sha_and_id" btree (target_project_id, merge_commit_sha, id)
"index_merge_requests_on_updated_by_id" btree (updated_by_id) WHERE updated_by_id IS NOT NULL
"index_on_merge_requests_for_latest_diffs" btree (target_project_id) INCLUDE (id, latest_merge_request_diff_id)
Check constraints:
"check_970d272570" CHECK (lock_version IS NOT NULL)
Foreign-key constraints:
"fk_06067f5644" FOREIGN KEY (latest_merge_request_diff_id) REFERENCES merge_request_diffs(id) ON DELETE SET NULL
"fk_6149611a04" FOREIGN KEY (assignee_id) REFERENCES users(id) ON DELETE SET NULL
"fk_641731faff" FOREIGN KEY (updated_by_id) REFERENCES users(id) ON DELETE SET NULL
"fk_6a5165a692" FOREIGN KEY (milestone_id) REFERENCES milestones(id) ON DELETE SET NULL
"fk_7e85395a64" FOREIGN KEY (sprint_id) REFERENCES sprints(id) ON DELETE SET NULL
"fk_a6963e8447" FOREIGN KEY (target_project_id) REFERENCES projects(id) ON DELETE CASCADE
"fk_ad525e1f87" FOREIGN KEY (merge_user_id) REFERENCES users(id) ON DELETE SET NULL
"fk_e719a85f8a" FOREIGN KEY (author_id) REFERENCES users(id) ON DELETE SET NULL
"fk_source_project" FOREIGN KEY (source_project_id) REFERENCES projects(id) ON DELETE SET NULL
Referenced by:
TABLE "environments" CONSTRAINT "fk_01a033a308" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE SET NULL
TABLE "merge_request_assignment_events" CONSTRAINT "fk_08f7602bfd" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "scan_result_policy_violations" CONSTRAINT "fk_17ce579abf" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "merge_requests_compliance_violations" CONSTRAINT "fk_290ec1ab02" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "approvals" CONSTRAINT "fk_310d714958" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "agent_activity_events" CONSTRAINT "fk_3af186389b" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE SET NULL
TABLE "merge_requests_approval_rules_merge_requests" CONSTRAINT "fk_74e3466397" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "merge_request_diffs" CONSTRAINT "fk_8483f3258f" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "status_check_responses" CONSTRAINT "fk_f3953d86c6" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "approval_merge_request_rules" CONSTRAINT "fk_rails_004ce82224" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "merge_request_context_commits" CONSTRAINT "fk_rails_0fe0039f60" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "description_versions" CONSTRAINT "fk_rails_12b144011c" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "resource_state_events" CONSTRAINT "fk_rails_3112bba7dc" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "merge_request_blocks" CONSTRAINT "fk_rails_364d4bea8b" FOREIGN KEY (blocked_merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "merge_request_assignees" CONSTRAINT "fk_rails_443443ce6f" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "merge_requests_closing_issues" CONSTRAINT "fk_rails_458eda8667" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "merge_request_merge_schedules" CONSTRAINT "fk_rails_5294434bc3" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "reviews" CONSTRAINT "fk_rails_5ca11d8c31" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "merge_request_approval_metrics" CONSTRAINT "fk_rails_5cb1ca73f8" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "resource_iteration_events" CONSTRAINT "fk_rails_6830c13ac1" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "resource_state_events" CONSTRAINT "fk_rails_7ddc5f7457" FOREIGN KEY (source_merge_request_id) REFERENCES merge_requests(id) ON DELETE SET NULL
TABLE "deployment_merge_requests" CONSTRAINT "fk_rails_86a6d8bf12" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "excluded_merge_requests" CONSTRAINT "fk_rails_8c973feffa" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "merge_request_cleanup_schedules" CONSTRAINT "fk_rails_92dd0e705c" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "resource_label_events" CONSTRAINT "fk_rails_9851a00031" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "resource_milestone_events" CONSTRAINT "fk_rails_a006df5590" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "merge_request_user_mentions" CONSTRAINT "fk_rails_aa1b2961b1" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "merge_trains" CONSTRAINT "fk_rails_b374b5225d" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "merge_request_predictions" CONSTRAINT "fk_rails_b3b78cbcd0" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "merge_request_reviewers" CONSTRAINT "fk_rails_d9fec24b9d" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "merge_request_metrics" CONSTRAINT "fk_rails_e6d7c24d1b" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "draft_notes" CONSTRAINT "fk_rails_e753681674" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "merge_request_blocks" CONSTRAINT "fk_rails_e9387863bc" FOREIGN KEY (blocking_merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
TABLE "timelogs" CONSTRAINT "fk_timelogs_merge_requests_merge_request_id" FOREIGN KEY (merge_request_id) REFERENCES merge_requests(id) ON DELETE CASCADE
Triggers:
merge_requests_loose_fk_trigger AFTER DELETE ON merge_requests REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records()
trigger_ecc2780007c2 BEFORE INSERT OR UPDATE ON merge_requests FOR EACH ROW EXECUTE FUNCTION trigger_ecc2780007c2()
Access method: heap
Options: autovacuum_enabled=true, fillfactor=80, toast.autovacuum_enabled=trueReferences
Screenshots or screen recordings
| Before | After |
|---|---|
How to set up and validate locally
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
Related to #507695
Edited by Sincheol (David) Kim