René's URL Explorer Experiment


Title: perf: store workspace agent session counts as JSONB by EhabY · Pull Request #28126 · coder/coder · GitHub

Open Graph Title: perf: store workspace agent session counts as JSONB by EhabY · Pull Request #28126 · coder/coder

X Title: perf: store workspace agent session counts as JSONB by EhabY · Pull Request #28126 · coder/coder

Description: Replaces the four session_count_* columns on workspace_agent_stats with a sparse session_counts JSONB object, so counting a new app needs no schema change. Only positive counts are stored, so an idle agent writes {} instead of four zeroes, and the template insights covering index drops four bigints from its INCLUDE list. The batcher normalizes and caps outside its lock, still ingests the deprecated fields from 2.10 agents, and marshals one object per row. Every reader and rollup moves to JSONB. Output column names and API shapes are unchanged. Resolves the session_count_sftp TODOs: a new session type is now a new key. Migration Converts the window DeleteOldWorkspaceAgentStats retains, MAX(template_usage_stats.start_time) minus one day. Anything older is already rolled up. When nothing has ever rolled up there is no watermark to fall back on, so every row converts instead of letting old activity roll up as zero minutes later. A backlog the rollup never consumed spanning more than 24 hours usually means the rollup is failing or blocked. The migration proceeds anyway and emits a warning naming the span, with a hint to check coderd logs and pg_locks after the upgrade: WARNING: migration 000590 found 30 days of workspace agent stats that template usage stats never rolled up Measured on 10M rows: a healthy rollup converts ~330k rows in 11s on PG13 in Docker (4.44s on PG17 with tmpfs, 6.13s on durable storage). A rollup 30 days behind, or a deployment that never rolled up, converts all 10M rows in about 3.5 minutes, roughly 21 seconds per million backlogged rows. Trade-offs The conversion is unbounded and holds ACCESS EXCLUSIVE, so agent stat writes block for its duration; DROP COLUMN and the index rebuild need the lock regardless. A deployment whose rollup has failed for months converts its whole backlog at ~21s per million rows. The alternative, aborting until the rollup is fixed, blocked the upgrade with no way to trigger a rollup on demand. jsonb parsing on a hot read path. UpsertTemplateUsageStats extracts keys per row, every five minutes over the retained window, where it compared four integers. Neither form was indexed; the cost buys schema-free new apps. Three queries change plan shape, not just their column expressions, so a regression is harder to attribute. Family grouping in #28337 needs one pass per row, and doing it here avoids rewriting the same blocks twice. One PR. The migration drops the columns, so splitting it leaves either old code on the new schema or new queries on a missing column. Rollback discards unknown keys. The down migration restores the four known fields only; nothing else has a column to land in. Depends on #28125. Phase 3 of #27410.

Open Graph Description: Replaces the four session_count_* columns on workspace_agent_stats with a sparse session_counts JSONB object, so counting a new app needs no schema change. Only positive counts are stored, so an i...

X Description: Replaces the four session_count_* columns on workspace_agent_stats with a sparse session_counts JSONB object, so counting a new app needs no schema change. Only positive counts are stored, so an i...

Opengraph URL: https://github.com/coder/coder/pull/28126

X: @github

direct link

Domain: github.com

