<!--IssueSummary start--> <details> <summary> Everyone can contribute. [Help move this issue forward](https://handbook.gitlab.com/handbook/marketing/developer-relations/contributor-success/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=606136) </details> <!--IssueSummary end--> # Summary The `security_findings` partition detach logic can be permanently blocked by a single `security_scans` record that misses purge processing. Because `SlidingListStrategy#extra_partitions` uses `take_while`, one blocked partition halts detach evaluation for **all** subsequent stale partitions. This causes unbounded storage growth on instances with sufficient scan volume. This is a follow-up defect to #569397 / !205123. That fix ensured partitions rotate correctly, but the detach mechanism has this separate defect that surfaces when the purge worker misses individual scans. # Environment - GitLab 18.11.3-ee, self-managed, EKS - PostgreSQL 16.10 on Amazon RDS - Zendesk ticket: [735407](https://gitlab.zendesk.com/agent/tickets/735407) # The two-part defect ## Part 1 — `detach_partition?` inspects only the last finding [`ee/app/models/security/finding.rb`](https://gitlab.com/gitlab-org/gitlab/-/blob/master/ee/app/models/security/finding.rb#L242): ```ruby def detach_partition?(partition_number) last_finding_in_partition(partition_number)&.scan&.findings_can_be_purged? end def last_finding_in_partition(partition_number) where(partition_number: partition_number).last end ``` If the single "last" finding's scan is not `status: :purged`, the whole partition is treated as non-detachable, regardless of the state of every other row. ## Part 2 — `SlidingListStrategy#extra_partitions` uses `take_while` [`lib/gitlab/database/partitioning/sliding_list_strategy.rb`](https://gitlab.com/gitlab-org/gitlab/-/blob/master/lib/gitlab/database/partitioning/sliding_list_strategy.rb): ```ruby extra = possibly_extra.take_while { |p| detach_partition_if.call(p) } ``` `take_while` stops iterating on the first `false`. Any partition that returns false blocks detach evaluation for all higher-numbered partitions permanently — even if they are perfectly eligible. **Combined effect**: one lagging scan → one blocked partition → all subsequent stale partitions accumulate indefinitely. # Concrete evidence from the incident Instance had 14 stale partitions (numbered 9–22), each 100–120 GB. Sampling the last finding's scan status per partition: | Partition | Last scan ID | Status | Scan age | Detach eligible? | |-----------|--------------|--------|----------|------------------| | 9 | 3187985 | 1 (succeeded) | 7 mo | age yes, status no → **blocked** | | 10 | 1996915 | 6 (purged) | 6 mo | **yes** → but blocked by 9 | | 11 | 1997354 | 1 (succeeded) | 5 mo | age yes, status no → blocked | | 12 | 4626690 | 1 (succeeded) | 4.5 mo | age yes, status no → blocked | | 13 | 2186782 | 1 (succeeded) | 4 mo | age yes, status no → blocked | | 14 | 2592395 | 6 (purged) | 3.5 mo | **yes** → but blocked by 9 | | 15 | 2600903 | 6 (purged) | 3 mo | not yet stale (\~85 days) | | 16-22 | various | mostly 1 | \< 90 days | not yet stale | | 23 | (active) | — | current | active | Sampling the tail of blocked partitions confirmed **only 1-2 findings from a single un-purged scan** were at the partition tail — everything else in the partition was already `status: 6`. Example from partition 9 (122 GB total): ```sql SELECT s.id, s.status, s.created_at FROM security_findings sf JOIN security_scans s ON s.id = sf.scan_id WHERE sf.id BETWEEN 1811739946 AND 1811839946 -- last 100K IDs of partition 9 AND s.status != 6; id | status | created_at ---------+--------+------------------------------- 3187985 | 1 | 2025-12-16 08:58:07.845025+00 3187985 | 1 | 2025-12-16 08:58:07.845025+00 ``` **One scan holding 122 GB attached.** # Proof of the `take_while` mechanism Direct reproduction in Rails console during the incident: ```ruby # Initial state — nothing detachable, despite 3 partitions meeting all criteria Security::Finding.partitioning_strategy.extra_partitions.map { |p| p.value } => [] # Force-purge the rogue scan in partition 9 only: # UPDATE security_scans SET status = 6 WHERE id = 3187985; # Framework advances past partition 9, evaluates 10, then halts at 11: Security::Finding.partitioning_strategy.extra_partitions.map { |p| p.value } => [9, 10] # Force-purge rogue scans in 11, 12, 13: # UPDATE security_scans SET status = 6 WHERE id IN (1997354, 4626690, 2186782); # Framework evaluates through 14, halts at 15 (which is <90 days old, correct): Security::Finding.partitioning_strategy.extra_partitions.map { |p| p.value } => [9, 10, 11, 12, 13, 14] ``` Each `UPDATE` unblocked exactly one partition of downstream evaluation. Definitive proof of the `take_while` halt-on-first-false behaviour. # How scans get stuck at non-purged status past retention Not investigated in depth during the incident. `PurgeScansService.purge_stale_records` uses `Security::Scan.stale.ordered_by_created_at_and_id` with a Redis cursor (`LAST_PURGED_SCAN_TUPLE`, 24h TTL) and batches of 100 with `MAX_STALE_SCANS_SIZE = 200_000` per run. Possible mechanisms: - Cursor persistence anomaly leaves individual records behind if a run crashes mid-batch. - A specific scan hits a validation/trigger error during `update_all(status: :purged)` and gets silently skipped. - The `stale` scope excludes something we haven't spotted. Either way, once a scan is \>90 days old, in the stale scope, but stuck at status != 6, the detach machinery cannot recover on its own. # Proposed fixes ## Fix A — `detach_partition?` should evaluate the whole partition, not just the last row Change the check from "the last finding's scan is purged" to "no scan in this partition is both stale-eligible and un-purged": ```ruby def detach_partition?(partition_number) return false unless oldest_record_stale?(partition_number) # existing 18.5 semantics !by_partition_number(partition_number) .joins(:scan) .where.not(scan: { status: :purged }) .exists? end ``` Cost: one exists check per partition per manager run. On indexed data this is cheap. ## Fix B — `SlidingListStrategy#extra_partitions` should not use `take_while` Replace with `select` or `filter_map`. One blocked partition should not halt evaluation of others: ```ruby extra = possibly_extra.select { |p| detach_partition_if.call(p) } ``` There may have been a reason for `take_while` (ordering invariant assumption). If so, it should be documented and the sliding-list contract clarified. Otherwise, filter is safer. ## Fix C (defence in depth) — investigate `PurgeScansService` cursor/batch semantics Separate issue-worthy. `LAST_PURGED_SCAN_TUPLE` cursor with 24h TTL, 100-row batches, and 200K per-run cap has enough moving parts to leave individual rows unprocessed. Deserves independent audit. # Workaround (documented for anyone hitting this) The [existing workaround in #569397](https://gitlab.com/gitlab-org/gitlab/-/work_items/569397) — TRUNCATE the offending partition — works but is heavy-handed. A less invasive workaround verified during this incident: 1. Identify the rogue scan(s) at the tail of each stuck stale partition: ```sql SELECT s.id, s.status, s.created_at FROM security_findings sf JOIN security_scans s ON s.id = sf.scan_id WHERE sf.id BETWEEN <last_id - 100000> AND <last_id> AND s.status != 6; ``` 2. Force-purge them: ```sql UPDATE security_scans SET status = 6 WHERE id IN (<rogue_ids>); ``` 3. Trigger detach: ```ruby Gitlab::Database::Partitioning::PartitionManager.new(Security::Finding).sync_partitions ``` 4. Wait 7 days for `Postgresql::DetachedPartitionDropper`, or manually `DROP TABLE` the detached standalone tables to reclaim storage immediately. # References - Ticket: [ZD 735407](https://gitlab.zendesk.com/agent/tickets/735407) (internal) - Original defect: #569397 - Partial fix: !205123 /cc @ghavenga (author of the #569397 fix)