Skip to content

Disable fastupdate on issues and merge_requests GIN indexes

Mario Celi requested to merge 378343-disable-fastupdate-gin-indexes into master

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.

Edited by Mario Celi

Merge request reports