Skip to content

Drop trace-inclusive artifact removal index from ci_job_artifacts

drew stachon requested to merge drop-larger-unlocked-artifact-index into master

What does this MR do and why?

This MR drops a partial index we used to queue up artifacts that have expired and need to be removed from ci_job_artifacts.

We've recently built a new partial index that's entirely the same, but additionally exclude trace-type artifacts since those are not expirable, i.e. have no expire_at value. The indexes are both on, and ordered by, expire_at so this older index has a big bunch of a few hundred million job traces at one end of the index that we'll never need or touch.

By also restricting to WHERE expire_at IS NOT NULL, we exclude job traces and reduce the size of the index by about 86%:

Index sizes from db-lab:

Schema Name Type Owner Table Size Description
public ci_job_artifacts_expire_at_unlocked_idx index joe_dcimino ci_job_artifacts 6602 MB CREATE INDEX ci_job_artifacts_expire_at_unlocked_idx ON ci_job_artifacts USING btree (expire_at) WHERE (locked = 0);
public index_ci_job_artifacts_on_expire_at_for_removal index joe_dcimino ci_job_artifacts 906 MB CREATE INDEX index_ci_job_artifacts_on_expire_at_for_removal ON ci_job_artifacts USING btree (expire_at) WHERE ((locked = 0) AND (expire_at IS NOT NULL));

The new index was built over the weekend of April 2-3, and as of April 4th I've verified that the new index has been built, and the planner has wisely switched over to using it instead of the one we're dropping:

image image

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 drew stachon

Merge request reports