Add partition pruning to Ci::Runner read paths (!240576) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

ci_runners and ci_runner_machines are LIST-partitioned by runner_type (1 = instance, 2 = group, 3 = project). PostgreSQL can skip irrelevant partitions when runner_type participates in the WHERE / JOIN ON clause — but several Ci::Runner read paths omit it today, forcing the planner to scan all three partitions.

This MR adds partition pruning to the Ci::Runner read paths in two independent ways:

1. Composite `foreign_key` on `has_many :runner_managers` (always-on)

has_many :runner_managers, inverse_of: :runner,
  foreign_key: [:runner_id, :runner_type], primary_key: [:id, :runner_type]

Rails 7+ supports a composite foreign_key array. This single declaration makes ActiveRecord automatically:

Add AND ci_runner_machines.runner_type = ? to every runner.runner_managers query.
Correlate joins(:runner_managers) on both runner_id AND runner_type, which means the existing with_version_prefix / with_upgrade_status scopes inherit partition pruning at the join target without any change to their bodies.

The correlation is logically a no-op (a runner manager always shares its parent runner's runner_type, enforced by the partition structure), so it is applied unconditionally — no feature flag. This matches the established codebase convention in Ci::RunningBuild, Ci::Pipeline, and Ci::Sources::Pipeline for partition-key correlation.

2. `runner_type` filter on `belonging_to_project` / `belonging_to_group` (FF-gated)

These scopes join ci_runners against ci_runner_projects / ci_runner_namespaces (tables that only ever contain project- and group-type runners respectively). Adding .project_type / .group_type to the chain is logically redundant from a data perspective but lets PostgreSQL prune the ci_runners partitions:

scope :belonging_to_project, ->(project_id) {
  scope = joins(:runner_projects).where(ci_runner_projects: { project_id: project_id })
  partition_pruning_enabled? ? scope.project_type : scope
}

Gated behind ci_runner_partition_pruning (gitlab_com_derisk, default off) so we can verify the impact on production query plans before flipping it on globally. Six compound scopes (belonging_to_group_and_ancestors, belonging_to_parent_groups_of_project, belonging_to_group_or_project_descendants, owned_or_instance_wide, group_or_instance_wide, usable_from_scope) and the runners finder + EE callers (StaleGroupRunnersPruneService, Analytics::DevopsAdoption::SnapshotCalculator) inherit the pruning automatically.

Why this scope deviates from the issue's proposed patch

The linked issue's example patch wraps the has_many :runner_managers filter in an instance-dependent lambda gated by the same FF. That approach hits two blockers in this codebase:

The activerecord-gitlab partitioning patch (check_eager_loadable!) rejects instance-dependent scopes on joins unless partition_foreign_key is set.
The partition_foreign_key machinery is hardcoded for integer partition_id ranges, not enum runner_type.

Composite foreign_key sidesteps both: it produces the same SQL with cleaner Rails-idiomatic code, no Arel.sql raw join string, and as a bonus it also covers the four production callers of runner.runner_managers (lib/api/ci/runner.rb, lib/api/ci/runners.rb, lib/api/entities/ci/runner_details.rb, app/services/ci/runners/unregister_runner_manager_service.rb) — these benefit from pruning without any caller changes and without a FF.

Sequence

This is MR 2 of a 3-MR series implementing #594861 (closed):

!239835 (merged) — query_constraints :id, :runner_type on Ci::RunnerManager (single-record write paths)
!240576 (merged) (this MR) — Ci::Runner read-path pruning (scopes + association)
MR 3 (planned) — Ci::Runner#ensure_manager write path, behind the same FF

Database changes

No schema migrations. The MR changes SQL emitted by the Ci::Runner model:

runner.runner_managers adds AND "ci_runner_machines"."runner_type" = ? (always).
Ci::Runner.joins(:runner_managers) adds AND "ci_runner_machines"."runner_type" = "ci_runners"."runner_type" to the ON clause (always). This affects with_version_prefix and with_upgrade_status.
Ci::Runner.belonging_to_project(id) adds AND "ci_runners"."runner_type" = 3 (FF on only).
Ci::Runner.belonging_to_group(id) adds AND "ci_runners"."runner_type" = 2 (FF on only).

Query Plans

## 1. `belonging_to_project` — feature flag gated

BEFORE (FF off)

EXPLAIN
SELECT "ci_runners".*
FROM "ci_runners"
INNER JOIN "ci_runner_projects"
  ON "ci_runner_projects"."runner_id" = "ci_runners"."id"
WHERE "ci_runner_projects"."project_id" = 642693;

 Nested Loop  (cost=0.57..44.83 rows=4 width=267)
   ->  Index Scan using index_ci_runner_projects_on_project_id on ci_runner_projects  (cost=0.43..7.46 rows=4 width=4)
         Index Cond: (project_id = 642693)
   ->  Append  (cost=0.14..9.31 rows=3 width=266)
         ->  Index Scan using check_5c34a3c1db on instance_type_ci_runners ci_runners_1  (cost=0.14..2.41 rows=1 width=349)
               Index Cond: (id = ci_runner_projects.runner_id)
         ->  Index Scan using check_81b90172a6 on group_type_ci_runners ci_runners_2  (cost=0.42..3.44 rows=1 width=263)
               Index Cond: (id = ci_runner_projects.runner_id)
         ->  Index Scan using check_619c71f3a2 on project_type_ci_runners ci_runners_3  (cost=0.43..3.45 rows=1 width=269)
               Index Cond: (id = ci_runner_projects.runner_id)

3-partition Append over instance/group/project.

AFTER (FF on) — adds `AND ci_runners.runner_type = 3`

EXPLAIN
SELECT "ci_runners".*
FROM "ci_runners"
INNER JOIN "ci_runner_projects"
  ON "ci_runner_projects"."runner_id" = "ci_runners"."id"
WHERE "ci_runner_projects"."project_id" = 642693
  AND "ci_runners"."runner_type" = 3;

 Nested Loop  (cost=0.85..21.29 rows=4 width=269)
   ->  Index Scan using index_ci_runner_projects_on_project_id on ci_runner_projects  (cost=0.43..7.46 rows=4 width=4)
         Index Cond: (project_id = 642693)
   ->  Index Scan using check_619c71f3a2 on project_type_ci_runners ci_runners  (cost=0.43..3.45 rows=1 width=269)
         Index Cond: (id = ci_runner_projects.runner_id)
         Filter: (runner_type = 3)

Single project_type_ci_runners scan. Top-level cost 44.83 -> 21.29, 3 partitions -> 1.

## 2. `belonging_to_group` — feature flag gated

BEFORE (FF off)

EXPLAIN
SELECT "ci_runners".*
FROM "ci_runners"
INNER JOIN "ci_runner_namespaces"
  ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id"
WHERE "ci_runner_namespaces"."namespace_id" = 2461061;

 Nested Loop  (cost=0.57..75.24 rows=7 width=267)
   ->  Index Scan using index_ci_runner_namespaces_on_namespace_id on ci_runner_namespaces  (cost=0.43..12.10 rows=7 width=4)
         Index Cond: (namespace_id = 2461061)
   ->  Append  (cost=0.14..8.99 rows=3 width=266)
         ->  Index Scan using check_5c34a3c1db on instance_type_ci_runners ci_runners_1  (cost=0.14..2.09 rows=1 width=349)
               Index Cond: (id = ci_runner_namespaces.runner_id)
         ->  Index Scan using check_81b90172a6 on group_type_ci_runners ci_runners_2  (cost=0.42..3.44 rows=1 width=263)
               Index Cond: (id = ci_runner_namespaces.runner_id)
         ->  Index Scan using check_619c71f3a2 on project_type_ci_runners ci_runners_3  (cost=0.43..3.45 rows=1 width=269)
               Index Cond: (id = ci_runner_namespaces.runner_id)

AFTER (FF on) — adds `AND ci_runners.runner_type = 2`

EXPLAIN
SELECT "ci_runners".*
FROM "ci_runners"
INNER JOIN "ci_runner_namespaces"
  ON "ci_runner_namespaces"."runner_id" = "ci_runners"."id"
WHERE "ci_runner_namespaces"."namespace_id" = 2461061
  AND "ci_runners"."runner_type" = 2;

 Nested Loop  (cost=0.85..36.28 rows=6 width=263)
   ->  Index Scan using index_ci_runner_namespaces_on_namespace_id on ci_runner_namespaces  (cost=0.43..12.10 rows=7 width=4)
         Index Cond: (namespace_id = 2461061)
   ->  Index Scan using check_81b90172a6 on group_type_ci_runners ci_runners  (cost=0.42..3.45 rows=1 width=263)
         Index Cond: (id = ci_runner_namespaces.runner_id)
         Filter: (runner_type = 2)

Single group_type_ci_runners scan. Top-level cost 75.24 -> 36.28, 3 partitions -> 1.

## 3. `runner_managers` composite foreign-key join — always on

This change correlates joins(:runner_managers) on runner_id AND runner_type.

Important nuance: when the query filters only by ci_runners.id (not a static runner_type), the planner cannot statically prune the ci_runner_machines partitions, because runner_type is only known at row time. The correlation shows up as a Join Filter and is logically a no-op (a manager always shares its parent runner's type). The measurable pruning win on the machines side comes when the query also constrains runner_type (e.g. composed with with_runner_type, or via the bare association on a loaded runner, see section 4).

BEFORE — join on `runner_id` only

EXPLAIN
SELECT "ci_runners".*
FROM "ci_runners"
INNER JOIN "ci_runner_machines"
  ON "ci_runner_machines"."runner_id" = "ci_runners"."id"
WHERE "ci_runners"."id" = 7310;

 Nested Loop  (cost=0.41..20.49 rows=15 width=294)
   ->  Append  (cost=0.27..10.23 rows=5 width=8)
         ->  Index Only Scan ... instance_type_ci_runner_machines ci_runner_machines_1 (Index Cond: runner_id = 7310)
         ->  Index Only Scan ... group_type_ci_runner_machines    ci_runner_machines_2 (Index Cond: runner_id = 7310)
         ->  Index Only Scan ... project_type_ci_runner_machines  ci_runner_machines_3 (Index Cond: runner_id = 7310)
   ->  Materialize
         ->  Append (instance/group/project ci_runners, Index Cond: id = 7310)

AFTER — join on `runner_id AND runner_type`

EXPLAIN
SELECT "ci_runners".*
FROM "ci_runners"
INNER JOIN "ci_runner_machines"
  ON "ci_runner_machines"."runner_id" = "ci_runners"."id"
  AND "ci_runner_machines"."runner_type" = "ci_runners"."runner_type"
WHERE "ci_runners"."id" = 7310;

 Nested Loop  (cost=0.41..20.46 rows=8 width=294)
   Join Filter: (ci_runner_machines.runner_type = ci_runners.runner_type)
   ->  Append  (cost=0.27..10.23 rows=5 width=10)
         ->  Index Only Scan ... instance_type_ci_runner_machines (Index Cond: runner_id = 7310)
         ->  Index Only Scan ... group_type_ci_runner_machines    (Index Cond: runner_id = 7310)
         ->  Index Only Scan ... project_type_ci_runner_machines  (Index Cond: runner_id = 7310)
   ->  Materialize
         ->  Append (instance/group/project ci_runners, Index Cond: id = 7310)

The added Join Filter correlates the two sides on runner_type. Row estimate drops 15 -> 8; cost is flat for the id-only shape. This is expected — the structural benefit is realised once runner_type is statically known (sections 1, 2, 4).

## 4. Bare `runner.runner_managers` association — always on

The composite FK makes the bare association include runner_type automatically. Captured shape (from a loaded runner, runner_type = 3):

SELECT "ci_runner_machines".* FROM "ci_runner_machines"
WHERE "ci_runner_machines"."runner_id" = 7310
  AND "ci_runner_machines"."runner_type" = 3

Here runner_type IS a static constant (read off the loaded runner), so the planner prunes to the single project_type_ci_runner_machines partition — the production win for the four runner.runner_managers callers (runner API, runner details entity, unregister service).

Summary

Path	FF	Pruning at plan time	Cost
`belonging_to_project`	on	yes, 3 -> 1 partition	44.83 -> 21.29
`belonging_to_group`	on	yes, 3 -> 1 partition	75.24 -> 36.28
`joins(:runner_managers)` (id-only filter)	n/a	no static prune; adds correlating Join Filter	~flat
`runner.runner_managers` (loaded runner)	n/a	yes, runner_type is constant -> 1 partition	n/a

Screenshots or screen recordings

N/A — backend-only change with no user-facing surface.

How to set up and validate locally

Tested end-to-end against the local GDK (this exact script is what I ran):

# MR 2 validation - run inside `rails console` in the gitlab repo.
# Uses existing GDK seed data; no factory creates.

# Pick an existing project runner that has a runner_manager and project assignment.
project_runner = Ci::Runner.project_type.joins(:runner_managers).joins(:runner_projects).first ||
                 Ci::Runner.project_type.joins(:runner_projects).first
raise 'no project runner in DB; seed one first' unless project_runner

project_id = project_runner.runner_projects.first.project_id

# Pick a group runner + its group.
group_runner = Ci::Runner.group_type.joins(:runner_namespaces).first
group_id = group_runner&.runner_namespaces&.first&.namespace_id
raise 'no group runner in DB; seed one first' unless group_id

# 1. Composite foreign_key adds runner_type to the bare association.
sql = project_runner.runner_managers.to_sql
raise "association missing runner_type: #{sql}" unless sql.include?('"runner_type" = 3')
puts "OK: association SQL includes runner_type"

# 2. joins(:runner_managers) correlates on runner_type.
sql = Ci::Runner.joins(:runner_managers).to_sql
raise "join missing correlation: #{sql}" unless sql.include?('"ci_runner_machines"."runner_type" = "ci_runners"."runner_type"')
puts "OK: joins(:runner_managers) correlates on runner_type"

# 3. with_version_prefix inherits the correlation.
sql = Ci::Runner.with_version_prefix('15.').to_sql
raise "with_version_prefix missing correlation: #{sql}" unless sql.include?('"ci_runner_machines"."runner_type" = "ci_runners"."runner_type"')
puts "OK: with_version_prefix correlates on runner_type"

# 4. FF off -> belonging_to_* scopes leave runner_type out (baseline).
Feature.disable(:ci_runner_partition_pruning)
raise 'belonging_to_project FF=off should NOT include runner_type' if Ci::Runner.belonging_to_project(project_id).to_sql.include?('"ci_runners"."runner_type"')
raise 'belonging_to_group FF=off should NOT include runner_type' if Ci::Runner.belonging_to_group(group_id).to_sql.include?('"ci_runners"."runner_type"')
puts "OK: FF=off keeps belonging_to_* scopes unchanged"

# 5. FF on -> the scopes add runner_type.
Feature.enable(:ci_runner_partition_pruning)
raise 'belonging_to_project FF=on missing runner_type=3' unless Ci::Runner.belonging_to_project(project_id).to_sql.match?(/"ci_runners"\."runner_type" = 3/)
raise 'belonging_to_group FF=on missing runner_type=2' unless Ci::Runner.belonging_to_group(group_id).to_sql.match?(/"ci_runners"\."runner_type" = 2/)
puts "OK: FF=on adds runner_type filter to belonging_to_* scopes"

# 6. Behavioural equivalence: flipping the FF does not change the result set.
Feature.disable(:ci_runner_partition_pruning)
baseline_project = Ci::Runner.belonging_to_project(project_id).pluck(:id).sort
baseline_group = Ci::Runner.belonging_to_group(group_id).pluck(:id).sort

Feature.enable(:ci_runner_partition_pruning)
raise 'belonging_to_project diverged with FF on' unless Ci::Runner.belonging_to_project(project_id).pluck(:id).sort == baseline_project
raise 'belonging_to_group diverged with FF on' unless Ci::Runner.belonging_to_group(group_id).pluck(:id).sort == baseline_group
puts "OK: result sets identical regardless of FF state"

# 7. Confirm pruning at the query-plan level.
Feature.enable(:ci_runner_partition_pruning)
puts "\n=== EXPLAIN (FF on) — belonging_to_project ==="
puts Ci::Runner.belonging_to_project(project_id).explain.inspect

Feature.disable(:ci_runner_partition_pruning)
puts "\n=== EXPLAIN (FF off) — belonging_to_project (baseline) ==="
puts Ci::Runner.belonging_to_project(project_id).explain.inspect

puts "\nAll validation checks passed."

Sample EXPLAIN output from my GDK (project_id = 28922):

FF off: Append across instance_type_ci_runners + group_type_ci_runners + project_type_ci_runners (3-partition scan).
FF on: single Index Scan using project_type_ci_runners_pkey, partition pruning confirmed.

Automated test coverage

Added to spec/models/ci/runner_spec.rb under a new describe 'partition pruning' block (13 examples): SQL-shape assertions for the FF-on and FF-off cases of both base scopes, result-set equivalence, and unconditional join correlation checks for joins(:runner_managers), with_version_prefix, and with_upgrade_status.

Local test runs (all green):

gdk predictive --rspec — 411 examples across spec/models/ci/runner_spec.rb, spec/models/every_model_spec.rb, spec/lib/gitlab/import_export/model_configuration_spec.rb, ee/spec/models/ee/ci/runner_spec.rb. 0 failures.
spec/finders/ci/runners_finder_spec.rb, runner_managers_finder_spec.rb, runner_jobs_finder_spec.rb — covered by predictive set + manual run, 322 examples, 0 failures.
spec/services/ci/runners/{unregister_runner_manager,stale_managers_cleanup,reconcile_existing_runner_versions}_service_spec.rb — 0 failures.
ee/spec/services/ci/runners/stale_group_runners_prune_service_spec.rb, ee/spec/lib/analytics/devops_adoption/snapshot_calculator_spec.rb — 30 examples, 0 failures (the two EE callers of belonging_to_* scopes).

References

Implements #594861 (closed)
Builds on !239835 (merged) (MR 1: query_constraints on Ci::RunnerManager).
Composite foreign_key convention: app/models/ci/running_build.rb:19, app/models/ci/pipeline.rb:171, app/models/ci/sources/pipeline.rb:18.

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.

Edited Jun 25, 2026 by Narendran

Add partition pruning to Ci::Runner read paths

What does this MR do and why?

1. Composite foreign_key on has_many :runner_managers (always-on)

2. runner_type filter on belonging_to_project / belonging_to_group (FF-gated)

Why this scope deviates from the issue's proposed patch

Sequence

Database changes

Query Plans

BEFORE (FF off)

AFTER (FF on) — adds AND ci_runners.runner_type = 3

BEFORE (FF off)

AFTER (FF on) — adds AND ci_runners.runner_type = 2

BEFORE — join on runner_id only

AFTER — join on runner_id AND runner_type

Screenshots or screen recordings

How to set up and validate locally

Automated test coverage

References

MR acceptance checklist

Merge request reports

1. Composite `foreign_key` on `has_many :runner_managers` (always-on)

2. `runner_type` filter on `belonging_to_project` / `belonging_to_group` (FF-gated)

AFTER (FF on) — adds `AND ci_runners.runner_type = 3`

AFTER (FF on) — adds `AND ci_runners.runner_type = 2`

BEFORE — join on `runner_id` only

AFTER — join on `runner_id AND runner_type`