Skip to content

Add unique indexes to ci_job_artifacts

Max Orefice requested to merge morefice/add-unique-indexes-to-ci-job-artifacts into master

Ref: #429002 (closed)

What does this MR do and why?

Following up !138038 (merged) where we introduced 2 new unique 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_job_artifacts 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_job_artifacts"
     Column      |           Type           | Collation | Nullable |                   Default                    | Storage  | Compression | Stats target | Description 
-----------------+--------------------------+-----------+----------+----------------------------------------------+----------+-------------+--------------+-------------
 project_id      | integer                  |           | not null |                                              | plain    |             |              | 
 file_type       | integer                  |           | not null |                                              | plain    |             |              | 
 size            | bigint                   |           |          |                                              | plain    |             |              | 
 created_at      | timestamp with time zone |           | not null |                                              | plain    |             |              | 
 updated_at      | timestamp with time zone |           | not null |                                              | plain    |             |              | 
 expire_at       | timestamp with time zone |           |          |                                              | plain    |             |              | 
 file            | character varying        |           |          |                                              | extended |             |              | 
 file_store      | integer                  |           |          | 1                                            | plain    |             |              | 
 file_sha256     | bytea                    |           |          |                                              | extended |             |              | 
 file_format     | smallint                 |           |          |                                              | plain    |             |              | 
 file_location   | smallint                 |           |          |                                              | plain    |             |              | 
 id              | bigint                   |           | not null | nextval('ci_job_artifacts_id_seq'::regclass) | plain    |             |              | 
 job_id          | bigint                   |           | not null |                                              | plain    |             |              | 
 locked          | smallint                 |           |          | 2                                            | plain    |             |              | 
 partition_id    | bigint                   |           | not null |                                              | plain    |             |              | 
 accessibility   | smallint                 |           | not null | 0                                            | plain    |             |              | 
 file_final_path | text                     |           |          |                                              | extended |             |              | 
Indexes:
    "ci_job_artifacts_pkey" PRIMARY KEY, btree (id)
+   "idx_ci_job_artifacts_on_job_id_file_type_and_partition_id_uniq" UNIQUE, btree (job_id, file_type, partition_id)
    "index_ci_job_artifacts_expire_at_unlocked_non_trace" btree (expire_at) WHERE locked = 0 AND file_type <> 3 AND expire_at IS NOT NULL
    "index_ci_job_artifacts_for_terraform_reports" btree (project_id, id) WHERE file_type = 18
    "index_ci_job_artifacts_id_for_terraform_reports" btree (id) WHERE file_type = 18
    "index_ci_job_artifacts_on_expire_at_and_job_id" btree (expire_at, job_id)
    "index_ci_job_artifacts_on_file_final_path" btree (file_final_path) WHERE file_final_path IS NOT NULL
    "index_ci_job_artifacts_on_file_store" btree (file_store)
    "index_ci_job_artifacts_on_file_type_for_devops_adoption" btree (file_type, project_id, created_at) WHERE file_type = ANY (ARRAY[5, 6, 8, 23])
    "index_ci_job_artifacts_on_id_partition_id_unique" UNIQUE, btree (id, partition_id)
    "index_ci_job_artifacts_on_id_project_id_and_created_at" btree (project_id, created_at, id)
    "index_ci_job_artifacts_on_id_project_id_and_file_type" btree (project_id, file_type, id)
    "index_ci_job_artifacts_on_job_id_and_file_type" UNIQUE, btree (job_id, file_type)
+   "index_ci_job_artifacts_on_partition_id_job_id" btree (partition_id, job_id)
    "index_ci_job_artifacts_on_project_id" btree (project_id)
    "index_ci_job_artifacts_on_project_id_and_id" btree (project_id, id)
    "index_ci_job_artifacts_on_project_id_for_security_reports" btree (project_id) WHERE file_type = ANY (ARRAY[5, 6, 7, 8])
    "tmp_index_ci_job_artifacts_on_expire_at_where_locked_unknown" btree (expire_at, job_id) WHERE locked = 2 AND expire_at IS NOT NULL
Check constraints:
    "check_27f0f6dbab" CHECK (file_store IS NOT NULL)
    "check_9f04410cf4" CHECK (char_length(file_final_path) <= 1024)
Foreign-key constraints:
    "fk_rails_c5137cb2c1_p" FOREIGN KEY (partition_id, job_id) REFERENCES p_ci_builds(partition_id, id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
    TABLE "ci_build_trace_metadata" CONSTRAINT "fk_21d25cac1a" FOREIGN KEY (trace_artifact_id) REFERENCES ci_job_artifacts(id) ON DELETE CASCADE
    TABLE "ci_job_artifact_states" CONSTRAINT "fk_rails_80a9cba3b2" FOREIGN KEY (job_artifact_id) REFERENCES ci_job_artifacts(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.

Edited by Max Orefice

Merge request reports