Drop index on ci_builds_metadata
Ref: #373239 (closed)
What does this MR do and why?
Following up !98429 (merged) where we scheduled the removal of 1 unique index during low traffic this weekend.
This will be no-op
operation gitlab.com
.
This MR adds a migration which will remove this index for users outside of gitlab.com
as explained in our documentation.
Why are we doing this?
This index must be removed in order to partition ci_builds_metadata
as the all unique constraints should include the partition key in order to be able to partition our table.
Database queries
✅ Verification that our index no longer exist on production
Table "public.ci_builds_metadata"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
---------------------------+------------------------+-----------+----------+------------------------------------------------+----------+--------------+-------------
project_id | integer | | not null | | plain | |
timeout | integer | | | | plain | |
timeout_source | integer | | not null | 1 | plain | |
config_options | jsonb | | | | extended | |
config_variables | jsonb | | | | extended | |
interruptible | boolean | | | | plain | |
has_exposed_artifacts | boolean | | | | plain | |
environment_auto_stop_in | character varying(255) | | | | extended | |
expanded_environment_name | character varying(255) | | | | extended | |
secrets | jsonb | | not null | '{}'::jsonb | extended | |
build_id | bigint | | not null | | plain | |
id | bigint | | not null | nextval('ci_builds_metadata_id_seq'::regclass) | plain | |
runtime_runner_features | jsonb | | not null | '{}'::jsonb | extended | |
id_tokens | jsonb | | not null | '{}'::jsonb | extended | |
partition_id | bigint | | not null | 100 | plain | |
Indexes:
"ci_builds_metadata_pkey" PRIMARY KEY, btree (id)
- "index_ci_builds_metadata_on_build_id" UNIQUE, btree (build_id)
"index_ci_builds_metadata_on_build_id_partition_id_unique" UNIQUE, btree (build_id, partition_id)
"index_ci_builds_metadata_on_build_id_and_has_exposed_artifacts" btree (build_id) WHERE has_exposed_artifacts IS TRUE
"index_ci_builds_metadata_on_build_id_and_id_and_interruptible" btree (build_id) INCLUDE (id) WHERE interruptible = true
"index_ci_builds_metadata_on_project_id" btree (project_id)
Foreign-key constraints:
"fk_e20479742e" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
Access method: heap
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.
Edited by Max Orefice