Optimize issue rebalancing UPDATE query [RUN ALL RSPEC] [RUN AS-IF-FOSS]
What does this MR do?
This MR aims to improve issue relative position rebalancing. The improvement is behind a feature flag (issue_rebalancing_optimization
).
Assumption:
We load the issues ordered by relative_position
, later on this (large) array is going to be batched. The update query will contain 100 issue ids where the ids can be from any random location in the table.
I think we can significantly speed up the update query if we improve the data locality. Update issues where the ids are close to each other.
The fix: we calculate the new position for the issues and then sort them by id
. This new array gets batched.
Testing (postgres.ai)
Snippet:
# Also change the MAX_ISSUE_COUNT to 100_000_000 in IssueRebalancingService
IssueRebalancingService.new(Project.find(278964).issues.take).execute
Unfortunately I couldn't run the whole rebalancing process with the current code because the queries are simply timing out.
TBD
Does this MR meet the acceptance criteria?
Conformity
-
Changelog entry -
Documentation (if required) -
Code review guidelines -
Merge request performance guidelines -
Style guides -
Database guides -
Separation of EE specific content
Availability and Testing
-
Review and add/update tests for this feature/bug. Consider all test levels. See the Test Planning Process. -
Tested in all supported browsers -
Informed Infrastructure department of a default or new setting change, if applicable per definition of done
Security
If this MR contains changes to processing or storing of credentials or tokens, authorization and authentication methods and other items described in the security review guidelines:
-
Label as security and @ mention @gitlab-com/gl-security/appsec
-
The MR includes necessary changes to maintain consistency between UI, API, email, or other methods -
Security reports checked/validated by a reviewer from the AppSec team