Delete generic packages not downloaded in N days

Tim Rizzirequested to merge
630800-generic-packages-cleanup-not-downloaded into master

What does this MR do and why?

  • Adds a package cleanup policy rule: delete generic packages not downloaded in N days. N is any whole number, 1 or more. null turns the rule off (default).
  • Teams use the generic package registry for short-lived data, like build caches. Today the only way to expire it is one DELETE API call per package. One customer sends about 40,000 of these calls a night.
  • This MR adds the rule through the API only. Set it with the GraphQL updatePackagesCleanupPolicy mutation and a new deleteGenericPackagesNotDownloadedInDays argument. The PackagesCleanupPolicy type exposes the same field. Both are marked experiment. There is no UI in this MR.
  • The rule sits behind the packages_cleanup_delete_generic_not_downloaded feature flag (gitlab_com_derisk, default off). With the flag off, the argument is ignored and the rule does not run.

How it works:

  • Which packages: generic packages whose last download date, or creation date if never downloaded, is older than N days. Uses the existing packages_packages.last_downloaded_at column.
  • Skipped packages: packages still processing or already pending destruction, packages that match a package protection rule with a minimum access level for delete, and all other package formats.
  • Deletion: marks the package's files, then the package itself, as pending_destruction. The existing Packages::CleanupPackageFileWorker deletes the files and removes the package once its last file is gone.
  • Scheduling: runs inside the existing Packages::Cleanup::ExecutePolicyWorker every 12 hours and shares its 250-second time budget. It runs before the duplicated-files rule, so a project whose duplicate cleanup keeps timing out still gets this rule.

Commits are split: migration, implementation and specs, and docs.

References

  • Issue: #630800
  • Related: #630803 (first-class Gradle/Bazel build cache)

Screenshots or screen recordings

Not applicable. This MR only changes the API and a background job. It has no UI changes.

How to set up and validate locally

  1. Enable the flag in a Rails console: 
    Feature.enable(:packages_cleanup_delete_generic_not_downloaded)
  2. Upload a generic package with the API. Then age it in the Rails console: 
    Packages::Package.find(<package_id>).update_columns(created_at: 40.days.ago, last_downloaded_at: nil)
  3. Set the rule in GraphiQL (/-/graphql-explorer): 
    mutation {
  updatePackagesCleanupPolicy(input: {
    projectPath: "<your-project>",
    deleteGenericPackagesNotDownloadedInDays: 30
  }) {
    packagesCleanupPolicy { deleteGenericPackagesNotDownloadedInDays }
    errors
  }
}
  4. Make the policy due, then run it: 
    policy = Project.find_by_full_path('<your-project>').packages_cleanup_policy
policy.update_column(:next_run_at, 1.minute.ago)
Packages::Cleanup::ExecutePolicyService.new(policy).execute
  5. Check the package status. It should be pending_destruction: 
    Packages::Package.find(<package_id>).status

Database review

  • Migrations: a regular migration adds a nullable integer column delete_generic_packages_not_downloaded_in_days to packages_cleanup_policies (a small table), with a check constraint that it is from 1 to 3650. A post-deploy migration replaces the partial index the policy scheduler uses (idx_enabled_pkgs_cleanup_policies_on_next_run_at_project_id) with one whose predicate also covers the new column (idx_active_pkgs_cleanup_policies_on_next_run_at_project_id), matching the updated Packages::Cleanup::Policy.active scope.
  • New queries: the scheduler query now filters keep_n_duplicated_package_files <> 'all' OR delete_generic_packages_not_downloaded_in_days IS NOT NULL. The new rule batches a project's packages one status at a time on the existing index_packages_packages_on_project_id_and_status_and_id index (500 per batch). Each batch then filters to generic packages, COALESCE(last_downloaded_at, created_at) < cutoff, and a NOT EXISTS against packages_protection_rules.
  • No new index on packages_packages: the table is on the Migration/PreventIndexCreation list, so the rule avoids needing one. The batch boundary query is an index-only scan.
