## Why are we doing this work 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](https://en.wikipedia.org/wiki/Command%E2%80%93query_separation#Command_query_responsibility_segregation). ## DRI BE DRI: @minac ## Relevant links - An example timeout issue: https://gitlab.com/gitlab-org/gitlab/-/issues/327607 ### Technical details/notes <details> <summary> The read table schema </summary> ```sql 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; ``` </details> <details> <summary> Indices </summary> ```sql 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); ... ``` </details>