BackfillMergeRequestDiffCommitsToPartitioned finalize fails with statement timeout

Description

On large GitLab Self-Managed instances, the BackfillMergeRequestDiffCommitsToPartitioned: merge_request_diff_commits batched background migration can fail at a high completion percentage (for example, 94%) and then become stuck in a finalizing state. Running the UI-provided finalize command fails with a statement-timeout error, blocking the upgrade to the next GitLab version.

Environment

Impacted Offerings

  • GitLab Self-Managed

Impacted Versions

  • GitLab 19.0 and later (where the migration is introduced)

Solution

Perform the following steps during a maintenance window. The steps temporarily raise the PostgreSQL statement timeout so the finalize task can complete without being cancelled.

Step 1: Reduce the sub-batch size

Reducing the sub_batch_size lowers the amount of work done per sub-batch, making each operation less likely to exceed the statement timeout.

sudo gitlab-rails runner 'Gitlab::Database::BackgroundMigration::BatchedMigration.find_by!(job_class_name: "BackfillMergeRequestDiffCommitsToPartitioned").update!(sub_batch_size: 500)'

If the migration continues to time out after this change, reduce the value further to 100. Ensure that sub_batch_size is always less than or equal to batch_size. You can verify the current values with:

sudo gitlab-psql -c "SELECT batch_size, sub_batch_size FROM batched_background_migrations WHERE job_class_name = 'BackfillMergeRequestDiffCommitsToPartitioned';"

Step 2: Temporarily raise the statement timeout

Note: The following example is raising the statement_timeout for an embedded DB (Omnibus Linux package). These steps will not work for an external DB.

Add the following settings to /etc/gitlab/gitlab.rb on the relevant nodes. Note that postgresql['statement_timeout'] takes a string value, while gitlab_rails['db_statement_timeout'] takes an integer value in milliseconds.

postgresql['statement_timeout'] = "0"
gitlab_rails['db_statement_timeout'] = 0

Then apply the configuration:

sudo gitlab-ctl reconfigure

A PostgreSQL restart is not required. For Linux package installations with bundled PostgreSQL, reconfigure reloads postgresql-runtime.conf in place.

You can verify the timeout is now disabled:

sudo gitlab-psql -c "SHOW statement_timeout;"

Step 3: Re-run the finalize task

Run the finalize task in a session that is resilient to disconnection (for example, using screen or tmux):

sudo gitlab-rake gitlab:background_migrations:finalize[BackfillMergeRequestDiffCommitsToPartitioned,merge_request_diff_commits,merge_request_diff_id,'["merge_request_diff_commits_xxxxxxxx"]']

Step 4: Monitor until complete

Monitor the migration status until it reaches finalized. You can check the status using the background migrations Admin UI or by querying the database directly:

sudo gitlab-psql -c "SELECT status, batch_size, sub_batch_size FROM batched_background_migrations WHERE job_class_name = 'BackfillMergeRequestDiffCommitsToPartitioned';"

In the batched_background_migrations table, status 3 corresponds to finished and status 6 corresponds to finalized. The migration must reach finalized (status 6) before you can proceed with the upgrade.

Step 5: Restore the original timeout settings

After the migration completes, remove or revert the timeout overrides in /etc/gitlab/gitlab.rb and reconfigure:

sudo gitlab-ctl reconfigure

Cause

On large instances, a sub-batch of the BackfillMergeRequestDiffCommitsToPartitioned migration can exceed the default 60-second statement_timeout, causing the finalize task to be cancelled by PostgreSQL. After a cancellation, the migration transitions to a finalizing state and does not automatically retry.

Additional Information

  • For general guidance on monitoring and resolving failed batched background migrations, refer to the Check migrations before upgrade documentation.
  • For guidance on the statement_timeout PostgreSQL tuning requirement, refer to the PostgreSQL requirements documentation.
  • If you have a Geo secondary site configured as a hot spare, Geo secondaries use PostgreSQL streaming replication and do not require these commands to be run on the secondary. You may optionally pause replication on the secondary before running the finalize task to create a clean DR checkpoint, then resume replication after the migration completes.

On this page...