Batch the push commit overlap lookup in MergeRequests::RefreshService

What does this MR do and why?

MergeRequests::RefreshService asks every open merge request on the pushed branch, one at a time, whether its diff contains any of the pushed commits. Each of those questions re-serialises the whole push commit list into bind parameters, so the cost scales with merge request count × push size. During INC-12753 that meant UpdateMergeRequestsWorker jobs running 5-10 hours at cpu/wall ~0.93.

Behind merge_request_refresh_batched_commit_lookup (project actor, default off), resolve the whole set in one batched query per 1,000-commit chunk instead of one query per merge request. Same merge requests selected, same diffs reloaded, no commits dropped.

On a 30,000-commit push with 200 open merge requests: CPU 83.1s → 0.84s, queries 12,019 → 79, allocations 128M → 1.7M. The batched cost is flat in merge request count; the old path is linear.

For database review - one EXPLAIN (ANALYZE, BUFFERS) plan per query the code sends, measured on a fresh production clone (2026-08-18). Inputs, plan shapes and the old-path comparison are in the details section below.

query	plan	buffers (warm)
sha resolve	warm	4,215
match, current table	warm / cold	1,814
match, partitioned table + project_id	warm / cold	3,509
sha fallback	warm	388

---

Detailed measurements, mechanism and verification

Mechanism

MergeRequestDiff#includes_any_commits? is called once per merge request, per BATCH_SIZE (1,000) chunk of push commits. Per call, ActiveRecord type-casts all 1,000 shas to bytea and builds a ~40KB SQL string. With M merge requests and C push commits that is M × ceil(C/1000) round trips and the same multiple of sha serialisations.

While mr_diff_commits_read_new_table is off - production's setting - each chunk costs two queries, because includes_any_commits? runs the metadata lookup and then the sha fallback (merge_request_diff.rb:544-547).

The new MergeRequestDiff.ids_including_any_commits(diff_ids, shas, project:) inverts the loop:

1. Resolve shas → merge_request_commits_metadata.id once per chunk, on the unique (project_id, sha) index.
2. Match every candidate diff in one query against those metadata ids.
3. While the new table is not the read source, union the sha-column fallback.

Diff ids are not chunked. Production EXPLAIN showed the match step is driven by the resolved metadata ids, not by the diff-id list, so widening it from 200 to 3,000 leaves the index scan untouched and only grows a 16kB hash. A diff is dropped from remaining once it matches, so later chunks skip it. Each lookup is distinct and bounded by its own input, so the rows returned scale with the number of candidate diffs rather than with the number of commits that matched.

Ruby cost - the incident metric

The incident was CPU-bound at cpu/wall ~0.93, so DB time was at most ~7% of those jobs. CPU and allocations are the figures that matter. GDK, synthetic history, mr_diff_commits_read_new_table pinned off to match production:

30,000-commit push, 200 open merge requests on the branch

scenario	wall	cpu	queries	allocations
per-merge-request (flag off)	94.00s	83.11s	12,019	128,072,538
batched lookup (flag on)	1.15s	0.84s	79	1,660,930
	−98.8%	−99.0%	−99.3%	−98.7%

Scaling on merge request count - the axis that caused the incident

	20 MRs	200 MRs	growth
per-merge-request, cpu	8.02s	83.11s	10.4× - linear
per-merge-request, queries	1,219	12,019	9.9× - linear
batched, cpu	0.67s	0.84s	1.25×
batched, queries	79	79	flat

Extrapolating to incident scale (100,000 commits, ~3,000 open merge requests): ~69 min → ~3s. The 10,000-commit cap would have given ~6.7 min, because it only shrinks the push side.

Database cost - real production data

Re-measured with EXPLAIN (ANALYZE, BUFFERS) on a fresh postgres.ai clone of gitlab-production-main, snapshot 2026-08-18. Inputs: the newest 1,000 real master commits of gitlab-org/gitlab (tip 313c8d19), and the latest regular diff id (from the merge request versions API) of the 200 most recently updated open merge requests targeting master in project 278964. There are 2,778 open merge requests targeting master today, so the earlier run's "all 199 open merge requests" selection cannot be reproduced; the earlier absolute figures (753 of 1,000 shas resolved then, 275 now) came from a different snapshot and are superseded by this set. The code sends at most three statements per 1,000-commit chunk, and each one is measured here on its own - no query in this section is wrapped in a CTE.

sha resolve - SELECT id FROM merge_request_commits_metadata WHERE project_id = 278964 AND sha IN (...). Plan: 4,215 buffers (~33 MiB), 6.1 ms execution, 2.4 ms planning, fully warm (reads: 0). Resolves 275 of the 1,000 shas, pruned to a single partition via merge_request_commits_metadata_1_project_id_sha_idx.