Migration output 
# down (both)
== 20260924180100 ReplacePackagesCleanupPoliciesNextRunAtIndex: reverting =====
-- transaction_open?(nil)
   -> 0.0000s
-- view_exists?(:postgres_partitions)
   -> 0.0631s
-- index_exists?(:packages_cleanup_policies, [:next_run_at, :project_id], {:where=>"keep_n_duplicated_package_files <> 'all'", :name=>"idx_enabled_pkgs_cleanup_policies_on_next_run_at_project_id", :algorithm=>:concurrently})
   -> 0.0054s
-- execute("SET statement_timeout TO 0")
   -> 0.0016s
-- add_index(:packages_cleanup_policies, [:next_run_at, :project_id], {:where=>"keep_n_duplicated_package_files <> 'all'", :name=>"idx_enabled_pkgs_cleanup_policies_on_next_run_at_project_id", :algorithm=>:concurrently})
   -> 0.0101s
-- execute("RESET statement_timeout")
   -> 0.0005s
-- transaction_open?(nil)
   -> 0.0000s
-- view_exists?(:postgres_partitions)
   -> 0.0007s
-- index_name_exists?(:packages_cleanup_policies, "idx_active_pkgs_cleanup_policies_on_next_run_at_project_id")
   -> 0.0012s
-- remove_index(:packages_cleanup_policies, {:algorithm=>:concurrently, :name=>"idx_active_pkgs_cleanup_policies_on_next_run_at_project_id"})
   -> 0.0062s
== 20260924180100 ReplacePackagesCleanupPoliciesNextRunAtIndex: reverted (0.1570s) 
== 20260924180000 AddDeleteGenericPackagesNotDownloadedInDaysToPackagesCleanupPolicies: reverting 
-- transaction_open?(nil)
   -> 0.0000s
-- remove_column(:packages_cleanup_policies, :delete_generic_packages_not_downloaded_in_days, {:if_exists=>true})
   -> 0.0399s
== 20260924180000 AddDeleteGenericPackagesNotDownloadedInDaysToPackagesCleanupPolicies: reverted (0.0692s) 
# up (both)
== 20260924180000 AddDeleteGenericPackagesNotDownloadedInDaysToPackagesCleanupPolicies: migrating 
-- transaction_open?(nil)
   -> 0.0000s
-- add_column(:packages_cleanup_policies, :delete_generic_packages_not_downloaded_in_days, :integer, {:if_not_exists=>true})
   -> 0.0346s
-- transaction_open?(nil)
   -> 0.0000s
-- transaction_open?(nil)
   -> 0.0000s
-- execute("ALTER TABLE packages_cleanup_policies\nADD CONSTRAINT check_packages_cleanup_policies_generic_not_downloaded_days\nCHECK ( delete_generic_packages_not_downloaded_in_days > 0 AND delete_generic_packages_not_downloaded_in_days <= 3650 )\nNOT VALID;\n")
   -> 0.0010s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- execute("ALTER TABLE packages_cleanup_policies VALIDATE CONSTRAINT check_packages_cleanup_policies_generic_not_downloaded_days;")
   -> 0.0008s
-- execute("RESET statement_timeout")
   -> 0.0004s
== 20260924180000 AddDeleteGenericPackagesNotDownloadedInDaysToPackagesCleanupPolicies: migrated (0.0624s) 
== 20260924180100 ReplacePackagesCleanupPoliciesNextRunAtIndex: migrating =====
-- transaction_open?(nil)
   -> 0.0000s
-- view_exists?(:postgres_partitions)
   -> 0.0454s
-- index_exists?(:packages_cleanup_policies, [:next_run_at, :project_id], {:where=>"keep_n_duplicated_package_files <> 'all' OR delete_generic_packages_not_downloaded_in_days IS NOT NULL", :name=>"idx_active_pkgs_cleanup_policies_on_next_run_at_project_id", :algorithm=>:concurrently})
   -> 0.0029s
-- execute("SET statement_timeout TO 0")
   -> 0.0004s
