Batch plainly on merge_requests, filter in the sub-batch

What does this MR do and why?

This is a follow-up to !251054 (merged), which added the background operation Gitlab::BackgroundOperation::MergeRequestsClearOldMergedCachedHtml. Related issue: #608121.

The operation clears title_html, description_html and cached_markdown_version on merge requests merged more than 3 years ago whose cached_markdown_version is at or above Gitlab::MarkdownCache.cached_markdown_version_for_bulk_clear. The cache is self-healing, so the cost of clearing a row someone later views is one re-render.

This MR removes the scope_to block and moves the join to merge_request_metrics and both filters into the sub-batch.

scope_to applies to the batch boundary queries as well as the sub-batch, so as merged, every boundary query carried the INNER JOIN merge_request_metrics and the merged_at predicate. A boundary query has no upper bound of its own. It takes LIMIT 2 OFFSET 99 from the cursor and reads forward until it finds the next window. Once iteration passes the last merge request old enough to qualify, there are no more matching rows, so each remaining boundary query keeps reading forward toward the maximum id looking for a match that does not exist. merged_at is roughly monotonic in merge_requests.id, so the ineligible stretch is the whole recent end of the table, and it grows every day. Batching on the primary key alone keeps each boundary query bounded to a fixed number of index entries no matter where the cursor sits.

The tradeoff is that unscoped batching means the operation visits every row in merge_requests. It runs one sub-batch query per window across the whole table, including windows where nothing can match. Those sub-batch queries are bounded — each one is constrained to a 100-id window — which the boundary queries were not.

Both filters now live in the sub-batch. The window is bound first with where(id: sub_batch.select(:id)) before the join, and the filters are chained on. That re-binding matters: the relation each_sub_batch yields is bounded by a LIMIT, not by an upper id, so chaining filters straight onto it would push them inside that LIMIT and let a sub-batch scan past its own window.

The join is 1:1 and index backed. merge_request_id sits under an equality predicate against a bounded id list, so index_merge_request_metrics_on_merge_request_id_and_merged_at — a partial index on (merge_request_id, merged_at) WHERE merged_at IS NOT NULL — serves the probe with merged_at as an index condition. Merge requests with no metrics row, and rows whose merged_at is NULL, drop out through the INNER JOIN and the index's partial predicate.

This also makes the operation the same shape as its sibling MergeRequestsClearStaleCachedHtml, which iterates merge_requests on id with no scope_to.

There is no schedule change. config/schedule.yml still batches merge_requests on id, so the existing entry is unchanged.

Database

The queries below are as generated by the code, captured from a run. The merged_at cutoff and the version bound are the literals from that run.

batch boundary query

Before, as merged, with scope_to:

SELECT "merge_requests"."id"
FROM "merge_requests"
INNER JOIN merge_request_metrics
  ON merge_request_metrics.merge_request_id = merge_requests.id
WHERE (merge_request_metrics.merged_at < '2023-08-28 01:03:00')
  AND ("merge_requests"."id") >= (1000000)
ORDER BY "merge_requests"."id" ASC
LIMIT 2 OFFSET 999

After, with this MR:

SELECT "merge_requests"."id"
FROM "merge_requests"
WHERE ("merge_requests"."id") >= (1000000)
ORDER BY "merge_requests"."id" ASC
LIMIT 2 OFFSET 999

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/55572/commands/159404

Sub-batch boundary query

Before, as merged, with scope_to:

SELECT "merge_requests".*
FROM "merge_requests"
INNER JOIN merge_request_metrics
  ON merge_request_metrics.merge_request_id = merge_requests.id
WHERE ("merge_requests"."id") <= (1002000)
  AND (merge_request_metrics.merged_at < '2023-08-28 00:06:18')
  AND ("merge_requests"."id") >= (1000000)
ORDER BY "merge_requests"."id" ASC
LIMIT 2 OFFSET 99

After, with this MR:

SELECT "merge_requests".*
FROM "merge_requests"
WHERE ("merge_requests"."id") <= (1002000)
  AND ("merge_requests"."id") >= (1000000)
ORDER BY "merge_requests"."id" ASC
LIMIT 2 OFFSET 499

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/55572/commands/159407

Sub-batch query

lock_version is bumped by Rails optimistic locking, not by the operation.

UPDATE "merge_requests"
SET "title_html" = NULL,
    "description_html" = NULL,
    "cached_markdown_version" = NULL,
    "lock_version" = COALESCE("lock_version", 0) + 1
WHERE ("merge_requests"."id") IN (
  SELECT "merge_requests"."id"
  FROM "merge_requests"
  INNER JOIN merge_request_metrics
    ON merge_request_metrics.merge_request_id = merge_requests.id
  WHERE "merge_requests"."id" IN (
    SELECT "merge_requests"."id"
    FROM "merge_requests"
    WHERE ("merge_requests"."id") <= (1002000)
      AND ("merge_requests"."id") >= (1000000)
    ORDER BY "merge_requests"."id" ASC
    LIMIT 500
  )
  AND (merge_request_metrics.merged_at < '2023-08-28 00:05:42')
  AND (merge_requests.cached_markdown_version >= 2162688)
)

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/55572/commands/159408

How to set up and validate locally

1. Run the spec:

   bundle exec rspec spec/lib/gitlab/background_operation/merge_requests_clear_old_merged_cached_html_spec.rb

   It passes locally with 6 examples.

2. One existing example was reworked because batching is no longer scoped. The operation now visits every row, so the example asserting that ineligible rows are never handed to a sub-batch was replaced by one asserting that they are visited and clear nothing.

MR acceptance checklist

This MR meets the definition of done.