Add temporary index for pipeline_artifacts_size
What does this MR do and why?
So that we can efficiently query project_statistics#pipeline_artifacts_size
during a backfill,
this MR adds a temporary index which can be removed once the related backfill has completed
Refs !126053 (merged) and https://gitlab.com/gitlab-org/gitlab/-/issues/412634
Database info
db:migrate
bin/rails db:migrate
main: == [advisory_lock_connection] object_id: 223980, pg_backend_pid: 59489
main: == 20230718145613 AddTempIndexForProjectStatisticsPipelineArtifactsSizeMigration: migrating
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0821s
main: -- index_exists?(:project_statistics, [:project_id], {:name=>"tmp_index_project_statistics_pipeline_artifacts_size", :where=>"pipeline_artifacts_size <> 0", :algorithm=>:concurrently})
main: -> 0.0041s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0002s
main: -- add_index(:project_statistics, [:project_id], {:name=>"tmp_index_project_statistics_pipeline_artifacts_size", :where=>"pipeline_artifacts_size <> 0", :algorithm=>:concurrently})
main: -> 0.0042s
main: -- execute("RESET statement_timeout")
main: -> 0.0003s
main: == 20230718145613 AddTempIndexForProjectStatisticsPipelineArtifactsSizeMigration: migrated (0.1055s)
main: == [advisory_lock_connection] object_id: 223980, pg_backend_pid: 59489
db:rollback
bin/rails db:rollback:main
main: == [advisory_lock_connection] object_id: 223460, pg_backend_pid: 60089
main: == 20230718145613 AddTempIndexForProjectStatisticsPipelineArtifactsSizeMigration: reverting
main: -- transaction_open?()
main: -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main: -> 0.0845s
main: -- indexes(:project_statistics)
main: -> 0.0039s
main: -- execute("SET statement_timeout TO 0")
main: -> 0.0001s
main: -- remove_index(:project_statistics, {:algorithm=>:concurrently, :name=>"tmp_index_project_statistics_pipeline_artifacts_size"})
main: -> 0.0014s
main: -- execute("RESET statement_timeout")
main: -> 0.0002s
main: == 20230718145613 AddTempIndexForProjectStatisticsPipelineArtifactsSizeMigration: reverted (0.1019s)
Cold query before migration (4 mins): https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/20495/commands/67128
Warm query before migration (3 secs): https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/20495/commands/67129
Warm query after migration (323ms): https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/20495/commands/67131
Screenshots or screen recordings
Screenshots are required for UI changes, and strongly recommended for all other merge requests.
Before | After |
---|---|
How to set up and validate locally
Numbered steps to set up and validate the change are strongly suggested.
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.