Skip to content

perf(coderd/database): index workspace builds by template version - #31624

Open
kylecarbs wants to merge 1 commit into
perf/db-audit-group-indexesfrom
perf/db-template-avg-build-time
Open

kylecarbs wants to merge 1 commit into
perf/db-audit-group-indexesfrom
perf/db-template-avg-build-time

Conversation

@kylecarbs

@kylecarbs kylecarbs commented Nov 2, 2026 •

Copy link
Copy Markdown
Member

GetTemplateAverageBuildTime runs once per template on every metrics cache refresh. Nothing indexed workspace_builds.template_version_id, so each call scanned every workspace build. At 300 templates and 2.1M builds that came to about 42 s of database time per refresh.

Adds a workspace_builds (template_version_id, created_at DESC) index. The query now reads the newest 100 successful builds per version, then takes the newest 100 overall. A template's newest 100 builds are always within each version's newest 100, so the result is the same.

Template size Before After
66.7k builds 145 ms 4.8 ms
2.2k builds 139 ms 4.4 ms

The rewrite is needed: with only the index, the large template got slower (145 to 407 ms). Results were identical for all 300 seeded templates plus a missing one, and for 40 templates with randomized durations, cancels, failures, and transitions. A new TestGetTemplateAverageBuildTime fails if the success filter moves after the per-version limit.

NOTE: This migration builds an index on workspace_builds (96 MB at 2.1M rows), which blocks writes to that table during the upgrade. Neither column changes after insert, so updates stay eligible for HOT.

Depends on #31623.

Generated by Coder Agents on behalf of @kylecarbs.

@linear-code

linear-code Bot commented Nov 9, 2026

Copy link
Copy Markdown

CORE-831

GetTemplateAverageBuildTime runs once per template on every metrics cache
refresh and scanned every workspace build because nothing indexed
workspace_builds.template_version_id.

Index (template_version_id, created_at DESC) and read the newest 100
successful builds per version before taking the newest 100 overall. The
newest 100 of a template are always within each version's newest 100, so
the result is unchanged. Adds a regression test for the success filter.

Generated by Coder Agents.
@kylecarbs
kylecarbs force-pushed the perf/db-template-avg-build-time branch from 8edcf1e to b5b4dcd Compare November 9, 2026 21:28

This branch has not been deployed

No deployments
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment

Labels

None yet

Projects

None yet

Development

Successfully merging this pull request may close these issues.

1 participant