perf: reduce calls to GetWorkspaceBuildByJobID #21298
Open
+147
−13
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
|
All contributors have signed the CLA ✍️ ✅ |
73627a0 to
915b2b0
Compare
…ance-identity Optimize handleAuthInstanceID to use a new combined query that fetches both workspace resource and provisioner job information in a single database call. Before: GetWorkspaceResourceByID cascades to GetProvisionerJobByID (which calls GetWorkspaceBuildByJobID), then we explicitly call GetProvisionerJobByID again (triggering GetWorkspaceBuildByJobID again). After: GetWorkspaceResourceWithJobByID fetches both resource and job info in one query, checking first that the right authorizeContext permissions are present. This reduces GetWorkspaceBuildByJobID calls from 2 to 0 per instance-identity request (~0.4 req/s).
f48b1de to
19cc851
Compare
sreya
approved these changes
Dec 16, 2025
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
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.
Another optimization with the help of tasks.
GetWorkspaceBuildByJobID is our most expensive query in the last 7d, called 3.3M times in that period. AFAICT this is called most frequently as a result of the
<provider>-instance-identityendpoint calls, which I believe are associated with agent -> coderd connection establishment. We may have an issue with connections having to be re-established frequently, but in any case we have at least two cascaded calls to this query as a result of the chain of DB calls in this function.Looking at a trace for this endpoint we can see that there's at least 13 RT to the database for the endpoint, 2 of which are
GetWorkspaceBuildByJobID. There's also ~0.4 calls per second to these endpoints, which means 0.8 calls per second toGetWorkspaceBuildByJobID, or ~484k per week. So this should result in just under 15% reduction in the call volume to this query.We're achieving this by circumventing the calls to
api.Database.GetWorkspaceResourceByIDandapi.Database.GetProvisionerJobByID, both of which eventually callGetWorkspaceBuildByJobIDas part of their auth checks in dbauthz, and instead calling a new function/queryGetWorkspaceResourceWithJobByIDwhich combines the two queries we actually need into one and checks the authorization context to ensure we have the right permissions without the need for an additional DB query to do so.