Backfill partitioned merge_request_diff_commits table

Eugenia Grieffrequested to merge
527230-backfill-merge-request-diff-commits-2 into master

What does this MR do and why?

Part of https://gitlab.com/groups/gitlab-org/-/work_items/18350+

This MR adds a batched background migration to populate the table merge_request_diff_commits_b5377a7a34 (created with !226992 (merged)), as well as creating the missing merge_request_commits_metadata records

References

Related to #527230

DB changes

To create the new records in merge_request_diff_commits_b5377a7a34 and merge_request_commits_metadata, it is necessary to query multiple tables:

  • merge_request_diff_commits: Contains the original data. Can be missing the values merge_request_commits_metadata_id and project_id
  • merge_request_diffs: Provides project_id in case it's missing from the original table
  • excluded_merge_requests: Provides a list of merge requests that should be omitted during migration (see https://gitlab.com/gitlab-org/gitlab/-/work_items/517248 for more details)

Parallelization

The migration BackfillMergeRequestDiffCommitsToPartitioned will be parallelized to reduce completion time on GitLab.com. By splitting the work across 4 parallel migrations using database views, we can utilize all available background migration workers.

This follows the same pattern used in !221430 (merged), which successfully parallelised a migration on ci_builds.

Implementation approach:

  • GitLab.com: Creates 4 database views splitting merge_request_diff_commits into ranges and queues 4 separate background migrations (one per view)
  • Self-managed instances: - Queues a single background migration on the full table

The view boundary values were initially estimated by dividing the observed maximum merge_request_diff_id (~1.6B) evenly into 4 ranges, assuming roughly uniform distribution. To validate this, a TABLESAMPLE BERNOULLI (1) query was run on the production replica to find the actual p25/p50/p75 percentiles of merge_request_diff_id.

query 
WITH boundaries AS (
    SELECT
      merge_request_diff_id,
      ntile(4) OVER (ORDER BY merge_request_diff_id, relative_order) AS bucket
    FROM merge_request_diff_commits
    TABLESAMPLE BERNOULLI (1)
  )
  SELECT
    bucket,
    MIN(merge_request_diff_id) AS lower_bound
  FROM boundaries
  GROUP BY bucket
  ORDER BY bucket;

result:

bucket | lower_bound
--------+-------------
      1 |          90
      2 |   405423843
      3 |  1010436901
      4 |  1224788900
(4 rows)

Migrations output

  • When not in Gitlab.com

    UP 
    ❯ bin/rails db:migrate
main: == [advisory_lock_connection] object_id: 140080, pg_backend_pid: 90408
main: == 20260410102004 CreateMergeRequestDiffCommitsViews: migrating ===============
main: == 20260410102004 CreateMergeRequestDiffCommitsViews: migrated (0.0091s) ======

main: == [advisory_lock_connection] object_id: 140080, pg_backend_pid: 90408
main: == [advisory_lock_connection] object_id: 140080, pg_backend_pid: 90409
main: == 20260410102007 QueueBackfillMergeRequestDiffCommitsToPartitioned: migrating
main: == 20260410102007 QueueBackfillMergeRequestDiffCommitsToPartitioned: migrated (0.0743s)

main: == [advisory_lock_connection] object_id: 140080, pg_backend_pid: 90409
    DOWN 
    main: == [advisory_lock_connection] object_id: 139280, pg_backend_pid: 89879
main: == 20260410102007 QueueBackfillMergeRequestDiffCommitsToPartitioned: reverting
main: == 20260410102007 QueueBackfillMergeRequestDiffCommitsToPartitioned: reverted (0.0644s)

main: == [advisory_lock_connection] object_id: 139280, pg_backend_pid: 89879

main: == [advisory_lock_connection] object_id: 139260, pg_backend_pid: 88863
main: == 20260410102004 CreateMergeRequestDiffCommitsViews: reverting ===============
main: == 20260410102004 CreateMergeRequestDiffCommitsViews: reverted (0.0089s) ======

main: == [advisory_lock_connection] object_id: 139260, pg_backend_pid: 88863

  • When in Gitlab.com

    UP 
      main: == [advisory_lock_connection] object_id: 140200, pg_backend_pid: 76242
  main: == [advisory_lock_connection] object_id: 140200, pg_backend_pid: 76242
  main: == 20260410102004 CreateMergeRequestDiffCommitsViews: migrating ===============
  main: -- execute("CREATE OR REPLACE VIEW merge_request_diff_commits_views_1 AS SELECT merge_request_diff_id, relative_order FROM merge_request_diff_commits WHERE (merge_request_diff_id, relative_order) >= (0, 0) AND (merge_request_diff_id, relative_order) < (405423843, 0)")
  main:    -> 0.0023s
  main: -- execute("CREATE OR REPLACE VIEW merge_request_diff_commits_views_2 AS SELECT merge_request_diff_id, relative_order FROM merge_request_diff_commits WHERE (merge_request_diff_id, relative_order) >= (405423843, 0) AND (merge_request_diff_id, relative_order) < (1010436901, 0)")
  main:    -> 0.0013s
  main: -- execute("CREATE OR REPLACE VIEW merge_request_diff_commits_views_3 AS SELECT merge_request_diff_id, relative_order FROM merge_request_diff_commits WHERE (merge_request_diff_id, relative_order) >= (1010436901, 0) AND (merge_request_diff_id, relative_order) < (1224788900, 0)")
  main:    -> 0.0011s
  main: -- execute("CREATE OR REPLACE VIEW merge_request_diff_commits_views_4 AS SELECT merge_request_diff_id, relative_order FROM merge_request_diff_commits WHERE (merge_request_diff_id, relative_order) >= (1224788900, 0)")
  main:    -> 0.0012s
  main: == 20260410102004 CreateMergeRequestDiffCommitsViews: migrated (0.0102s) ======
  
  main: == [advisory_lock_connection] object_id: 140200, pg_backend_pid: 76247
  main: == 20260410102007 QueueBackfillMergeRequestDiffCommitsToPartitioned: migrating
  main: == 20260410102007 QueueBackfillMergeRequestDiffCommitsToPartitioned: migrated (0.0246s)
  main: == [advisory_lock_connection] object_id: 140200, pg_backend_pid: 76247
    DOWN 
      main: == [advisory_lock_connection] object_id: 139400, pg_backend_pid: 84186
  main: == 20260410102004 CreateMergeRequestDiffCommitsViews: reverting ===============
  main: -- execute("DROP VIEW IF EXISTS merge_request_diff_commits_views_1;")
  main:    -> 0.0291s
  main: -- execute("DROP VIEW IF EXISTS merge_request_diff_commits_views_2;")
  main:    -> 0.0014s
  main: -- execute("DROP VIEW IF EXISTS merge_request_diff_commits_views_3;")
  main:    -> 0.0283s
  main: -- execute("DROP VIEW IF EXISTS merge_request_diff_commits_views_4;")
  main:    -> 0.0014s
  main: == 20260410102004 CreateMergeRequestDiffCommitsViews: reverted (0.0706s) ======
  main: == [advisory_lock_connection] object_id: 139400, pg_backend_pid: 84186
  
  main: == [advisory_lock_connection] object_id: 139420, pg_backend_pid: 84795
  main: == 20260410102007 QueueBackfillMergeRequestDiffCommitsToPartitioned: reverting
  main: == 20260410102007 QueueBackfillMergeRequestDiffCommitsToPartitioned: reverted (0.1057s)
  main: == [advisory_lock_connection] object_id: 139420, pg_backend_pid: 84795

Backfill query plans

SUB_BATCH_SIZE: 1000

Initial plan

query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/50115/commands/148796

Current query plan: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/50167/commands/148963

Backfill migration data flow

Click to expand

Screenshot_2026-02-19_at_14.27.28

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.

Edited by Eugenia Grieff

Merge request reports