Add unique index to ci_builds
Ref: #382033 (closed)
What does this MR do and why?
Following up !106128 (merged) where we introduced new indexes during low traffic this weekend.
This will be no-op operation gitlab.com.
This MR adds a migration which will create an index 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 as the primary key must 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"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
-------------------------+-----------------------------+-----------+----------+---------------------------------------+----------+--------------+-------------
status | character varying(255) | | | | extended | |
finished_at | timestamp without time zone | | | | plain | |
created_at | timestamp without time zone | | | | plain | |
updated_at | timestamp without time zone | | | | plain | |
started_at | timestamp without time zone | | | | plain | |
runner_id | integer | | | | plain | |
coverage | double precision | | | | plain | |
commit_id | integer | | | | plain | |
name | character varying(255) | | | | extended | |
options | text | | | | extended | |
allow_failure | boolean | | not null | false | plain | |
stage | character varying(255) | | | | extended | |
trigger_request_id | integer | | | | plain | |
stage_idx | integer | | | | plain | |
tag | boolean | | | | plain | |
ref | character varying(255) | | | | extended | |
user_id | integer | | | | plain | |
type | character varying(255) | | | | extended | |
target_url | character varying(255) | | | | extended | |
description | character varying(255) | | | | extended | |
project_id | integer | | | | plain | |
erased_by_id | integer | | | | plain | |
erased_at | timestamp without time zone | | | | plain | |
artifacts_expire_at | timestamp without time zone | | | | plain | |
environment | character varying | | | | extended | |
when | character varying | | | | extended | |
yaml_variables | text | | | | extended | |
queued_at | timestamp without time zone | | | | plain | |
lock_version | integer | | | 0 | plain | |
coverage_regex | character varying | | | | extended | |
auto_canceled_by_id | integer | | | | plain | |
retried | boolean | | | | plain | |
protected | boolean | | | | plain | |
failure_reason | integer | | | | plain | |
scheduled_at | timestamp with time zone | | | | plain | |
token_encrypted | character varying | | | | extended | |
upstream_pipeline_id | integer | | | | plain | |
resource_group_id | bigint | | | | plain | |
waiting_for_resource_at | timestamp with time zone | | | | plain | |
processed | boolean | | | | plain | |
scheduling_type | smallint | | | | plain | |
id | bigint | | not null | nextval('ci_builds_id_seq'::regclass) | plain | |
stage_id | bigint | | | | plain | |
partition_id | bigint | | not null | 100 | plain | |
Indexes:
"ci_builds_pkey" PRIMARY KEY, btree (id)
+ "index_ci_builds_on_id_partition_id_unique" UNIQUE, btree (id, partition_id)
"index_ci_builds_on_token_encrypted" UNIQUE, btree (token_encrypted) WHERE token_encrypted IS NOT NULL
"index_ci_builds_on_token_encrypted_partition_id_unique" UNIQUE, btree (token_encrypted, partition_id) WHERE token_encrypted IS NOT NULL INVALID
"ci_builds_gitlab_monitor_metrics" btree (status, created_at, project_id) WHERE type::text = 'Ci::Build'::text
"index_ci_builds_on_auto_canceled_by_id" btree (auto_canceled_by_id)
"index_ci_builds_on_commit_id_and_stage_idx_and_created_at" btree (commit_id, stage_idx, created_at)
"index_ci_builds_on_commit_id_and_status_and_type" btree (commit_id, status, type)
"index_ci_builds_on_commit_id_and_type_and_name_and_ref" btree (commit_id, type, name, ref)
"index_ci_builds_on_commit_id_and_type_and_ref" btree (commit_id, type, ref)
"index_ci_builds_on_commit_id_artifacts_expired_at_and_id" btree (commit_id, artifacts_expire_at, id) WHERE type::text = 'Ci::Build'::text AND (retried = false OR retried IS NULL) AND (name::text = ANY (ARRAY['sast'::character varying::text, 'secret_detection'::character varying::text, 'dependency_scanning'::character varying::text, 'container_scanning'::character varying::text, 'dast'::character varying::text]))
"index_ci_builds_on_project_id_and_id" btree (project_id, id)
"index_ci_builds_on_project_id_and_name_and_ref" btree (project_id, name, ref) WHERE type::text = 'Ci::Build'::text AND status::text = 'success'::text AND (retried = false OR retried IS NULL)
"index_ci_builds_on_resource_group_and_status_and_commit_id" btree (resource_group_id, status, commit_id) WHERE resource_group_id IS NOT NULL
"index_ci_builds_on_runner_id_and_id_desc" btree (runner_id, id DESC)
"index_ci_builds_on_stage_id" btree (stage_id)
"index_ci_builds_on_status_and_type_and_runner_id" btree (status, type, runner_id)
"index_ci_builds_on_updated_at" btree (updated_at)
"index_ci_builds_on_upstream_pipeline_id" btree (upstream_pipeline_id) WHERE upstream_pipeline_id IS NOT NULL
"index_ci_builds_on_user_id" btree (user_id)
"index_ci_builds_on_user_id_and_created_at_and_type_eq_ci_build" btree (user_id, created_at) WHERE type::text = 'Ci::Build'::text
"index_ci_builds_project_id_and_status_for_live_jobs_partial2" btree (project_id, status) WHERE type::text = 'Ci::Build'::text AND (status::text = ANY (ARRAY['running'::character varying::text, 'pending'::character varying::text, 'created'::character varying::text]))
"index_ci_builds_runner_id_running" btree (runner_id) WHERE status::text = 'running'::text AND type::text = 'Ci::Build'::text
"index_partial_ci_builds_on_user_id_name_parser_features" btree (user_id, name) WHERE type::text = 'Ci::Build'::text AND (name::text = ANY (ARRAY['container_scanning'::character varying::text, 'dast'::character varying::text, 'dependency_scanning'::character varying::text, 'license_management'::character varying::text, 'license_scanning'::character varying::text, 'sast'::character varying::text, 'coverage_fuzzing'::character varying::text, 'secret_detection'::character varying::text]))
"index_secure_ci_builds_on_user_id_name_created_at" btree (user_id, name, created_at) WHERE type::text = 'Ci::Build'::text AND (name::text = ANY (ARRAY['container_scanning'::character varying::text, 'dast'::character varying::text, 'dependency_scanning'::character varying::text, 'license_management'::character varying::text, 'license_scanning'::character varying::text, 'sast'::character varying::text, 'coverage_fuzzing'::character varying::text, 'apifuzzer_fuzz'::character varying::text, 'apifuzzer_fuzz_dnd'::character varying::text, 'secret_detection'::character varying::text]))
"index_security_ci_builds_on_name_and_id_parser_features" btree (name, id) WHERE (name::text = ANY (ARRAY['container_scanning'::character varying::text, 'dast'::character varying::text, 'dependency_scanning'::character varying::text, 'license_management'::character varying::text, 'sast'::character varying::text, 'secret_detection'::character varying::text, 'coverage_fuzzing'::character varying::text, 'license_scanning'::character varying::text, 'apifuzzer_fuzz'::character varying::text, 'apifuzzer_fuzz_dnd'::character varying::text])) AND type::text = 'Ci::Build'::text
"partial_index_ci_builds_on_scheduled_at_with_scheduled_jobs" btree (scheduled_at) WHERE scheduled_at IS NOT NULL AND type::text = 'Ci::Build'::text AND status::text = 'scheduled'::text
Check constraints:
"check_1e2fbd1b39" CHECK (lock_version IS NOT NULL)
Foreign-key constraints:
"fk_3a9eaa254d" FOREIGN KEY (stage_id) REFERENCES ci_stages(id) ON DELETE CASCADE
"fk_6661f4f0e8" FOREIGN KEY (resource_group_id) REFERENCES ci_resource_groups(id) ON DELETE SET NULL
"fk_87f4cefcda" FOREIGN KEY (upstream_pipeline_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
"fk_a2141b1522" FOREIGN KEY (auto_canceled_by_id) REFERENCES ci_pipelines(id) ON DELETE SET NULL
"fk_d3130c9a7f" FOREIGN KEY (commit_id) REFERENCES ci_pipelines(id) ON DELETE CASCADE
Referenced by:
TABLE "ci_unit_test_failures" CONSTRAINT "fk_0f09856e1f" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
TABLE "ci_sources_pipelines" CONSTRAINT "fk_be5624bf37" FOREIGN KEY (source_job_id) REFERENCES ci_builds(id) ON DELETE CASCADE
TABLE "ci_resources" CONSTRAINT "fk_e169a8e3d5" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE SET NULL
TABLE "p_ci_builds_metadata" CONSTRAINT "fk_e20479742e" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
TABLE "ci_build_pending_states" CONSTRAINT "fk_rails_0bbbfeaf9d" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
TABLE "ci_build_trace_chunks" CONSTRAINT "fk_rails_1013b761f2" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
TABLE "ci_build_report_results" CONSTRAINT "fk_rails_16cb1ff064" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
TABLE "ci_build_needs" CONSTRAINT "fk_rails_3cf221d4ed" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
TABLE "ci_builds_runner_session" CONSTRAINT "fk_rails_70707857d3" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
TABLE "ci_pending_builds" CONSTRAINT "fk_rails_725a2644a3" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
TABLE "ci_build_trace_metadata" CONSTRAINT "fk_rails_aebc78111f" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
TABLE "ci_job_artifacts" CONSTRAINT "fk_rails_c5137cb2c1" FOREIGN KEY (job_id) REFERENCES ci_builds(id) ON DELETE CASCADE
TABLE "ci_running_builds" CONSTRAINT "fk_rails_da45cfa165" FOREIGN KEY (build_id) REFERENCES ci_builds(id) ON DELETE CASCADE
TABLE "ci_job_variables" CONSTRAINT "fk_rails_fbf3b34792" FOREIGN KEY (job_id) REFERENCES ci_builds(id) ON DELETE CASCADE
Triggers:
ci_builds_loose_fk_trigger AFTER DELETE ON ci_builds REFERENCING OLD TABLE AS old_table FOR EACH STATEMENT EXECUTE FUNCTION insert_into_loose_foreign_keys_deleted_records()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