Skip to content

Prepare index for optimized searches from the vulnerability_reads table

mo khan requested to merge 382625/add-index-to-vulnerability-reads into master

What does this MR do and why?

We need to be able to find all vulnerabilities for a project that are on the default branch and were detected by a specific scanner. To accomplish that, this MR introduces a post migration to begin generation of a composite database index on vulnerability_reads(project_id, scanner_id, vulnerability_id). Additional context can be found in #382625 (comment 1259314425).

This MR follows the guidance found in Database development guidance.

SELECT "vulnerabilities".*
FROM "vulnerabilities"
INNER JOIN "vulnerability_reads" "vulnerability_read"
  ON "vulnerability_read"."vulnerability_id" = "vulnerabilities"."id"
WHERE "vulnerability_read"."project_id" = 1216
  AND "vulnerability_read"."scanner_id" = 489;

https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/15140/commands/52583

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by mo khan

Merge request reports