react-profiling0
react-import-mapreact,react-dom,react-dom/client,react-dom/profiling,react-is,react/compiler-runtime,react/jsx-dev-runtime,react/jsx-runtime,scheduler@b8488ee2c322
route-pattern/:user_id/:repository/pull/:id/files(.:format)
route-controllerpull_requests
route-actionfiles
fetch-noncev2:e465b232-20ad-340a-cbfc-33fc68f56249
current-catalog-service-hashae870bc5e265a340912cde392f23dad3671a0a881730ffdadd82f2f57d81641b
request-id9A1C:1DCB8C:195BA95:2171B17:6B014167
html-safe-nonce34417b6953006d981177c0ba7d6e375c148764917b963b3bbbd9792fc4a9ccb1
visitor-payloadeyJyZWZlcnJlciI6IiIsInJlcXVlc3RfaWQiOiI5QTFDOjFEQ0I4QzoxOTVCQTk1OjIxNzFCMTc6NkIwMTQxNjciLCJ2aXNpdG9yX2lkIjoiNzExODk1MTU1ODEyMDgxNjk5OSIsInJlZ2lvbl9lZGdlIjoiaWFkIiwicmVnaW9uX3JlbmRlciI6ImlhZCJ9
visitor-hmacb0bacf4e39d16d8882c1343074b1e48da1654bedf096217e62c6eeabc8bd47f4
hovercard-subject-tagpull_request:4272485165
github-keyboard-shortcutsrepository,pull-request-list,pull-request-conversation,pull-request-files-changed,copilot
google-site-verificationApib7-x98H0j5cPqHWwSMm6dNU4GmODRoqxLiDzdx9I
octolytics-urlhttps://collector.github.com/github/collect
analytics-location///pull_requests/show/files
fb:app_id1401488693436528
apple-itunes-appapp-id=1477376905, app-argument=https://github.com/coder/coder/pull/28126/files
twitter:imagehttps://avatars.githubusercontent.com/u/12927950?s=400&v=4
twitter:cardsummary_large_image
og:imagehttps://avatars.githubusercontent.com/u/12927950?s=400&v=4
og:image:altReplaces the four session_count_* columns on workspace_agent_stats with a sparse session_counts JSONB object, so counting a new app needs no schema change. Only positive counts are stored, so an i...
og:site_nameGitHub
og:typeobject
hostnamegithub.com
expected-hostnamegithub.com
Nonecc5dfdd32f62ebd3223cab3af3aa2b268d3605218237326a48a99d1efd89495c
turbo-cache-controlno-preview
diff-viewunified
go-importgithub.com/coder/coder git https://github.com/coder/coder.git
octolytics-dimension-user_id95932066
octolytics-dimension-user_logincoder
octolytics-dimension-repository_id440752086
octolytics-dimension-repository_nwocoder/coder
octolytics-dimension-repository_publictrue
octolytics-dimension-repository_is_forkfalse
octolytics-dimension-repository_network_root_id440752086
octolytics-dimension-repository_network_root_nwocoder/coder
turbo-body-classeslogged-out env-production page-responsive full-width
disable-turbotrue
browser-stats-urlhttps://api.github.com/_private/browser/stats
browser-errors-urlhttps://api.github.com/_private/browser/errors
release8c77c9e8439031ad54c75a250694842892f693e1
ui-targetfull
theme-color#1e2327
color-schemelight dark

Links:

