Add query_constraints to Ci::RunnerManager for partition pruning

Narendranrequested to merge
narendran-kannan/594861/runner-manager-query-constraints into master

What does this MR do and why?

ci_runner_machines is LIST-partitioned by runner_type (1 = instance, 2 = group, 3 = project) with composite primary key (id, runner_type). PostgreSQL can skip entire partitions when runner_type is part of the WHERE clause — but single-record write paths (reload, update, destroy) currently issue queries keyed only on id, forcing the planner to scan all three partitions.

This MR declares query_constraints :id, :runner_type on Ci::RunnerManager. With this in place, ActiveRecord includes runner_type in the WHERE clause for every instance-level reload, UPDATE, and DELETE (these use _in_memory_query_constraints_hash). The change is logically a no-op (a runner manager always has the same runner_type as its parent runner) but enables partition pruning at the query plan level.

Scope note: Class-level lookups via Ci::RunnerManager.find(id) / find_by(id:) do not consult query_constraints — Rails 7.2 FinderMethods#find_one builds where(primary_key => id) only and still scans all three partitions. The codebase has no RunnerManager.find callers today, but read-path lookups (e.g. GraphQL/GlobalID resolution) would still scan all three partition without runner_type condition.

The two hot production paths that benefit immediately:

  • RunnerManager#update_columns — called on every runner heartbeat via Ci::RunnerManager#heartbeat.
  • runner_manager.destroy! — called by Ci::Runners::UnregisterRunnerManagerService.

This is the first MR in a series that implements the partition pruning work for runner-related models. Subsequent MRs (behind a feature flag) will add runner_type filters to Ci::Runner scopes (belonging_to_project, belonging_to_group, with_version_prefix, with_upgrade_status) and to the runner.runner_managers association reads.

Why :id, :runner_type and not :runner_id, :runner_type?

The linked issue's example patch suggests query_constraints :runner_id, :runner_type, matching the Ci::RunnerTagging declaration. That combination is unsafe for Ci::RunnerManager because (runner_id, runner_type) is not unique — a runner has many managers. Under Rails 7.2, query_constraints columns are used in the WHERE clause of UPDATE and DELETE (ActiveRecord docs), so runner_manager.destroy! would delete all managers belonging to that runner. I caught this with the Ci::Runners::UnregisterRunnerManagerService spec failing before switching to :id, :runner_type. RunnerTagging does not exhibit this bug because it is never destroyed individually — only in bulk via association replacement.

The composite primary key on the partitioned table is (id, runner_type), so declaring query_constraints :id, :runner_type aligns the ActiveRecord write semantics with the actual database PK.

Query Plans

Operation BEFORE query (id only) BEFORE plan AFTER query (id + runner_type) AFTER plan
SELECT (reload) SELECT * FROM ci_runner_machines WHERE id = 121405279 LIMIT 1; #154143 SELECT * FROM ci_runner_machines WHERE id = 121405279 AND runner_type = 3 LIMIT 1; #154144
UPDATE (heartbeat) UPDATE ci_runner_machines SET contacted_at = NOW() WHERE id = 121405279; #154145 UPDATE ci_runner_machines SET contacted_at = NOW() WHERE id = 121405279 AND runner_type = 3; #154146
DELETE (unregister) DELETE FROM ci_runner_machines WHERE id = 121405278; #154147 DELETE FROM ci_runner_machines WHERE id = 121405277 AND runner_type = 3; #154148

See the full follow-up note for headline numbers (partitions touched, execution time, WAL bytes) and expanded plan output for each operation.

References

Screenshots or screen recordings

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

How to set up and validate locally

# Verify the declared constraints
Ci::RunnerManager.query_constraints_list
# => ["id", "runner_type"]

# Set up a runner with two managers
runner = FactoryBot.create(:ci_runner, :group, groups: [FactoryBot.create(:group)])
manager1 = FactoryBot.create(:ci_runner_machine, runner: runner, system_xid: 'sys-1')
manager2 = FactoryBot.create(:ci_runner_machine, runner: runner, system_xid: 'sys-2')

# 1. Verify SELECT-by-PK (reload) includes runner_type
ActiveSupport::Notifications.subscribed(->(_, _, _, _, p) { puts p[:sql] if p[:sql].include?('ci_runner_machines') }, 'sql.active_record') do
  manager1.reload
end
# Expect: SELECT ... FROM "ci_runner_machines" WHERE "ci_runner_machines"."id" = $1 AND "ci_runner_machines"."runner_type" = $2

# 2. Verify UPDATE includes runner_type
ActiveSupport::Notifications.subscribed(->(_, _, _, _, p) { puts p[:sql] if p[:sql].include?('UPDATE "ci_runner_machines"') }, 'sql.active_record') do
  manager1.update_columns(architecture: 'amd64')
end
# Expect: UPDATE "ci_runner_machines" SET ... WHERE "ci_runner_machines"."id" = $1 AND "ci_runner_machines"."runner_type" = $2

# 3. Verify DELETE includes runner_type AND only the target row is removed
ActiveSupport::Notifications.subscribed(->(_, _, _, _, p) { puts p[:sql] if p[:sql].include?('DELETE FROM "ci_runner_machines"') }, 'sql.active_record') do
  manager1.destroy!
end
# Expect: DELETE FROM "ci_runner_machines" WHERE "ci_runner_machines"."id" = $1 AND "ci_runner_machines"."runner_type" = $2

raise 'manager1 should be destroyed' if Ci::RunnerManager.exists?(manager1.id)
raise 'manager2 must NOT be destroyed' unless Ci::RunnerManager.exists?(manager2.id)
puts 'OK: destroy is scoped to a single row'

# 4. Confirm partition pruning at the query plan level
puts Ci::RunnerManager.where(id: manager2.id, runner_type: manager2.runner_type).explain
# Expect a single partition (e.g. group_type_ci_runner_machines) in the plan, not all three.

Automated test coverage is added in spec/models/ci/runner_manager_spec.rb under the new describe 'query_constraints' block (5 examples covering the declaration, the SELECT/UPDATE/DELETE shapes, and the single-row-destroy safety property).

Full RSpec runs performed locally with all suites green:

  • spec/models/ci/runner_manager_spec.rb — 121 examples (+5 new), 0 failures
  • spec/models/ci/runner_spec.rb — 343 examples, 0 failures
  • spec/models/ci/build_spec.rb -e runner_manager — 11 examples, 0 failures
  • spec/finders/ci/runner_managers_finder_spec.rb, runner_jobs_finder_spec.rb, runners_finder_spec.rb
  • spec/services/ci/runners/unregister_runner_manager_service_spec.rb, stale_managers_cleanup_service_spec.rb, reconcile_existing_runner_versions_service_spec.rb
  • spec/requests/api/ci/runner/ — 359 examples, 0 failures (covers register, heartbeat, ensure_manager)

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 by Narendran

Merge request reports