Loading
Index resource groups by project and ID for the REST list
What does this MR do and why?
Context
REST GET /projects/:id/resource_groups orders a project's resource groups by ID, but no index covers the project and ID together. On projects with many resource groups, a page can walk the table's primary key instead of the project's rows, and deep pages read every skipped row from the table. The figures and the agreed approach are in https://gitlab.com/gitlab-org/gitlab/-/work_items/638254#note_4175668075.
This MR
- A post-deploy migration adds an index on
ci_resource_groups (project_id, id). It returns the same rows in the same order, so it takes no feature flag. - Behind a feature flag, the list skips the offset with
Gitlab::Pagination::Offset::PaginationWithIndexOnlyScan: it reads the page's IDs from the index and loads only those rows. Rows, order and pagination headers don't change. PaginationWithIndexOnlyScannow takesLIMITandOFFSETfrom the relation when it loads, so pagination without a count (over 10,000 rows) still detects the next page and keeps the next-page headers. This fix is not behind the flag; the helper's only other caller, audit events, doesn't reach the optimized query today.
Feature Flag: resource_groups_list_index_only_scan
References
- https://gitlab.com/gitlab-org/gitlab/-/work_items/638254+
- https://gitlab.com/gitlab-org/gitlab/-/work_items/639053+
Database queries
See MR comments.
How to set up and validate locally
- Run the migration:
bin/rails db:migrate. - Create a few resource groups in a project, for example by running pipelines whose jobs set
resource_group:. - Run this with a personal access token (
read_apiscope):Note the resource groups returned and thecurl --include --header "PRIVATE-TOKEN: <your_access_token>" "http://gdk.test:3000/api/v4/projects/<id>/resource_groups?per_page=2&page=2"X-Next-PageandLinkheaders. - In a Rails console, run
Feature.enable(:resource_groups_list_index_only_scan)and wait a minute. - Run the
curlagain. The resource groups and headers are the same, andlog/development.logshows a query startingWITH "index_only_scan_pagination_cte" AS MATERIALIZEDwithLATERAL.
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 Leaminn Ma