getGroupRunnersCountEE statement timeout for namespaces with lots of Runners

Summary

A large GitLab Ultimate customer engaged GitLab Support with this problem. When a namespace has a lot of Runners, accessing the Runners UI results in the getGroupRunnersCountEE getting a 503 error, and exception message PG::QueryCanceled: ERROR: canceling statement due to statement timeout. The duration of these requests is 15s consistently (db_ci_replica_duration_s).

SQL 
/*application:web,correlation_id:a05a52e37addf81b-DFW,endpoint_id:GraphqlController#execute,db_config_database:gitlabhq_production,db_config_name:ci_replica*/ SELECT COUNT(*) FROM ((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\" IN (SELECT \"ci_namespace_mirrors\".\"namespace_id\" FROM \"ci_namespace_mirrors\" WHERE (traversal_ids @> $1)))\nUNION ALL\n(SELECT DISTINCT \"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\" IN (SELECT \"ci_project_mirrors\".\"project_id\" FROM \"ci_project_mirrors\" WHERE \"ci_project_mirrors\".\"namespace_id\" IN (SELECT \"ci_namespace_mirrors\".\"namespace_id\" FROM \"ci_namespace_mirrors\" WHERE (traversal_ids @> $2))))) ci_runners

Steps to reproduce

This is reproducible by accessing the affected customer's Runners page for their root namespace.

GitLab Support's successful reproduction attempt Correlation ID: a05b1d099e63d718-BNE

Example Project

What is the current bug behavior?

GraphQL query times out with 503 status code,Runners UI does not render, error message displays Something went wrong while fetching runner data

What is the expected correct behavior?

Request does not time out, Runners are rendered on the screen without error

Relevant logs and/or screenshots

Output of checks

Results of GitLab environment info

Expand for output related to GitLab environment info 
(For installations with omnibus-gitlab package run and paste the output of:
`sudo gitlab-rake gitlab:env:info`)

(For installations from source run and paste the output of:
`sudo -u git -H bundle exec rake gitlab:env:info RAILS_ENV=production`)

Results of GitLab application Check

Expand for output related to the GitLab application check 
(For installations with omnibus-gitlab package run and paste the output of:
sudo gitlab-rake gitlab:check SANITIZE=true)


(For installations from source run and paste the output of:
sudo -u git -H bundle exec rake gitlab:check RAILS_ENV=production SANITIZE=true)


(we will only investigate if the tests are passing)

Possible fixes

Root cause (validated on Database Lab)

Ci::Runner.belonging_to_group_or_project_descendants and usable_from_scope resolve descendant namespaces via Ci::NamespaceMirror.by_group_and_descendants(id), which filters with the GIN operator traversal_ids @> '{id}'. For a namespace with a large subtree this forces an expensive bitmap heap scan.

Validated against root namespace 4249178 (~104K descendant namespaces):

Case Access path Time
Current, root namespace, cold cache GIN @> bitmap heap scan (~84K blocks, ~657 MiB) 53.8s → timeout
Proposed fix, root namespace, cold traversal_ids_unnest index-only scan 1.9s
Proposed fix, non-root depth-3 subgroup traversal_ids_unnest index-only scan (0 heap fetches) 8.4ms

The intermittent nature on the ci_replica is explained by the cold/warm cache split (224ms–1.5s warm vs 53.8s cold). The covering index index_ci_namespace_mirrors_on_traversal_ids_unnest serves the same result as an index-only scan (~50x less I/O), but is only reachable via the (traversal_ids[1..N]) IN (...) form used by the existing contains_traversal_ids scope — not via GIN @>.

Proposed fix

Route descendant-namespace resolution through the unnest covering index instead of GIN:

  1. Ci::NamespaceMirror (app/models/ci/namespace_mirror.rb) — add a scope that resolves a group's subtree via contains_traversal_ids([group.traversal_ids]) (index-only path).
  2. Ci::Runner (app/models/ci/runner.rb) — repoint belonging_to_group_or_project_descendants and usable_from_scope to the new scope. Signatures change to accept the group (needs traversal_ids, not just id). Update call sites in RunnersFinder#group_runners; :direct membership is unaffected.
  3. Feature flag — gate the access-path change (modeled on ci_runner_partition_pruning) for DB-dashboard observability; fall back to the GIN path when disabled.
  4. Tests — spec/models/ci/runner_spec.rb, spec/models/ci/namespace_mirror_spec.rb, spec/finders/ci/runners_finder_spec.rb; assert SQL uses the prefix/IN form; cover root + non-root groups and flag on/off.
Secondary optimization (in scope)

The validated plan shows the project-scoped branch is the slower half (~1.3s): a DISTINCT ci_runners.* with a sort over 24 wide columns plus a join to the partitioned ci_runners table purely to count. Slim it: count runner_id from the join tables, replace DISTINCT ci_runners.* with distinct-on-id, and avoid joining ci_runners for the count.

Patch release information for backports

If the bug fix needs to be backported in a patch release to a version under the maintenance policy, please follow the steps on the patch release runbook for GitLab engineers.

Refer to the internal "Release Information" dashboard for information about the next patch release, including the targeted versions, expected release date, and current status.

High-severity bug remediation

To remediate high-severity issues requiring an internal release for single-tenant SaaS instances, refer to the internal release process for engineers.

Edited by 🤖 GitLab Bot 🤖