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_deriskfeature 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 (:instancefor instance-level exports). With the flag off, the export runs exactly as onmaster. - Add a
preload_notes:option toVulnerabilities::Read.with_export_entities, defaulting totrue. With the flag on, the export passesfalseso the scope skips thenotespreload. The export service is the scope's only caller. - Add
Vulnerability.preload_latest_notes_for_summary(vulnerabilities, limit:). The export calls it withNOTES_PER_VULNERABILITY_LIMIT = 20. It:- Loads the notes with one
LATERALquery over the vulnerability IDs. Each vulnerability reads its latest 20 notes backwards fromindex_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 skipsnote_html. - Preloads
authorandupdated_byfor the loaded notes and assigns the notes to each vulnerability'snotesassociation.Vulnerability#notes_summaryruns no extra queries.
- Loads the notes with one
- 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_summarytakes 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 textN earlier notes not included. When the limit leaves out more than 100 notes, the text isMore 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 notesPlan (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 sWarm (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 msAfter: 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 notesPlan (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: -1763980089561201758Why 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" ASCPlan (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 sWarm (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 msPlan (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 sWarm (command id 506012):
Buffers: shared hit=13511
Time: 48.892 ms
- execution: 31.206 msLookup 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_summaryfrom 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_htmlornotes.*. - Adding a note by a new author and editor doesn't add queries.
- The Comments column matches
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
Confirmedstatus. - When the limit cuts a discussion whose first kept reply is
going to patch this, the reply doesn't getpatchas 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_entitiesstill runs 9 queries, and 8 withpreload_notes: false.- With
vulnerability_export_latest_notes_onlydisabled,export_service_spec.rbchecks that the export keeps all notes even with the limit stubbed to 1, and loads them with the originalnotes.*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.