Skip to content

perf: use the build parameter index in GetTemplateParameterInsights - #31765

Merged
dylanhuff-at-coder merged 3 commits into
mainfrom
dylan/core-439-template-insights-prometheus-scaling
Nov 10, 2026
Merged

dylanhuff-at-coder merged 3 commits into
mainfrom
dylan/core-439-template-insights-prometheus-scaling

Conversation

@dylanhuff-at-coder

@dylanhuff-at-coder dylanhuff-at-coder commented Nov 5, 2026 •

Copy link
Copy Markdown
Collaborator

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).

The pre-push hook skipped tests on this push because of a stale origin/main in my checkout. An earlier full run only failed in agent/ and site/.

Part of CORE-439.

…rameterInsights

The final join matched build parameters with
`utp.workspace_build_ids @> ARRAY[wbp.workspace_build_id]`, which leaves
only `name` as a usable join key. Postgres ends up scanning every row in
workspace_build_parameters and running the array check per row, so the
query gets slower as build history grows no matter how small the window
is. The insights metrics collector runs it every 5 minutes on every
replica.

Unnest the build IDs and join on (workspace_build_id, name) instead so
the existing unique index serves the lookup. Each build ID shows up at
most once per group, so the result is the same.

Adds TestGetTemplateParameterInsights to pin grouping across templates,
latest-in-window build selection and per-value counts. It passes against
both the old and new query.
…s in the Prometheus example

Every coderd replica exports the same coderd_insights_* values, so the
`sum by (template_name)` example read N times too high on an N replica
deployment. Take the max per app first, then sum per template and
organization, and add a note with the other metric family notes that
these metrics should be aggregated across replicas with max.
@linear-code

linear-code Bot commented Nov 5, 2026

Copy link
Copy Markdown

CORE-439

@dylanhuff-at-coder

Copy link
Copy Markdown
Collaborator Author

/coder-agents-review Please limit the review to P0, P1, and P2 issues only.

@coder-agents-review

coder-agents-review Bot commented Nov 5, 2026 •

Copy link
Copy Markdown
Contributor

Chat: Review posted | View chat
Requested: 2026-11-05 03:43 UTC by @dylanhuff-at-coder
Spend: $4.79 / $100.00

Review history
  • R1 (2026-11-05): 12 reviewers, COMMENT. Review

deep-review v0.13.0 | Round 1 | 42640dc..7e80fc4

Last posted: Round 1, no findings, COMMENT. Review

Finding inventory

Finding inventory: PR #31765

Findings

# Sev Status Location Summary Round Reviewer Posted
CRF-1 P3 Dropped by orchestrator (below P2; PR author requested P0-P2 only) docs/admin/integrations/prometheus.md:102 "Every replica exports the same values" is inaccurate; replica windows are unaligned R1 Netero No
CRF-2 Note Dropped by orchestrator (below P2; PR author requested P0-P2 only) coderd/database/querier_test.go:888 New test passes on base SQL and cannot detect unnest double counting R1 Netero No
CRF-3 Nit Dropped by orchestrator (below P2; PR author requested P0-P2 only) coderd/database/querier_test.go:936 Fixture comment restates the addParam calls R1 Gon P2 No
CRF-4 Nit Dropped by orchestrator (below P2; PR author requested P0-P2 only) coderd/database/querier_test.go:967 joinIDs comment restates the sort; name hides the sort R1 Gon P2 No
CRF-5 Nit Dropped by orchestrator (below P2; PR author requested P0-P2 only) coderd/database/queries/insights.sql:1004 One-letter alias b(id) for unnested build IDs R1 Gon No
CRF-6 Note Dropped by orchestrator (below P2; PR author requested P0-P2 only) docs/admin/integrations/prometheus.md:413 Docs sentence explains the grouping but not why the inner max exists R1 Gon No
CRF-7 OOS Out of scope (body) scripts/githooks/pre-push:108 echo | grep -q under pipefail exits 141 on large change lists; hook skips tests for Go changes R1 Leorio No
CRF-8 OOS Out of scope (body) coderd/prometheusmetrics/insights/metricscollector.go:87 Per-replica insights windows are now-relative and unaligned, so replica values differ for the same period R1 Netero No

