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)
Edited by Pam Artiaga