Add indexes to ci_builds_metadata
Ref: #373239 (closed)
What does this MR do and why?
Following up !97924 (merged) where we introduced 2 new indexes during low traffic this weekend.
This will be no-op
operation gitlab.com
.
This MR adds a migration which will create our indexes for users outside of gitlab.com
as explained in our documentation.
Why are we doing this?
Those indexes are required in order to partition ci_builds_metadata
as the primary key and all unique constraints should include the partition key in order to be able to partition our table.
Database queries
✅ Verification that our indexes 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_id_partition_id_unique" UNIQUE, btree (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