Create vulnerability read model

Why are we doing this work

As we are constantly getting time-out errors while loading the group&instance-level vulnerability reports pages, we wanted to implement a performant solution to query our complex data model. We are basically creating a new table to denormalize our data to make it easier to query which will improve the performance. This approach is called "Command Query Responsibility Segregation" which in short is CQRS.

The first implementation task of having a separate read model is defining & creating the read model which will be done within the scope of this issue.

Relevant links

• An example timeout issue: #327607 (closed)

Implementation plan

• database Introduce a new table called vulnerability_reads
• database Create the necessary indices on this table to speed up querying it

Technical details/notes

The read table schema

CREATE TABLE vulnerability_reads (
  vulnerability_id bigint NOT NULL,
  project_id int NOT NULL,
  report_type smallint NOT NULL,
  scanner_id bigint NOT NULL,
  severity smallint NOT NULL, -- Maybe :'"char"' column to have just 1 byte field?
  state smallint NOT NULL, -- Maybe :'"char"' column to have just 1 byte field?
  has_issues boolean DEFAULT false NOT NULL,
  resolved_on_default_branch boolean DEFAULT false NOT NULL
  uuid uuid NOT NULL
  location_image text
);

alter table only vulnerability_reads add constraint ... foreign key (vulnerability_id) references vulnerabilities(id) on delete cascade;

Indices

  create unique index index_1 on vulnerability_reads using btree(vulnerability_id);
  create index index_2 on vulnerability_reads using btree(project_id, state, severity, vulnerability_id desc);
  ...