Make the security scan purge worker drain-and-self-throttle (#606136)

What does this MR do and why?

security_findings partitions cannot detach while an un-purged security_scans record sits at a partition tail. On high-volume instances Security::PurgeScansService never clears that tail, so partitions stick and Sec storage grows without bound. This is the incident behind #606136, where a manual force-purge reclaimed roughly 333GB.

The old purge worker starved. It ran weekends only (cron "0 */4 * * 6,0"), capped each run at 200K scans, and walked a forward-only keyset cursor with a 24h TTL. A busy instance outran it, the cursor reset to the head before it reached the newest stale scans, and the tail was perpetually stranded.

This started as two MRs (a scheduling half and a tail-resilient-cursor half). Working it through, the cursor tweak turned out to be the wrong lever, because a smarter cursor is still a fixed-rate worker fighting an unbounded backlog, so it can always be outrun at enough volume. Both halves are now one change here, and the separate cursor MR (!249177 (closed)) is closed. The worker no longer depends on a cursor trick or a quiet weekend. It runs often, drains continuously from a persisted cursor, and yields to database health.

What is always-on vs behind the flag

To be clear about scope, since only part of this is flag-gated:

1. Always-on (ships unconditionally): the daily cadence, the bounded-runtime run, the self-re-enqueuing drain, and the exclusive lease. With the flag off, the worker behaves as it did before on a per-batch basis (fixed MAX_BATCH_SIZE, no health queries) but now runs daily and re-enqueues itself to work through a backlog.
2. Behind security_scans_purge_db_health_check (ops, default off): only the database-health modulation (the pre-run gate, the mid-run hard-stop check, and the batch-size/backoff policy). With the flag off, no health is evaluated at all.

The change

1. Daily eligibility. Cron moves from weekends-only "0 */4 * * 6,0" to "0 */3 * * *" (every 3 hours, every day), so the worker is eligible often enough to keep up.
2. Self-re-enqueuing drain. Each run is bounded (MAX_RUNTIME 3 minutes, plus the existing 200K count cap). If a run stops on a bound with stale work still remaining, it re-enqueues itself. Forward progress is guaranteed by the persisted keyset cursor, not by a depth or attempt counter: each run resumes strictly after the last purged (created_at, id) tuple, so the chain always advances and cannot re-purge the same rows. Runs are wrapped in an exclusive lease, so a cron tick landing mid-drain (or vice versa) no-ops instead of double-purging. The frequent cron is the safety net if a chain ever stops early.
3. Health-gated execution (flag-gated). Behind the ops flag, the worker consults Gitlab::Database::HealthStatus (the same primitive that throttles batched background migrations). The batch size for a run is chosen once at the start of that run from the current health reading: full MAX_BATCH_SIZE when healthy, MIN_BATCH_SIZE (with a small inter-batch pause) under moderate pressure. It does not resize within a run. Mid-run, health is re-checked periodically and can hard-stop the run, but does not shrink the batch. Adaptivity is across runs: the next run re-probes and ramps back up once the database has recovered. Indicators: AutovacuumActiveOnTable on security_scans and security_findings, plus global WriteAheadLog and PatroniApdex.

The cursor TTL moves from 24h to 8 days so drain progress survives across the many short runs a large backlog takes, instead of resetting to the head.

What this does and does not promise

This keeps the purge draining continuously and lets it react to load. It does not by itself guarantee an instance is caught up: if new stale scans are produced faster than the chain drains, the tail can still lag, though far less than under the weekend-only schedule. For an instance that is already deep in the hole (the #606136 situation), the immediate relief is still the manual/force purge that reclaimed the ~333GB; this change is what keeps it from getting back there.

A note on retry: false

The worker sets sidekiq_options retry: false deliberately. It is self-re-enqueuing and cursor-backed, so a failed run should not fan out into Sidekiq retries. The persisted cursor plus the 3-hourly cron resume the drain cleanly on the next tick.

Database

The only new/changed query is the batch purge in Security::PurgeScansService#purge. The stale set is Security::Scan.stale, which is created_at < cutoff AND status <> :purged. update_all ignores the LIMIT on the keyset batch relation, so the update is scoped to the batch's rows via a bounded id subquery:

UPDATE security_scans
SET status = 6
WHERE security_scans.id IN (
  SELECT security_scans.id
  FROM security_scans
  WHERE security_scans.status <> 6
    AND security_scans.created_at < '<retention_cutoff>'
  ORDER BY security_scans.created_at ASC, security_scans.id ASC
  LIMIT 100
)

(status = 6 is purged.) This matches the partial index index_security_scans_for_non_purged_records, btree (created_at, id) WHERE (status <> 6), so the inner select is a pure keyset index scan with no residual filter, bounded by LIMIT @batch_size (max 100) and resumed from the persisted (created_at, id) cursor. See the query plan in a comment below.

Files changed

1. ee/config/schedule.yml, cron weekend-only to every 3h daily.
2. ee/app/workers/security/scans/purge_worker.rb, exclusive lease, retry: false, self-re-enqueue while work remains.
3. ee/app/services/security/purge_scans_service.rb, bounded runtime, health-gated pre-run and mid-run modulation, batch-size ramp across runs, id-scoped batch update, Result outcome.
4. config/feature_flags/ops/security_scans_purge_db_health_check.yml, new ops flag, default off.
5. Specs for the service and worker (runtime and count bounds, drain, health hard-stop/moderate/healthy, flag-off parity, lease contention, re-enqueue matrix).

Feature flag

Flag	Type	Default
security_scans_purge_db_health_check	ops	off

Closes #606136