Drop `index_vulnerability_occurrences_for_issue_links_migration`
requested to merge 471153-evaluate-and-remove-index-index_vulnerability_occurrences_for_issue_links_migration into master
What does this MR do and why?
This index was added in 13.3
to support a data migration in Add migration helper index for Vulnerabilities:... (!38898 - merged) • Tiger Watson • 13.3
Verification that index is unused
The "Finding unused indexes" docs say that you can check if an index is still used in production using the following thanos query:
sum by (type)(rate(pg_stat_user_indexes_idx_scan{env="gprd", indexrelname="index_vulnerability_occurrences_for_issue_links_migration"}[180d]))
doing so results in no usage:
thanos results
thanos results |
---|
thanos deprecation
There is a warning about thanos being deprecated, so perhaps I need to check grafana instead
I ran a similar query in grafana and also see no usages
sum by(type) (rate(pg_stat_user_indexes_idx_scan{indexrelname="index_vulnerability_occurrences_for_issue_links_migration"}[30d]))
Migration output
bundle exec rails db:migrate
main: == [advisory_lock_connection] object_id: 129000, pg_backend_pid: 329519
main: == 20240709093324 DropIndexVulnerabilityOccurrencesForIssueLinksMigration: migrating
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0289s
main: -- indexes(:vulnerability_occurrences)
main: -> 0.0056s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0002s
main: -- remove_index(:vulnerability_occurrences, {:algorithm=>:concurrently, :name=>"index_vulnerability_occurrences_for_issue_links_migration"})
main: -> 0.0034s
main: -- execute("RESET statement_timeout")
main: -> 0.0002s
main: == 20240709093324 DropIndexVulnerabilityOccurrencesForIssueLinksMigration: migrated (0.0482s)
main: == [advisory_lock_connection] object_id: 129000, pg_backend_pid: 329519
ci: == [advisory_lock_connection] object_id: 129220, pg_backend_pid: 329521
ci: == 20240709093324 DropIndexVulnerabilityOccurrencesForIssueLinksMigration: migrating
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0006s
ci: -- indexes(:vulnerability_occurrences)
ci: -> 0.0047s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0002s
ci: -- remove_index(:vulnerability_occurrences, {:algorithm=>:concurrently, :name=>"index_vulnerability_occurrences_for_issue_links_migration"})
ci: -> 0.0027s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0001s
ci: == 20240709093324 DropIndexVulnerabilityOccurrencesForIssueLinksMigration: migrated (0.0197s)
ci: == [advisory_lock_connection] object_id: 129220, pg_backend_pid: 329521
bundle exec rails db:migrate:down:main VERSION=20240709093324
main: == [advisory_lock_connection] object_id: 128500, pg_backend_pid: 334691
main: == 20240709093324 DropIndexVulnerabilityOccurrencesForIssueLinksMigration: reverting
main: -- transaction_open?(nil)
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0101s
main: -- index_exists?(:vulnerability_occurrences, "project_id, report_type, encode(project_fingerprint, 'hex'::text)", {:name=>"index_vulnerability_occurrences_for_issue_links_migration", :algorithm=>:concurrently})
main: -> 0.0044s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0002s
main: -- add_index(:vulnerability_occurrences, "project_id, report_type, encode(project_fingerprint, 'hex'::text)", {:name=>"index_vulnerability_occurrences_for_issue_links_migration", :algorithm=>:concurrently})
main: -> 0.0101s
main: -- execute("RESET statement_timeout")
main: -> 0.0002s
main: == 20240709093324 DropIndexVulnerabilityOccurrencesForIssueLinksMigration: reverted (0.0381s)
main: == [advisory_lock_connection] object_id: 128500, pg_backend_pid: 334691
bundle exec rails db:migrate:down:ci VERSION=20240709093324
ci: == [advisory_lock_connection] object_id: 128500, pg_backend_pid: 335058
ci: == 20240709093324 DropIndexVulnerabilityOccurrencesForIssueLinksMigration: reverting
ci: -- transaction_open?(nil)
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0025s
ci: -- index_exists?(:vulnerability_occurrences, "project_id, report_type, encode(project_fingerprint, 'hex'::text)", {:name=>"index_vulnerability_occurrences_for_issue_links_migration", :algorithm=>:concurrently})
ci: -> 0.0048s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0002s
ci: -- add_index(:vulnerability_occurrences, "project_id, report_type, encode(project_fingerprint, 'hex'::text)", {:name=>"index_vulnerability_occurrences_for_issue_links_migration", :algorithm=>:concurrently})
ci: -> 0.0157s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0006s
ci: == 20240709093324 DropIndexVulnerabilityOccurrencesForIssueLinksMigration: reverted (0.0430s)
ci: == [advisory_lock_connection] object_id: 128500, pg_backend_pid: 335058
MR acceptance checklist
Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.
resolves: #471153 (closed)
Changelog: removed
Edited by Michael Becker