Remove a partial index with locked_state on merge_requests
What does this MR do and why?
Drop an unnecessary index on merge_requests
idx_merge_requests_on_target_project_id_and_locked_state
seems to
be unnecessary since we have idx_mrs_on_target_id_and_created_at_and_state_id
which covers the same use case.
It currently uses only ~2 MiB on production, but removing unnecessary index would help reducing WAL rate.
idx_merge_requests_on_target_project_id_and_locked_state
seems to have been added in !34127 (merged) and idx_mrs_on_target_id_and_created_at_and_state_id
got created subsequently via !57267 (merged)
Running the original query mentioned in !34127 (merged) resulted in a similar query plan as follows.
Before: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27369/commands/85189
After: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27369/commands/85196
Migration (Up)
(remove-idx_merge_requests_on_target_project_id_and_locked_state ✗) ➜ rake db:migrate [20:32 04/04]
main: == [advisory_lock_connection] object_id: 121600, pg_backend_pid: 73209
main: == 20240404092530 RemoveIdxMergeRequestsOnTargetProjectIdAndLockedState: migrating
main: -- index_exists?(:merge_requests, :target_project_id, {:name=>"idx_merge_requests_on_target_project_id_and_locked_state"})
main: -> 0.0222s
main: == 20240404092530 RemoveIdxMergeRequestsOnTargetProjectIdAndLockedState: migrated (0.0259s)
main: == [advisory_lock_connection] object_id: 121600, pg_backend_pid: 73209
ci: == [advisory_lock_connection] object_id: 121860, pg_backend_pid: 73211
ci: == 20240404092530 RemoveIdxMergeRequestsOnTargetProjectIdAndLockedState: migrating
ci: -- index_exists?(:merge_requests, :target_project_id, {:name=>"idx_merge_requests_on_target_project_id_and_locked_state"})
ci: -> 0.0097s
ci: == 20240404092530 RemoveIdxMergeRequestsOnTargetProjectIdAndLockedState: migrated (0.0180s)```
Migration (Down)
(remove-idx_merge_requests_on_target_project_id_and_locked_state ✗) ➜ rake db:rollback:main STEP=1 [20:32 04/04]
main: == [advisory_lock_connection] object_id: 121240, pg_backend_pid: 72553
main: == 20240404092530 RemoveIdxMergeRequestsOnTargetProjectIdAndLockedState: reverting
main: == 20240404092530 RemoveIdxMergeRequestsOnTargetProjectIdAndLockedState: reverted (0.0221s)
(remove-idx_merge_requests_on_target_project_id_and_locked_state ✗) ➜ rake db:rollback:ci STEP=1 [20:32 04/04]
ci: == [advisory_lock_connection] object_id: 121240, pg_backend_pid: 71903
ci: == 20240404092530 RemoveIdxMergeRequestsOnTargetProjectIdAndLockedState: reverting
ci: == 20240404092530 RemoveIdxMergeRequestsOnTargetProjectIdAndLockedState: reverted (0.0238s)
ci: == [advisory_lock_connection] object_id: 121240, pg_backend_pid: 71903
dskim@dskim-gitlab-mbp: ~/forge/gitlab/gitlab-development-kit/gitlab [3.2.3]
Async Index removal verification
gitlabhq_development=# \di+ idx_merge_requests_on_target_project_id_and_locked_state
Did not find any relation named "idx_merge_requests_on_target_project_id_and_locked_state".
Related to https://gitlab.com/gitlab-org/gitlab/-/issues/448711#note_1831284182