security_findings partition detach permanently blocked by a single un-purged scan at partition tail

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

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 (closed) / !205123 (merged). 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

The two-part defect

Part 1 — detach_partition? inspects only the last finding

ee/app/models/security/finding.rb:

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:

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):

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:

# 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":

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:

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 — 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:

    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:

    UPDATE security_scans SET status = 6 WHERE id IN (<rogue_ids>);

  3. Trigger detach:

    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

/cc @ghavenga (author of the #569397 (closed) fix)

Edited by 🤖 GitLab Bot 🤖