Add unique index ot `ci_triggers.token`
What does this MR do and why?
With @OmarQunsulGitlab we accidentally found quite high rate of sequential scans in the ci_triggers
table on Grafana: https://dashboards.gitlab.net/d/000000167/postgresql-tuple-statistics?orgId=1&from=1687151659746&to=1687151985556&var-env=gprd&var-instance=patroni-ci-2004-101-db-gprd.c.gitlab-production.internal&var-db=gitlabhq_production&var-top_dead_tup=All
It seems like there is no unique index on the token
table which means that the uniqueness check will do a full-table scan. Additionally, we have a finder call here: https://gitlab.com/gitlab-org/gitlab/-/blob/master/app/services/ci/pipeline_trigger_service.rb
DB
Query:
- Before: https://postgres.ai/console/gitlab/gitlab-production-ci/sessions/19713/commands/64562
- After: https://console.postgres.ai/gitlab/gitlab-production-ci/sessions/19713/commands/64564
The maximum length I see on PRD is 46 chars and I don't see duplicated values, adding the index should be relatively safe.
Up:
main: == 20230619081412 AddIndexToCiTriggersToken: migrating ========================
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0483s
main: -- index_exists?(:ci_triggers, :token, {:unique=>true, :name=>"index_ci_triggers_on_token", :algorithm=>:concurrently})
main: -> 0.0042s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0004s
main: -- add_index(:ci_triggers, :token, {:unique=>true, :name=>"index_ci_triggers_on_token", :algorithm=>:concurrently})
main: -> 0.0025s
main: -- execute("RESET statement_timeout")
main: -> 0.0004s
main: == 20230619081412 AddIndexToCiTriggersToken: migrated (0.0719s) ===============
ci: == 20230619081412 AddIndexToCiTriggersToken: migrating ========================
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0008s
ci: -- index_exists?(:ci_triggers, :token, {:unique=>true, :name=>"index_ci_triggers_on_token", :algorithm=>:concurrently})
ci: -> 0.0042s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0004s
ci: -- add_index(:ci_triggers, :token, {:unique=>true, :name=>"index_ci_triggers_on_token", :algorithm=>:concurrently})
ci: -> 0.0024s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0003s
ci: == 20230619081412 AddIndexToCiTriggersToken: migrated (0.0256s) ===============
Down:
main: == 20230619081412 AddIndexToCiTriggersToken: reverting ========================
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0553s
main: -- indexes(:ci_triggers)
main: -> 0.0057s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0003s
main: -- remove_index(:ci_triggers, {:algorithm=>:concurrently, :name=>"index_ci_triggers_on_token"})
main: -> 0.0014s
main: -- execute("RESET statement_timeout")
main: -> 0.0004s
main: == 20230619081412 AddIndexToCiTriggersToken: reverted (0.0914s) ===============
ci: == 20230619081412 AddIndexToCiTriggersToken: reverting ========================
ci: -- transaction_open?()
ci: -> 0.0000s
ci: -- view_exists?(:postgres_partitions)
ci: -> 0.0471s
ci: -- indexes(:ci_triggers)
ci: -> 0.0042s
ci: -- execute("SET statement_timeout TO 0")
ci: -> 0.0003s
ci: -- remove_index(:ci_triggers, {:algorithm=>:concurrently, :name=>"index_ci_triggers_on_token"})
ci: -> 0.0011s
ci: -- execute("RESET statement_timeout")
ci: -> 0.0004s
ci: == 20230619081412 AddIndexToCiTriggersToken: reverted (0.0727s) ===============
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.