Drop trace-inclusive artifact removal index from ci_job_artifacts
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:
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.