Reindex vulnerabilities when their due date changes
What does this MR do and why?
- Due dates are written with
upsert_all/delete_all, which skip callbacks, so a changed due date never reached the newdue_dateElasticsearch field from !266731 (merged). BulkSetDueDatesServicenow syncs the vulnerabilities whose due date actually changed, after each batch, including when a later write fails.- A new ES migration,
BackfillDueDateInVulnerabilities, reindexes vulnerabilities that already have a due date, so the filter in !266733 (merged) doesn't miss them.
Closes #638048. Design notes are in the issue.
How to test
Requires Elasticsearch in GDK.
- Set a due date on a vulnerability with the
bulkSetVulnerabilityFindingsDueDatesmutation. - Let the ES bookkeeping worker run, then check that the vulnerability's ES document has the new
due_date. - Set the same date again and check that nothing is queued for ES.
- Remove the due date and check that
due_dateis gone from the document.
Database queries and plans
All three run on the sec database. Values below are examples.
1. Backfill batch (Vulnerabilities::Read.with_due_date(after_finding_id:), from the ES migration). The migration pages by finding ID (document_primary_key is vulnerability_occurrence_id), and the cursor also applies inside the due dates subquery. Each 30,000-row batch runs this up to 30 times, 1,000 rows at a time (DEFAULT_LIMIT_PER_ITERATION in MigrationDatabaseBackfillHelper).
SELECT "vulnerability_reads".*
FROM "vulnerability_reads"
WHERE (vulnerability_occurrence_id > 0)
AND "vulnerability_reads"."vulnerability_occurrence_id" IN (
SELECT "vulnerability_finding_due_dates"."vulnerability_occurrence_id"
FROM "vulnerability_finding_due_dates"
WHERE (vulnerability_occurrence_id > 0)
)
ORDER BY "vulnerability_reads"."vulnerability_occurrence_id" ASC
LIMIT 1000- First page: https://console.postgres.ai/gitlab/gitlab-production-sec/sessions/85956/commands/192785 (18 ms, 5,088 buffers, all cached). @bala.kumar's run with a cold cache read 799 buffers in 78 ms (!266732 (comment 4208913262)).
- A page from the middle (cursor at the 9,001st due date): https://console.postgres.ai/gitlab/gitlab-production-sec/sessions/85956/commands/192787 (13 ms, 5,131 buffers).
- Postgres reads the next due dates from
index_vulnerability_finding_due_dates_on_occurrence_idfrom the cursor, looks up each read throughindex_vulnerability_reads_on_vulnerability_occurrence_id_unique, and stops after 1,000 (1,014 lookups). There is no sort: the rows come back in index order through a Gather Merge. - With about 18,500 due dates on GitLab.com, a full backfill is about 19 of these queries, about 96k buffers in total, all in one migration run.
completed? runs two more queries on every migration run:
-- documents_after_current_id.empty?
SELECT 1 AS one FROM "vulnerability_reads"
WHERE (vulnerability_occurrence_id > 0)
AND "vulnerability_reads"."vulnerability_occurrence_id" IN (
SELECT "vulnerability_finding_due_dates"."vulnerability_occurrence_id" FROM "vulnerability_finding_due_dates"
WHERE (vulnerability_occurrence_id > 0))
LIMIT 1
-- documents_after_current_id.maximum(:vulnerability_occurrence_id)
SELECT MAX("vulnerability_reads"."vulnerability_occurrence_id") FROM "vulnerability_reads"
WHERE (vulnerability_occurrence_id > 0)
AND "vulnerability_reads"."vulnerability_occurrence_id" IN (
SELECT "vulnerability_finding_due_dates"."vulnerability_occurrence_id" FROM "vulnerability_finding_due_dates"
WHERE (vulnerability_occurrence_id > 0))empty?: https://console.postgres.ai/gitlab/gitlab-production-sec/sessions/85956/commands/192788 (8 ms, 8 buffers).MAX: https://console.postgres.ai/gitlab/gitlab-production-sec/sessions/85956/commands/192789 (55 ms, 81,614 buffers, 336 read from disk). It still visits every due date after the cursor, which is fine for a one-off migration of this size.
2. Set or change due dates (BulkSetDueDatesService#upsert_batch, up to 1,000 rows per batch)
RETURNING is new in this MR. Rows whose due date is unchanged are skipped by the WHERE, so only changed findings come back.
INSERT INTO "vulnerability_finding_due_dates"
("vulnerability_occurrence_id", "project_id", "due_date", "created_at", "updated_at")
VALUES (1, 1, '2026-11-05', '2026-11-05 10:06:54.378772', '2026-11-05 10:06:54.378772')
ON CONFLICT ("vulnerability_occurrence_id") DO UPDATE
SET due_date = EXCLUDED.due_date, updated_at = EXCLUDED.updated_at
WHERE vulnerability_finding_due_dates.due_date IS DISTINCT FROM EXCLUDED.due_date
RETURNING "vulnerability_occurrence_id"Plan: https://postgres.ai/console/gitlab/gitlab-production-sec/sessions/81213/commands/189057 (24 ms for a full 1,000-row batch).
- The plan was run as
INSERT ... SELECTfrom the 1,000 newest due dates, each moved 30 days, so every row takes theON CONFLICT ... DO UPDATEpath. The app sends aVALUESlist instead, but conflict handling is the same.
3. Remove due dates (BulkSetDueDatesService#delete_batch, up to 1,000 IDs per batch)
The SELECT is new in this MR. delete_all can't return IDs, so the service plucks the findings that actually have a due date first.
SELECT "vulnerability_finding_due_dates"."vulnerability_occurrence_id"
FROM "vulnerability_finding_due_dates"
WHERE "vulnerability_finding_due_dates"."vulnerability_occurrence_id" IN (1, 2);
DELETE FROM "vulnerability_finding_due_dates"
WHERE "vulnerability_finding_due_dates"."vulnerability_occurrence_id" IN (1, 2);Plans, for a full 1,000-row batch:
SELECT: https://postgres.ai/console/gitlab/gitlab-production-sec/sessions/81213/commands/189061 (4.4 ms)DELETE: https://postgres.ai/console/gitlab/gitlab-production-sec/sessions/81213/commands/189063 (4.9 ms, 1,000 rows deleted)- Both were run with
IN (SELECT ... ORDER BY id DESC LIMIT 1000)to pick real rows. The app sends a literal ID list.
Queries 2 and 3 use the unique index index_vulnerability_finding_due_dates_on_occurrence_id.
GDK verification
Ran BulkSetDueDatesService from this branch on vulnerability 422, then drained the ES queue:
| Scenario | Synced IDs | ES doc due_date |
|---|---|---|
| Change to 2026-12-15 | [422] |
"2026-12-15" |
| Same date again | [] |
unchanged |
| Remove | [422] |
absent |
| Remove again | [] |
unchanged |
| Restore 2026-10-31 | [422] |
"2026-10-31" |
Forced delete_all to raise in a batch that also set 422's date: the service raised, and 422's new date still reached ES.
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist.