Perform Audit - identify and address underperforming SQL queries (Composition Analysis)
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)
- 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
Audited controllers
Dependency Scanning
License Compliance
-
Toggling of the auto-fix feature
See thread where we came up to this list.
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.
Edited by Fabien Catteau