Skip to content

Commit aac10a7

Browse files
dylanhuff-at-coderclaude
dylanhuff-at-coder
and
claude
committed
perf: use the build parameter index in GetTemplateParameterInsights (#31765)
Speeds up `GetTemplateParameterInsights`, which the insights collector runs every 5 minutes on every replica. The `@>` array join could only hash on `name`, so every run scanned all of `workspace_build_parameters`. Unnesting the build IDs and joining on `(workspace_build_id, name)` lets the existing unique index serve it, and the results don't change since each build ID shows up at most once per group. On 8.4M synthetic build parameters the collector's 5 minute window went from 5,193 ms to about 31 ms. `TestGetTemplateParameterInsights` is new and passes on both the old and new SQL. This also fixes the Prometheus docs example, which summed per-replica copies of the same metric. I split the `workspace_app_stats` index into #31766 so this one has no migration and can be backported to 2.34 through 2.39 (2.34 to 2.38 without the docs hunk). Part of [CORE-439](https://linear.app/codercom/issue/CORE-439). (cherry picked from commit 3728e7a) The docs hunk is dropped on this branch because its Prometheus docs have no example query; only the generated metrics table. Co-Authored-By: Claude Opus 5.5 <noreply@anthropic.com>
1 parent e3d7870 commit aac10a7

3 files changed

Lines changed: 128 additions & 2 deletions

File tree

‎coderd/database/querier_test.go‎

Lines changed: 118 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -712,6 +712,124 @@ func TestGetProvisionerDaemonsWithStatusByOrganization(t *testing.T) {
712712
})
713713
}
714714

