Schedule removing health status index

Nicolas Dularrequested to merge
nd/remove-health-status-index into master

What does this MR do and why?

Schedule removing health status index

This schedules the removal of the health status index on the issues table.

The usage is low, and we have a more specific index already that filters for the project_id first and has a preferred usage.

Changelog: changed

References

Example queries for gitlab-org/gitlab for different scenarios. In all cases it uses the project_id_health_status index. This makes sense as a query for health_status alone, without any other filter is unrealistic, we can drop that index.

In none of the cases it used the index we're about to remove. It actually only started using the other health_status index with project_id on the last use case (which also shows low usage).

Screenshots or screen recordings

Before After

How to set up and validate locally

bin/rails db:migrate
main: == [advisory_lock_connection] object_id: 145180, pg_backend_pid: 2866
main: == 20250804112937 RemoveIdxIssuesOnHealthStatusNotNull: migrating =============
main: -- index_exists?(:issues, [:health_status], {:name=>"idx_issues_on_health_status_not_null"})
main:    -> 0.0273s
main: -- quote_column_name("idx_issues_on_health_status_not_null")
main:    -> 0.0000s
main: == 20250804112937 RemoveIdxIssuesOnHealthStatusNotNull: migrated (0.0482s) ====

main: == [advisory_lock_connection] object_id: 145180, pg_backend_pid: 2866
ci: == [advisory_lock_connection] object_id: 145180, pg_backend_pid: 2868
ci: == 20250804112937 RemoveIdxIssuesOnHealthStatusNotNull: migrating =============
ci: -- index_exists?(:issues, [:health_status], {:name=>"idx_issues_on_health_status_not_null"})
ci:    -> 0.0086s
ci: -- quote_column_name("idx_issues_on_health_status_not_null")
ci:    -> 0.0000s
ci: == 20250804112937 RemoveIdxIssuesOnHealthStatusNotNull: migrated (0.0228s) ====

ci: == [advisory_lock_connection] object_id: 145180, pg_backend_pid: 2868
gitlabhq_development=# select * from postgres_async_indexes order by id desc limit 1;
 id |          created_at           |          updated_at           |                 name                 |                           definition                           | table_name | attempts | last_error
----+-------------------------------+-------------------------------+--------------------------------------+----------------------------------------------------------------+------------+----------+------------
 39 | 2025-08-04 11:31:35.587478+00 | 2025-08-04 11:31:35.587478+00 | idx_issues_on_health_status_not_null | DROP INDEX CONCURRENTLY "idx_issues_on_health_status_not_null" | issues     |        0 |
(1 row)
bundle exec rails gitlab:db:reindex

➜  gitlab git:(nd/remove-health-status-index) bundle exec rails gitlab:db:reindex
➜  gitlab git:(nd/remove-health-status-index) gdk psql
psql (16.8)
Type "help" for help.

gitlabhq_development=# \d idx_issues_on_health_status_not_null
Did not find any relation named "idx_issues_on_health_status_not_null".
gitlabhq_development=#

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Nicolas Dular

Merge request reports