Add a background worker that deletes ML experiments in bounded slices (!264622) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

This adds a background worker that deletes an ML experiment and its runs, with their metrics, parameters and metadata, in statements of bounded size. It is the deletion half of the fix for experiment and model deletion, which today cascades through every run in one DELETE inside the request. On its own it does nothing: no code marks experiments yet, so the cron finds no work. No feature flag; rollback is a revert.

Design notes

Pull-based, like package cleanup. Ml::ScheduleExperimentDeletionCronWorker runs every 5 minutes (config/schedule.yml) and calls Ml::DestroyExperimentWorker.perform_with_capacity when Ml::Experiment.ready_for_deletion.exists?. Ml::DestroyExperimentWorker is a LimitedCapacity::Worker with 2 slots (MAX_CAPACITY; deletions are rare, and 2 slots bound the WAL rate; an application setting would need a migration). There is no job per deleted experiment. Each job:

claims the experiment marked first: ORDER BY deleted_on, id LIMIT 1 FOR UPDATE SKIP LOCKED, then moves its deleted_on 10 minutes ahead, in one short transaction. Other jobs skip it; if the job dies, the claim expires and another job picks it up.
deletes one slice: for each batch of 100 runs (each_batch on the unique (experiment_id, eid) index), it deletes the runs' metrics, params and metadata in statements of at most 10,000 rows, then the runs. It stops at 100,000 rows or 2 minutes, so the database health check (defer_on_database_health_signal) runs between slices.
puts the experiment back at the end of the queue (deleted_on = now), or deletes the experiment row when no runs are left.

LimitedCapacity::Worker re-enqueues while remaining_work_count is above 0. An experiment counts as marked when ml_experiments.deleted_on is set. The column exists already and no code writes it today, so this MR is inert until !263939 marks experiments behind ml_delete_experiments_in_batches. Merging the worker first also means it is deployed before anything relies on it.

Rows per statement (the threshold). A child statement deletes at most 10,000 metric, param or metadata rows. A run statement deletes at most 100 runs, and their children are already gone. A job deletes at most 100,000 rows.

This MR (targets master).
!263939 marks experiments for deletion behind ml_delete_experiments_in_batches. It is stacked on this MR and merges after it.
!264621 (merged) routes the web delete actions through the services. It is independent and can merge in any order.

References

Related to https://gitlab.com/gitlab-org/gitlab/-/work_items/635041

Screenshots or screen recordings

No UI change.

How to set up and validate locally

In the rails console, create an experiment with runs and metrics, and mark it as the follow-up MR will:

project = Project.find_by_full_path('gitlab-org/gitlab-test'); user = User.find_by_username('root')
exp = Ml::Experiment.create!(project: project, user: user, name: 'delete-me-worker')
3.times { |i| Ml::Candidate.create!(experiment: exp, project: project, user: user, name: "run#{i}").metrics.create!(name: 'loss', value: i, step: 0, tracked_at: 0) }
exp.update!(deleted_on: Time.current)

Run Ml::ScheduleExperimentDeletionCronWorker.new.perform (or wait up to 5 minutes for the cron).
Expect: Ml::Experiment.find_by(id: exp.id) is nil and Ml::Candidate.where(experiment_id: exp.id).count is 0.
Create another experiment without setting deleted_on and run the cron worker again. Expect: it is not touched, and no Ml::DestroyExperimentWorker job is enqueued.

Database review: queries and plans (Database Lab, gitlab-production-main)

Ids and values are placeholders. Every write ran in a rolled-back transaction. "Cold" means pages read from disk on the clone; a worker never reads the same batch twice, so the batch size is chosen on cold numbers. Table sizes: ml_candidate_metrics 7.5 GB, ml_candidate_metadata 1.2 GB, ml_candidate_params 552 MB, ml_candidates 403 MB, ml_experiments 11 MB. No migration. On master, deleting an experiment or model cascades through all of these tables in one DELETE; !263939 hands that work to this worker.

Why bounded statements: the current cascade. One run batch of 7 dense runs (2.55 million metric rows) is one 16.1 s statement that writes 454 MB of WAL; this is also the open-ended last-batch form, the only batch for an experiment with 100 runs or fewer:

DELETE FROM "ml_candidates" WHERE "ml_candidates"."experiment_id" = <experiment_id> AND "ml_candidates"."eid" >= '<eid_start>'

Delete on ml_candidates  (cost=0.43..6.82 rows=0 width=0) (actual time=0.068..0.068 rows=0 loops=1)
  Buffers: shared hit=31 dirtied=7
  WAL: records=7 fpi=7 bytes=57249
  ->  Index Scan using index_ml_candidates_on_experiment_id_and_eid on ml_candidates  (cost=0.43..6.82 rows=5 width=6) (actual time=0.011..0.021 rows=7 loops=1)
        Index Cond: ((experiment_id = <experiment_id>) AND (eid >= '<eid_start>'::uuid))
Trigger for constraint fk_ml_candidate_metrics_on_candidate_id: time=16031.116 calls=7
Trigger for constraint fk_ml_candidate_params_on_candidate_id: time=19.725 calls=7
Trigger for constraint fk_rails_5117dddf22: time=39.582 calls=7
Execution Time: 16090.533 ms
WAL for the statement, cascade included (LSN difference): 454 MB

DELETE FROM ml_models WHERE id = <model_id> on the model with the most versions (its runs have no metrics), cold:

Trigger for constraint fk_e86e0bfa5a on ml_model_versions: time=1423.563 calls=3161
Trigger for constraint fk_rails_5b67cc9107 on ml_model_versions: time=56.737 calls=3161
Trigger for constraint fk_ml_candidate_metrics_on_candidate_id on ml_candidates: time=1329.795 calls=3161
Trigger for constraint fk_ml_candidate_params_on_candidate_id on ml_candidates: time=374.271 calls=3161
Trigger for constraint fk_rails_5117dddf22 on ml_candidates: time=4406.612 calls=3161
Execution Time: 7772.683 ms

1. Pick and claim. ml_experiments has 444 heap pages and no index on deleted_on, so the pick is a sequential scan: 53 ms cold and 2 ms warm with nothing marked (today), 94 ms cold with 50 marked. It runs once per job; the remaining_work_count and cron queries below also run a few times per job or every 5 minutes. A partial index is not needed at this size.

SELECT "ml_experiments".* FROM "ml_experiments" WHERE "ml_experiments"."deleted_on" <= '<now>' ORDER BY "ml_experiments"."deleted_on" ASC, "ml_experiments"."id" ASC LIMIT 1 FOR UPDATE SKIP LOCKED

Nothing marked, cold:

Limit  (cost=2207.65..2207.67 rows=1 width=103) (actual time=53.171..53.173 rows=0 loops=1)
  Buffers: shared hit=6 read=444 dirtied=5
  ->  LockRows  (cost=2207.65..2207.67 rows=1 width=103) (actual time=53.170..53.171 rows=0 loops=1)
        ->  Sort  (cost=2207.65..2207.66 rows=1 width=103) (actual time=53.169..53.170 rows=0 loops=1)
              Sort Key: ml_experiments.deleted_on, ml_experiments.id
              Sort Method: quicksort  Memory: 25kB
              ->  Seq Scan on public.ml_experiments  (cost=0.00..2207.64 rows=1 width=103) (actual time=53.149..53.149 rows=0 loops=1)
                    Filter: (ml_experiments.deleted_on <= now())
                    Rows Removed by Filter: 29849
                    Buffers: shared read=444 dirtied=5
Execution Time: 53.215 ms

50 experiments marked, cold:

Limit  (cost=2207.65..2207.67 rows=1 width=103) (actual time=93.490..93.492 rows=1 loops=1)
  Buffers: shared hit=19 read=432 dirtied=2
  ->  LockRows  (cost=2207.65..2207.67 rows=1 width=103) (actual time=93.488..93.490 rows=1 loops=1)
        ->  Sort  (cost=2207.65..2207.66 rows=1 width=103) (actual time=93.480..93.481 rows=1 loops=1)
              Sort Key: deleted_on, id
              Sort Method: quicksort  Memory: 30kB
              ->  Seq Scan on ml_experiments  (cost=0.00..2207.64 rows=1 width=103) (actual time=90.204..93.399 rows=50 loops=1)
                    Filter: (deleted_on <= now())
                    Rows Removed by Filter: 29799
