Load only the latest notes per vulnerability in CSV export

What does this MR do and why?

Behind the vulnerability_export_latest_notes_only feature flag, this MR makes the vulnerability CSV export load at most the latest 20 notes of each vulnerability, in one query per batch of 100 vulnerabilities, and select only the columns the Comments column needs. When the limit cuts a discussion with replies, a second query loads that discussion's first note so the replies keep their status.

Exports time out on projects where some vulnerabilities have thousands of notes (#627540). The export walks vulnerability_reads in batches of 100. For each batch, Vulnerabilities::Read.with_export_entities preloaded vulnerability: { notes: [:updated_by, :author] }. That loaded every note of all 100 vulnerabilities in one query and selected notes.*, including note_html. On a production clone, the query for the batch in the failing job took 47s cold. The statement timeout is 15s.

An earlier attempt at the export timeouts, !253454 (merged), optimized the security database scans, not the notes preload. The export filters rolled out in #581520 (closed) go through the same batches.

Why smaller batches weren't enough

The first version of this MR loaded notes in slices of 10 vulnerabilities. That didn't bound the query. A few vulnerabilities carry thousands of system notes each, so one slice of 10 still returned 41,442 notes and took 40.6s cold. Most of that time was random heap reads, about one page per row.

Changes

  • Add a gitlab_com_derisk feature flag, vulnerability_export_latest_notes_only, disabled by default. The export service checks it once per export segment, with the exported project or group as the actor (:instance for instance-level exports). With the flag off, the export runs exactly as on master.
  • Add a preload_notes: option to Vulnerabilities::Read.with_export_entities, defaulting to true. With the flag on, the export passes false so the scope skips the notes preload. The export service is the scope's only caller.
  • Add Vulnerability.preload_latest_notes_for_summary(vulnerabilities, limit:). The export calls it with NOTES_PER_VULNERABILITY_LIMIT = 20. It:
    • Loads the notes with one LATERAL query over the vulnerability IDs. Each vulnerability reads its latest 20 notes backwards from index_notes_on_noteable_id_noteable_type_and_id. The IDs are inlined as an array because notes and vulnerabilities are in different databases.
    • Selects only EE::Vulnerability::NOTES_SUMMARY_COLUMNS, so it skips note_html.
    • Preloads author and updated_by for the loaded notes and assigns the notes to each vulnerability's notes association. Vulnerability#notes_summary runs no extra queries.
  • Keep each reply's status when the limit cuts a discussion. The Comments column gives every reply the status taken from the first note of its discussion, usually a status system note. For vulnerabilities that reach the limit, a second query loads the first notes of the discussions that may have been cut, and notes_summary takes the status from them. Only discussion notes can have replies, and a system note always starts its discussion, so the query only looks up discussions whose loaded notes are all user replies or comments.
  • Mark the notes that the limit leaves out. The latest notes query also counts the notes of each vulnerability, up to limit + OMITTED_NOTES_COUNT_LIMIT + 1. With the export limit of 20, that is 121. The count is in the same query, so a batch has no extra query. When the limit leaves out notes, the Comments cell starts with an entry that has an empty date, author and status, and the text N earlier notes not included. When the limit leaves out more than 100 notes, the text is More than 100 earlier notes not included.
  • Document the limit and the marker on the Comments field in doc/user/application_security/vulnerability_report/_index.md, with a version history entry for 19.7 and a feature flag note. The flag definition's milestone is also 19.7.

Effect on the CSV

With the flag off, nothing changes. With the flag on:

  • A vulnerability with 20 notes or fewer gets the same Comments column as before.
  • A vulnerability with more than 20 notes gets only its latest 20. The cell starts with an entry that gives the number of notes left out. If the cut falls inside a discussion, the kept replies still show their status, but the discussion's earlier notes do not appear.

This MR doesn't close #627540. I'll close it after I've checked the export in production.

Feature flag

  • Flag: vulnerability_export_latest_notes_only (gitlab_com_derisk, disabled by default)
  • Rollout issue: #631655

Limits

  • The Comments column keeps the latest 20 notes of each vulnerability. System notes count toward the 20.
  • The marker gives the exact number of notes left out, up to 100. The section "Why the count stops at 100" explains this limit.
  • The export has no new option.

I asked product to confirm these defaults by 2026-11-11 (comment). #639066 proposes an instance setting for the number of notes, and an export of all notes for users who need the full history.

Database review

This MR has no migrations or schema changes. The After plans ran on a gitlab-production-main clone through Database Lab (postgres.ai joe). The joe results don't include links, so each plan lists its command id, with vulnerability and discussion IDs redacted. The Before plan is from the root cause comment in the issue.

Before

SELECT "notes".*
FROM "notes"
WHERE "notes"."noteable_type" = 'Vulnerability'
  AND "notes"."noteable_id" IN ({...ids redacted...})
Plan (the 100 vulnerabilities in the failing job's batch, cold)
Index Scan using index_notes_on_noteable_id_and_noteable_type_system_author_id on notes (actual time=9.240..47230.311 rows=41073 loops=1)
  Buffers: shared hit=4390 read=37061

Time: 47.260 s (cold)
Time: 60 ms (warm)

After: latest notes

Generated from the code with rails runner:

SELECT "notes"."id", "notes"."type", "notes"."note", "notes"."noteable_type",
       "notes"."noteable_id", "notes"."discussion_id", "notes"."system",
       "notes"."author_id", "notes"."updated_by_id", "notes"."created_at",
       "notes"."updated_at", "notes"."last_edited_at"
FROM unnest(ARRAY[{...ids redacted...}]::bigint[]) AS vulnerability_ids(id),
LATERAL (
  SELECT "notes"."id", "notes"."type", "notes"."note", "notes"."noteable_type",
         "notes"."noteable_id", "notes"."discussion_id", "notes"."system",
         "notes"."author_id", "notes"."updated_by_id", "notes"."created_at",
         "notes"."updated_at", "notes"."last_edited_at"
  FROM "notes"
  WHERE "notes"."noteable_type" = 'Vulnerability'
    AND "notes"."noteable_id" = vulnerability_ids.id
  ORDER BY "notes"."id" DESC
  LIMIT 20
) AS notes
Plan (100 vulnerabilities, cold and warm)

This batch isn't the failing job's batch. It's the 100 open vulnerabilities with the lowest IDs in a large project, read in the same ID order the export uses. Together they have about 348k notes, compared with 41,073 notes for the Before batch.

Cold (command id 505432):

Nested Loop  (cost=0.71..3005.97 rows=2000 width=246) (actual time=0.124..1328.233 rows=1660 loops=1)
  Buffers: shared hit=1119 read=1044 dirtied=99
  I/O Timings: read=1312.449 write=0.000
  ->  Function Scan on pg_catalog.unnest vulnerability_ids  (cost=0.00..1.00 rows=100 width=8) (actual time=0.011..0.070 rows=100 loops=1)
        Function Call: unnest('{...ids redacted...}'::bigint[])
  ->  Limit  (cost=0.70..29.85 rows=20 width=246) (actual time=3.106..13.275 rows=17 loops=100)
        Buffers: shared hit=1119 read=1044 dirtied=99
        ->  Index Scan Backward using index_notes_on_noteable_id_noteable_type_and_id on public.notes  (cost=0.70..38.59 rows=26 width=246) (actual time=3.105..13.270 rows=17 loops=100)
              Index Cond: ((notes.noteable_id = vulnerability_ids.id) AND ((notes.noteable_type)::text = 'Vulnerability'::text))
              Buffers: shared hit=1119 read=1044 dirtied=99

Time: 1.348 s
  - planning: 19.496 ms
  - execution: 1.329 s
    - I/O read: 1.312 s

Warm (command id 505434):

Nested Loop  (cost=0.71..3005.97 rows=2000 width=246) (actual time=0.104..4.010 rows=1660 loops=1)
  Buffers: shared hit=2163

Time: 21.874 ms
  - planning: 17.753 ms
  - execution: 4.121 ms

After: latest notes and the capped count

This is the query that the export runs. Generated from the code with rails runner:

SELECT "notes"."id", "notes"."type", "notes"."note", "notes"."noteable_type",
       "notes"."noteable_id", "notes"."discussion_id", "notes"."system",
       "notes"."author_id", "notes"."updated_by_id", "notes"."created_at",
       "notes"."updated_at", "notes"."last_edited_at",
       capped_notes_counts.total AS capped_notes_count
FROM unnest(ARRAY[{...ids redacted...}]::bigint[]) AS vulnerability_ids(id),
LATERAL (
  SELECT COUNT(*) AS total
  FROM (
    SELECT 1
    FROM "notes"
    WHERE "notes"."noteable_type" = 'Vulnerability'
      AND "notes"."noteable_id" = vulnerability_ids.id
    ORDER BY "notes"."id" DESC
    LIMIT 121
  ) AS capped_notes
) AS capped_notes_counts,
LATERAL (
  SELECT "notes"."id", "notes"."type", "notes"."note", "notes"."noteable_type",
         "notes"."noteable_id", "notes"."discussion_id", "notes"."system",
         "notes"."author_id", "notes"."updated_by_id", "notes"."created_at",
         "notes"."updated_at", "notes"."last_edited_at"
  FROM "notes"
  WHERE "notes"."noteable_type" = 'Vulnerability'
    AND "notes"."noteable_id" = vulnerability_ids.id
  ORDER BY "notes"."id" DESC
  LIMIT 20
) AS notes
Plan (100 vulnerabilities, cold, command id 575098)

These are the same 100 vulnerabilities as in the latest notes plan above (command id 505432). This plan ran on a new clone, so it is cold.

 Nested Loop  (cost=5.26..3550.35 rows=2000 width=257) (actual time=101.477..2815.002 rows=1667 loops=1)
   Buffers: shared hit=9261 read=2357 dirtied=209
   WAL: records=359 fpi=209 bytes=1648129
   I/O Timings: read=2710.960 write=0.000
   ->  Nested Loop  (cost=4.55..458.00 rows=100 width=16) (actual time=98.626..1529.356 rows=100 loops=1)
         Buffers: shared hit=8113 read=1329 dirtied=192
         WAL: records=342 fpi=192 bytes=1515984
         I/O Timings: read=1433.816 write=0.000
         ->  Function Scan on pg_catalog.unnest vulnerability_ids  (cost=0.00..1.00 rows=100 width=8) (actual time=0.012..0.068 rows=100 loops=1)
               Function Call: unnest('{...ids redacted...}'::bigint[])
               I/O Timings: read=0.000 write=0.000
         ->  Aggregate  (cost=4.55..4.56 rows=1 width=8) (actual time=15.291..15.291 rows=1 loops=100)
               Buffers: shared hit=8113 read=1329 dirtied=192
               WAL: records=342 fpi=192 bytes=1515984
               I/O Timings: read=1433.816 write=0.000
               ->  Limit  (cost=0.70..4.22 rows=26 width=12) (actual time=2.873..15.281 rows=100 loops=100)
                     Buffers: shared hit=8113 read=1329 dirtied=192
                     WAL: records=342 fpi=192 bytes=1515984
                     I/O Timings: read=1433.816 write=0.000
                     ->  Index Only Scan Backward using index_notes_on_noteable_id_noteable_type_and_id on public.notes  (cost=0.70..4.22 rows=26 width=12) (actual time=2.872..15.271 rows=100 loops=100)
                           Index Cond: ((notes.noteable_id = vulnerability_ids.id) AND (notes.noteable_type = 'Vulnerability'::text))
                           Heap Fetches: 431
                           Buffers: shared hit=8113 read=1329 dirtied=192
                           WAL: records=342 fpi=192 bytes=1515984
                           I/O Timings: read=1433.816 write=0.000
   ->  Limit  (cost=0.70..30.72 rows=20 width=249) (actual time=1.010..12.847 rows=17 loops=100)
         Buffers: shared hit=1148 read=1028 dirtied=17
         WAL: records=17 fpi=17 bytes=132145
         I/O Timings: read=1277.144 write=0.000
         ->  Index Scan Backward using index_notes_on_noteable_id_noteable_type_and_id on public.notes notes_1  (cost=0.70..39.73 rows=26 width=249) (actual time=1.010..12.821 rows=17 loops=100)
               Index Cond: ((notes_1.noteable_id = vulnerability_ids.id) AND ((notes_1.noteable_type)::text = 'Vulnerability'::text))
               Buffers: shared hit=1148 read=1028 dirtied=17
               WAL: records=17 fpi=17 bytes=132145
               I/O Timings: read=1277.144 write=0.000
Settings: jit = 'off', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '472585MB', work_mem = '230MB'
Query ID: -1763980089561201758

Why the count stops at 100

The count reads the index entries of the notes of each vulnerability, and some vulnerabilities have thousands. All three plans use the same 100 vulnerabilities. The plans for the exact count and for a limit of 1,000 used an earlier alias for the count subquery. The query is otherwise the same.

Count Command id Buffers (hit + read) Reads Time
Exact, no limit 573484 279,065 (~2.1 GiB) 18,737 19.2 s cold
Up to 1,000 573492 67,913 (~531 MiB) 7,203 391 ms
Up to 100 (this MR) 575098 11,618 (~91 MiB) 2,357 2.83 s cold

Compare the buffers, not the times. The cache state is different for each run.

The exact count takes longer than the 15 s statement timeout. A limit of 1,000 touches about 6 times as many pages as a limit of 100 (67,913 compared to 11,618), and does 3 times as many reads. The only change to the marker is "More than 1,000" instead of "More than 100". With a limit of 100, the count touches 9,442 pages (1,329 reads), and the latest notes touch 2,176 pages (1,028 reads).

After: first notes of cut discussions

This query only runs for vulnerabilities that reach the limit, and only for discussions whose loaded notes are all user discussion notes. It doesn't run for the batch above, because all 1,660 loaded notes are individual system notes.

index_notes_on_discussion_id has no id column, so the query reads every note of each discussion it looks up. Its cost is the total number of notes in those discussions. A sample of 103 threaded vulnerability discussions had 206 notes, about 2 each (command id 505874). That sample includes status threads, which this query now skips. A second plan below uses 2,000 vulnerability discussions that contain user discussion notes (4,029 notes). 2,000 is the most discussions one batch can ask for. A single very long thread still makes this query read all of its notes, as every export did before this MR. An index on notes (discussion_id, id) would bound it, but notes is a large table, so I've left that out of this MR.

SELECT DISTINCT ON (notes.discussion_id) notes.id, notes.discussion_id, notes.note
FROM "notes"
WHERE "notes"."discussion_id" IN ({...discussion ids redacted...})
ORDER BY "notes"."discussion_id" ASC, "notes"."id" ASC
Plan (2,000 one-note discussions, cold and warm)

This plan shows the query's shape and a lower bound, not the worst case. 2,000 is the most discussions one batch can ask for (100 vulnerabilities × 20 notes). I used the 2,000 most recent vulnerability discussions, stored in a scratch table on the clone and passed as = ANY(ARRAY(SELECT ...)), which runs the same index scan as the IN list. Each is a single individual note, which the code doesn't look up, and a real cut thread has at least two notes. Creating the scratch table read some of the same pages, so the cold run is probably a bit optimistic.

Cold (command id 505689):

Unique  (cost=98.25..1903.70 rows=1141 width=188) (actual time=130.411..7651.596 rows=2000 loops=1)
  Buffers: shared hit=6338 read=5684
  I/O Timings: read=7599.590 write=0.000
  ->  Incremental Sort  (cost=2.25..1804.85 rows=1141 width=188) (actual time=130.410..7651.143 rows=2000 loops=1)
        Sort Key: notes.discussion_id, notes.id
        Presorted Key: notes.discussion_id
        ->  Index Scan using index_notes_on_discussion_id on public.notes  (cost=0.70..1753.51 rows=1141 width=188) (actual time=9.721..7648.689 rows=2000 loops=1)
              Index Cond: ((notes.discussion_id)::text = ANY (((InitPlan 1).col1)::text[]))

Time: 7.668 s
  - execution: 7.652 s
    - I/O read: 7.600 s

Warm (command id 505704):

Unique  (actual time=1.579..30.726 rows=2000 loops=1)
  Buffers: shared hit=12022

Time: 48.268 ms
  - execution: 30.861 ms
Plan (2,000 discussions with user discussion notes, cold and warm)

Same method as above, with a scratch table of 2,000 vulnerability discussions that contain at least one user DiscussionNote.

Cold (command id 506010):

Unique  (cost=98.25..1903.70 rows=1141 width=188) (actual time=99.055..10754.927 rows=2000 loops=1)
  ->  Index Scan using index_notes_on_discussion_id on public.notes  (cost=0.70..1753.51 rows=1141 width=188) (actual time=10.908..10746.677 rows=4029 loops=1)
  Buffers: shared hit=4923 read=8588

Time: 10.838 s
  - execution: 10.755 s

Warm (command id 506012):

Buffers: shared hit=13511

Time: 48.892 ms
  - execution: 31.206 ms

Lookup worst case

The first-note lookup's realistic worst case is about 10.8s cold and 49ms warm, for a batch with 2,000 discussions that have user replies. That's under the 15s statement timeout. On master, the notes preload for the failing batch times out outright. I'm accepting this cost for now and will revisit the lookup once product decides whether truncated replies should keep their status.

Summary

Query Batch Rows Buffers Cold Warm (total)
Before Failing job's 100 vulnerabilities 41,073 41,451 47.3s 60ms
Slices of 10 (first version of this MR) 10 vulnerabilities 41,442 41,533 40.6s 74.7ms
After: latest notes 100 vulnerabilities, about 348k notes 1,660 2,163 1.35s 21.9ms
After: latest notes and the capped count Same 100 vulnerabilities 1,667 11,618 2.83s not measured
After: first notes of cut discussions 2,000 one-note discussions (lower bound) 2,000 12,022 7.7s 48.3ms
After: first notes of cut discussions 2,000 discussions with user discussion notes 2,000 (4,029 notes read) 13,511 10.8s 48.9ms

The cold times are almost all I/O on a clone with an empty cache. The latest-notes query reads at most 2,000 rows per batch, however many notes a vulnerability has. The first-notes query has no such bound, since it reads whole threads. It only runs for vulnerabilities at the limit whose loaded notes include user-only discussions, and it didn't run for the batch I measured. I'm not sure how the cold numbers translate to the production replicas, so I'll check the export logs after this is deployed.

Testing

Specs run with the flag on by default.

  • ee/spec/services/vulnerability_exports/export_service_spec.rb, using a system note, a threaded discussion with an edited reply, and a multi-line note:
    • The Comments column matches notes_summary from a fully loaded association.
    • With the limit stubbed to 1, a vulnerability with 3 notes exports only its latest note.
    • A batch runs one notes query, which doesn't select note_html or notes.*.
    • Adding a note by a new author and editor doesn't add queries.
  • ee/spec/models/ee/vulnerability_spec.rb, for .preload_latest_notes_for_summary:
    • One notes query, with no extra query for individual notes or for a discussion with a loaded system note.
    • The latest notes are kept, ordered by ID, and a vulnerability without notes gets an empty loaded association.
    • When the limit cuts a discussion that starts with a status system note, the kept reply keeps the Confirmed status.
    • When the limit cuts a discussion whose first kept reply is going to patch this, the reply doesn't get patch as its status.
    • When the limit leaves out notes, the summary starts with the number of notes left out.
    • The count is exact up to OMITTED_NOTES_COUNT_LIMIT, and the marker says "More than" after that.
    • When the limit leaves out no notes, the summary has no marker.
  • ee/spec/models/vulnerabilities/read_spec.rb: with_export_entities still runs 9 queries, and 8 with preload_notes: false.
  • With vulnerability_export_latest_notes_only disabled, export_service_spec.rb checks that the export keeps all notes even with the limit stubbed to 1, and loads them with the original notes.* preload.
  • With the flag enabled only for the exported project, the export uses the limit.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist.

Edited by Gregory Havenga

Merge request reports

Loading
Loading