Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit7e8fcb4

Browse files
authored
perf: optimize prebuilds membership reconciliation to check orgs not presets (#20493)
## DescriptionThe membership reconciliation ensures the prebuilds system user is amember of all organizations with prebuilds configured. To supportprebuilds quota management, each organization must have a prebuildsgroup that the system user belongs to.## ProblemPreviously, membership reconciliation iterated over all presets to checkand update membership status. This meant database queries`GetGroupByOrgAndName` and `InsertGroupMember` were executed for eachpreset. Since presets are unique combinations of `(organization,template, template version, preset)`, this resulted in several redundantchecks for the same organization.In dogfood, `InsertGroupMember` was called thousands of times per day,even though memberships were already configured ([internal Grafanadashboard link](https://grafana.dev.coder.com/goto/46MZ1UgDg?orgId=1))<img width="5382" height="1788" alt="Screenshot 2025-10-28 at 16 01 36"src="https://github.com/user-attachments/assets/757b7253-106f-4f72-8586-8e2ede9f18db"/>## SolutionThis PR introduces `GetOrganizationsWithPrebuildStatus`, a single querythat returns:* All unique organizations with prebuilds configured* Whether the prebuilds user is a member of each organization* Whether the prebuilds group exists in each organization* Whether the prebuilds user is in the prebuilds groupThe membership reconciliation logic now:* Fetches status for all organizations in one query* Only performs inserts for organizations missing required membershipsor groups* Safely handles concurrent operations via unique constraint violations* This reduces database load from `O(presets)` to `O(organizations)` perreconciliation loop, with a single read query when everything isconfigured.## Changes* Add `GetOrganizationsWithPrebuildStatus` SQL query* Update `membership.ReconcileAll` to use organization-basedreconciliation instead of preset-based* Update tests to reflect new behaviorRelated to internal thread:https://codercom.slack.com/archives/C07GRNNRW03/p1760535570381369
1 parentdd28eef commit7e8fcb4

File tree

10 files changed

+397
-212
lines changed

10 files changed

+397
-212
lines changed

‎coderd/database/dbauthz/dbauthz.go‎

Lines changed: 7 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2648,6 +2648,13 @@ func (q *querier) GetOrganizationsByUserID(ctx context.Context, userID database.
26482648
returnfetchWithPostFilter(q.auth,policy.ActionRead,q.db.GetOrganizationsByUserID)(ctx,userID)
26492649
}
26502650

2651+
func (q*querier)GetOrganizationsWithPrebuildStatus(ctx context.Context,arg database.GetOrganizationsWithPrebuildStatusParams) ([]database.GetOrganizationsWithPrebuildStatusRow,error) {
2652+
iferr:=q.authorizeContext(ctx,policy.ActionRead,rbac.ResourceOrganization.All());err!=nil {
2653+
returnnil,err
2654+
}
2655+
returnq.db.GetOrganizationsWithPrebuildStatus(ctx,arg)
2656+
}
2657+
26512658
func (q*querier)GetParameterSchemasByJobID(ctx context.Context,jobID uuid.UUID) ([]database.ParameterSchema,error) {
26522659
version,err:=q.db.GetTemplateVersionByJobID(ctx,jobID)
26532660
iferr!=nil {

‎coderd/database/dbauthz/dbauthz_test.go‎

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3759,6 +3759,14 @@ func (s *MethodTestSuite) TestPrebuilds() {
37593759
dbm.EXPECT().GetPrebuildMetrics(gomock.Any()).Return([]database.GetPrebuildMetricsRow{},nil).AnyTimes()
37603760
check.Args().Asserts(rbac.ResourceWorkspace.All(),policy.ActionRead)
37613761
}))
3762+
s.Run("GetOrganizationsWithPrebuildStatus",s.Mocked(func(dbm*dbmock.MockStore,faker*gofakeit.Faker,check*expects) {
3763+
arg:= database.GetOrganizationsWithPrebuildStatusParams{
3764+
UserID:uuid.New(),
3765+
GroupName:"test",
3766+
}
3767+
dbm.EXPECT().GetOrganizationsWithPrebuildStatus(gomock.Any(),arg).Return([]database.GetOrganizationsWithPrebuildStatusRow{},nil).AnyTimes()
3768+
check.Args(arg).Asserts(rbac.ResourceOrganization.All(),policy.ActionRead)
3769+
}))
37623770
s.Run("GetPrebuildsSettings",s.Mocked(func(dbm*dbmock.MockStore,_*gofakeit.Faker,check*expects) {
37633771
dbm.EXPECT().GetPrebuildsSettings(gomock.Any()).Return("{}",nil).AnyTimes()
37643772
check.Args().Asserts()

‎coderd/database/dbmetrics/querymetrics.go‎

Lines changed: 7 additions & 0 deletions
Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.

‎coderd/database/dbmock/dbmock.go‎

Lines changed: 15 additions & 0 deletions
Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.

‎coderd/database/querier.go‎

Lines changed: 3 additions & 0 deletions
Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.

‎coderd/database/queries.sql.go‎

Lines changed: 87 additions & 0 deletions
Some generated files are not rendered by default. Learn more aboutcustomizing how changed files appear on GitHub.

‎coderd/database/queries/prebuilds.sql‎

Lines changed: 44 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -327,3 +327,47 @@ SET
327327
FROM jobs_to_cancel
328328
WHEREprovisioner_jobs.id=jobs_to_cancel.id
329329
RETURNINGjobs_to_cancel.id,jobs_to_cancel.workspace_id,jobs_to_cancel.template_id,jobs_to_cancel.template_version_preset_id;
330+
331+
-- name: GetOrganizationsWithPrebuildStatus :many
332+
-- GetOrganizationsWithPrebuildStatus returns organizations with prebuilds configured and their
333+
-- membership status for the prebuilds system user (org membership, group existence, group membership).
334+
WITH orgs_with_prebuildsAS (
335+
-- Get unique organizations that have presets with prebuilds configured
336+
SELECT DISTINCTo.id,o.name
337+
FROM organizations o
338+
INNER JOIN templates tONt.organization_id=o.id
339+
INNER JOIN template_versions tvONtv.template_id=t.id
340+
INNER JOIN template_version_presets tvpONtvp.template_version_id=tv.id
341+
WHEREtvp.desired_instancesIS NOT NULL
342+
),
343+
prebuild_user_membershipAS (
344+
-- Check if the user is a member of the organizations
345+
SELECTom.organization_id
346+
FROM organization_members om
347+
INNER JOIN orgs_with_prebuilds owpONowp.id=om.organization_id
348+
WHEREom.user_id= @user_id::uuid
349+
),
350+
prebuild_groupsAS (
351+
-- Check if the organizations have the prebuilds group
352+
SELECTg.organization_id,g.idas group_id
353+
FROM groups g
354+
INNER JOIN orgs_with_prebuilds owpONowp.id=g.organization_id
355+
WHEREg.name= @group_name::text
356+
),
357+
prebuild_group_membershipAS (
358+
-- Check if the user is in the prebuilds group
359+
SELECTpg.organization_id
360+
FROM prebuild_groups pg
361+
INNER JOIN group_members gmONgm.group_id=pg.group_id
362+
WHEREgm.user_id= @user_id::uuid
363+
)
364+
SELECT
365+
owp.idAS organization_id,
366+
owp.nameAS organization_name,
367+
(pum.organization_idIS NOT NULL)::booleanAS has_prebuild_user,
368+
pg.group_idAS prebuilds_group_id,
369+
(pgm.organization_idIS NOT NULL)::booleanAS has_prebuild_user_in_group
370+
FROM orgs_with_prebuilds owp
371+
LEFT JOIN prebuild_groups pgONpg.organization_id=owp.id
372+
LEFT JOIN prebuild_user_membership pumONpum.organization_id=owp.id
373+
LEFT JOIN prebuild_group_membership pgmONpgm.organization_id=owp.id;

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp