Perform Audit - identify and address underperforming SQL queries (Threat Insights)

Spike time-box: 3 days.

Audit the codebase in your area

Target codebase.

Please complete this audit. Things to look for:

• Known n+1 issues
• Read-only queries that can be redirected to read-only replicas
• Opportunities for caching data on frequent reads
• Lazy loading data (example)
• Removing or not exact caching costly but not super needed information totally or through a feature flag (so we only turn it off for .com for the time being). See comment below.
• Unknown unknowns: please think creatively about ways to find and optimize queries, or otherwise relieve DB load!
  • Use Elasticsearch with PostgreSQL slow logs to collect more information when you have suspicious queries.
  • Look at this dashboard for endpoints that have a high number of SQL calls

Audit your Current Backlog

You might have already identified some of the issues above and captured them as issues. Please assign those to team members and if possible add them to the overall Epic.

Resources for Team Members to use

Query optimizations - Tips, Tools and Links on what to look for

Outcome

1. Issues of improvement opportunities
   1. Query optimization
   2. Caching
   3. Moving to read-only replica
   4. Query frequency reduction
   5. Lazy loading
   6. Result truncating (example: #325440 (comment 534199203))
2. If nothing is discovered as list above, please briefly summarize the queries reviewed in this issue for the record so we won't have to review them again in the future.

When complete, please add a check mark in the Original Issue.

Findings:

• Issues that groupthreat insights is currently working on or will work in upcoming milestones for improving performance:

  • Optimize N+1 query for reset_remediations, update_feedbacks, and update_finding_signatures in StoreReportService
  • Refactor Security::StoreReportService
  • Improve the performance of vulnerabilitySeveritiesCount
  • Benchmark Current DB Calls
  • Optimize and reenable pageInfo.hasNextPage for Vulnerabilities
  • Move some calculations of failed security job information to SQL
  • Performance Improvement Spike: Group & Project-level Vulnerability Reports
  • Technical Spike: Security Dashboard - quickly load Vulnerabilities for Groups and Instances
  • Improve N+1 query in VulnerableProjectsByGradeType
  • N+1 query in loading vulnerability feedback
  • Fix N+1 query for VulnerabilityType https://gitlab.com/gitlab-org/gitlab/-/blob/master/ee/app/graphql/types/vulnerability_type.rb
  • Adjust vulnerability statistics daily only for the necessary projects
  • Improve performance of the Vulnerability Findings computed state

• Review postgres-checkup report

• Added issues after this audit:

  • Improve the performance of Security::FindingsFinder
  • N+1 query in loading vulnerability policy
  • N+1 query in Vulnerabilities::ProjectsGrade