Prune security_findings partition fanout in has_vulnerabilities check

Gregory Havengarequested to merge
ghavenga/29649-prune-security-findings-partition into master

What / Why

Corrective action for INC-13566 (gitlab-com/gl-infra/production-engineering#29649).

The has_vulnerabilities attribute in Security::VulnerabilityReportDataEntity ran pipeline.security_findings.exists? with no partition_number predicate. This attribute is rendered on every EE pipeline page tab. security_findings is PARTITION BY LIST (partition_number), so the query could not prune and the planner had to consider every partition. During the incident this query picked a parallel sequential scan that removed about 483M rows by filter.

Fix

With prune_security_findings_partition enabled:

  1. If the pipeline has no security scans, has_vulnerabilities returns "false" without querying security_findings at all.
  2. Otherwise the check adds by_partition_number(first_scan.findings_partition_number), so the predicate lands on the partition key and Postgres prunes to the single partition that holds the pipeline's findings. The same pattern is already in production at scan_result_policies/findings_finder.rb:62.

The first scan comes from one security_scans.first query, the same query security_findings_partition_number runs, so the flag-on path runs no more queries than before. It runs one fewer when the pipeline has no scans.

With the flag disabled, behaviour is byte-identical to before.

No-scans short-circuit

pipeline.security_findings_partition_number (ee/app/models/ee/ci/pipeline.rb:266-267) falls back to Security::Finding.active_partition_number when the pipeline has no security scans. The active partition is the newest one, so it is the partition most likely to have no planner statistics yet. Stale or missing leaf statistics were the INC-13566 failure mode, and the incident pipeline 2796061674 has 0 security scans. Pruning alone would have sent every no-scans pipeline page to that newest partition. A pipeline with no scans cannot have findings, so the check now returns "false" without touching security_findings. This removes the security_findings query from every page view of a pipeline without scans.

The short-circuit sits inside the flag branch so that disabling the flag restores the exact previous query, which keeps the flag a single kill switch for the whole change.

Why pruning to one partition is correct

In short, a single pipeline's findings always live in exactly one partition, so pruning to it can never drop real results. This is a write-time invariant, not a read-time assumption.

Full correctness reasoning
  • On scan store (store_scan_service.rb:83) every scan is stamped with the same findings_partition_number. The first scan seeds it, and every later scan copies the seed via security_scans.first. If the sliding-list active partition rotates mid-store, later scans still copy the seed and do not re-read the active partition.
  • Each finding (store_findings_service.rb:76) inherits its scan's findings_partition_number, and partition_number is attr_readonly, so it can never change after insert.
  • Therefore all of a pipeline's findings are in the partition recorded on its first scan, which is the partition the check prunes to.
  • security_scans.findings_partition_number is NOT NULL, so the check needs no nil guard.

The multi-pipeline case (self and descendants) does not share this single-partition guarantee and is handled separately in !252313 (closed) with a multi-partition IN (...) set.

Query plans

Taken on postgres.ai gitlab-production-sec on 2026-09-28 against incident pipeline 2796061674, using the exact incident query.

Before (no partition_number predicate), Append over 29 partitions (security_findings_351 to security_findings_379):

EXPLAIN, Append over 29 partitions, planning 34.763 ms, execution 0.421 ms 
SELECT 1 AS one FROM "security_findings" INNER JOIN "security_scans" ON "security_findings"."scan_id" = "security_scans"."id" WHERE "security_scans"."pipeline_id" = 2796061674 LIMIT 1
Limit  (cost=1.01..317.25 rows=1 width=4)
  ->  Nested Loop  (cost=1.01..10436.99 rows=33 width=4)
        ->  Index Scan using index_security_scans_on_length_of_warnings on security_scans  (cost=0.57..10.36 rows=9 width=8)
              Index Cond: (pipeline_id = '2796061674'::bigint)
        ->  Append  (cost=0.44..905.25 rows=25326 width=8)
              ->  Index Only Scan using security_findings_351_scan_id_deduplicated_idx on security_findings_351 security_findings_1  (cost=0.44..35.71 rows=1657 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_352_scan_id_deduplicated_idx on security_findings_352 security_findings_2  (cost=0.44..50.31 rows=1524 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_353_scan_id_deduplicated_idx on security_findings_353 security_findings_3  (cost=0.44..32.54 rows=888 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_354_scan_id_deduplicated_idx on security_findings_354 security_findings_4  (cost=0.44..31.09 rows=929 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_355_scan_id_deduplicated_idx on security_findings_355 security_findings_5  (cost=0.56..23.71 rows=823 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_356_scan_id_deduplicated_idx on security_findings_356 security_findings_6  (cost=0.56..19.86 rows=817 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_357_scan_id_deduplicated_idx on security_findings_357 security_findings_7  (cost=0.56..28.44 rows=807 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_358_scan_id_deduplicated_idx on security_findings_358 security_findings_8  (cost=0.56..28.92 rows=769 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_359_scan_id_deduplicated_idx on security_findings_359 security_findings_9  (cost=0.57..26.44 rows=761 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_360_scan_id_deduplicated_idx on security_findings_360 security_findings_10  (cost=0.57..30.93 rows=853 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_361_scan_id_deduplicated_idx on security_findings_361 security_findings_11  (cost=0.56..26.56 rows=770 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_362_scan_id_deduplicated_idx on security_findings_362 security_findings_12  (cost=0.56..25.59 rows=802 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_363_scan_id_deduplicated_idx on security_findings_363 security_findings_13  (cost=0.56..26.61 rows=810 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_364_scan_id_deduplicated_idx on security_findings_364 security_findings_14  (cost=0.56..24.54 rows=803 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_365_scan_id_deduplicated_idx on security_findings_365 security_findings_15  (cost=0.57..17.48 rows=734 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_366_scan_id_deduplicated_idx on security_findings_366 security_findings_16  (cost=0.56..18.50 rows=805 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_367_scan_id_deduplicated_idx on security_findings_367 security_findings_17  (cost=0.56..24.64 rows=856 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_368_scan_id_deduplicated_idx on security_findings_368 security_findings_18  (cost=0.56..18.73 rows=813 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_369_scan_id_deduplicated_idx on security_findings_369 security_findings_19  (cost=0.56..17.86 rows=817 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_370_scan_id_deduplicated_idx on security_findings_370 security_findings_20  (cost=0.57..28.68 rows=931 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_371_scan_id_deduplicated_idx on security_findings_371 security_findings_21  (cost=0.56..34.30 rows=921 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_372_scan_id_deduplicated_idx on security_findings_372 security_findings_22  (cost=0.56..23.53 rows=840 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_373_scan_id_deduplicated_idx on security_findings_373 security_findings_23  (cost=0.57..30.13 rows=858 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_374_scan_id_deduplicated_idx on security_findings_374 security_findings_24  (cost=0.56..31.38 rows=859 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_375_scan_id_deduplicated_idx on security_findings_375 security_findings_25  (cost=0.57..19.93 rows=872 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_376_scan_id_deduplicated_idx on security_findings_376 security_findings_26  (cost=0.57..26.30 rows=888 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_377_scan_id_deduplicated_idx on security_findings_377 security_findings_27  (cost=0.56..25.74 rows=806 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_378_scan_id_deduplicated_idx on security_findings_378 security_findings_28  (cost=0.56..27.80 rows=796 width=8)
                    Index Cond: (scan_id = security_scans.id)
              ->  Index Only Scan using security_findings_379_scan_id_deduplicated_idx on security_findings_379 security_findings_29  (cost=0.44..22.37 rows=517 width=8)
                    Index Cond: (scan_id = security_scans.id)
stats:

Time: 35.184 ms
  - planning: 34.763 ms
  - execution: 0.421 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 4 (~32.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

After (partition_number = 379, the active partition), prunes to 1 partition:

EXPLAIN, single-partition scan, planning 4.966 ms, execution 0.056 ms 
SELECT 1 AS one FROM "security_findings" INNER JOIN "security_scans" ON "security_findings"."scan_id" = "security_scans"."id" WHERE "security_scans"."pipeline_id" = 2796061674 AND "security_findings"."partition_number" = 379 LIMIT 1
Limit  (cost=1.01..3796.78 rows=1 width=4)
  ->  Nested Loop  (cost=1.01..3796.78 rows=1 width=4)
        ->  Index Scan using index_security_scans_on_length_of_warnings on security_scans  (cost=0.57..10.36 rows=9 width=8)
              Index Cond: (pipeline_id = '2796061674'::bigint)
        ->  Index Scan using security_findings_379_scan_id_deduplicated_idx on security_findings_379 security_findings  (cost=0.44..415.54 rows=517 width=8)
              Index Cond: (scan_id = security_scans.id)
              Filter: (partition_number = 379)
stats:

Time: 5.022 ms
  - planning: 4.966 ms
  - execution: 0.056 ms
    - I/O read: 0.000 ms
    - I/O write: 0.000 ms

Shared buffers:
  - hits: 4 (~32.00 KiB) from the buffer pool
  - reads: 0 from the OS file cache, including disk I/O
  - dirtied: 0
  - writes: 0

Pruning takes the plan from 29 partitions to 1 and planning time from 34.763 ms to 4.966 ms. Pipeline 2796061674 has no scans, so with the short-circuit the flag-on path does not run this query for it at all. The pruned plan above is what runs for a pipeline that has scans. For that pipeline, the partition is the one recorded on its first scan, not the active one.

Feature flag

Gated behind prune_security_findings_partition (gitlab_com_derisk, default off). The rollout issue is #631155.

Testing

ee/spec/serializers/security/vulnerability_report_data_entity_spec.rb covers:

  • Flag on and flag off, with the same "true" / "false" results.
  • A QueryRecorder assertion that the emitted SQL carries "security_findings"."partition_number" = N when the flag is on and does not when it is off.
  • A QueryRecorder assertion that a pipeline with no security scans returns "false" and runs no security_findings SQL. This spec failed on the previous code (it ran the security_findings query) and passes with the short-circuit.

Verified locally, 11 examples, 0 failures.

Scope

Only the has_vulnerabilities check. The sibling has_security_findings_in_self_and_descendants? is !252313 (closed). Making pruning the default association behaviour is the broader follow-up !252326 (closed).

Edited by Gregory Havenga

Merge request reports