Contested and acknowledged

Round log

Round 1

Round type: round 1, full panel. Netero first pass: 1 P3, 1 Note (no P0, panel proceeded). Law not run (effective additions 128). Panel: knuckle, ging-go, killua, pen-botter, pariston, mafuuu, bisky, komugi, gon, leorio, razor, plus wildcard kite. Size 12 (multi-domain: SQL, Go, docs). Mafu-san not selected (commits authored by a human).

Cross-check: no P0-P2 findings. Gon's two P2 test comment findings (CRF-3, CRF-4) downgraded to Nit. Keep-argument written first: redundant comments drift from the code and the joinIDs name hides the sort. Downgrade reason: both comments are accurate today, the helper is six lines, and no behavior or reader decision depends on them. The PR author requested P0-P2 findings only, so every below-P2 item is recorded here and not posted. SQL equivalence verified by Knuckle, Pariston, and Netero via the unique constraints on workspace_builds(workspace_id, build_number) and template_version_parameters(template_version_id, name). Pariston measured no regression at wide windows (PG16 synthetic, full-window: new 20.7 s vs old >170 s). Pre-push hook claim (CRF-7) verified by orchestrator at scripts/githooks/pre-push:23,108. Reviewed against 42640dc..7e80fc4.

About deep-review

CRF = Coder Review Finding (P0-P4, Nit, Note)

Reviewer Focus
Bisky tests
Chopper ops/errors
Churn-guard change verification
Ging language modernization
Gon naming
Hisoka edge cases
Killua perf
Kite change integrity
Knov contracts
Knuckle SQL
Komugi flake/determinism
Kurapika security
Law decomposition
Leorio docs
Luffy product
Mafu-san process
Mafuuu contracts
Melody dispatch/pairing
Meruem structural
Nami frontend
Netero mechanical checks
Pariston premise testing
Pen-botter product gaps
Razor verification
Robin duplication
Ryosuke Go arch
Takumi concurrency
Zoro shape

🤖 Managed by Coder Agents.

@coder-agents-review coder-agents-review Bot left a comment

Copy link
Copy Markdown
Contributor

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

This PR replaces the @> array join in GetTemplateParameterInsights with an unnest equality join on (workspace_build_id, name), adds TestGetTemplateParameterInsights, and fixes the replica aggregation in the Prometheus docs example. No P0, P1, or P2 findings; per your request, lower-severity items are not posted. The panel confirmed the result is unchanged: the unique constraints on workspace_builds(workspace_id, build_number) and template_version_parameters(template_version_id, name) keep build IDs distinct within each group, so unnest cannot double count.

Out of scope (needs a ticket or explicit acceptance by a human):

  • scripts/githooks/pre-push:108: echo "$changed" | grep -qE ... runs under set -o pipefail; with a large change list grep -q exits early, echo exits 141, and the hook prints "no Go/TS changes, skipping tests" for pushes that change Go files.
  • coderd/prometheusmetrics/insights/metricscollector.go:87: each replica's 5 minute window is relative to its own time.Now() and ticker start, so coderd_insights_* values differ between replicas for the same period and no cross-replica aggregation gives one exact figure.

🤖 This review was automatically generated with Coder Agents.

@dylanhuff-at-coder
dylanhuff-at-coder marked this pull request as ready for review November 5, 2026 17:33

@BobbyHo BobbyHo left a comment

Copy link
Copy Markdown
Collaborator

Choose a reason for hiding this comment

The reason will be displayed to describe this comment to others. Learn more.

lgtm, nice optimization!

Comment thread docs/admin/integrations/prometheus.md Resolved
@coderagents

coderagents Bot commented Nov 6, 2026 •

Copy link
Copy Markdown
Contributor

Documentation Check

