Batch GraphQL containerRepositoriesCount to avoid N+1

What does this MR do and why?

Types::ProjectType#container_repositories_count resolved containerRepositoriesCount as project.container_repositories.size. ActiveRecord::Associations::CollectionProxy#size does not memoize, so every time the field is resolved it issues a fresh SELECT COUNT(*). When a query reaches this field once per node under containerRepositories (e.g. containerRepositories { nodes { project { containerRepositoriesCount } } }), the count re-runs once per node, so the query count grows linearly with page size.

This was flagged by N+1 triage on the FetchProjectContainerRepositories operation (an external client's operation, not defined by a .graphql file in this repo): 2,961 requests in 24h, N+1 risk 0.95, p95 422 queries on the main replica, p95 DB duration 0.28s, estimated saving of 20.5 avoidable queries/minute.

Even though Postgres' per-request query cache can serve the repeated identical COUNT(*) queries, they still count towards the db_*_count metrics that feed the p95 figure above (see lib/gitlab/metrics/subscribers/active_record.rb, where :count is incremented regardless of cache hit; cached_count is a separate, additional counter). So the repeats are real, measured load even when Postgres itself does little extra work for them.

Fix, following the existing AlertManagement::Alert.counts_by_project_id pattern:

• Added ContainerRepository.counts_by_project_id (reorder(nil).group(:project_id).count) in app/models/container_repository.rb. The reorder(nil) matches Issue.counts_by_state and keeps the scope safe to chain after an ordered scope: ContainerRepository includes Sortable and has scope :ordered, and without it ContainerRepository.ordered.counts_by_project_id raises PG::GroupingError.
• container_repositories_count in app/graphql/types/project_type.rb now resolves through BatchLoader::GraphQL.for(project.id).batch(default_value: 0), calling ContainerRepository.for_project_id(project_ids).counts_by_project_id once per request no matter how many times the field is reached. default_value: 0 preserves the existing null: false contract for projects without container repositories.
• Removed the now-redundant container_repositories_count: [:container_repositories] lookahead preload in app/graphql/resolvers/concerns/projects/look_ahead_preloads.rb. It used to preload every container repository row just to count them; the batch loader replaces that with a single grouped count. The container_repositories: [:container_repositories] entry, used for the connection field itself, is untouched.

Measured locally with ActiveRecord::QueryRecorder.new(skip_cached: false) (this matches the production metric, since it also counts cached queries):

Scenario A — the reported shape, project { containerRepositories(first: N) { nodes { id name path project { id containerRepositoriesCount } } } }, one project with 20 container repositories, a different user per request:

page size	before	after
2 nodes	48 queries	47 queries
20 nodes	65 queries	46 queries
growth 2 -> 20	+17	none

The per-row SELECT COUNT(*) FROM container_repositories WHERE project_id = ? went from 20 (one per node) to 0; a single grouped count runs instead.

Scenario B — group { projects(first: N) { nodes { id containerRepositoriesCount } } }, 20 projects with one container repository each. This shape was already flat before the fix (the lookahead preload already covered it): 52 queries at first=2 and 51 at first=20, unchanged before and after. The fix doesn't reduce the query count here, but it replaces preloading every container repository row into memory with a single grouped COUNT.

Also identified while triaging this issue, but not fixed in this MR:

• Under the same nested-project selection, jiraImportStatus also re-runs once per node (SELECT ... FROM jira_imports ... ORDER BY created_at DESC LIMIT 1, +18 queries at page 20). That's owned by Category:Importers, not Category:Container Registry, and is listed separately on the issue.
• The group-level group { containerRepositories } path has its own N+1: rows are loaded via ContainerRepository.for_group_and_its_subgroups, which doesn't set inverse_of, so each row loads its own Project and route. Also listed on the issue, not addressed here.
• Types::GroupType#container_repositories_count still uses group.container_repositories.size with a matching lookahead preload in app/graphql/resolvers/concerns/resolves_groups.rb, so the group-level field keeps the pattern this MR removes for projects. It needs a different query (it goes through projects), so it's a natural follow-up rather than part of this change.

All three are out of scope for this change and need separate fixes.

References

• Issue: #629359 (closed)
• Epic (describes how the N+1 top-offenders list was built): &23614

Screenshots or screen recordings

No UI change; this is a backend query-shape change only. The before/after evidence is the query-count table in the "What does this MR do and why?" section above.

How to set up and validate locally

1. Check out this branch and start the GDK as usual.
2. Run the new permanent N+1 spec, which asserts the query count doesn't grow as container repositories are added to a page:

   bundle exec rspec spec/requests/api/graphql/project/container_repositories_spec.rb

   To see it catch the regression, stash the app changes (keep the spec) and re-run; it fails on master with something like Expected 47 queries, but got 50.
3. Run the model spec for the new scope:

   bundle exec rspec spec/models/container_repository_spec.rb

4. Optional manual check: open a project's Container Registry page with the performance bar enabled and confirm the query count doesn't scale with the number of container repositories listed.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability. Please refer to the acceptance checklist for additional guidance.

• Changelog: Changelog: performance trailer is on the commit; no user-facing changelog entry needed.
• No database migration.
• No feature flag: this is a pure query-shape change with no behaviour change, so it doesn't need one.
• No user-facing strings changed.
• Database review requested for the new counts_by_project_id scope and the grouped COUNT query it issues (new SQL and index coverage below).

New/changed SQL:

SELECT COUNT(*) AS "count_all", "container_repositories"."project_id" AS "container_repositories_project_id"
FROM "container_repositories"
WHERE "container_repositories"."project_id" IN (278964, 13083)
GROUP BY "container_repositories"."project_id"

index_container_repositories_on_project_id_and_id on container_repositories (project_id, id) covers both the filter and the grouping (confirmed by the plan below).

Project IDs are the real production ones documented in doc/development/database_review.md: 278964 (gitlab-org/gitlab) and 13083 (gitlab-org/gitlab-foss).

Before

SELECT COUNT(*) FROM "container_repositories" WHERE "container_repositories"."project_id" = 278964

Ran once per container repository node in the page, so a page of N nodes issued N of these.

Plan from Postgres.ai (Database Lab): https://postgres.ai/console/gitlab/gitlab-production-main/sessions/58070/commands/162777

Aggregate  (cost=3.89..3.90 rows=1 width=8) (actual time=0.037..0.037 rows=1 loops=1)
  Buffers: shared hit=7
  I/O Timings: read=0.000 write=0.000
  ->  Index Only Scan using index_container_repositories_on_project_id_and_id on public.container_repositories  (cost=0.43..3.83 rows=23 width=0) (actual time=0.025..0.031 rows=30 loops=1)
        Index Cond: (container_repositories.  [...truncated in Joe output...]

0.922 ms total (0.804 ms planning, 0.118 ms execution), no I/O read, 7 buffer hits, 0 reads.

After

Plan from Postgres.ai (Database Lab): https://postgres.ai/console/gitlab/gitlab-production-main/sessions/58070/commands/162769

GroupAggregate  (cost=0.43..6.88 rows=47 width=12) (actual time=4.730..6.774 rows=2 loops=1)
  Group Key: container_repositories.project_id
  Buffers: shared hit=3 read=9 dirtied=2
  WAL: records=2 fpi=2 bytes=16122
  I/O Timings: read=6.636 write=0.000
  ->  Index Only Scan using index_container_repositories_on_project_id_and_id on public.container_repositories  (cost=0.43..6.18 rows=47 width=4) (actual time=3.068..6.757 rows=39 loops=1)
        Index Cond: (container_repositories.project_id = ANY ('{278964,13083}'::integer[]))
        Heap Fetches: 2
        Buffers: shared hit=3 read=9 dirtied=2
        WAL: records=2 fpi=2 bytes=16122
        I/O Timings: read=6.636 write=0.000
Settings: effective_cache_size = '472585MB', work_mem = '230MB', jit = 'off', random_page_cost = '1.5', seq_page_cost = '4'
Query ID: 3239232434469049379

7.818 ms total (0.978 ms planning, 6.840 ms execution), of which 6.636 ms of execution is I/O read. Buffers: 3 hits, 9 reads (~96 KiB), 2 dirtied.

• The covering index prediction held: index_container_repositories_on_project_id_and_id is used as an Index Only Scan. No sequential scan, and no separate sort step — GroupAggregate consumes the index order directly, so GROUP BY project_id costs nothing extra.
• Heap Fetches: 2 out of 39 index rows scanned, so almost everything is served from the index alone; the visibility map is doing its job.
• Nearly all of the 6.84 ms execution time is cold-cache I/O (6.636 ms read). On a warm buffer pool this is a fraction of a millisecond of actual CPU work. WAL: records=2 fpi=2 is hint-bit / visibility-map maintenance on first touch of cold pages, not per-execution write cost.
• Planner estimate of 47 rows against 39 actual is close enough; no misestimation risk here.
• Comfortably inside GitLab's query timing guidelines.
• Scaling: the IN list holds one entry per distinct project in the GraphQL batch. For the FetchProjectContainerRepositories operation that's a single project, so the list has one element; the two-element list above is the worst case only in the sense that cost grows linearly with distinct project IDs, bounded by the GraphQL page size.

Before vs after

The raw totals (0.922 ms before vs 7.818 ms after) are not a like-for-like comparison and should not be read as the new query being slower.

The before plan ran fully warm: 7 buffer hits, 0 reads, 0.000 ms I/O. The after plan ran cold: 9 reads, and 6.636 ms of its 6.840 ms execution was I/O read on the Database Lab clone.

Like-for-like on CPU, subtracting I/O: before is 0.118 ms of execution, after is 6.840 − 6.636 = 0.204 ms. Same order of magnitude.

The after query also does strictly more work per call: it counts two projects (39 index rows) where the before query counts one (30 index rows).

Both use the same access path, an Index Only Scan on index_container_repositories_on_project_id_and_id, so nothing about the index changed; only the number of calls did.

The actual win is call count, not per-call cost. The before query ran once per container repository node, so a page of N nodes issued N of these, each with its own ~0.8 ms planning and ~7 buffer hits, against exactly one call after the change regardless of page size.

For a 20-node page, that's roughly 20 calls and ~140 buffer hits before, versus 1 call and 12 buffers after. This is an illustration from these two plans' numbers, not a measured production figure.

This is what takes the per-page db_main_replica_count contribution of this field from N down to 1.

Test runs (all local, all passing):

• spec/requests/api/graphql/project/container_repositories_spec.rb, spec/requests/api/graphql/group/container_repositories_spec.rb, spec/models/container_repository_spec.rb: 161 examples, 0 failures
• spec/graphql/types/project_type_spec.rb, spec/graphql/resolvers/concerns/resolves_groups_spec.rb: passing
• spec/graphql/resolvers/projects_resolver_spec.rb, spec/requests/api/graphql/projects/projects_spec.rb, spec/requests/api/graphql/namespace/projects_spec.rb: 63 examples, 0 failures
• RuboCop clean on all five changed files.