Skip to content

Drop broken ci_builds index after it's fixed duplicated has been created

Mikołaj Wawrzyniak requested to merge mwaw/clean_redundant_index_security_ci_builds_on_name_and_id_parser_features_broken into master

What does this MR do and why?

index_security_ci_builds_on_name_and_id_parser_features index is marked as broken on gitlab.com database. This index supported few heavy metrics that are included in weekly Service Ping reporting. With broken index reporting process performance has decreased dramatically from ~8 hours to over 48 hours after which it was canceled

Broken index was recently changed by !70245 (diffs) during id column type change.

Following https://www.postgresql.org/docs/12/sql-createindex.html

If a problem arises while scanning the table, such as a uniqueness violation in a unique index, the CREATE INDEX command will fail but leave behind an "invalid" index. This index will be ignored for querying purposes because it might be incomplete; however it will still consume update overhead. The psql \d command will report such an index as INVALID:

...

The recommended recovery method in such cases is to drop the index and try again to perform CREATE INDEX CONCURRENTLY. (Another possibility is to rebuild the index with REINDEX INDEX CONCURRENTLY).

This MR it is final step in 3 step migration (step 1 and step 2) process. It removes redundant and possibly invalid index_security_ci_builds_on_name_and_id_parser_features_broken index

Relates: #343455 (closed)

Screenshots or screen recordings

These are strongly recommended to assist reviewers and reduce the time to merge your change.

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Tiger Watson

Merge request reports