Execution Time: 93.552 ms

The claim is UPDATE "ml_experiments" SET "deleted_on" = '<now + 10 minutes>' WHERE "ml_experiments"."id" = <id> (primary key). remaining_work_count and the cron check:

SELECT COUNT(*) FROM (SELECT 1 AS one FROM "ml_experiments" WHERE "ml_experiments"."deleted_on" <= '<now>' LIMIT 3) subquery_for_count
SELECT 1 AS one FROM "ml_experiments" WHERE "ml_experiments"."deleted_on" <= '<now>' LIMIT 1

Aggregate  (cost=2207.65..2207.66 rows=1 width=8) (actual time=2.040..2.040 rows=1 loops=1)
  Buffers: shared hit=419
  ->  Limit  (cost=0.00..2207.64 rows=1 width=4) (actual time=2.033..2.034 rows=3 loops=1)
        ->  Seq Scan on ml_experiments  (cost=0.00..2207.64 rows=1 width=4) (actual time=2.032..2.033 rows=3 loops=1)
              Filter: (deleted_on <= now())
              Rows Removed by Filter: 28006
Execution Time: 2.065 ms

2. Run batch boundaries (experiment with the most runs)

SELECT "ml_candidates"."eid" FROM "ml_candidates" WHERE "ml_candidates"."experiment_id" = <experiment_id> AND "ml_candidates"."eid" >= '<eid_start>' ORDER BY "ml_candidates"."eid" ASC LIMIT 1 OFFSET 100

Limit  (cost=3.44..3.47 rows=1 width=16) (actual time=0.064..0.065 rows=1 loops=1)
  Buffers: shared hit=7
  ->  Index Only Scan using index_ml_candidates_on_experiment_id_and_eid on public.ml_candidates  (cost=0.43..27171.09 rows=902533 width=16) (actual time=0.048..0.060 rows=101 loops=1)
        Index Cond: ((ml_candidates.experiment_id = <experiment_id>) AND (ml_candidates.eid >= '<eid_start>'::uuid))
        Heap Fetches: 0
Execution Time: 0.091 ms

The first boundary query (ORDER BY eid LIMIT 1, no OFFSET) uses the same index: 7 buffers, 0.07 ms.

3. Delete a batch's metrics (params and metadata: see 3a), first statement for an experiment with dense runs, cold:

DELETE FROM "ml_candidate_metrics" WHERE ("ml_candidate_metrics"."id") IN (SELECT "ml_candidate_metrics"."id" FROM "ml_candidate_metrics" WHERE "ml_candidate_metrics"."candidate_id" IN (SELECT "ml_candidates"."id" FROM "ml_candidates" WHERE "ml_candidates"."experiment_id" = <experiment_id> AND "ml_candidates"."eid" >= '<eid_start>') LIMIT 10000)

Delete on ml_candidate_metrics  (cost=6884.10..20822.84 rows=0 width=0) (actual time=335.734..335.737 rows=0 loops=1)
  Buffers: shared hit=60398 read=539 dirtied=458
  WAL: records=10000 fpi=456 bytes=4259952
  ->  Nested Loop  (cost=6884.10..20822.84 rows=3906 width=38) (actual time=266.189..330.139 rows=10000 loops=1)
        ->  HashAggregate  (cost=6883.53..6922.59 rows=3906 width=40) (actual time=263.822..265.964 rows=10000 loops=1)
              Group Key: "ANY_subquery".id
              ->  Subquery Scan on "ANY_subquery"  (cost=0.87..6873.77 rows=3906 width=40) (actual time=0.716..260.429 rows=10000 loops=1)
                    ->  Limit  (cost=0.87..6834.71 rows=3906 width=8) (actual time=0.711..258.618 rows=10000 loops=1)
                          ->  Nested Loop  (cost=0.87..6834.71 rows=3906 width=8) (actual time=0.711..257.776 rows=10000 loops=1)
                                ->  Index Scan using index_ml_candidates_on_experiment_id_and_eid on ml_candidates  (cost=0.43..87.99 rows=99 width=8) (actual time=0.007..0.012 rows=3 loops=1)
                                      Index Cond: ((experiment_id = <experiment_id>) AND (eid >= '<eid_start>'::uuid))
                                ->  Index Scan using index_ml_candidate_metrics_on_candidate_id on ml_candidate_metrics ml_candidate_metrics_1  (cost=0.44..57.91 rows=1024 width=16) (actual time=0.549..85.550 rows=3333 loops=3)
                                      Index Cond: (candidate_id = ml_candidates.id)
        ->  Index Scan using ml_candidate_metrics_pkey on ml_candidate_metrics  (cost=0.56..3.56 rows=1 width=14) (actual time=0.006..0.006 rows=1 loops=10000)
              Index Cond: (id = "ANY_subquery".id)
