Batch merge train car lookups during a refresh

What does this MR do and why?

MergeTrains::RefreshService refreshes every car on a train in one serialized pass, holding an exclusive lease for the duration. Each car resolves its own project, user, merge request, pipeline and diff lazily, so the pass costs a query per car and the queries a refresh sends to the main database grow with the length of the train.

MergeTrains::Train#refreshable_cars now batches those lookups when asked to. Every car on a train targets the same project and the train already holds it, so it is passed to the preloader as an available record rather than being reloaded once per association.

Behind the merge_trains_batch_car_lookups feature flag, default off.

Measured effect

Queries per MergeTrains::RefreshService#execute, measured with ActiveRecord::QueryRecorder (skip_cached: false) on a train whose cars are all waiting on a running pipeline, so no pipeline is created and nothing merges:

Cars on the train	Flag off	Flag on
2	33	17
6	89	25

That is 14 queries per car before and 2 after. Extrapolated to the default max_pipelines_per_merge_train of 20, a full train goes from roughly 285 queries to roughly 53.

These are test-environment counts, so the absolute numbers will not match production. The slope is the part that transfers. A refresh that actually merges or creates pipelines is not measured here, because pipeline creation cannot complete in the test environment.

Why this is behind a flag

Preloading moves two reads from per-car to once-per-refresh.

Merge request state. validate_merge_request! gates a merge on open?, broken?, draft? and auto_merge_enabled?, all read from the preloaded merge request. A car other than the first can merge within the same pass: once the first car merges cleanly, the next car's effectively_first_car? becomes true (it queries any_prev), and if that car's pipeline was already successful it merges, having validated against state captured at the start of the pass. Without the preload its merge request is loaded when the car is reached, so a merge request that became a draft or was closed mid-pass is caught. The window is as long as the preceding cars take.

Closing that window is cheap: resetting the merge request immediately before merge! would make the merge decision read committed state, at one query per car that actually merges. That is a separate change, deliberately not bundled here.

Project settings. All cars now share the train's project instance, so merge_trains_enabled? is read once per refresh rather than once per car. This replaces a partial-run failure mode, where cars refreshed before a settings change proceed and later ones abort, with one consistent reading per pass.

Pipelines keep their partition pruning

Pipelines are preloaded through the relation, not through the Preloader call, because with_partition_aware_preload hooks Relation#preload_associations. A direct Preloader bypasses it and loses partition pruning on p_ci_pipelines.

Two specs pin this down, and both fail if :pipeline is moved into the Preloader call: one asserts Gitlab::Ci::Pipeline::BulkByIdLookup is invoked, and one asserts that with a warm partition cache the p_ci_pipelines query carries a partition_id predicate.

What stays a query, and why

The remaining 2 queries per car are MergeTrains::Car#prev_active and that car's merge request.

An earlier revision of this MR also derived prev_active from the ordered car list in memory, taking the cost to zero. That is not safe: a car that fails validation is destroyed inside the refresh loop, and a car can also be removed concurrently by a user cancelling it. Either way the in-memory list still holds the removed car, whose status attribute is untouched, so active? keeps returning true and the following car resolves a deleted car as its predecessor. previous_ref then points at a train ref the destroy hook has already cleaned up, and the following car gets dropped from the train in turn.

prev_active decides which commit a train ref is built on, so it needs to read committed state rather than a snapshot taken before the loop. The spec does not treat a car removed during the refresh as a predecessor pins this down.

What this MR does not address

Pipeline recreation still runs inline for every car following a car that got a new pipeline, and Ci::CreatePipelineService dominates any refresh that does real work. That load lands mostly on the CI database rather than main, and moving it off the serialized path is a separate change.

How to set up and validate locally

bundle exec rspec ee/spec/services/merge_trains/refresh_service_spec.rb \
  ee/spec/models/merge_trains/train_spec.rb

Database review

All queries below are issued by MergeTrains::Train#refreshable_cars(preload: true), once per refresh. LIMIT / IN list sizes are bounded by max_pipelines_per_merge_train (plan limit, default 20).

No new access paths. Every one of these previously ran once per car with a single-valued predicate against the same index. The change replaces N single-value lookups with one IN list, so the row set is identical and the round trips drop from O(cars) to O(1).

Plans captured on Database Lab (gitlab-production-main / -ci, clone state 2026-08-18).

1. Refreshable cars for the train — merge_trains (main)

SELECT merge_trains.* FROM merge_trains
WHERE merge_trains.target_project_id = 278964
  AND merge_trains.target_branch = 'master'
  AND merge_trains.status IN (0, 2, 3, 4)
ORDER BY merge_trains.id ASC
LIMIT 20;

Plan — 16.9 ms, 19 buffers:

Limit (actual rows=1)
  -> Sort  Sort Key: merge_trains.id
     -> Index Scan using index_for_status_per_branch_per_project on public.merge_trains
        Index Cond: ((target_project_id = 278964) AND (target_branch = 'master')
                     AND (status = ANY ('{0,2,3,4}')))

Unchanged by this MR — included because the scope was touched.

2. Policy violations — scan_result_policy_violations (main)

SELECT scan_result_policy_violations.* FROM scan_result_policy_violations
WHERE scan_result_policy_violations.status = 0
  AND scan_result_policy_violations.merge_request_id IN (...);  -- <= 20 ids

Plan — 4.8 ms, 26 buffers. The id list is expressed as a subquery over merge_trains there so the ids are real; the application passes literals.

Index Scan using index_scan_result_policy_violations_on_merge_request_id
  Index Cond: (merge_request_id = ...)
  Filter: (status = 0)

Same index as the previous per-car query; only the predicate changed from = X to IN (<= 20). Rows per merge request are bounded by index_scan_result_policy_violations_on_policy_and_merge_request, which is unique on (scan_result_policy_id, merge_request_id).

3. Pipelines — p_ci_pipelines (ci)

SELECT p_ci_pipelines.* FROM p_ci_pipelines WHERE p_ci_pipelines.id IN (...);  -- <= 20 ids

This is a ci database query and the access path is pre-existing: pipelines are preloaded through the relation, which resolves them via Gitlab::Ci::Pipeline::BulkByIdLookup. With a warm partition cache that adds AND partition_id IN (...) and prunes; ids the cache cannot resolve fall through to a plain id IN (...).

Plan for the unpruned shape — it fans out into an Append across every pipeline partition (p_ci_pipelines_15 … _26+), 307 ms and ~10.4 MiB of buffers. That figure also covers the subquery used to obtain real ids, so it is not attributable to the IN lookup alone; it is included to show the fan-out shape that partition pruning avoids. The two specs described above exist to keep that pruning in place.

Note this is still strictly better than before: the per-car path used the single-id equivalent (Ci::Pipeline.find_by_id) with the same fallback, so an unpruned lookup used to happen up to once per car and now happens at most once per refresh.

4. Primary-key batches (main)

SELECT * FROM users              WHERE id IN (...);  -- <= 20 ids
SELECT * FROM merge_requests     WHERE id IN (...);  -- <= 20 ids
SELECT * FROM merge_request_diffs WHERE id IN (...); -- <= 20 ids

Primary-key lookups, no plans included.

5. Project

Fetched once per refresh via MergeTrains::Train#project and handed to the preloader as an available_records entry, so each car's target_project and each merge request's source_project / target_project resolve in memory. Net effect is one fewer projects lookup than before, not more.

References

• #464678