feat: store session usage per app and family in template usage stats - #29109
Merged
Merged
Conversation
This was referenced Sep 9, 2026
EhabY
force-pushed
the
feat/session-family-usage-rollup
branch
from
September 9, 2026 14:33
83aa8d3 to
cd9543f
Compare
EhabY
removed this pull request from stack #29110
September 9, 2026 14:34
EhabY
changed the base branch from
feat/session-count-family-attribution
to
feat/session-count-generic-queries
September 9, 2026 14:34
EhabY
added this pull request to stack #29135
September 9, 2026 14:35
EhabY
force-pushed
the
feat/session-family-usage-rollup
branch
from
September 10, 2026 11:27
cd9543f to
bdac30a
Compare
EhabY
force-pushed
the
feat/session-family-usage-rollup
branch
from
September 10, 2026 11:54
bdac30a to
d34d4b6
Compare
EhabY
force-pushed
the
feat/session-family-usage-rollup
branch
from
September 10, 2026 12:07
d34d4b6 to
32b28f7
Compare
EhabY
marked this pull request as ready for review
September 10, 2026 12:12
EhabY
force-pushed
the
feat/session-family-usage-rollup
branch
from
September 14, 2026 18:10
a16831d to
d17e5b7
Compare
code-asher
reviewed
Sep 15, 2026
EhabY
force-pushed
the
feat/session-family-usage-rollup
branch
3 times, most recently
from
September 16, 2026 12:42
44025d6 to
cea8c2f
Compare
code-asher
reviewed
Sep 17, 2026
code-asher
left a comment
Member
There was a problem hiding this comment.
this is mostly me trying to figure out the sql lol
maybe we wanna get one more pair of eyes with more sql experience than me 😆
EhabY
force-pushed
the
feat/session-family-usage-rollup
branch
from
September 17, 2026 08:55
eda10b7 to
bd6bc7c
Compare
EhabY
force-pushed
the
feat/session-family-usage-rollup
branch
from
September 17, 2026 14:16
6100fae to
532f05d
Compare
code-asher
approved these changes
Sep 18, 2026
code-asher
left a comment
Member
There was a problem hiding this comment.
Sweet! Thanks for bearing with me on the sql
Replace the fixed family-minute columns with per-app and per-family child tables, and skip the child writes for buckets whose session usage digest is unchanged.
Drop template_usage_stats_session_families and fold app names into families in Go, so the registry no longer reaches SQL and attribution applies to every bucket rather than only the ones the rollup revisits.
The child writes cover every bucket the rollup recomputed instead of only the ones the main upsert returned, so nothing needs the digest to notice a session-only change, and the conflict guards already leave an unchanged row alone. Also counts an app's minutes with COUNT(DISTINCT), folds an unregistered app name into ssh on downgrade, trims the insights queries, and corrects the NOTE about missing connection counts.
Keep the child table window sargable so a dashboard-sized range uses an index scan instead of scanning all history, and let COUNT(DISTINCT) dedupe minutes in the grouping rather than sorting the expanded rows, which spilled to disk past a few thousand agents. Also type session_app_usage_seconds via a sqlc column override, which drops the decode and its error path from the collector.
EhabY
force-pushed
the
feat/session-family-usage-rollup
branch
from
September 18, 2026 12:56
532f05d to
48e7514
Compare
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
Sign up for free
to subscribe to this conversation on GitHub.
Already have an account?
Sign in.
2 participants
Add this suggestion to a batch that can be applied as a single commit.This suggestion is invalid because no changes were made to the code.Suggestions cannot be applied while the pull request is closed.Suggestions cannot be applied while viewing a subset of changes.Only one suggestion per line can be applied in a batch.Add this suggestion to a batch that can be applied as a single commit.Applying suggestions on deleted lines is not supported.You must change the existing code in this line in order to create a valid suggestion.Outdated suggestions cannot be applied.This suggestion has been applied or marked resolved.Suggestions cannot be applied from pending reviews.Suggestions cannot be applied on multi-line comments.Suggestions cannot be applied while the pull request is queued to merge.Suggestion cannot be applied right now. Please check back later.
Store per-app session minutes in
template_usage_stats_session_apps, replacing the fixed family-minute columns. A new app no longer requires a rollup schema change, and the API, Prometheus, and telemetry outputs are unchanged.Reads group app names into families through the registry in
codersdk, so the registry never reaches SQL and attribution applies to all history rather than only the buckets the rollup still revisits.GetTemplateInsightsByTemplate, which feeds Prometheus, groups by app for the same reason, so both surfaces report a family identically. A bucket the rollup recomputes rewrites no child row whose minutes are unchanged, which the insert's conflict guard handles on its own.Semantic change: a family total is the sum of its apps, not the distinct minutes any of them was active, so a minute two apps of one family share counts in both. Reported numbers are unchanged today, because every agent reports one canonical app name per family. The difference appears once #28338 lands the producers and clients send names such as
cursororzed. The apps report already overlaps across rows, and the dashboard builds its percentage denominator from the sum of the rows, so a family row stays consistent with the per-app breakdown it will sit above.Migration
000596: backfill the family totals the fixed columns recorded, sftp included, under the family name as the app name, which the registry maps back to itself. Workspace web-app usage stays separate. The migration alterstemplate_usage_stats; the exclusive locks the ALTERs acquire last until the migration transaction commits. Downgrading folds app names back into the five fixed columns, counting a name its copy of the registry does not know as ssh, and discards the per-app detail. Already-merged migration000590is unchanged.The commits after the first are the response to review and read on their own; the first is the rest of the work squashed.
Follows merged #28337 and #29134; #28338 enables the producers. Covers the storage portion of CLIENT-642 (#27413). Dynamic external projections (#27411 and the remaining #27413 work) and the connection-log migration (#27412) remain separate.
Design decisions and performance context
work_mem=8MB.template_usage_stats: jsonb is cheaper to write and store but roughly 2.3x the read, and it widens the main row by 22% for every query that scans it.GetTemplateInsightssplits the half-hour cap into the buckets that can reach it and the rest, because the cap applies per user across templates. One grouping sets pass that caps every bucket gives identical output and takes 823 ms against 460 ms.COUNT(DISTINCT)rather than a minute bitmask, which costs 1.00 s against 0.73 s over 422k agent stats. That is the cold path, reached only whentemplate_usage_statsis empty; a steady-state run covers about two hours of stats, where the difference is around 15 ms.GetTemplateInsights429 ms with the family table against 444 ms folding in Go, 777 ms folding in SQL, and 1034 ms from a jsonb column; cold rollup 3386 ms against 2687 ms; storage 221 MB against 151 MB. These are fixture measurements, not production guarantees.Description updated by Coder Agents for @EhabY.
🤖 Generated with Claude Code