vulnerability_identifiers CVE-name lookup: missing name-leading index + unbounded cross-project fan-out drives patroni-sec cache-miss CPU spikes

Summary

Investigation of periodic cluster-wide CPU spikes on the Sec production Patroni cluster (gprd-patroni-sec-v18, tracked for right-sizing in dbo-issue-tracker#757) traced the spikes to cache-miss reads on vulnerability_identifiers, driven by the CVE-name matching lookup used on the advisory/CVE-enrichment path:

SELECT ... FROM vulnerability_identifiers
WHERE LOWER(external_type) = 'cve' AND name = 'CVE-2023-32681';

(The same shape appears as the join pm_cve_enrichment.cve = vulnerability_identifiers.name in the vulnerability-filter path, e.g. !214982 (closed).)

There are two independent problems.

Problem 1: no name-leading index (skip-scan)

All indexes on vulnerability_identifiers lead with project_id; the only partial CVE index is on (id). A name-only predicate (no project_id) cannot seek, so Postgres skip-scans the (project_id, name) index across every project_id prefix.

Measured on a postgres.ai / Database Lab clone of gitlab-production-sec (PG18, snapshot 2026-08-18), EXPLAIN (ANALYZE, BUFFERS), table ~107M rows / ~19GB heap / ~25GB indexes:

Current (project_id, name) With (name) WHERE lower(external_type)='cve'
Execution time 34,691 ms 645 ms
Index searches 177,536 1
Shared block reads 308,197 6,038
I/O wait 33,165 ms 631 ms

Roughly 54x faster and 51x fewer block reads. Each new-advisory scan wave runs many of these cross-project name lookups; with no name index, each does 100k-300k block reads, which is the source of the blks_read bursts (~30k to ~250k blocks/s) and the resulting all-node CPU spikes (60-88 of 96 cores) observed on the cluster.

Proposed fix 1

Add a name-leading partial index for the CVE case:

CREATE INDEX CONCURRENTLY index_vulnerability_identifiers_on_name_where_cve
ON vulnerability_identifiers USING btree (name)
WHERE (lower((external_type)::text) = 'cve');

This must be coordinated with the in-flight list-partitioning of vulnerability_identifiers (@Quintasan, work items #596856 (closed) / #596857 (closed) / #596858 (closed)) so the index is defined partition-compatible (include partition_id if required post-partitioning).

Problem 2: unbounded cross-project fan-out (memory risk)

An index fixes per-lookup latency but not result-set size. For a single CVE the lookup returns all matching identifier rows across all projects (6,129 in the sampled case; a widely-present CVE such as Log4Shell would be far larger). The advisory/policy-evaluation read path is not bounded by project scope the way the report resolver is, so a popular CVE can materialize a very large set into memory in one shot.

Note the ingestion/write side already batches correctly (Gitlab::VulnerabilityScanning::AdvisoryScanner -> Sbom::PossiblyAffectedOccurrencesFinder#execute_in_batches -> bulk_vulnerability_ingestion); this issue is specifically about the read/matching side.

Proposed fix 2

  • Bound / paginate the CVE-name matching read so a single popular CVE cannot load an unbounded set into memory.
  • Prefer migrating the match off the global string join (vulnerability_identifiers.name = pm_cve_enrichment.cve) onto the security_finding_enrichments cache and its cve_enrichment_id FK (see Security::CveEnrichmentFilterable#with_cve_enrichment_filters), which removes the string join and caps the working set.

Why this matters now

  • VAC is at 100% Open Beta and non-default tracked contexts have grown sharply (~40 on Aug 7 to ~1,701 on Aug 18), so vulnerability_identifiers and its working set keep growing.
  • The spikes are a right-sizing input for dbo#757: on the current c4-highmem-96 the working set mostly stays in page cache, but halving RAM (the proposed c4-highmem-48 downsize) against a growing table would turn these occasional cache-miss bursts into a sustained IOPS+CPU regression. Fixing this at the query/index level is the durable path.
  • The same tables underlie the vulnerability_consistency_checks repair-path statement timeouts on the largest project, so this fix should help there too.

Evidence / method

  • Mimir (mimir-gitlab-gprd): node CPU, pg_stat_database read/write counters, per-queryid pg_stat_statements_*, pg_statio_user_tables per-table block reads.
  • postgres.ai / Database Lab clone of gitlab-production-sec for the EXPLAIN (ANALYZE, BUFFERS) before/after above.

/cc @ryaanwells @Quintasan