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
completedrows 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_retentionops 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: neitherstatusnorcompleted_athas 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
- Retention work item: https://gitlab.com/gitlab-org/gitlab/-/work_items/635483
- Tracking issue: https://gitlab.com/gitlab-org/gitlab/-/work_items/629743
- Capacity investigation: https://gitlab.com/gitlab-com/gl-infra/capacity-planning-trackers/gitlab-com/-/work_items/3200
How to set up and validate locally
-
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) -
Enqueue the operation and run the scheduler:
Gitlab::Database::BackgroundOperation::WorkerCellLocal.enqueue('MergeRequestCleanupSchedulesDeleteCompleted', 'merge_request_cleanup_schedules', 'merge_request_id') -
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.