- Notifications
You must be signed in to change notification settings - Fork1k
chore(coderd/database): optimize AuditLogs queries (cherry-pick #18600)#19193
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to ourterms of service andprivacy statement. We’ll occasionally send you account related emails.
Already on GitHub?Sign in to your account
Merged
ethanndickson merged 1 commit intorelease/2.24fromethan/2.24-audit-log-optimization-backportAug 7, 2025
Merged
chore(coderd/database): optimize AuditLogs queries (cherry-pick #18600)#19193
ethanndickson merged 1 commit intorelease/2.24fromethan/2.24-audit-log-optimization-backportAug 7, 2025
+763 −244
Conversation
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.Learn more about bidirectional Unicode characters
This comment was marked as outdated.
This comment was marked as outdated.
Closes#17689This PR optimizes the audit logs query performance by extracting thecount operation into a separate query and replacing the OR-basedworkspace_builds with conditional joins.## Query changes* Extracted count query to separate one* Replaced single `workspace_builds` join with OR conditions withseparate conditional joins* Added conditional joins* `wb_build` for workspace_build audit logs (which is a direct lookup) * `wb_workspace` for workspace create audit logs (via workspace)Optimized AuditLogsOffset query:https://explain.dalibo.com/plan/4g1hbedg4a564bg8New CountAuditLogs query:https://explain.dalibo.com/plan/ga2fbcecb9efbce3
97a3fc2 toa788e2fComparekacpersaw approved these changesAug 7, 2025
5ff7496 intorelease/2.24 36 checks passed
Uh oh!
There was an error while loading.Please reload this page.
Sign up for freeto subscribe to this conversation on GitHub. Already have an account?Sign in.
Labels
None yet
2 participants
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Uh oh!
There was an error while loading.Please reload this page.
A customer who recently upgraded their deployment to 2.24 is seeing their audit log queries take longer than a minute to load (resulting in a gateway timeout). As such, Support's requested we backport this fix to 2.24 (in the next patch), as it does not require a database migration.
Original PR Description (#18600):
Closes#17689
This PR optimizes the audit logs query performance by extracting the count operation into a separate query and replacing the OR-based workspace_builds with conditional joins.
Query changes
workspace_buildsjoin with OR conditions with separate conditional joinswb_buildfor workspace_build audit logs (which is a direct lookup)wb_workspacefor workspace create audit logs (via workspace)Optimized AuditLogsOffset query:
https://explain.dalibo.com/plan/4g1hbedg4a564bg8
New CountAuditLogs query:
https://explain.dalibo.com/plan/ga2fbcecb9efbce3