Skip to content

Schedule async index build for ci_job_artifacts

drew stachon requested to merge prepare-async-artifacts-index-build into master

Our purpose-built index for removal of expired records from ci_job_artifacts does not exclude artifacts where expire_at is null. This means job traces, which get unlocked but have no expiration, are polluting an index that would otherwise be a clean ready-for-removal queue and much smaller.

Example Query

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/9403/commands/33365

The DestroyAllExpiredService currently makes this query to the ci_job_artifacts_expire_at_unlocked_idx index:

Ci::JobArtifact.expired_before(@start_at).artifact_unlocked.limit(BATCH_SIZE)
SELECT "ci_job_artifacts"."project_id", "ci_job_artifacts"."file_type", "ci_job_artifacts"."size", "ci_job_artifacts"."created_at", "ci_job_artifacts"."updated_at", "ci_job_artifacts"."expire_at", "ci_job_artifacts"."file", "ci_job_artifacts"."file_store", "ci_job_artifacts"."file_sha256", "ci_job_artifacts"."file_format", "ci_job_artifacts"."file_location", "ci_job_artifacts"."id", "ci_job_artifacts"."job_id", "ci_job_artifacts"."locked" FROM "ci_job_artifacts" WHERE "ci_job_artifacts"."expire_at" < '2022-03-24 20:53:34.670678' AND "ci_job_artifacts"."locked" = 0 LIMIT 1000

This index is going to be exactly the same as ci_job_artifacts_expire_at_unlocked_idx, but also exclude records where expire_at = null, and therefore won't ever get expired or turn up in this query.

We'll build the new index and have them both available at the same time, and can drop the older, larger index once we have visibility into the query running smoothly using this new one.

Edited by drew stachon

Merge request reports