Execution Time: 335.862 ms

The whole 7-run batch from the plan above, in one rolled-back transaction (later statements are slower there than in production, because a transaction cannot skip its own dead index entries): 257 metric statements, at most 582 ms each, 51 s and 477 MB of WAL in total. The densest single run alone: 62 statements, at most 467 ms each. Batching bounds each statement; the total WAL stays the same. The run DELETE for the batch then cascades nothing:

Delete on ml_candidates  (cost=0.43..6.82 rows=0 width=0) (actual time=1.015..1.015 rows=0 loops=1)
  WAL: records=7 fpi=7 bytes=57249
  ->  Index Scan using index_ml_candidates_on_experiment_id_and_eid on ml_candidates  (cost=0.43..6.82 rows=5 width=6) (actual time=0.008..0.018 rows=7 loops=1)
        Index Cond: ((experiment_id = <experiment_id>) AND (eid >= '<eid_start>'::uuid))
Trigger for constraint fk_ml_candidate_metrics_on_candidate_id: time=195.140 calls=7
Trigger for constraint fk_ml_candidate_params_on_candidate_id: time=50.701 calls=7
Trigger for constraint fk_rails_5117dddf22: time=51.773 calls=7
Execution Time: 298.736 ms

3a. Delete a batch's params and metadata. The SQL is the same as for metrics, on ml_candidate_params and ml_candidate_metadata. Both plans are cold.

Params: an experiment with 43 runs and 9,988 params, so its only statement deletes close to the 10,000-row limit:

Delete on ml_candidate_params  (cost=259.78..643.13 rows=0 width=0) (actual time=194.746..194.749 rows=0 loops=1)
  Buffers: shared hit=50288 read=345 dirtied=251
  WAL: records=9992 fpi=250 bytes=2578002
  ->  Nested Loop  (cost=259.78..643.13 rows=111 width=38) (actual time=148.902..189.863 rows=9988 loops=1)
        ->  HashAggregate  (cost=259.35..260.46 rows=111 width=40) (actual time=142.077..143.801 rows=9988 loops=1)
              Group Key: "ANY_subquery".id
              ->  Subquery Scan on "ANY_subquery"  (cost=0.86..259.07 rows=111 width=40) (actual time=5.813..138.974 rows=9988 loops=1)
                    ->  Limit  (cost=0.86..257.96 rows=111 width=8) (actual time=5.796..137.408 rows=9988 loops=1)
                          ->  Nested Loop  (cost=0.86..257.96 rows=111 width=8) (actual time=5.795..136.636 rows=9988 loops=1)
                                ->  Index Scan using index_ml_candidates_on_experiment_id_and_eid on ml_candidates  (cost=0.43..48.36 rows=52 width=8) (actual time=0.316..1.100 rows=43 loops=1)
                                      Index Cond: ((experiment_id = <experiment_id>) AND (eid >= '<eid_start>'::uuid))
                                ->  Index Scan using index_ml_candidate_params_on_candidate_id on ml_candidate_params ml_candidate_params_1  (cost=0.43..3.85 rows=18 width=16) (actual time=0.605..3.129 rows=232 loops=43)
                                      Index Cond: (candidate_id = ml_candidates.id)
        ->  Index Scan using ml_candidate_params_pkey on ml_candidate_params  (cost=0.43..3.45 rows=1 width=14) (actual time=0.004..0.004 rows=1 loops=9988)
              Index Cond: (id = "ANY_subquery".id)