Blocking

  • docs/admin/integrations/prometheus.md (open) - "Every coderd replica exports the same coderd_insights_* values" overstates the guarantee; each replica computes over its own unaligned rolling window, so values can differ at window boundaries. State that each replica exports the same metric series (or that they're computed per-replica over a rolling window) and keep max, not sum. This was addressed in 28d1bdd.
Findings for agents
- id: accuracy-1
  persona: accuracy
  class: wrong
  blocking: true
  file: docs/admin/integrations/prometheus.md
  line: 102
  quote: "Every `coderd` replica exports the same `coderd_insights_*` values, so aggregate across replicas with `max`, not `sum`."
  evidence:
    - cli/server.go:347
    - coderd/prometheusmetrics/insights/metricscollector.go:83-91
  rule: null
  problem: A reader is told replicas export identical values, but each replica computes over its own unaligned [now-5m, now] window, so values can differ across replicas at window boundaries.
  fix: "Each `coderd` replica exports the `coderd_insights_*` metrics over its own rolling window, so aggregate across replicas with `max`, not `sum`."
  verified: true

Automated review via Coder Agents. Reply to a finding's thread with the reason if the finding is wrong.

…lica windows

Each replica computes the coderd_insights_* series over its own rolling
5 minute window, so the values aren't guaranteed to match across
replicas. The guidance to aggregate with max stays the same.
dylanhuff-at-coder added a commit that referenced this pull request Nov 10, 2026
…31766)

Adds an index on `workspace_app_stats(session_ended_at)`. The table had
no time index and is never purged, so the insights collector's app usage
query scanned all of it every 5 minutes on every replica. On 3M
synthetic rows that query went from 124 ms to 19 ms.

**NOTE**: the index build holds a lock that blocks app stat writes until
the migration batch commits (about 1.4 to 3.4 s per 3M rows in testing),
and active session upserts stop being HOT updates.

This is separate from #31765 so that one can be backported without a
migration. dbrollup's `UpsertTemplateUsageStats` still needs its own
query change to use this index on Postgres 17+ (noted on
[CORE-776](https://linear.app/codercom/issue/CORE-776)). The migration
number may need a bump at merge time.

I skipped the pre-push hook for this push since `make pre-push` also
fails on clean `main` in my workspace. This branch only failed in tests
it doesn't touch.

<details><summary>Failing pre-push tests</summary>

Clean `main` (42640dc), two runs: 9 Go and 122 JS failures, then 6 Go
and 104 JS.

This branch, Go: `agent/agentscripts`
TestCloseWaitsForCanceledStartReports,
TestExecuteCanceledScriptReportsCanceled and
TestStartCronDuringCloseDoesNotBlockClose, plus `agent/agentssh`
TestNewServer_CloseActiveConnections, TestSSHShutdownReason and
Test_sessionStart_orphan. JS: timeouts across 19 files, mostly
`AgentsPageLayout`, `AgentCreatePage`, `ChatTopBar` and `WorkspacePage`.

</details>

Part of [CORE-439](https://linear.app/codercom/issue/CORE-439).
@dylanhuff-at-coder
dylanhuff-at-coder merged commit 3728e7a into main Nov 10, 2026
70 of 75 checks passed
@dylanhuff-at-coder
dylanhuff-at-coder deleted the dylan/core-439-template-insights-prometheus-scaling branch November 10, 2026 17:30
@dylanhuff-at-coder
dylanhuff-at-coder temporarily deployed to main November 10, 2026 17:30 — with GitHub Actions Inactive
@github-actions github-actions Bot locked and limited conversation to collaborators Nov 10, 2026
@dylanhuff-at-coder
dylanhuff-at-coder temporarily deployed to main November 10, 2026 17:31 — with GitHub Actions Inactive
@dylanhuff-at-coder
dylanhuff-at-coder temporarily deployed to main November 10, 2026 17:32 — with GitHub Actions Inactive
@dylanhuff-at-coder
dylanhuff-at-coder temporarily deployed to main November 10, 2026 17:32 — with GitHub Actions Inactive
@dylanhuff-at-coder
dylanhuff-at-coder temporarily deployed to main November 10, 2026 17:32 — with GitHub Actions Inactive
@dylanhuff-at-coder
dylanhuff-at-coder temporarily deployed to main November 10, 2026 17:32 — with GitHub Actions Inactive

This branch was previously deployed

1 inactive deployment
main — 28d1bdd0 Deployed Nov 10, 2026 by dylanhuff-at-coder via Backport to release/2.38 #6883
Sign up for free to subscribe to this conversation on GitHub. Already have an account? Sign in.

Labels

Projects

None yet

Development

Successfully merging this pull request may close these issues.

2 participants