Fix secure product metrics index inconsistency
Background
At !72198 (merged) secure product service ping metrics was quarantine as a result of index_security_ci_builds_on_name_and_id_parser_features
being broken by !70245 (diffs), which caused catastrophic performance degradation (from ~12 hours of complete service ping reporting to 130 days just for 10 out of ~1800 metrics). 7 out of 10 broken metrics are categorized as operational meaning they've been selected into small (~200 metrics) subset of metrics with higher impact for business decisions.
Goal
Fix broken index and bring back metrics from quarantine.
Technical plan
The following discussion from !71712 (merged) should be addressed:
-
@abrandl started a discussion: Ok, so I think we need to do this as a follow-up for 14.5:
- Revert the change in
db/structure.sql
from !71712 (merged) - Add a migration to deal with all possible situations and remove the inconsistency
The migration would perform these steps:
- Drop
index_security_ci_builds_on_name_and_id_parser_features*
indexes if INVALID - Rename
index_security_ci_builds_on_name_and_id_parser_features_broken
toindex_security_ci_builds_on_name_and_id_parser_features
if the target index does not exist - Drop
index_security_ci_builds_on_name_and_id_parser_features_broken
ifindex_security_ci_builds_on_name_and_id_parser_features
exists - Create
index_security_ci_builds_on_name_and_id_parser_features
if it still does not exist (in case there is only the invalid one)
After that, we can assume that any installation (existing and future) only has
index_security_ci_builds_on_name_and_id_parser_features
(which is not INVALID) andindex_security_ci_builds_on_name_and_id_parser_features_broken
does not exist. - Revert the change in