Optimise namespace hierarchy traversal and querying
It has been noted that there is a pervasive issue with regards to namespace traversal in queries across GitLab, resulting in a variety of current and upcoming features failing to meet performance requirements when interacting with very large Group's.
This issue is serving as a centralisation point to track features and merge requests that we find to be affected by this growing issue.
@ahegyi created Optimize group hierarchy retrieval in group-lev... (&11469) to track research into the process to optimise the retrieval of the hierarchy itself.
History:
In this slack thread(internal only, 90 day expiry) @robotmay_gitlab proposed the use of the Ancestry gem as a potential solution to Namespace hierarchy traversal performance issues.
In groupthreat insights the SBOM implementation of group level project search has been repeatedly encountering database performance constraints related to significant namespace data reads: #419533 (comment 1485559234)
Considering there are existing namespaces within GitLab which already suffer from these problems, and an existing API that can cause issues if used on the right namespace (Namespace text search), it may be worth looking into improving this.
Proposal
- A side-by-side implementation of the Ancestry gem with which we can do a performance evaluation.
Affected MR's
A list of MR's with query plans present consisting of significant data read associated with namespace traversal.
!129203 (merged)