Drop unused ci builds index

Ref: #271620 (closed)

What does this MR do and why?

This MR drops our index_ci_builds_on_project_id_for_successfull_pages_deploy which does not seem to be used according to Thanos.

Screenshots or screen recordings

Database

List of relations  
 Schema |                            Name                            | Type  |  Owner  |   Table   |  Size  | Description   
--------+------------------------------------------------------------+-------+---------+-----------+--------+-------------  
 public | index_ci_builds_on_project_id_for_successfull_pages_deploy | index | joe_292 | ci_builds | 827 MB |   
(1 row)  

Migrations up

➜  gitlab git:(master) ✗ rails db:migrate:main
ci: == 20220630085003 DropProjectSuccessfullPagesDeployIndexFromCiBuilds: migrating
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- indexes(:ci_builds)
ci:    -> 0.0091s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0001s
ci: -- remove_index(:ci_builds, {:algorithm=>:concurrently, :name=>"index_ci_builds_on_project_id_for_successfull_pages_deploy"})
ci:    -> 0.0021s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: == 20220630085003 DropProjectSuccessfullPagesDeployIndexFromCiBuilds: migrated (0.0159s)

➜  gitlab git:(master) ✗ rails db:migrate:main
main: == 20220630085003 DropProjectSuccessfullPagesDeployIndexFromCiBuilds: migrating
main: -- transaction_open?()
main:    -> 0.0000s
main: -- indexes(:ci_builds)
main:    -> 0.0094s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0002s
main: -- remove_index(:ci_builds, {:algorithm=>:concurrently, :name=>"index_ci_builds_on_project_id_for_successfull_pages_deploy"})
main:    -> 0.0042s
main: -- execute("RESET statement_timeout")
main:    -> 0.0003s
main: == 20220630085003 DropProjectSuccessfullPagesDeployIndexFromCiBuilds: migrated (0.0200s)

Migrations down

➜  gitlab git:(master) ✗ rails db:rollback:ci
ci: == 20220630085003 DropProjectSuccessfullPagesDeployIndexFromCiBuilds: reverting
ci: -- transaction_open?()
ci:    -> 0.0000s
ci: -- index_exists?(:ci_builds, :project_id, {:where=>"(((type)::text = 'GenericCommitStatus'::text) AND ((stage)::text = 'deploy'::text) AND ((name)::text = 'pages:deploy'::text) AND ((status)::text = 'success'::text))", :name=>"index_ci_builds_on_project_id_for_successfull_pages_deploy", :algorithm=>:concurrently})
ci:    -> 0.0084s
ci: -- execute("SET statement_timeout TO 0")
ci:    -> 0.0002s
ci: -- add_index(:ci_builds, :project_id, {:where=>"(((type)::text = 'GenericCommitStatus'::text) AND ((stage)::text = 'deploy'::text) AND ((name)::text = 'pages:deploy'::text) AND ((status)::text = 'success'::text))", :name=>"index_ci_builds_on_project_id_for_successfull_pages_deploy", :algorithm=>:concurrently})
ci:    -> 0.0033s
ci: -- execute("RESET statement_timeout")
ci:    -> 0.0002s
ci: == 20220630085003 DropProjectSuccessfullPagesDeployIndexFromCiBuilds: reverted (0.0163s)

➜  gitlab git:(master) ✗ rails db:rollback:main
main: == 20220630085003 DropProjectSuccessfullPagesDeployIndexFromCiBuilds: reverting
main: -- transaction_open?()
main:    -> 0.0000s
main: -- index_exists?(:ci_builds, :project_id, {:where=>"(((type)::text = 'GenericCommitStatus'::text) AND ((stage)::text = 'deploy'::text) AND ((name)::text = 'pages:deploy'::text) AND ((status)::text = 'success'::text))", :name=>"index_ci_builds_on_project_id_for_successfull_pages_deploy", :algorithm=>:concurrently})
main:    -> 0.0083s
main: -- execute("SET statement_timeout TO 0")
main:    -> 0.0001s
main: -- add_index(:ci_builds, :project_id, {:where=>"(((type)::text = 'GenericCommitStatus'::text) AND ((stage)::text = 'deploy'::text) AND ((name)::text = 'pages:deploy'::text) AND ((status)::text = 'success'::text))", :name=>"index_ci_builds_on_project_id_for_successfull_pages_deploy", :algorithm=>:concurrently})
main:    -> 0.0028s
main: -- execute("RESET statement_timeout")
main:    -> 0.0001s
main: == 20220630085003 DropProjectSuccessfullPagesDeployIndexFromCiBuilds: reverted (0.0151s)

Impact

Reduced ci_builds size by 0.8GB

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.

db/post_migrate/20220630085003_drop_project_successfull_pages_deploy_index_from_ci_builds.rb

+# frozen_string_literal: true
+
+class DropProjectSuccessfullPagesDeployIndexFromCiBuilds < Gitlab::Database::Migration[2.0]
+  disable_ddl_transaction!
+
+  INDEX_NAME = 'index_ci_builds_on_project_id_for_successfull_pages_deploy'
+
+  def up
+    remove_concurrent_index_by_name :ci_builds, INDEX_NAME
+  end
+
+  # rubocop:disable Migration/PreventIndexCreation
+  def down
+    add_concurrent_index :ci_builds,
+      :project_id,
+      where: "(((type)::text = 'GenericCommitStatus'::text) AND ((stage)::text = 'deploy'::text) AND " \
+             "((name)::text = 'pages:deploy'::text) AND ((status)::text = 'success'::text))",
+      name: INDEX_NAME
+  end
+  # rubocop:enable Migration/PreventIndexCreation
+end