Add temporary index to deployments to facilitate adding actors to a FF in bulk
This index will facilitate bulk inserting FF actors during a Change Request. See: gitlab-com/gl-infra/production#6981 (closed)
Index creation time was 1.2 min: https://gitlab.slack.com/archives/CLJMDRD8C/p1651717694827759
Index size is 40 Mb: https://gitlab.slack.com/archives/CLJMDRD8C/p1651717851140159
Index name
I know we should use the where
in the name to make it more explicit, but since there are so many things on this index, its index name would get too big. So I didn't add the where
.
Although, please not that this is a temporary index, so it should anyway not collide if other index names, and it will be removed soon.
Queries which will benefit from this index
This queries are a bit slow, but they should be executed as part of a one-off script:
- https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/9954/commands/35218#visualize-depesz
- https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/9954/commands/35219#visualize-depesz
Migration check
λ gitlab git:(jcunha-add-tp-index-for-ff-cr) ✗ be rake db:rollback VERSION="20220505022001"
== 20220505022001 AddIndexToDeploymentsOnCreatedAtClusterIdAndProjectId: reverting
-- transaction_open?()
-> 0.0000s
-- indexes(:deployments)
-> 0.0226s
-- execute("SET statement_timeout TO 0")
-> 0.0010s
-- remove_index(:deployments, {:algorithm=>:concurrently, :name=>"tp_index_created_at_cluster_id_project_id_on_deployments"})
-> 0.0048s
-- execute("RESET statement_timeout")
-> 0.0007s
== 20220505022001 AddIndexToDeploymentsOnCreatedAtClusterIdAndProjectId: reverted (0.0402s)
λ gitlab git:(jcunha-add-tp-index-for-ff-cr) ✗ bundle exec rake db:migrate RAILS_ENV=development
== 20220505022001 AddIndexToDeploymentsOnCreatedAtClusterIdAndProjectId: migrating
-- transaction_open?()
-> 0.0000s
-- index_exists?(:deployments, [:created_at, :cluster_id, :project_id], {:name=>"tp_index_created_at_cluster_id_project_id_on_deployments", :where=>"cluster_id is not null and created_at > '2022-04-03 00:00:00'", :algorithm=>:concurrently})
-> 0.0242s
-- execute("SET statement_timeout TO 0")
-> 0.0008s
-- add_index(:deployments, [:created_at, :cluster_id, :project_id], {:name=>"tp_index_created_at_cluster_id_project_id_on_deployments", :where=>"cluster_id is not null and created_at > '2022-04-03 00:00:00'", :algorithm=>:concurrently})
-> 0.0057s
-- execute("RESET statement_timeout")
-> 0.0025s
== 20220505022001 AddIndexToDeploymentsOnCreatedAtClusterIdAndProjectId: migrated (0.0457s)
Edited by João Alexandre Cunha