Remove unused indexes on `merge_requests` table
Related to #402490 (closed)
There are many indexes on the merge_requests table that have no usage or very low usage. All indexes slow down write operations and consume disk space. This is particularly problematic at the moment as the primary database is consuming a lot of CPU. Every update to this table needs to update all references in all indexes so unused indexes are very wasteful for large tables.
Unused indexes
From the the index usage metrics the following indexes have not been used at all in the last 8 weeks:
| Index | Issue | MR |
|---|---|---|
| idx_merge_requests_on_state_id_and_merge_status | #402490 (closed) | !115926 (merged) |
| index_merge_requests_on_target_project_id_and_iid_jira_title | #403327 (closed) | |
| index_merge_requests_on_target_project_id_iid_jira_description | #403327 (closed) | |
| merge_request_mentions_temp_index | This one | !116729 (merged) |
| merge_requests_state_id_temp_index | This one | !116726 (merged) |
What to do?
Take a look through the indexes and unless you can find a good reason to keep them then create an MR like !115926 (merged) to remove them. Some good reasons to keep them:
- They are used for upcoming features that are being implemented or disabled by a feature flag (you can determine this by seeing when they were added)
- They are known to be used for self-managed features. To determine this you might need deeper knowledge of the features and possibly looking up the MR that introduced this index. Absent all evidence that it is used for self-managed then we should probably delete them
Availability and Testing
Regression testing, please make sure e2e:package-and-test job is passing.
Process
The process to remove database index uses 3 steps (as documented here: https://docs.gitlab.com/ee/development/database/adding_database_indexes.html#drop-indexes-asynchronously )
- Schedule the index to be removed in the database as asynchronously (usually on a weekend):
- Asynchronous removals:
- Asynchronously remove `merge_request_state_id_t... (!120348 - merged)
- Async remove 'merge_request_mentions_temp_index... (!116726 - merged)
- Verify the MR was deployed and the index doesn't exist in production:
- Use database lab for this: https://console.postgres.ai/
- After confirming the indexes are removed, they can be removed from structure.sql synchronously: