Add retention cleanup for merge_request_cleanup_schedules

What does this MR do and why?

Adds a recurring background operation that enforces the data retention policy for merge_request_cleanup_schedules: completed rows are deleted 90 days after completed_at.

A row in this table directs MergeRequestCleanupRefsWorker to prune a merged or closed merge request's refs, and nothing reads the row again after the worker marks it completed. Until now rows were only removed when the merge request was reopened or deleted, so the table kept a row for almost every merged or closed merge request and has grown to about 55 GiB.

Design:

  • Cell-local operation (a recurring cron job across organizations, per the decision tree), enqueued weekly (0 4 * * 1).
  • Only completed rows are deleted. Unstarted rows are still waiting for the worker, running rows are in flight, and failed rows are kept until #603579 makes them retryable.
  • Gated behind the merge_request_cleanup_schedules_retention ops feature flag (default off). The retention policy will name this flag when enforcement is turned on; disabling it stops deletion at the next job.
  • Batching is unscoped with per-sub-batch filtering, mirroring Gitlab::BackgroundOperation::MergeRequestsClearOldMergedCachedHtml: neither status nor completed_at has an index that could order a scoped boundary query by the cursor, and once the historical backlog is purged the eligible rows are sparse, so a scoped boundary query would walk every surviving row looking for a match.

Database

Generated per-sub-batch DELETE, captured from the operation's SQL log and rewritten with production-representative values (LIMIT 500 is the configured sub_batch_size; the id window is one sub-batch's cursor bounds; the cutoff is RETENTION_PERIOD.ago evaluated per job):

DELETE FROM "merge_request_cleanup_schedules"
WHERE "merge_request_cleanup_schedules"."merge_request_id" IN (
  SELECT "merge_request_cleanup_schedules"."merge_request_id"
  FROM "merge_request_cleanup_schedules"
  WHERE "merge_request_cleanup_schedules"."merge_request_id" >= 324104000
    AND "merge_request_cleanup_schedules"."merge_request_id" <= 324104650
  ORDER BY "merge_request_cleanup_schedules"."merge_request_id" ASC
  LIMIT 500
)
AND "merge_request_cleanup_schedules"."status" = 2
AND (completed_at < '2026-07-25 06:55:07.316499')

The subquery is bounded by the sub-batch's cursor window on both sides, so the status and completed_at filters on the outer DELETE can never widen the scan past the window.

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/71217/commands/175406

References

How to set up and validate locally

  1. In the Rails console, enable the ops flag and create eligible rows:

    Feature.enable(:merge_request_cleanup_schedules_retention)
    mr = MergeRequest.last
    MergeRequest::CleanupSchedule.create!(merge_request: mr, project_id: mr.target_project_id, scheduled_at: 1.year.ago, status: 2, completed_at: 91.days.ago)
  2. Enqueue the operation and run the scheduler:

    Gitlab::Database::BackgroundOperation::WorkerCellLocal.enqueue('MergeRequestCleanupSchedulesDeleteCompleted', 'merge_request_cleanup_schedules', 'merge_request_id')
  3. Verify the row is gone and that rows with other statuses, or completed less than 90 days ago, remain.

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.

🤖 Generated with claude-flow

Edited by zli

Merge request reports

Loading
Loading