<!--IssueSummary start--> <details> <summary> Everyone can contribute. [Help move this issue forward](https://handbook.gitlab.com/handbook/marketing/developer-relations/engineering/community-contributors-workflows/#contributor-links) while earning points, leveling up and collecting rewards. </summary> - [Close this issue](https://contributors.gitlab.com/manage-issue?action=close&projectId=278964&issueIid=634531) </details> <!--IssueSummary end--> ## Summary On GitLab.com, package cleanup policies run days apart instead of every 12 hours. Two statement timeouts cut throughput: 1. `CleanupPackageRegistryWorker` runs `Packages::PackageFile.pending_destruction.exists?` before it enqueues any cleanup job. When that query times out, the whole hour is skipped. This also skips package file deletion and the npm, Helm, and NuGet cleanups. 2. `ExecutePolicyService` starts `each_batch` with a `project_id` query that times out for some projects. Because `LimitedCapacity::Worker` stops a chain on any exception, both chains stop within 1 to 3 minutes of each hourly cron. Reported in https://gitlab.com/gitlab-com/request-for-help/-/work_items/5514. ## Impact - All projects with a cleanup policy on GitLab.com. Duplicate package files stay for days instead of 12 hours. - While cause 1 is active, `CleanupPackageFileWorker`, `Npm::CleanupStaleMetadataCacheWorker`, `Helm::CleanupStaleMetadataCacheWorker`, and `Nuget::CleanupStaleSymbolsWorker` are not enqueued either, so files marked `pending_destruction` are not deleted. ## Evidence <details> <summary>Cause 1: <code>pending_destruction.exists?</code> times out and blocks the enqueue</summary> [`app/workers/packages/cleanup_package_registry_worker.rb#L13-21`](https://gitlab.com/gitlab-org/gitlab/-/blob/89f9fcc6877ddd518c9e1897159b49d5c908a197/app/workers/packages/cleanup_package_registry_worker.rb#L13-21) runs five checks in sequence with no rescue. Line 14 failed with a statement timeout on `main_replica`: ```sql SELECT $1 AS one FROM "packages_package_files" WHERE "packages_package_files"."status" = $2 LIMIT $3 ``` | Date (UTC) | Hours where line 14 failed | | --- | --- | | 2026-10-04 | 02:20 to 16:20 (15 runs in a row) | | 2026-10-05 | 00:20 to 07:20, 09:20 to 11:20 | | 2026-10-06 | 07:20, 08:20, 10:20, 11:20, 12:20, 23:20 | `Packages::Cleanup::ExecutePolicyWorker` has zero `start` logs in exactly those hours. In other hours, the cron sometimes failed later on a `SELECT COUNT(*)` in `log_counts`. Those hours still had policy jobs, because the enqueue had already happened. This query should be cheap: `index_packages_package_files_on_id_for_cleanup` is a partial index on `(id) WHERE status = 1`. We have not found out why it times out. One possible cause is dead tuples at the head of that index after a large deletion, made worse because the query runs on a replica. `CleanupPackageRegistryWorker` by job status, hourly ([Kibana](https://log.gprd.gitlab.net/app/r/s/spmWo)):  `ExecutePolicyWorker` job starts, hourly. The empty hours match the hours where line 14 failed ([Kibana](https://log.gprd.gitlab.net/app/r/s/zliuk)):  </details> <details> <summary>Cause 2: <code>project_id</code> query in <code>ExecutePolicyService</code> times out and ends the job chain</summary> [`ExecutePolicyService#installable_package_files`](https://gitlab.com/gitlab-org/gitlab/-/blob/89f9fcc6877ddd518c9e1897159b49d5c908a197/app/services/packages/cleanup/execute_policy_service.rb#L39-41) uses `PackageFile.installable.for_projects(project)`, and `each_batch` starts with: ```sql SELECT "packages_package_files"."id" FROM "packages_package_files" WHERE "packages_package_files"."status" = $1 AND "packages_package_files"."project_id" = $2 ORDER BY "packages_package_files"."id" ASC LIMIT $3 ``` `packages_package_files` has no index that includes `project_id`. https://gitlab.com/gitlab-org/gitlab/-/merge_requests/205423 changed this filter from `package_id IN (project.packages.installable)` to `project_id`. Its database review covered the `GROUP BY` query inside a batch, not this start query. A failed job does not re-enqueue ([`limited_capacity/worker.rb#L61-64`](https://gitlab.com/gitlab-org/gitlab/-/blob/89f9fcc6877ddd518c9e1897159b49d5c908a197/app/workers/concerns/limited_capacity/worker.rb#L61-64)), so each timeout ends one of the `package_registry_cleanup_policies_worker_capacity` chains until the next hourly cron. Sidekiq logs for `Packages::Cleanup::ExecutePolicyWorker`, 2026-10-07 21:20 to 2026-10-08 00:21 UTC and 2026-10-17 17:20 to 2026-10-19 11:22 UTC: - Every hour: exactly 2 `ActiveRecord::QueryCanceled` failures on the query above, both within minutes of the :20 cron. - Median active time: 1.4 minutes per hour. Average throughput: about 99 policies per hour. - 4,246 distinct projects ran in 42 hours and none ran twice, so the queue is more than 42 hours behind the 12-hour cadence. </details> ## Proposal 1. **Isolate the checks in `CleanupPackageRegistryWorker#perform`.** A failure in one `exists?` should not skip the others. Run `enqueue_cleanup_policy_jobs` regardless of the `pending_destruction` check, and log each failure. Small change, low risk. 2. **Fix the start query in `ExecutePolicyService`.** Either restore the `package_id` subquery, or add an index on `packages_package_files (project_id, id) WHERE status = 0`. Needs database review with `EXPLAIN (ANALYZE, BUFFERS)` on a project that times out. 3. **Keep the `ExecutePolicyWorker` chain going when one policy fails.** Rescue and log the error in `ExecutePolicyWorker#perform_work` so one timeout costs one policy, not the rest of the hour. This keeps the shared `LimitedCapacity::Worker` behavior unchanged. 4. **Find out why `pending_destruction.exists?` times out on the replica.** Needs help from the database team: dead tuples and vacuum stats for `packages_package_files` around 2026-10-04. Optional: alert when `CleanupPackageRegistryWorker` fails several hours in a row. The failures above went unnoticed until a customer reported them.