-- add_index(:packages_cleanup_policies, [:next_run_at, :project_id], {:where=>"keep_n_duplicated_package_files <> 'all' OR delete_generic_packages_not_downloaded_in_days IS NOT NULL", :name=>"idx_active_pkgs_cleanup_policies_on_next_run_at_project_id", :algorithm=>:concurrently})
   -> 0.0066s
-- execute("RESET statement_timeout")
   -> 0.0003s
-- transaction_open?(nil)
   -> 0.0000s
-- view_exists?(:postgres_partitions)
   -> 0.0004s
-- index_name_exists?(:packages_cleanup_policies, "idx_enabled_pkgs_cleanup_policies_on_next_run_at_project_id")
   -> 0.0017s
-- remove_index(:packages_cleanup_policies, {:algorithm=>:concurrently, :name=>"idx_enabled_pkgs_cleanup_policies_on_next_run_at_project_id"})
   -> 0.0052s
== 20260924180100 ReplacePackagesCleanupPoliciesNextRunAtIndex: migrated (0.1440s)

Query plans

Plans come from postgres.ai's gitlab-production-main clone. The clone has this MR's column and index applied, the rule turned on for 5,000 policies, and 20,000 seeded generic packages (16,000 older than 30 days) plus one file each and two delete protection rules, all under project 278964.

Query Where Plan Time (warm)
1a Scheduler: next policy (Policy.runnable ... FOR UPDATE SKIP LOCKED) ExecutePolicyWorker#next_policy 163725 154 ms
1a baseline (master, without the new OR) — 163726 62 ms
1b Scheduler: remaining work count ExecutePolicyWorker#remaining_work_count 163718 1.1 s cold
2a Batch start ExecutePolicyService#delete_generic_packages_not_downloaded_with_status 163719 1.8 ms
2b Batch stop same 163720 0.5 ms
3 Batch filter (generic, not downloaded since, no recent upload, no delete protection rule) same 166285 about 4 ms
3 before the recent-upload check — 163721 2.4 ms
4 Mark packages pending_destruction mark_packages_for_destruction 163722 40 ms
5a Mark files: batch start MarkPackageFilesForDestructionService (existing) 163723 4 ms
5b Mark files: batch update same 163727 18 ms

Notes

  • Scheduler cost: 1a and its baseline have the same plan shape: an index scan on packages_cleanup_policies_pkey with a filter, then the existing EXISTS semi-join on index_packages_packages_on_project_id_and_package_type. Buffers go from 92,015 to 95,510 (+4%), in line with the extra policies the new OR matches (17,310 vs 16,480 rows). The first cold run took 13.9 s, almost all I/O (163717).
  • Partial index not chosen: on production the planner doesn't use the partial next_run_at index for 1a, before or after this MR. The index swap keeps its predicate in sync with the active scope. It doesn't change this plan, but the old index could never serve the updated scope, because its predicate doesn't cover the new OR.
  • Batch filter: the earlier plan (163721) returns 389 of 500 rows. The 111 excluded rows match the seed-build-cache-1* delete protection rule, so that NOT EXISTS works.
  • Recent uploads: plan 166285 adds the NOT EXISTS on packages_package_files. It uses an Index Only Scan on index_packages_package_files_on_package_id_and_created_at_desc, 500 loops at 0.002 ms, about 1 ms per batch. It returns 450 of 500 seeded packages. The 50 kept packages each had a file uploaded 1 day ago. The plan's 2.4 s total is a cold read in Joe's InitPlan that finds the seeded id range. The production query passes literal ids, and its own nodes take about 4 ms.
  • db:check-migrations skipped: the job failed with "rollback of added migrations does not revert db/structure.sql". Its diff contains none of this MR's tables or indexes, only master's: ai_events_counts moves position and orbit_namespace_enrollments (added to master after this branch was rebased) disappears. This MR's two migrations roll back cleanly, so the job is skipped with pipeline:skip-check-migrations.
  • Update batch sizes: query 4 now runs 100 package ids at a time, and 5b marks 200 files at a time, matching the duplicate files rule. The plans above were measured at 500 rows, so these batches touch fewer rows than shown.
  • 5b: the app sends a literal id list. The plan uses packages_package_files_pkey with the batch id range. An earlier run with a subquery instead of literal ids took 545 ms (163724) and isn't representative.
