Repository navigation
Conversation
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.
f8c843e to
998111b
Compare
|
Warning Risk: one app with a numeric slug makes the query ~40× slower. Fix: reject port-shaped names in -- 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
What it sacrifices:
Also: the plan is estimated at 477k, just under the 500k JIT threshold. About 10% growth in 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.
GetTemplateAppInsightsjoined 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:
8080,8443s) are dropped up front, using the same test ascoderd/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 newworkspace_apps (slug, created_at)index.OFFSET 0corrects a row estimate that pushed the plan into about 1 s of JIT. Ajsonb_typeof(...) = 'object'guard avoids errors on malformed values; the rollup only writes objects.End to end,
/insights/templateswent from 26.2 to 1.7 s (with #31628 also applied).Behavior change from
main: an app whose slug looks like a port (for example8080) 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 usagemainreported 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.main80808080TestTemplateAppInsightsIgnoresPortSlugscovers8080,8443s,0080(all ports) and99999(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).
mainon the seed, and on 21 parameter sets with extra edge-case data. The edge cases cover: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.