Prepare async index for CVE-name lookups on vulnerability_identifiers

Michał Zającrequested to merge
quintasan/vuln-identifiers-name-cve-index into master

What does this MR do and why?

Queues asynchronous creation of index_vulnerability_identifiers_on_name_where_cve on vulnerability_identifiers (name) WHERE lower(external_type::text) = 'cve' via prepare_async_index.

Cache-miss reads on vulnerability_identifiers during CVE-name matching were causing periodic all-node CPU spikes on the Sec production Patroni cluster (gprd-patroni-sec-v18). All existing indexes on the table lead with project_id, so a name-only predicate (used by the CVE-enrichment lookup) forces a skip-scan across every project_id prefix. On a production-sized Database Lab clone, adding this name-leading partial index cut execution time from ~34.7s to ~0.6s and shared block reads by ~51x. Full investigation, query plans, and before/after numbers: #617135 (closed).

The table is not yet list-partitioned (partitioning of vulnerability_identifiers is in progress under &21705, currently at the composite-FK step). The index intentionally does not include partition_id: PostgreSQL only requires the partitioning key in unique constraints and their referencing foreign keys, not in secondary indexes, and prepare_async_index itself raises if run against an already-partitioned table.

vulnerability_identifiers is not on the rubocop large/over-limit table list, but given its production size (~107M rows / ~19GB heap), the index is created asynchronously rather than synchronously to avoid a long blocking build on GitLab.com.

The migration performs no DDL itself, it only registers the index definition in postgres_async_indexes (no db/structure.sql change). On GitLab.com the index is then built by the scheduled async job as:

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

The synchronous migration follows in #617830 (closed).

References

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Merge request reports