match, current table - SELECT DISTINCT merge_request_diff_id FROM merge_request_diff_commits WHERE merge_request_diff_id IN (...) AND merge_request_commits_metadata_id IN (...). Cold: 158143, 361 ms total, 5,633 hit + 461 read, mostly I/O. Warm: 158144, 19 ms execution, 40 ms planning, 6,045 hit + 5 read (~47 MiB total). Nested loop over the 275 resolved ids via index_mrdc_on_merge_request_commits_metadata_id (loops=275), with the 200 diff ids applied as a filter; 0 rows matched, 3 removed by the diff-id filter.

The console returns plans, not result rows, so the 275 resolved ids from the sha resolve could not be copied out and pasted in as literals for the match query. The measured statement instead feeds them through a plain, non-CTE subquery; Postgres flattens that to a semi-join and picks the same nested-loop plan a literal id list would drive. The resolve subtree inside that plan costs 4,222 buffers, matching its standalone measurement above, so the match step is isolated cleanly: 1,814 buffers is the match query's own cost, not the resolve's.

sha fallback - SELECT DISTINCT merge_request_diff_id FROM merge_request_diff_commits WHERE merge_request_diff_id IN (...) AND sha IN (...). Plan: 388 buffers, 0.6 ms execution, 36 ms planning, 0 rows (176 removed by the sha filter). Drives off the merge_request_diff_commits primary key. Only runs while mr_diff_commits_read_new_table is off, which is production's current setting.

match, partitioned table + project_id (future read state) - once the table swap lands, the model emits the same query text against merge_request_diff_commits_b5377a7a34 with AND project_id = 278964, and the sha fallback stops running. Cold: 158145, 519 ms total, 6,712 hit + 1,847 read. Warm: 158146, 24 ms execution, 8.5 ms planning, 7,744 hit + 4 read (~60.5 MiB total). Same nested-loop shape, probing merge_request_diff_commits_b5_merge_request_commits_metadat_idx on partition merge_request_diff_commits_b5377a7a34_1; the match subtree alone is 3,509 buffers, with project_id and the diff-id list arriving as a filter; 0 rows, 3 removed.

Per 1,000-commit chunk, covering all 200 merge requests together, the current production read state (resolve + match + sha fallback) costs 4,215 + 1,814 + 388 = ~6,417 buffers (~50 MiB) warm. The earlier snapshot (a different clone, a different diff-id set) measured the old per-merge-request path's floor at 269,526 buffers (753 resolved ids, 199 diff ids) - that figure is dated and not directly comparable to this set, but it remains a floor, not the old path's real cost, since it fused all 199 old-path queries into a single plan rather than paying the resolve 199 separate times.

Confirmation that production is worse than a default GDK run suggests: with mr_diff_commits_read_new_table pinned off, the unbounded run measures 1,219 queries / 13.6M allocations against 619 / 7.57M with it on - almost exactly 2×.

Correctness verification

The committed spec asserts the mechanism. Selection correctness was verified against a purpose-built stacked-merge-request fixture (two branches open against the target with their diffs stored, then the target branch advances and swallows an early commit of each while their heads stay unlanded, plus a control branch that never enters the push):

• flag off selects 2, flag on selects 2, identical sets, on both the old-table and new-table read paths
• reload_diff fires on exactly those 2; the control merge request is untouched
• both remain open, confirming this is the stacked case and not manual-merge closure

includes_any_commits? is left unchanged. Its spec suite asserts exact EXISTS query shapes and counts, so rebuilding it on top of the batched method would break established coverage for no gain; the shared fallback structure is cross-referenced in comments on both so they do not drift.

Notes for review

• Planning time is 36-48ms for the batched match (~2,000 literals per statement) against ~1.9ms for the sha resolve alone. At 100 chunks that is ~4s per push, against the alternative. Postgres folds the IN list into a single bytea[] constant, so pg_stat_statements normalisation is not a concern.
• Index usage: the sha resolve prunes to one merge_request_commits_metadata partition and uses index_merge_request_commits_metadata_on_project_id_and_sha. The match uses index_mrdc_on_merge_request_commits_metadata_id. merge_request_diff_commits is not partitioned.
• project_id filter is applied when mr_diff_commits_project_id_pruning is enabled, matching includes_any_commits?.
• Frequency: zero of the 200 real open merge requests targeting master in gitlab-org/gitlab had any of the newest 1,000 master commits in their diff. The gate is normally false; it fires for stacked merge requests and partial landings.
• Why the cap was abandoned: an earlier revision of this MR capped the push at 10,000 commits. That was linear in merge request count too, so it missed the axis that caused the incident, and it bought its improvement by dropping commits. Worse, retention under commits_between(limit:) is decided by a commit-date priority walk from the tip, not by distance from the tip - ListCommits with order: NONE is reverse chronological. A branch merged as the tip's own first parent, with its head two hops away, was verifiably dropped by the cap. The cap's original justification - that a manual merge "lands at the tip of the push, which is always retained" - does not hold.