Skip to content

Remove a partial index with locked_state on merge_requests

Sincheol (David) Kim requested to merge remove-idx_merge_requests_on_target_project_id_and_locked_state into master

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

Edited by Sincheol (David) Kim

Merge request reports