Skip to content

Add unique index ot `ci_triggers.token`

Adam Hegyi requested to merge ah-add-index-to-ci-triggers-token into master

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:

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.

Edited by Adam Hegyi

Merge request reports