SQL 
-- 1a Scheduler: next policy
SELECT "packages_cleanup_policies".* FROM "packages_cleanup_policies" WHERE ("packages_cleanup_policies"."keep_n_duplicated_package_files" != 'all' OR "packages_cleanup_policies"."delete_generic_packages_not_downloaded_in_days" IS NOT NULL) AND (next_run_at < NOW()) AND (EXISTS (SELECT 1 FROM "packages_packages" WHERE "packages_packages"."status" IN (0, 1, 5) AND (packages_packages.project_id = packages_cleanup_policies.project_id))) ORDER BY "packages_cleanup_policies"."next_run_at" ASC LIMIT 1 FOR UPDATE SKIP LOCKED;

-- 1b Scheduler: remaining work count
SELECT COUNT(*) FROM (SELECT 1 FROM "packages_cleanup_policies" WHERE ("packages_cleanup_policies"."keep_n_duplicated_package_files" != 'all' OR "packages_cleanup_policies"."delete_generic_packages_not_downloaded_in_days" IS NOT NULL) AND (next_run_at < NOW()) AND (EXISTS (SELECT 1 FROM "packages_packages" WHERE "packages_packages"."status" IN (0, 1, 5) AND (packages_packages.project_id = packages_cleanup_policies.project_id))) ORDER BY "packages_cleanup_policies"."next_run_at" ASC LIMIT 21) subquery_for_count;

-- 2a Batch start
SELECT "packages_packages"."id" FROM "packages_packages" WHERE "packages_packages"."project_id" = 278964 AND "packages_packages"."status" = 0 ORDER BY "packages_packages"."id" ASC LIMIT 1;

-- 2b Batch stop
SELECT "packages_packages"."id" FROM "packages_packages" WHERE "packages_packages"."project_id" = 278964 AND "packages_packages"."status" = 0 AND (packages_packages.id >= 1) ORDER BY "packages_packages"."id" ASC LIMIT 1 OFFSET 500;

-- 3 Batch filter
SELECT "packages_packages"."id" FROM "packages_packages" WHERE "packages_packages"."project_id" = 278964 AND "packages_packages"."status" = 0 AND (packages_packages.id >= 1 AND packages_packages.id < 1000000) AND "packages_packages"."package_type" = 7 AND COALESCE("packages_packages"."last_downloaded_at", "packages_packages"."created_at") < NOW() - INTERVAL '30 days' AND (NOT EXISTS (SELECT 1 FROM "packages_package_files" WHERE "packages_package_files"."package_id" = "packages_packages"."id" AND "packages_package_files"."created_at" >= NOW() - INTERVAL '30 days')) AND (NOT EXISTS (SELECT 1 FROM "packages_protection_rules" WHERE "packages_protection_rules"."project_id" = "packages_packages"."project_id" AND "packages_protection_rules"."package_type" = "packages_packages"."package_type" AND "packages_protection_rules"."minimum_access_level_for_delete" IS NOT NULL AND ("packages_packages".name ILIKE REPLACE(REPLACE(REPLACE(package_name_pattern, '%', '\%'), '_', '\_'), '*', '%'))));

-- 4 Mark packages pending_destruction
UPDATE "packages_packages" SET "status" = 4 WHERE "packages_packages"."id" IN (<up to 100 ids>);

-- 5a Mark files: batch start
SELECT "packages_package_files"."id" FROM "packages_package_files" WHERE "packages_package_files"."package_id" IN (<up to 500 ids>) ORDER BY "packages_package_files"."id" ASC LIMIT 1;

-- 5b Mark files: batch update
UPDATE "packages_package_files" SET "status" = 1 WHERE "packages_package_files"."package_id" IN (<up to 500 ids>) AND "packages_package_files"."id" BETWEEN 1 AND 200;

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Tim Rizzi

Merge request reports