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);
...
Edited by Sashi Kumar Kumaresan