Skip to content

perf(coderd/database): look up template app insights from usage names - #31629

Open
kylecarbs wants to merge 2 commits into
perf/db-agent-stats-user-activityfrom
perf/db-template-app-insights
Open

kylecarbs wants to merge 2 commits into
perf/db-agent-stats-user-activityfrom
perf/db-template-app-insights

Conversation

@kylecarbs

@kylecarbs kylecarbs commented Nov 2, 2026 •

Copy link
Copy Markdown
Member

GetTemplateAppInsights joined all 3.4M workspace apps up through agents, resources, builds, and workspaces, then sorted them with a 270 MB disk spill. The final grouping sorted 138k rows, each carrying a 300-element template list (a 665 MB spill).

It now starts from the (template, app name) pairs present in the usage rows:

  • Port-shaped names (8080, 8443s) are dropped up front, using the same test as coderd/workspaceapps/request.go, which always routes them as ports. Other names that are no app's slug ([terminal]) are rejected with one probe on a new workspace_apps (slug, created_at) index.
  • Each remaining pair is checked with a short lookup that stops at the first match, searching live workspaces before deleted ones.
  • Display name and icon come from the newest matching app. With a template filter, that walk stops after the 1,000 newest apps and falls back to the filtered templates' apps, so a template that hasn't been rebuilt recently can't make it slow.
  • OFFSET 0 corrects a row estimate that pushed the plan into about 1 s of JIT. A jsonb_typeof(...) = 'object' guard avoids errors on malformed values; the rollup only writes objects.
Query (warm) Before After
7 days, all templates 14,273 ms 1,614 ms (743 ms with JIT off)
7 days, one large template 670 ms 180 ms

End to end, /insights/templates went from 26.2 to 1.7 s (with #31628 also applied).

Behavior change from main: an app whose slug looks like a port (for example 8080) no longer appears in app insights. The app proxy routes such names as ports, so the app can never be opened by its slug, and the usage main reported under it was port traffic. Without this, one such app made the query about 20x slower: every other template's port 8080 usage walked all of that template's builds. Thanks @EhabY for finding this.

7 days, all templates, with port 8080 usage everywhere main Before the fix With the fix
No app slugged 8080 13.2 to 23.1 s 1.37 s 1.28 s
One template has an app slugged 8080 11.9 s 25.6 s 1.28 s

TestTemplateAppInsightsIgnoresPortSlugs covers 8080, 8443s, 0080 (all ports) and 99999 (too large for a port, so an app).

Review focus: this is the most complex rewrite in the stack, and partly heuristic (the 1,000-app walk and fallback).

  • Apart from port-shaped slugs, row count and md5 matched main on the seed, and on 21 parameter sets with extra edge-case data. The edge cases cover:
    • templates whose workspaces are all deleted
    • a template missing an app, and templates with only old apps
    • tied newest apps, users on two templates in one bucket
    • unaligned buckets and NULL usage
  • Timing is sensitive to JIT. The plan's estimated cost crosses the JIT threshold depending on table statistics, so production timings may land on either side of about 0.7 s.

NOTE: This migration builds an index on workspace_apps (about 110 MB at 3.4M rows), which blocks writes to that table during the upgrade. The indexed columns never change.

Depends on #31628.

Generated by Coder Agents on behalf of @kylecarbs.

@linear-code

linear-code Bot commented Nov 9, 2026

Copy link
Copy Markdown

CORE-836

GetTemplateAppInsights joined every workspace app through agents,
resources, builds, and workspaces and sorted the result, spilling to
disk. Start from the (template, app name) pairs present in the usage
rows, reject names that are no app's slug with one probe on a new
(slug, created_at) index, and look up display names and icons from the
newest matching app.

Generated by Coder Agents.
@kylecarbs
kylecarbs force-pushed the perf/db-template-app-insights branch from f8c843e to 998111b Compare November 9, 2026 21:28
@EhabY

EhabY commented Nov 10, 2026

Copy link
Copy Markdown
Collaborator

Warning

Risk: one app with a numeric slug makes the query ~40× slower.
If one template has an app with slug 8080, the other templates' port-8080 usage takes the slow path. For each of them, the apps probe walks every build, then sequentially scans workspaces. On the stack's seed (PG17, 4 GB): ~20 s, against 8.3 s before and 0.5 s without that app.

Fix: reject port-shaped names in app_slugs, like [terminal]:

-- Same test as strconv.ParseUint(strings.TrimSuffix(name, "s"), 10, 16).
NOT CASE WHEN names.slug ~ '^[0-9]+s?$' THEN rtrim(names.slug, 's')::numeric <= 65535 ELSE FALSE END

workspaceapps/request.go always routes a port-shaped name as a port, so that usage is never app usage. With the fix, the case above runs at the normal speed.

What it sacrifices:

  • An app whose slug looks like a port (e.g. 8080) no longer appears in insights. That app can't be opened by its slug, so the usage shown for it was port traffic.
  • The SQL duplicates the port check in request.go, so the two must stay in sync.

Also: the plan is estimated at 477k, just under the 500k JIT threshold. About 10% growth in workspace_apps pushed it over, and the same query went from 0.5 to 1.0 s. Consider SET LOCAL jit = off for this query.

I would prefer if someone with more SQL knowledge looks at this since it's a bit hard for me to parse 🙏

Review on #31629 found that one app with a port-shaped slug such as
8080 sent every other template's port 8080 usage down the per-template
build walk, taking the query from about 1.3 s to about 25 s (main: 12 s).

Reject names that coderd/workspaceapps routes as ports before the slug
lookup. Such an app can't be opened by its slug, so its usage was port
traffic. This is the only output change from main: that port traffic is
no longer reported under the app.

Generated by Coder Agents.

@cstyan cstyan 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 with the fix from @EhabY 's review

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.

3 participants