Resolve orphan artifact lookups in one query per batch

Gitlab::Cleanup::RemoteArtifacts inherited a per-file lookup from RemoteObjectStorage: find_tracked_paths ran one exists? query for every object in the bucket. On a bucket holding billions of artifact objects the scan cannot complete, so gitlab:cleanup:untracked_object_storage_files is unusable at exactly the scale where orphan cleanup matters.

This overrides find_tracked_paths to resolve the whole batch with a single query on the primary key, comparing job_id and file in memory. RemoteUploads already batches its lookup this way. Paths that do not match the expected format are still reported as tracked, so an unrecognized layout is never deleted.

Motivated by https://gitlab.com/gitlab-com/request-for-help/-/work_items/5282 and https://gitlab.com/gitlab-com/request-for-help/-/work_items/5220, where a 2.3-billion-object artifacts bucket needs reconciling against the database.

Database queries

One query per batch of 100 objects replaces one query per object.

Before, once per object:

SELECT 1 AS one FROM "p_ci_job_artifacts"
WHERE "p_ci_job_artifacts"."id" = $1
  AND "p_ci_job_artifacts"."job_id" = $2
  AND "p_ci_job_artifacts"."file" = $3
LIMIT 1;

After, once per batch:

SELECT "p_ci_job_artifacts"."id", "p_ci_job_artifacts"."job_id", "p_ci_job_artifacts"."file"
FROM "p_ci_job_artifacts"
WHERE "p_ci_job_artifacts"."id" IN ($1, ... $100);

EXPLAIN: https://console.postgres.ai/gitlab/projects/gitlab-production-ci/sessions/56709/commands/161097

Partition pruning

p_ci_job_artifacts is partitioned, and this query filters on id alone, so the planner reads every partition. The filter cannot be narrowed: the lookup starts from an object storage key, and that key carries no partition_id.

The check-ci-partition-pruning fingerprint is therefore recorded under allowed: in scripts/database/query_analyzers.yml rather than todos:, because there is no future fix to track.

This remains strictly better than what it replaces. The previous per-object query was equally unable to prune and ran once per object instead of once per 100. Batch size is fixed at RemoteObjectStorage::BATCH_SIZE = 100 — the Rake task does not expose it, so the IN list cannot grow.

Behaviour change

The path pattern is now anchored at both ends, and the filename segment no longer accepts a slash. Two classes of key that the unanchored pattern used to accept now fall through to "unknown format", so the task reports them as tracked rather than reclaiming them:

• a key behind any prefix
• a key whose filename segment holds a slash

This is deliberate. Under the old pattern the positional parse shifted on those keys, the lookup found no row, and the object was deleted. CarrierWave sanitises the stored filename with File.basename, so the column never holds a slash and no tracked artifact reaches either case.

The quarantined spec is also updated here https://gitlab.com/gitlab-org/quality/test-failure-issues/-/work_items/9380