Product Intelligence Performance Audit - Identify and address underperforming SQL queries
Audit the codebase in your area
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 [Lazily load pipeline artifact in project pipelines index page (#324003 (closed))] (#324003 (comment 526293202)))
- 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
- Issues of improvement opportunities
- Query optimization
- Caching
- Moving to read-only replica
- Query frequency reduction
- Lazy loading
- Result truncating (example: #325440 (comment 534199203))
- 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.