Skip to content

Drop index_notes_on_note_gin_trigram index where exists

Mario Celi requested to merge mcelicalderong-drop-dev-staging-notes-note-gin-index into master

What does this MR do and why?

This migration removes the index_notes_on_note_gin_trigram index from environments where it exists. !71982 (diffs) removed the index from production, but for some reason the index had a slightly different name in staging and dev environments. Currently this index is causing query timeouts in staging as we know cleaning the gin pending list can cause this like it does in other tables such as issues

Related discussion in https://gitlab.slack.com/archives/C8HG8D9MY/p1665755624748799 (internal only)

Migration output

UP

bin/rails db:migrate
main: == 20221014190040 DropNotesNoteTrigramIndex: migrating ========================
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:notes)
main:    -> 0.0124s
main: -- current_schema()
main:    -> 0.0012s
main: == 20221014190040 DropNotesNoteTrigramIndex: migrated (0.0173s) ===============

ci: == 20221014190040 DropNotesNoteTrigramIndex: migrating ========================
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- indexes(:notes)
ci:    -> 0.0102s
ci: -- current_schema()
ci:    -> 0.0003s
ci: == 20221014190040 DropNotesNoteTrigramIndex: migrated (0.0147s) ===============

DOWN

bin/rails db:rollback:ci && bin/rails:rollback:main
ci: == 20221014190040 DropNotesNoteTrigramIndex: reverting ========================
ci: == 20221014190040 DropNotesNoteTrigramIndex: reverted (0.0007s) ===============

main: == 20221014190040 DropNotesNoteTrigramIndex: reverting ========================
main: == 20221014190040 DropNotesNoteTrigramIndex: reverted (0.0005s) ===============

Index list in staging

\d notes

Indexes:
    "notes_pkey" PRIMARY KEY, btree (id)
    "index_notes_for_cherry_picked_merge_requests" btree (project_id, commit_id) WHERE noteable_type::text = 'MergeRequest'::text
    "index_notes_on_author_id_and_created_at_and_id" btree (author_id, created_at, id)
    "index_notes_on_commit_id" btree (commit_id)
    "index_notes_on_created_at" btree (created_at)
    "index_notes_on_discussion_id" btree (discussion_id)
    "index_notes_on_id_where_confidential" btree (id) WHERE confidential = true
    "index_notes_on_id_where_internal" btree (id) WHERE internal = true
    "index_notes_on_line_code" btree (line_code)
    "index_notes_on_note_gin_trigram" gin (note gin_trgm_ops)
    "index_notes_on_noteable_id_and_noteable_type_and_system" btree (noteable_id, noteable_type, system)
    "index_notes_on_project_id_and_id_and_system_false" btree (project_id, id) WHERE NOT system
    "index_notes_on_project_id_and_noteable_type" btree (project_id, noteable_type)
    "index_notes_on_review_id" btree (review_id)
    "note_mentions_temp_index" btree (id, noteable_type) WHERE note ~~ '%@%'::text
    "snippet_mentions_temp_index" btree (id) WHERE note ~~ '%@%'::text AND noteable_type::text = 'Snippet'::text
    "test" gin (note gin_trgm_ops)

From the output we can see that we also have another test gin index we have to drop, but this only exists in staging, so I'm going to follow https://about.gitlab.com/handbook/engineering/infrastructure/change-management/ in order to manually drop that one in staging.

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 Mario Celi

Merge request reports