Execution Time: 195.004 ms

Metadata: the experiment that holds the run with the most metadata rows (3,326). Its 21 runs have 7,979 metadata rows:

Delete on ml_candidate_metadata  (cost=58.09..161.46 rows=0 width=0) (actual time=5140.351..5140.355 rows=0 loops=1)
  Buffers: shared hit=45730 read=5287 dirtied=3772
  WAL: records=8005 fpi=3769 bytes=31022459
  ->  Nested Loop  (cost=58.09..161.46 rows=30 width=38) (actual time=3241.884..5119.286 rows=7979 loops=1)
        ->  HashAggregate  (cost=57.66..57.96 rows=30 width=40) (actual time=3238.124..3240.630 rows=7979 loops=1)
              Group Key: "ANY_subquery".id
              ->  Subquery Scan on "ANY_subquery"  (cost=0.86..57.58 rows=30 width=40) (actual time=1.180..3233.240 rows=7979 loops=1)
                    ->  Limit  (cost=0.86..57.28 rows=30 width=8) (actual time=1.168..3229.024 rows=7979 loops=1)
                          ->  Nested Loop  (cost=0.86..57.28 rows=30 width=8) (actual time=1.167..3228.034 rows=7979 loops=1)
                                ->  Index Scan using index_ml_candidates_on_experiment_id_and_eid on ml_candidates  (cost=0.43..7.67 rows=6 width=8) (actual time=0.194..0.971 rows=21 loops=1)
                                      Index Cond: ((experiment_id = <experiment_id>) AND (eid >= '<eid_start>'::uuid))
                                ->  Index Scan using index_ml_candidate_metadata_on_candidate_id_and_name on ml_candidate_metadata ml_candidate_metadata_1  (cost=0.43..8.19 rows=8 width=16) (actual time=0.996..153.581 rows=380 loops=21)
                                      Index Cond: (candidate_id = ml_candidates.id)
        ->  Index Scan using ml_candidate_metadata_pkey on ml_candidate_metadata  (cost=0.43..3.45 rows=1 width=14) (actual time=0.235..0.235 rows=1 loops=7979)
              Index Cond: (id = "ANY_subquery".id)
Execution Time: 5.141 s

Disk reads take 5.06 s of the 5.1 s: the statement reads 5,287 pages for 7,979 rows. The rows are small (about 90 bytes), but they are spread out, about 2 per heap page. Warm, the same statement takes 77 ms and writes 0.4 MB of WAL.

4. WAL per batch of 100 ordinary runs (current cascade, rolled back; the worker splits the same rows across the child statements and the run statement):

Batch	Rows deleted (runs + metrics + metadata)	Time	WAL
Experiment with the most runs, mid-experiment	100 + 100 + 386	253 ms	3.0 MB
Experiment with more metrics per run, cold	100 + 1,300 + 400	902 ms	10.7 MB

eid is random, so the 100 runs of a batch sit on about 100 heap pages, and each page change after a checkpoint writes a full-page image. Batching by id would write less WAL but needs an (experiment_id, id) index on ml_candidates, which the saving does not justify. A job stops at 100,000 rows, so it writes at most a few hundred MB before the health check runs again.

5. Delete the experiment after its runs

DELETE FROM "ml_experiments" WHERE "ml_experiments"."id" = <experiment_id>

Delete on ml_experiments  (cost=0.29..3.31 rows=0 width=0) (actual time=0.015..0.015 rows=0 loops=1)
  ->  Index Scan using ml_experiments_pkey on ml_experiments  (cost=0.29..3.31 rows=1 width=6) (actual time=0.008..0.008 rows=1 loops=1)
        Index Cond: (id = <experiment_id>)
Trigger for constraint fk_56d6ed4d3d: time=4.424 calls=1
Trigger for constraint fk_rails_6b39844d44: time=4.975 calls=1
Execution Time: 9.435 ms

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 Oct 27, 2026 by Eduardo Bonet

Add a background worker that deletes ML experiments in bounded slices

What does this MR do and why?

References

Screenshots or screen recordings

How to set up and validate locally

MR acceptance checklist

Merge request reports