# Production Change ## Change Summary The migration in [!244091](https://gitlab.com/gitlab-org/gitlab/-/merge_requests/244091) would be skipped for normal post-deployment migration process due to hard to acquire exclusive locks on busy `merge_request_diff_commits` table. We plan to skip the migration (mark as succeed and never run) for normal post-deployment migration process for `main` DB (`ci` and `sec` will run as they contain no data), and in this CR, we execute it during low-traffic hours. ## Change Details 1. **Services Impacted** - ~"Service::Postgres" 2. **Change Technician** - {+ DRI for the execution of this change +} 3. **Change Reviewer** - @krasio 4. **Scheduled Date and Time (UTC in format YYYY-MM-DD HH:MM)** - {+ TBD +} 5. **Time tracking** - 120 minutes (Migration exec: under 1s. Lock acquisition: expected under 2 minutes; bounded at ~45 minutes by `with_lock_retries` before the retry budget is exhausted. Rollback: identical profile) 6. **Downtime Component** - No downtime required > [!NOTE] > To find a **Change Reviewer**, use the [Reviewer Roulette dashboard](https://gitlab-org.gitlab.io/gitlab-roulette/?currentProject=production&order=1) > scoped to the `production` project. Spin the wheel. If roulette selects the current EOC (check > [the incident.io GitLab.com Production EOC schedule](https://app.incident.io/gitlab/on-call/schedules/01K5YWAGZ7YCQGAG7ATQ9XQWHW)), > please re-spin to select another reviewer. > [!IMPORTANT] > If your change involves scheduled maintenance, add a step to set and > [unset maintenance mode](https://gitlab.com/gitlab-com/runbooks/-/blob/master/docs/monitoring/set_maintenance_window.md) > per our runbooks. This will make sure SLA calculations adjust for the maintenance period. ## Preparation > [!NOTE] > The following checklists must be done in advance, before setting the label ~"change::scheduled" ### Change Reviewer checklist ~C4 ~C3 ~C2 ~C1: - [ ] Check if the following applies: - The **scheduled day and time** of execution of the change is appropriate. - The [change plan](#detailed-steps-for-the-change) is technically accurate. - The change plan includes **estimated timing values** based on previous testing. - The change plan includes a viable [rollback plan](#rollback). - The specified [metrics/monitoring dashboards](#key-metrics-to-observe) provide sufficient visibility for the change. ~C2 ~C1: - [ ] Check if the following applies: - The complexity of the plan is appropriate for the corresponding risk of the change. (i.e. the plan contains clear details). - The change plan includes success measures for all steps/milestones during the execution. - The change adequately minimizes risk within the environment/service. - The performance implications of executing the change are well-understood and documented. - The specified metrics/monitoring dashboards provide sufficient visibility for the change. - If not, is it possible (or necessary) to make changes to observability platforms for added visibility? - The change has a primary and secondary SRE with knowledge of the details available during the change window. - The change window has been agreed with Release Managers in advance of the change. If the change is planned for APAC hours, this issue has an agreed pre-change approval. - The labels ~"blocks deployments" and/or ~"blocks feature-flags" are applied as necessary. ### Change Technician checklist - [x] The [Change Criticality](https://handbook.gitlab.com/handbook/engineering/infrastructure-platforms/change-management/#change-criticalities) has been set appropriately and requirements have been reviewed. - [x] The [change plan](#detailed-steps-for-the-change) is technically accurate. - [x] The [rollback plan](#rollback) is technically accurate and detailed enough to be executed by anyone with access. - [x] This Change Issue is linked to the appropriate Issue and/or Epic - [ ] Change has been tested in staging and results noted in a comment on this issue. - [ ] A dry-run has been conducted and results noted in a comment on this issue. - [ ] The change execution window respects the [Production Change Lock periods](https://handbook.gitlab.com/handbook/engineering/infrastructure-platforms/change-management/#production-change-lock-pcl). - [ ] Once all boxes above are checked, mark the change request as scheduled: `/label ~"change::scheduled"` - [ ] For ~C1 and ~C2 change issues, the change event is added to the [GitLab Production](https://calendar.google.com/calendar/embed?src=gitlab.com_si2ach70eb1j65cnu040m3alq0%40group.calendar.google.com) calendar by the [change-scheduler bot](https://gitlab.com/gitlab-com/gl-infra/ops-team/toolkit/change-scheduler). It is schedule to run every 2 hours. - [ ] For ~C1 and ~C2 change issues, Platform Leadership provides approval with the ~platform_leadership_approved label on the issue. Mention `@gitlab-org/saas-platforms/change-review-leadership` in this issue with a reference to [review guidelines](https://handbook.gitlab.com/handbook/engineering/infrastructure-platforms/change-management/platform-leadership-review/) to get approval and provide visibility to all infrastructure managers. - [ ] For ~C1, ~C2, or ~"blocks deployments" change issues, confirm with Release managers that the change does not overlap or hinder any release process (In `#production` channel, mention `@release-managers` and this issue and await their acknowledgment.) - [ ] For ~C1 change issues or ~C2 change issues happening during weekend, SREs on-call must be informed [at least 2 weeks in advance](https://handbook.gitlab.com/handbook/engineering/infrastructure-platforms/change-management/#approval). Check [the incident.io GitLab.com Production EOC schedule](https://app.incident.io/gitlab/on-call/schedules/01K5YWAGZ7YCQGAG7ATQ9XQWHW) to find who will be on-call at the scheduled day and time. ## Detailed steps for the change ### Pre-execution steps > [!NOTE] > The following steps should be done right at the scheduled time of the change request. The [preparation steps](#preparation) are > listed below. - [ ] Make sure all tasks in [Change Technician checklist](#change-technician-checklist) are done - [ ] For ~C1 and ~C2 change issues, the SRE on-call has been informed prior to change being rolled out. - [ ] The SRE on-call provided approval with the ~eoc_approved label on the issue. - [ ] For ~C1, ~C2, or ~"blocks deployments" change issues, Release managers have been informed prior to change being rolled out. (In `#production` channel, mention `@release-managers` and this issue and await their acknowledgment.) - [ ] There are currently no [active incidents](https://gitlab.com/gitlab-com/gl-infra/production/-/issues/?sort=created_date&state=opened&label_name%5B%5D=Incident%3A%3AActive&or%5Blabel_name%5D%5B%5D=severity%3A%3A1&or%5Blabel_name%5D%5B%5D=severity%3A%3A2&first_page_size=20) that are ~severity::1 or ~severity::2 - [ ] If the change involves doing maintenance on a database host, an appropriate silence targeting the host(s) should be added for the duration of the change. - [ ] Check for long-running locks that would block the ACCESS EXCLUSIVE acquisition, and wait for them to clear. In #22716, an autovacuum held ShareUpdateExclusiveLock for 36 minutes. ```sql SELECT pg_stat_activity.pid, pg_stat_activity.state, now() - pg_stat_activity.xact_start AS txn_age, pg_locks.mode, pg_class.relname FROM pg_locks JOIN pg_stat_activity ON pg_stat_activity.pid = pg_locks.pid LEFT JOIN pg_class ON pg_class.oid = pg_locks.relation WHERE pg_class.relname IN ('merge_request_diff_commits', 'merge_request_diff_commits_b5377a7a34') AND pg_locks.mode IN ('ShareUpdateExclusiveLock', 'AccessExclusiveLock', 'ExclusiveLock') ORDER BY txn_age DESC; ``` - [ ] Check for a wraparound prevention vacuum on the involved tables. Check autovacuum activity - https://dashboards.gitlab.net/goto/afvt5fqrlvlkwa?orgId=1. ### Change steps - steps to take to execute the change _Estimated Time to Complete (mins)_ - 120 - [ ] Set label \~"change::in-progress" `/label ~change::in-progress` - [ ] Connect to the gprd (GitLab production) regional cluster: gke_gitlab-production_us-east1_gprd-gitlab-gke. GKE stands for Google Kubernetes Engine. - [ ] Apply a PodDisruptionBudget (PDB) that protects the toolbox pod. Use a manifest with maxUnavailable set to 0 and selector app: toolbox, in namespace gitlab. ``` kubectl apply -f - <<'EOF' apiVersion: policy/v1 kind: PodDisruptionBudget metadata: name: gitlab-migrations-toolbox-pdb namespace: gitlab spec: maxUnavailable: 0 selector: matchLabels: app: toolbox EOF ``` - [ ] Open a shell in the toolbox pod: `kubectl exec -it deploy/gitlab-migrations-toolbox --container toolbox --namespace gitlab -- /bin/bash` - [ ] Start a Rails console in that shell: `gitlab-rails console` - [ ] Load the migration file: `require Rails.root.join('db/post_migrate/20260824105520_swap_merge_request_diff_commits_table.rb')` - [ ] Run the migration: `SwapMergeRequestDiffCommitsTable.new.up` - [ ] Verify the console output shows success. - [ ] Verify the schema on `main` DB matches expected state: <details> <summary>Click to expand schema verification</summary> - `merge_request_diff_commits` (previously `merge_request_diff_commits_b5377a7a34`) ```sql Partitioned table "public.merge_request_diff_commits" Column | Type | Collation | Nullable | Default | Storage -----------------------------------+---------+-----------+----------+---------+--------- merge_request_commits_metadata_id | bigint | | not null | | plain merge_request_diff_id | bigint | | not null | | plain project_id | bigint | | not null | | plain relative_order | integer | | not null | | plain Partition key: RANGE (project_id) Indexes: "merge_request_diff_commits_pkey" PRIMARY KEY, btree (merge_request_diff_id, relative_order, project_id) "index_merge_request_diff_commits_b5377a7a34_on_project_id" btree (project_id) "index_partitioned_mrdc_on_merge_request_commits_metadata_id" btree (merge_request_commits_metadata_id) Triggers: table_sync_trigger_57c8465cd7_reverse_delete AFTER DELETE ON merge_request_diff_commits REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION table_sync_function_0992e728d3_reverse_delete() table_sync_trigger_57c8465cd7_reverse_insert AFTER INSERT ON merge_request_diff_commits REFERENCING NEW TABLE AS new_table FOR EACH STATEMENT EXECUTE FUNCTION table_sync_function_0992e728d3_reverse_insert() Partitions: gitlab_partitions_dynamic.merge_request_diff_commits_1 FOR VALUES FROM ('1') TO ('2000001'), gitlab_partitions_dynamic.merge_request_diff_commits_10000001 FOR VALUES FROM ('10000001') TO ('12000001'), gitlab_partitions_dynamic.merge_request_diff_commits_100000001 FOR VALUES FROM ('100000001') TO ('102000001'), gitlab_partitions_dynamic.merge_request_diff_commits_102000001 FOR VALUES FROM ('102000001') TO ('104000001'), gitlab_partitions_dynamic.merge_request_diff_commits_104000001 FOR VALUES FROM ('104000001') TO ('106000001'), gitlab_partitions_dynamic.merge_request_diff_commits_12000001 FOR VALUES FROM ('12000001') TO ('14000001'), gitlab_partitions_dynamic.merge_request_diff_commits_14000001 FOR VALUES FROM ('14000001') TO ('16000001'), gitlab_partitions_dynamic.merge_request_diff_commits_16000001 FOR VALUES FROM ('16000001') TO ('18000001'), gitlab_partitions_dynamic.merge_request_diff_commits_18000001 FOR VALUES FROM ('18000001') TO ('20000001'), gitlab_partitions_dynamic.merge_request_diff_commits_2000001 FOR VALUES FROM ('2000001') TO ('4000001'), gitlab_partitions_dynamic.merge_request_diff_commits_20000001 FOR VALUES FROM ('20000001') TO ('22000001'), gitlab_partitions_dynamic.merge_request_diff_commits_22000001 FOR VALUES FROM ('22000001') TO ('24000001'), gitlab_partitions_dynamic.merge_request_diff_commits_24000001 FOR VALUES FROM ('24000001') TO ('26000001'), gitlab_partitions_dynamic.merge_request_diff_commits_26000001 FOR VALUES FROM ('26000001') TO ('28000001'), gitlab_partitions_dynamic.merge_request_diff_commits_28000001 FOR VALUES FROM ('28000001') TO ('30000001'), gitlab_partitions_dynamic.merge_request_diff_commits_30000001 FOR VALUES FROM ('30000001') TO ('32000001'), gitlab_partitions_dynamic.merge_request_diff_commits_32000001 FOR VALUES FROM ('32000001') TO ('34000001'), gitlab_partitions_dynamic.merge_request_diff_commits_34000001 FOR VALUES FROM ('34000001') TO ('36000001'), gitlab_partitions_dynamic.merge_request_diff_commits_36000001 FOR VALUES FROM ('36000001') TO ('38000001'), gitlab_partitions_dynamic.merge_request_diff_commits_38000001 FOR VALUES FROM ('38000001') TO ('40000001'), gitlab_partitions_dynamic.merge_request_diff_commits_4000001 FOR VALUES FROM ('4000001') TO ('6000001'), gitlab_partitions_dynamic.merge_request_diff_commits_40000001 FOR VALUES FROM ('40000001') TO ('42000001'), gitlab_partitions_dynamic.merge_request_diff_commits_42000001 FOR VALUES FROM ('42000001') TO ('44000001'), gitlab_partitions_dynamic.merge_request_diff_commits_44000001 FOR VALUES FROM ('44000001') TO ('46000001'), gitlab_partitions_dynamic.merge_request_diff_commits_46000001 FOR VALUES FROM ('46000001') TO ('48000001'), gitlab_partitions_dynamic.merge_request_diff_commits_48000001 FOR VALUES FROM ('48000001') TO ('50000001'), gitlab_partitions_dynamic.merge_request_diff_commits_50000001 FOR VALUES FROM ('50000001') TO ('52000001'), gitlab_partitions_dynamic.merge_request_diff_commits_52000001 FOR VALUES FROM ('52000001') TO ('54000001'), gitlab_partitions_dynamic.merge_request_diff_commits_54000001 FOR VALUES FROM ('54000001') TO ('56000001'), gitlab_partitions_dynamic.merge_request_diff_commits_56000001 FOR VALUES FROM ('56000001') TO ('58000001'), gitlab_partitions_dynamic.merge_request_diff_commits_58000001 FOR VALUES FROM ('58000001') TO ('60000001'), gitlab_partitions_dynamic.merge_request_diff_commits_6000001 FOR VALUES FROM ('6000001') TO ('8000001'), gitlab_partitions_dynamic.merge_request_diff_commits_60000001 FOR VALUES FROM ('60000001') TO ('62000001'), gitlab_partitions_dynamic.merge_request_diff_commits_62000001 FOR VALUES FROM ('62000001') TO ('64000001'), gitlab_partitions_dynamic.merge_request_diff_commits_64000001 FOR VALUES FROM ('64000001') TO ('66000001'), gitlab_partitions_dynamic.merge_request_diff_commits_66000001 FOR VALUES FROM ('66000001') TO ('68000001'), gitlab_partitions_dynamic.merge_request_diff_commits_68000001 FOR VALUES FROM ('68000001') TO ('70000001'), gitlab_partitions_dynamic.merge_request_diff_commits_70000001 FOR VALUES FROM ('70000001') TO ('72000001'), gitlab_partitions_dynamic.merge_request_diff_commits_72000001 FOR VALUES FROM ('72000001') TO ('74000001'), gitlab_partitions_dynamic.merge_request_diff_commits_74000001 FOR VALUES FROM ('74000001') TO ('76000001'), gitlab_partitions_dynamic.merge_request_diff_commits_76000001 FOR VALUES FROM ('76000001') TO ('78000001'), gitlab_partitions_dynamic.merge_request_diff_commits_78000001 FOR VALUES FROM ('78000001') TO ('80000001'), gitlab_partitions_dynamic.merge_request_diff_commits_8000001 FOR VALUES FROM ('8000001') TO ('10000001'), gitlab_partitions_dynamic.merge_request_diff_commits_80000001 FOR VALUES FROM ('80000001') TO ('82000001'), gitlab_partitions_dynamic.merge_request_diff_commits_82000001 FOR VALUES FROM ('82000001') TO ('84000001'), gitlab_partitions_dynamic.merge_request_diff_commits_84000001 FOR VALUES FROM ('84000001') TO ('86000001'), gitlab_partitions_dynamic.merge_request_diff_commits_86000001 FOR VALUES FROM ('86000001') TO ('88000001'), gitlab_partitions_dynamic.merge_request_diff_commits_88000001 FOR VALUES FROM ('88000001') TO ('90000001'), gitlab_partitions_dynamic.merge_request_diff_commits_90000001 FOR VALUES FROM ('90000001') TO ('92000001'), gitlab_partitions_dynamic.merge_request_diff_commits_92000001 FOR VALUES FROM ('92000001') TO ('94000001'), gitlab_partitions_dynamic.merge_request_diff_commits_94000001 FOR VALUES FROM ('94000001') TO ('96000001'), gitlab_partitions_dynamic.merge_request_diff_commits_96000001 FOR VALUES FROM ('96000001') TO ('98000001'), gitlab_partitions_dynamic.merge_request_diff_commits_98000001 FOR VALUES FROM ('98000001') TO ('100000001') ``` - `merge_request_diff_commits_archived` (previously `merge_request_diff_commits`) ```sql Table "public.merge_request_diff_commits_archived" Column | Type | Collation | Nullable | Default | Storage -----------------------------------+-----------------------------+-----------+----------+-------------+---------- authored_date | timestamp without time zone | | | | plain committed_date | timestamp without time zone | | | | plain merge_request_diff_id | integer | | not null | | plain relative_order | integer | | not null | | plain sha | bytea | | | | extended message | text | | | | extended trailers | jsonb | | | '{}'::jsonb | extended commit_author_id | bigint | | | | plain committer_id | bigint | | | | plain merge_request_commits_metadata_id | bigint | | | | plain project_id | bigint | | | | plain Indexes: "merge_request_diff_commits_archived_pkey" PRIMARY KEY, btree (merge_request_diff_id, relative_order) "index_merge_request_diff_commits_on_project_id" btree (project_id) "index_merge_request_diff_commits_on_sha" btree (sha) "index_mrdc_on_merge_request_commits_metadata_id" btree (merge_request_commits_metadata_id) WHERE merge_request_commits_metadata_id IS NOT NULL ``` Notes for validation - Only the primary key constraints are renamed by the swap. All other indexes keep their pre-swap names, so `merge_request_diff_commits` retains indexes with the `_b5377a7a34` infix and `merge_request_diff_commits_archived` retains indexes named `..._merge_request_diff_commits_...`. This is expected. - The migration runs as a single transaction. A failed attempt rolls back completely and leaves the schema untouched, so it is safe to retry. </details> - [ ] Delete the PodDisruptionBudget created above: `kubectl -n gitlab delete pdb gitlab-migrations-toolbox-pdb` - [ ] Set label ~"change::complete" `/label ~change::complete` > [!NOTE] > Abort criterion: if lock acquisition degrades the rails_primary_sql apdex, or visibly stalls merge request diff writes, stop the change. Delete the PodDisruptionBudget and reschedule. Do not grind for hours. ## Rollback ### Rollback steps - steps to be taken in the event of a need to rollback this change *Estimated Time to Complete (mins)* - NA - [ ] In case the migration fails after several attempts, then we need to abort this CR. (no rollback is required). - [ ] The main consequences would be, we could not apply changes to `main` DB in migrations. It's very unlikely this would cause any negative impact to the production `main` DB. - [ ] Set label ~"change::aborted" `/label ~change::aborted` ## Monitoring ### Key metrics to observe > [!NOTE] > Adjust the time window to the execution day when reviewing these dashboards. - Metric: rails_primary_sql SLI Apdex - Location: https://dashboards.gitlab.net/d/patroni-main/patroni3a-overview?orgId=1&from=now-6h%2Fm&to=now%2Fm&timezone=utc&var-PROMETHEUS_DS=mimir-gitlab-gprd&var-environment=gprd - What changes to this metric should prompt a rollback: sustained apdex degradation during lock attempts - Metric: write activity (INSERT, UPDATE, DELETE) on `merge_request_diff_commits` and `merge_request_diff_commits_b5377a7a34` - Location: https://dashboards.gitlab.net/goto/ffv30abq3wv0gf, https://dashboards.gitlab.net/goto/cfv30dpa2w5xcc, https://dashboards.gitlab.net/goto/efv30hqcgzdogc and Kibana https://log.gprd.gitlab.net/app/r/s/CPgAu - What changes to this metric should prompt a rollback: sustained pileup of blocked writes on these tables during lock attempts - Metric: autovacuum activity on `merge_request_diff_commits` and `merge_request_diff_commits_b5377a7a34` - Location: https://log.gprd.gitlab.net/app/r/s/9DSC8 - What changes to this metric should prompt a rollback: a running wraparound prevention vacuum blocks the run; wait for it to finish before attempting - Metric: merge request endpoint error rates - Location: web service dashboards - What changes to this metric should prompt a rollback: elevated errors on merge request diff writes - Metric: Query shape changes and partition pruning effectiveness - Location: Query execution analysis dashboard https://log.gprd.gitlab.net/app/r/s/N7wqy and Sentry error logs - What changes to this metric should prompt a rollback: if queries are not using the new partitioned table structure correctly, or if partition pruning is not being applied after the swap. Check that queries are filtering by `project_id` to enable partition elimination. - Root cause: the flag [`mr_diff_commits_read_new_table`](https://gitlab.com/gitlab-org/gitlab/-/work_items/600673) controls which table queries read from. After the swap, queries should be optimized for the partitioned table's structure. If queries still use the old shape, performance will degrade and come commits might be excluded from results.