Technical Spike: Security Dashboard - quickly load Vulnerabilities for Groups and Instances
Problem to solve
Display vulnerabilities by age in security dashboards epic requires us to show Vulnerabilities count by age in the Group and Instance Security Dashboard. The major problem here is that the query to fetch all Vulnerabilities for a given group takes too long.
With cold cache > 10s: https://console.postgres.ai/gitlab/gitlab-production-tunnel/sessions/3020/commands/9929
With warm cache > 100ms: https://console.postgres.ai/gitlab/gitlab-production-tunnel/sessions/3020/commands/9930
Proposed solutions
Calculate numbers for predefined time windows everyday
Proposed by @minac in #234193 (comment 529033113)
Pros:
- Fairly easy to implement.
Cons:
- Impossible for users to define different time windows for their projects based on their own company policies.
- Requires us to run a database migration to recalculate all the values in case if we want to change the time windows.
Create materialized views
Proposed by @Quintasan. Essentially we would create MATERIALIZED VIEW for Group and Instance level queries for Vulnerabilities and refresh them daily or at user's request.
Pros:
- We can create MATERIALIZED VIEW per namespace (eg.
vulnerabilities_by_age_<namespace name>
), this means we can create them with custom time buckets - We can compute them on-demand so if a certain namespace doesn't use that feature then we won't compute them unless they explicitly request them
- Can be refreshed daily or manually
- Once created they are really fast
Cons:
- I don't think we ever did MATERIALIZED VIEW in GitLab
- MATERIALIZED means the output is stored on the disk so Database Team might not like it
Edited by Michał Zając