Skip to contenthttps://github.com/coder/coder/pull/28126/files#start-of-content
https://github.com/
Sign inhttps://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fcoder%2Fcoder%2Fpull%2F28126%2Ffiles
GitHubRepository hosting, code review, and collaborationhttps://github.com/platform
CopilotAI coding agent for tasks, projects, and automated workhttps://github.com/features/copilot
ActionsCI/CD and automationhttps://github.com/features/actions
Code QualityQuality analysis, fixes, and enforcementhttps://github.com/features/code-quality
CodespacesCloud development environmentshttps://github.com/features/codespaces
Code SecurityVulnerability detection and remediationhttps://github.com/security/advanced-security/code-security
Secret ProtectionSecret scanning and leak preventionhttps://github.com/security/advanced-security/secret-protection
All Featureshttps://github.com/features
Documentationhttps://docs.github.com/en
Traininghttps://learn.github.com/skills
Certificationshttps://learn.github.com/credentials
Build with Copilothttps://github.com/features/ai
Customize Copilothttps://docs.github.com/en/copilot/how-tos/copilot-on-github/customize-copilot/customize-copilot-overview
Integrationshttps://github.com/marketplace
Trending reposhttps://github.com/trending
GitHub Sponsorshttps://github.com/open-source/sponsors
Developer articleshttps://github.com/resources/articles
ChangelogYour source for the latest features, improvements, and fixes across GitHub products, all in one place.https://github.blog/changelog/
Enterpriseshttps://github.com/enterprise
Teamhttps://github.com/team
Startupshttps://github.com/enterprise/startups
Nonprofitshttps://github.com/solutions/industry/nonprofits
Healthcarehttps://github.com/solutions/industry/healthcare
Financial serviceshttps://github.com/solutions/industry/financial-services
Manufacturinghttps://github.com/solutions/industry/manufacturing
Governmenthttps://github.com/solutions/industry/government
See all industrieshttps://github.com/solutions/industry
Why GitHubhttps://github.com/why-github
Customer Storieshttps://github.com/customer-stories
Business insightshttps://github.com/solutions/executive-insights
Trust Centerhttps://github.com/trust-center
GitHub Advanced SecurityEnterprise-grade code securityhttps://github.com/security/advanced-security
Copilot for businessEnterprise-grade AI featureshttps://github.com/features/copilot/copilot-business
Premium SupportEnterprise-grade 24/7 supporthttps://github.com/enterprise/premium-support
Documentationhttps://docs.github.com/en
Resource centerhttps://github.com/resources
Bloghttps://github.blog/
Events & webinarshttps://github.com/resources/events
Community Forumhttps://github.com/orgs/community/discussions
Customer Storieshttps://github.com/customer-stories
Integrationshttps://github.com/marketplace
Customer supporthttps://help.github.com/
Traininghttps://learn.github.com/skills
GitHub Statushttps://www.githubstatus.com/
Abouthttps://github.com/about
Partnershttps://github.com/partners
Changeloghttps://github.blog/changelog/
GitHub UniverseThe event for developers, by developers — talks, workshops, and what’s next for GitHub.https://githubuniverse.com/
Pricinghttps://github.com/pricing
Sign inhttps://github.com/login?return_to=https%3A%2F%2Fgithub.com%2Fcoder%2Fcoder%2Fpull%2F28126%2Ffiles
Sign uphttps://github.com/signup?ref_cta=Sign+up&ref_loc=header+logged+out&ref_page=%2F%3Cuser-name%3E%2F%3Crepo-name%3E%2Fpull_requests%2Fshow%2Ffiles&source=header-repo&source_repo=coder%2Fcoder
Reloadhttps://github.com/coder/coder/pull/28126/files
Reloadhttps://github.com/coder/coder/pull/28126/files
Reloadhttps://github.com/coder/coder/pull/28126/files
coderhttps://github.com/coder
coderhttps://github.com/coder/coder
Codehttps://github.com/coder/coder
Issues320 (320)https://github.com/coder/coder/issues
Pull requests468 (468)https://github.com/coder/coder/pulls
Discussionshttps://github.com/coder/coder/discussions
Actionshttps://github.com/coder/coder/actions
Projectshttps://github.com/coder/coder/projects
Security and quality49 (49)https://github.com/coder/coder/security
Insightshttps://github.com/coder/coder/insights
Sign up for GitHub https://github.com/signup?return_to=%2Fcoder%2Fcoder%2Fissues%2Fnew%2Fchoose
terms of servicehttps://docs.github.com/terms
privacy statementhttps://docs.github.com/privacy
Sign inhttps://github.com/login?return_to=%2Fcoder%2Fcoder%2Fissues%2Fnew%2Fchoose
EhabYhttps://github.com/EhabY
mainhttps://github.com/coder/coder/tree/main
perf/workspace-agent-session-counts-jsonbhttps://github.com/coder/coder/tree/perf/workspace-agent-session-counts-jsonb
Conversation 9 https://github.com/coder/coder/pull/28126
Commits 1 https://github.com/coder/coder/pull/28126/commits
Checks 31 https://github.com/coder/coder/pull/28126/checks
Files changed https://github.com/coder/coder/pull/28126/files
Please reload this pagehttps://github.com/coder/coder/pull/28126/files
perf: store workspace agent session counts as JSONB https://github.com/coder/coder/pull/28126/files#top
Show all changes 1 commit https://github.com/coder/coder/pull/28126/files
c8d5de0 perf: store workspace agent session counts as JSONB EhabY Sep 1, 2026 https://github.com/coder/coder/pull/28126/commits/c8d5de0aa667f263feeb23fe9557fb395402ff9a
Clear filters https://github.com/coder/coder/pull/28126/files
Please reload this pagehttps://github.com/coder/coder/pull/28126/files
Please reload this pagehttps://github.com/coder/coder/pull/28126/files
dbgen.go https://github.com/coder/coder/pull/28126/files#diff-92f52d4b046f067db8464b8668167ea0c03e2f84d4e1d709e080641d494802de
dbpurge_test.go https://github.com/coder/coder/pull/28126/files#diff-0c52c3aacce78a57c18b09705a907c8955c1f3c6bf3bcb8980d204f5cc3d1d81
dbrollup_test.go https://github.com/coder/coder/pull/28126/files#diff-4a76799ff5e8746078945f6b3941bf232e610aeddf80ef8f64de7cbe51966a56
dump.sql https://github.com/coder/coder/pull/28126/files#diff-a29e74f67def8d2522ccc182c3f3202bb086399e8fb59309fa7d0abc6b39a8e9
000590_workspace_agent_session_counts.down.sql https://github.com/coder/coder/pull/28126/files#diff-d1b599758322189dcbfcfa34d42b8f6f1ca10cb6e2cc7fbe8c8cc227b28e6385
000590_workspace_agent_session_counts.up.sql https://github.com/coder/coder/pull/28126/files#diff-74dc0f5c4b45e7653d078a363b62ef9e05da21f75fbfdee2f9012d2f434944e1
migrate_test.go https://github.com/coder/coder/pull/28126/files#diff-cd36bbc9c3cbdb00fb02c742dc4ee5ff7362801d664cc8b2dc3a086fe1808a88
000590_workspace_agent_session_counts.up.sql https://github.com/coder/coder/pull/28126/files#diff-0e835793a94a969d580e00658bea4cc36aeac0fbd9a67c0e8de2d9f51c26c0ec
models.go https://github.com/coder/coder/pull/28126/files#diff-b9c30e0e1f057da4bab9c301730ed1ebc8a82e9b0b0fa6ffa29bbe24117b5f21
querier.go https://github.com/coder/coder/pull/28126/files#diff-a96e7cef920f37ba1f1032eba4b241d1a9a06bc2089062b4ed3b1aa73c6bcd48
querier_test.go https://github.com/coder/coder/pull/28126/files#diff-e13ad743f9f1f9497c5baef54782ac6ff18a256f13cc916024ea8f1f91297740
queries.sql.go https://github.com/coder/coder/pull/28126/files#diff-bc904829e61be3669efa87d9f79fe9137ea288eebef74d7ae9c87cfab9833c43
insights.sql https://github.com/coder/coder/pull/28126/files#diff-08027849cf1bf5c8110e3f35173fbc8b614c0d04a3f0b8ff10cd31166745f51a
workspaceagentstats.sql https://github.com/coder/coder/pull/28126/files#diff-bce6d2bfffe2a1c06d503ab8a4644443c910fc0ad27968c58187ac6631aa7240
metricscache_test.go https://github.com/coder/coder/pull/28126/files#diff-616736e98a327673fe18dae238c8fd0aea2f6de74a0a6211fa499fc74b207da8
batcher.go https://github.com/coder/coder/pull/28126/files#diff-52bdeefd6bcfee640700554e4709a8ed33cebf53f77d7758a9c9d4796caed47d
batcher_internal_test.go https://github.com/coder/coder/pull/28126/files#diff-25991d8c76015c1605e70b87a0273c4441d587232754345e3772a9146ab8b720
coderd/database/dbgen/dbgen.gohttps://github.com/coder/coder/pull/28126/files#diff-92f52d4b046f067db8464b8668167ea0c03e2f84d4e1d709e080641d494802de
View file https://github.com/coder/coder/blob/c8d5de0aa667f263feeb23fe9557fb395402ff9a/coderd/database/dbgen/dbgen.go
Open in desktop https://desktop.github.com
https://github.co/hiddenchars
https://github.com/coder/coder/pull/28126/{{ revealButtonHref }}
https://github.com/coder/coder/pull/28126/files#diff-92f52d4b046f067db8464b8668167ea0c03e2f84d4e1d709e080641d494802de
https://github.com/coder/coder/pull/28126/files#diff-92f52d4b046f067db8464b8668167ea0c03e2f84d4e1d709e080641d494802de
coderd/database/dbpurge/dbpurge_test.gohttps://github.com/coder/coder/pull/28126/files#diff-0c52c3aacce78a57c18b09705a907c8955c1f3c6bf3bcb8980d204f5cc3d1d81
View file https://github.com/coder/coder/blob/c8d5de0aa667f263feeb23fe9557fb395402ff9a/coderd/database/dbpurge/dbpurge_test.go
Open in desktop https://desktop.github.com
https://github.co/hiddenchars
https://github.com/coder/coder/pull/28126/{{ revealButtonHref }}
https://github.com/coder/coder/pull/28126/files#diff-0c52c3aacce78a57c18b09705a907c8955c1f3c6bf3bcb8980d204f5cc3d1d81
https://github.com/coder/coder/pull/28126/files#diff-0c52c3aacce78a57c18b09705a907c8955c1f3c6bf3bcb8980d204f5cc3d1d81
https://github.com/coder/coder/pull/28126/files#diff-0c52c3aacce78a57c18b09705a907c8955c1f3c6bf3bcb8980d204f5cc3d1d81
https://github.com/coder/coder/pull/28126/files#diff-0c52c3aacce78a57c18b09705a907c8955c1f3c6bf3bcb8980d204f5cc3d1d81
coderd/database/dbrollup/dbrollup_test.gohttps://github.com/coder/coder/pull/28126/files#diff-4a76799ff5e8746078945f6b3941bf232e610aeddf80ef8f64de7cbe51966a56
View file https://github.com/coder/coder/blob/c8d5de0aa667f263feeb23fe9557fb395402ff9a/coderd/database/dbrollup/dbrollup_test.go
Open in desktop https://desktop.github.com
https://github.co/hiddenchars
https://github.com/coder/coder/pull/28126/{{ revealButtonHref }}
https://github.com/coder/coder/pull/28126/files#diff-4a76799ff5e8746078945f6b3941bf232e610aeddf80ef8f64de7cbe51966a56
https://github.com/coder/coder/pull/28126/files#diff-4a76799ff5e8746078945f6b3941bf232e610aeddf80ef8f64de7cbe51966a56
https://github.com/coder/coder/pull/28126/files#diff-4a76799ff5e8746078945f6b3941bf232e610aeddf80ef8f64de7cbe51966a56
https://github.com/coder/coder/pull/28126/files#diff-4a76799ff5e8746078945f6b3941bf232e610aeddf80ef8f64de7cbe51966a56
https://github.com/coder/coder/pull/28126/files#diff-4a76799ff5e8746078945f6b3941bf232e610aeddf80ef8f64de7cbe51966a56
coderd/database/dump.sqlhttps://github.com/coder/coder/pull/28126/files#diff-a29e74f67def8d2522ccc182c3f3202bb086399e8fb59309fa7d0abc6b39a8e9
View file https://github.com/coder/coder/blob/c8d5de0aa667f263feeb23fe9557fb395402ff9a/coderd/database/dump.sql
Open in desktop https://desktop.github.com
how customized files appear on GitHubhttps://docs.github.com/github/administering-a-repository/customizing-how-changed-files-appear-on-github
Please reload this pagehttps://github.com/coder/coder/pull/28126/files
coderd/database/migrations/000590_workspace_agent_session_counts.down.sqlhttps://github.com/coder/coder/pull/28126/files#diff-d1b599758322189dcbfcfa34d42b8f6f1ca10cb6e2cc7fbe8c8cc227b28e6385
View file https://github.com/coder/coder/blob/c8d5de0aa667f263feeb23fe9557fb395402ff9a/coderd/database/migrations/000590_workspace_agent_session_counts.down.sql
Open in desktop https://desktop.github.com
https://github.co/hiddenchars
https://github.com/coder/coder/pull/28126/{{ revealButtonHref }}
coderd/database/migrations/000590_workspace_agent_session_counts.up.sqlhttps://github.com/coder/coder/pull/28126/files#diff-74dc0f5c4b45e7653d078a363b62ef9e05da21f75fbfdee2f9012d2f434944e1
View file https://github.com/coder/coder/blob/c8d5de0aa667f263feeb23fe9557fb395402ff9a/coderd/database/migrations/000590_workspace_agent_session_counts.up.sql
Open in desktop https://desktop.github.com
https://github.co/hiddenchars
https://github.com/coder/coder/pull/28126/{{ revealButtonHref }}
Please reload this pagehttps://github.com/coder/coder/pull/28126/files
Please reload this pagehttps://github.com/coder/coder/pull/28126/files
Please reload this pagehttps://github.com/coder/coder/pull/28126/files
https://github.com
Termshttps://docs.github.com/site-policy/github-terms/github-terms-of-service
Privacyhttps://docs.github.com/site-policy/privacy-policies/github-privacy-statement
Securityhttps://github.com/security
Statushttps://www.githubstatus.com/
Communityhttps://github.community/
Docshttps://docs.github.com/
Contacthttps://support.github.com?tags=dotcom-footer

Viewport: width=device-width


URLs of crawlers that visited me.