Disable fastupdate on issues and merge_requests GIN indexes
What does this MR do and why?
Disabled fastupdate on the following GIN indexes:
- index_issues_on_description_trigram
- index_issues_on_title_trigram
- index_merge_requests_on_description_trigram
- index_merge_requests_on_title_trigram
On large tables fastupdate will accumulate gin index updates and cause timeouts when the changes are applied. Disabling fastupdate will cause more predicatable query times
Related to #378343 (closed)
More context on gitlab-com/gl-infra/production#7840 (closed). This was already manually applied in some environments like production. I confirmed that executing the command even if the config value was already updated will not make the migration fail.
Migration output
UP
bin/rails db:migrate
main: == 20221019194751 DisableFastupdateOnIssuesTitleGinIndex: migrating ===========
main: -- transaction_open?()
main: -> 0.0000s
main: -- execute("ALTER INDEX index_issues_on_title_trigram SET ( fastupdate = false ) ;\n")
main: -> 0.0007s
main: == 20221019194751 DisableFastupdateOnIssuesTitleGinIndex: migrated (0.0125s) ==
main: == 20221019195754 DisableFastupdateOnIssuesDescriptionGinIndex: migrating =====
main: -- transaction_open?()
main: -> 0.0000s
main: -- execute("ALTER INDEX index_issues_on_description_trigram SET ( fastupdate = false ) ;\n")
main: -> 0.0007s
main: == 20221019195754 DisableFastupdateOnIssuesDescriptionGinIndex: migrated (0.0029s)
main: == 20221019200033 DisableFastupdateOnMergeRequestsTitleGinIndex: migrating ====
main: -- transaction_open?()
main: -> 0.0000s
main: -- execute("ALTER INDEX index_merge_requests_on_title_trigram SET ( fastupdate = false ) ;\n")
main: -> 0.0028s
main: == 20221019200033 DisableFastupdateOnMergeRequestsTitleGinIndex: migrated (0.0050s)
main: == 20221019200206 DisableFastupdateOnMergeRequestsDescriptionGinIndex: migrating
main: -- transaction_open?()
main: -> 0.0000s
main: -- execute("ALTER INDEX index_merge_requests_on_description_trigram SET ( fastupdate = false ) ;\n")
main: -> 0.0007s
main: == 20221019200206 DisableFastupdateOnMergeRequestsDescriptionGinIndex: migrated (0.0024s)
ci: == 20221019194751 DisableFastupdateOnIssuesTitleGinIndex: migrating ===========
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- execute("ALTER INDEX index_issues_on_title_trigram SET ( fastupdate = false ) ;\n")
ci: -> 0.0008s
ci: == 20221019194751 DisableFastupdateOnIssuesTitleGinIndex: migrated (0.0035s) ==
ci: == 20221019195754 DisableFastupdateOnIssuesDescriptionGinIndex: migrating =====
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- execute("ALTER INDEX index_issues_on_description_trigram SET ( fastupdate = false ) ;\n")
ci: -> 0.0004s
ci: == 20221019195754 DisableFastupdateOnIssuesDescriptionGinIndex: migrated (0.0021s)
ci: == 20221019200033 DisableFastupdateOnMergeRequestsTitleGinIndex: migrating ====
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- execute("ALTER INDEX index_merge_requests_on_title_trigram SET ( fastupdate = false ) ;\n")
ci: -> 0.0014s
ci: == 20221019200033 DisableFastupdateOnMergeRequestsTitleGinIndex: migrated (0.0043s)
ci: == 20221019200206 DisableFastupdateOnMergeRequestsDescriptionGinIndex: migrating
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- execute("ALTER INDEX index_merge_requests_on_description_trigram SET ( fastupdate = false ) ;\n")
ci: -> 0.0022s
ci: == 20221019200206 DisableFastupdateOnMergeRequestsDescriptionGinIndex: migrated (0.0046s)
DOWN
bin/rails db:rollback:main db:rollback:ci STEP=4
main: == 20221019200206 DisableFastupdateOnMergeRequestsDescriptionGinIndex: reverting
main: -- transaction_open?()
main: -> 0.0000s
main: -- execute("ALTER INDEX index_merge_requests_on_description_trigram RESET ( fastupdate ) ;\n")
main: -> 0.0009s
main: == 20221019200206 DisableFastupdateOnMergeRequestsDescriptionGinIndex: reverted (0.0141s)
main: == 20221019200033 DisableFastupdateOnMergeRequestsTitleGinIndex: reverting ====
main: -- transaction_open?()
main: -> 0.0000s
main: -- execute("ALTER INDEX index_merge_requests_on_title_trigram RESET ( fastupdate ) ;\n")
main: -> 0.0006s
main: == 20221019200033 DisableFastupdateOnMergeRequestsTitleGinIndex: reverted (0.0029s)
main: == 20221019195754 DisableFastupdateOnIssuesDescriptionGinIndex: reverting =====
main: -- transaction_open?()
main: -> 0.0000s
main: -- execute("ALTER INDEX index_issues_on_description_trigram RESET ( fastupdate ) ;\n")
main: -> 0.0006s
main: == 20221019195754 DisableFastupdateOnIssuesDescriptionGinIndex: reverted (0.0032s)
main: == 20221019194751 DisableFastupdateOnIssuesTitleGinIndex: reverting ===========
main: -- transaction_open?()
main: -> 0.0000s
main: -- execute("ALTER INDEX index_issues_on_title_trigram RESET ( fastupdate ) ;\n")
main: -> 0.0007s
main: == 20221019194751 DisableFastupdateOnIssuesTitleGinIndex: reverted (0.0036s) ==
ci: == 20221019200206 DisableFastupdateOnMergeRequestsDescriptionGinIndex: reverting
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- execute("ALTER INDEX index_merge_requests_on_description_trigram RESET ( fastupdate ) ;\n")
ci: -> 0.0017s
ci: == 20221019200206 DisableFastupdateOnMergeRequestsDescriptionGinIndex: reverted (0.0073s)
ci: == 20221019200033 DisableFastupdateOnMergeRequestsTitleGinIndex: reverting ====
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- execute("ALTER INDEX index_merge_requests_on_title_trigram RESET ( fastupdate ) ;\n")
ci: -> 0.0009s
ci: == 20221019200033 DisableFastupdateOnMergeRequestsTitleGinIndex: reverted (0.0051s)
ci: == 20221019195754 DisableFastupdateOnIssuesDescriptionGinIndex: reverting =====
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- execute("ALTER INDEX index_issues_on_description_trigram RESET ( fastupdate ) ;\n")
ci: -> 0.0010s
ci: == 20221019195754 DisableFastupdateOnIssuesDescriptionGinIndex: reverted (0.0052s)
ci: == 20221019194751 DisableFastupdateOnIssuesTitleGinIndex: reverting ===========
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- execute("ALTER INDEX index_issues_on_title_trigram RESET ( fastupdate ) ;\n")
ci: -> 0.0010s
ci: == 20221019194751 DisableFastupdateOnIssuesTitleGinIndex: reverted (0.0045s) ==
MR acceptance checklist
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.
-
I have evaluated the MR acceptance checklist for this MR.
Edited by Mario Celi