Drop index_deployments_on_project_id_and_ref concurrently

What does this MR do and why?

This is part of an epic to optimize the deployments table by dropping unused indexes: &10185

MR to drop the index in production asynchronously: !133057 (merged)

Issue: #402511 (closed)

Screenshots or screen recordings

Migration output

main: == [advisory_lock_connection] object_id: 227020, pg_backend_pid: 6709
main: == 20231010011850 DropIndexDeploymentsOnProjectIdAndRef: migrating ============
main: -- transaction_open?(nil)
main:    -> 0.0000s
main: -- view_exists?(:postgres_partitions)
main:    -> 0.0938s
main: -- indexes(:deployments)
main:    -> 0.0059s
main: -- current_schema(nil)
main:    -> 0.0001s
main: == 20231010011850 DropIndexDeploymentsOnProjectIdAndRef: migrated (0.1073s) ===

main: == [advisory_lock_connection] object_id: 227020, pg_backend_pid: 6709

Check index existence on production DB

Running \d index_deployments_on_project_id_and_ref on the Postgres console doesn't give us an answer:

But when running \d deployments, the index_deployments_on_project_id_and_ref is no longer in the deployments table definition:

deployments table definition in `gitlab-production-main`

Table "public.deployments"
     Column      |            Type             | Collation | Nullable |                 Default                 
-----------------+-----------------------------+-----------+----------+-----------------------------------------
 id              | integer                     |           | not null | nextval('deployments_id_seq'::regclass)
 iid             | integer                     |           | not null | 
 project_id      | integer                     |           | not null | 
 environment_id  | integer                     |           | not null | 
 ref             | character varying           |           | not null | 
 tag             | boolean                     |           | not null | 
 sha             | character varying           |           | not null | 
 user_id         | integer                     |           |          | 
 deployable_type | character varying           |           |          | 
 created_at      | timestamp without time zone |           |          | 
 updated_at      | timestamp without time zone |           |          | 
 on_stop         | character varying           |           |          | 
 finished_at     | timestamp with time zone    |           |          | 
 status          | smallint                    |           | not null | 
 cluster_id      | integer                     |           |          | 
 deployable_id   | bigint                      |           |          | 
 archived        | boolean                     |           | not null | false
Indexes:
    "deployments_pkey" PRIMARY KEY, btree (id)
    "index_deployments_for_visible_scope" btree (environment_id, finished_at DESC) WHERE status = ANY (ARRAY[1, 2, 3, 4, 6])
    "index_deployments_on_archived_project_id_iid" btree (archived, project_id, iid)
    "index_deployments_on_cluster_id_and_status" btree (cluster_id, status)
    "index_deployments_on_created_at" btree (created_at)
    "index_deployments_on_deployable_type_and_deployable_id" btree (deployable_type, deployable_id)
    "index_deployments_on_environment_id_and_id" btree (environment_id, id)
    "index_deployments_on_environment_id_and_ref" btree (environment_id, ref)
    "index_deployments_on_environment_id_status_and_finished_at" btree (environment_id, status, finished_at)
    "index_deployments_on_environment_id_status_and_id" btree (environment_id, status, id)
    "index_deployments_on_environment_status_sha" btree (environment_id, status, sha)
    "index_deployments_on_id_and_status_and_created_at" btree (id, status, created_at)
    "index_deployments_on_project_and_environment_and_updated_at_id" btree (project_id, environment_id, updated_at, id)
    "index_deployments_on_project_and_finished" btree (project_id, finished_at) WHERE status = 2
    "index_deployments_on_project_id_and_id" btree (project_id, id DESC)
    "index_deployments_on_project_id_and_iid" UNIQUE, btree (project_id, iid)
    "index_deployments_on_project_id_and_status_and_created_at" btree (project_id, status, created_at)
    "index_deployments_on_project_id_and_updated_at_and_id" btree (project_id, updated_at DESC, id DESC)
    "index_deployments_on_user_id_and_status_and_created_at" btree (user_id, status, created_at)
    "partial_index_deployments_for_legacy_successful_deployments" btree (id) WHERE finished_at IS NULL AND status = 2
Foreign-key constraints:
    "fk_009fd21147" FOREIGN KEY (environment_id) REFERENCES environments(id) ON DELETE CASCADE
    "fk_b9a3851b82" FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
Referenced by:
    TABLE "deployment_approvals" CONSTRAINT "fk_2d060dfc73" FOREIGN KEY (deployment_id) REFERENCES deployments(id) ON DELETE CASCADE
    TABLE "deployment_clusters" CONSTRAINT "fk_rails_6359a164df" FOREIGN KEY (deployment_id) REFERENCES deployments(id) ON DELETE CASCADE
    TABLE "deployment_merge_requests" CONSTRAINT "fk_rails_dcbce9f4df" FOREIGN KEY (deployment_id) REFERENCES deployments(id) ON DELETE CASCADE

How to set up and validate locally

N/A

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.

Related to #402511 (closed)