715+
func TestGetTemplateParameterInsights(t *testing.T) {
716+
t.Parallel()
717+
718+
db, _ := dbtestutil.NewDB(t)
719+
ctx := testutil.Context(t, testutil.WaitLong)
720+
startTime := dbtime.Now().Add(-time.Hour).Truncate(time.Minute)
721+
endTime := startTime.Add(30 * time.Minute)
722+
723+
org := dbgen.Organization(t, db, database.Organization{})
724+
user := dbgen.User(t, db, database.User{})
725+
newTemplate := func() (database.Template, database.TemplateVersion) {
726+
tpl := dbgen.Template(t, db, database.Template{OrganizationID: org.ID, CreatedBy: user.ID})
727+
ver := dbgen.TemplateVersion(t, db, database.TemplateVersion{
728+
OrganizationID: org.ID,
729+
CreatedBy: user.ID,
730+
TemplateID: uuid.NullUUID{UUID: tpl.ID, Valid: true},
731+
})
732+
return tpl, ver
733+
}
734+
addParam := func(ver database.TemplateVersion, name, description string) {
735+
dbgen.TemplateVersionParameter(t, db, database.TemplateVersionParameter{
736+
TemplateVersionID: ver.ID,
737+
Name: name,
738+
DisplayName: name,
739+
Description: description,
740+
Options: []byte("[]"),
741+
})
742+
}
743+
newWorkspace := func(tpl database.Template) database.WorkspaceTable {
744+
return dbgen.Workspace(t, db, database.WorkspaceTable{OrganizationID: org.ID, TemplateID: tpl.ID, OwnerID: user.ID})
745+
}
746+
addBuild := func(ws database.WorkspaceTable, ver database.TemplateVersion, buildNumber int32, createdAt time.Time, values map[string]string) {
747+
job := dbgen.ProvisionerJob(t, db, nil, database.ProvisionerJob{OrganizationID: org.ID, InitiatorID: user.ID})
748+
build := dbgen.WorkspaceBuild(t, db, database.WorkspaceBuild{
749+
WorkspaceID: ws.ID,
750+
TemplateVersionID: ver.ID,
751+
BuildNumber: buildNumber,
752+
InitiatorID: user.ID,
753+
JobID: job.ID,
754+
CreatedAt: createdAt,
755+
})
756+
params := make([]database.WorkspaceBuildParameter, 0, len(values))
757+
for name, value := range values {
758+
params = append(params, database.WorkspaceBuildParameter{WorkspaceBuildID: build.ID, Name: name, Value: value})
759+
}
760+
dbgen.WorkspaceBuildParameters(t, db, params)
761+
}
762+
763+
// Templates A and B share an identical region definition, so they form one
764+
// group. Template C uses the same name with a different description, so it
765+
// forms its own group. Only template A has a size parameter.
766+
tplA, verA := newTemplate()
767+
tplB, verB := newTemplate()
768+
tplC, verC := newTemplate()
769+
addParam(verA, "region", "Shared region")
770+
addParam(verA, "size", "Size")
771+
addParam(verB, "region", "Shared region")
772+
addParam(verC, "region", "Other region")
773+
774+
// Only the latest build inside the window counts for each workspace.
775+
wsA1 := newWorkspace(tplA)
776+
addBuild(wsA1, verA, 1, startTime.Add(-10*time.Minute), map[string]string{"region": "ap", "size": "small"})
777+
addBuild(wsA1, verA, 2, startTime.Add(5*time.Minute), map[string]string{"region": "eu", "size": "small"})
778+
addBuild(wsA1, verA, 3, startTime.Add(10*time.Minute), map[string]string{"region": "us", "size": "large"})
779+
addBuild(wsA1, verA, 4, endTime.Add(2*time.Minute), map[string]string{"region": "ap", "size": "small"})
780+
addBuild(newWorkspace(tplA), verA, 1, startTime.Add(15*time.Minute), map[string]string{"region": "us", "size": "small"})
781+
addBuild(newWorkspace(tplB), verB, 1, startTime.Add(20*time.Minute), map[string]string{"region": "eu"})
782+
addBuild(newWorkspace(tplC), verC, 1, startTime.Add(25*time.Minute), map[string]string{"region": "eu"})
783+
// Builds outside the window are ignored.
784+
addBuild(newWorkspace(tplB), verB, 1, endTime.Add(time.Minute), map[string]string{"region": "ap"})
785+
addBuild(newWorkspace(tplA), verA, 1, startTime.Add(-time.Minute), map[string]string{"region": "ap", "size": "large"})
786+
787+
type paramCount struct {
788+
TemplateIDs string
789+
Name string
790+
Description string
791+
Value string
792+
Count int64
793+
}
794+
// joinIDs makes template ID sets comparable regardless of order.
795+
joinIDs := func(ids ...uuid.UUID) string {
796+
s := make([]string, 0, len(ids))
797+
for _, id := range ids {
798+
s = append(s, id.String())
799+
}
800+
slices.Sort(s)
801+
return strings.Join(s, ",")
802+
}
803+
query := func(templateIDs []uuid.UUID) []paramCount {
804+
rows, err := db.GetTemplateParameterInsights(ctx, database.GetTemplateParameterInsightsParams{
805+
StartTime: startTime,
806+
EndTime: endTime,
807+
TemplateIDs: templateIDs,
808+
})
809+
require.NoError(t, err)
810+
got := make([]paramCount, 0, len(rows))
811+
for _, row := range rows {
812+
got = append(got, paramCount{joinIDs(row.TemplateIDs...), row.Name, row.Description, row.Value, row.Count})
813+
}
814+
return got
815+
}
816+
817+
require.ElementsMatch(t, []paramCount{
818+
{joinIDs(tplA.ID, tplB.ID), "region", "Shared region", "us", 2},
819+
{joinIDs(tplA.ID, tplB.ID), "region", "Shared region", "eu", 1},
820+
{joinIDs(tplA.ID), "size", "Size", "large", 1},
821+
{joinIDs(tplA.ID), "size", "Size", "small", 1},
822+
{joinIDs(tplC.ID), "region", "Other region", "eu", 1},
823+
}, query(nil))
824+
825+
require.ElementsMatch(t, []paramCount{
826+
{joinIDs(tplA.ID), "region", "Shared region", "us", 2},
827+
{joinIDs(tplA.ID), "size", "Size", "large", 1},
828+
{joinIDs(tplA.ID), "size", "Size", "small", 1},
829+
{joinIDs(tplC.ID), "region", "Other region", "eu", 1},
830+
}, query([]uuid.UUID{tplA.ID, tplC.ID}))
831+
}
832+
715833
func TestGetWorkspaceAgentUsageStats(t *testing.T) {
716834
t.Parallel()
717835

‎coderd/database/queries.sql.go‎

Lines changed: 5 additions & 1 deletion
Some generated files are not rendered by default. Learn more about customizing how changed files appear on GitHub.

‎coderd/database/queries/insights.sql‎

Lines changed: 5 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -799,7 +799,11 @@ SELECT
799799
wbp.value,
800800
COUNT(wbp.value) AS count
801801
FROM unique_template_params utp
802-
JOIN workspace_build_parameters wbp ON (utp.workspace_build_ids @> ARRAY[wbp.workspace_build_id] AND utp.name = wbp.name)
802+
-- Each build ID appears at most once per group (one parameter row per version
803+
-- and name, one latest build per workspace), so unnest cannot double count.
804+
-- The equality join lets Postgres use the (workspace_build_id, name) index.
805+
CROSS JOIN LATERAL unnest(utp.workspace_build_ids) AS b(id)
806+
JOIN workspace_build_parameters wbp ON (wbp.workspace_build_id = b.id AND wbp.name = utp.name)
803807
GROUP BY utp.num, utp.template_ids, utp.name, utp.type, utp.display_name, utp.description, utp.options, wbp.value;
804808

805809
-- name: GetUserStatusCounts :many

0 commit comments

Comments
 (0)