Repository navigation
perf: store workspace agent session counts as JSONB #28126
New issue
Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.
By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.
Already on GitHub? Sign in to your account
Merged
Merged
Changes from all commits
Commits
File filter
Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
There are no files selected for viewing
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
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
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
Some generated files are not rendered by default. Learn more about how customized files appear on GitHub.
Oops, something went wrong.
23 changes: 23 additions & 0 deletions
23
coderd/database/migrations/000590_workspace_agent_session_counts.down.sql
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
| Original file line number | Diff line number | Diff line change |
|---|---|---|
| @@ -0,0 +1,23 @@ | ||
| ALTER TABLE workspace_agent_stats | ||
| ADD COLUMN session_count_vscode bigint DEFAULT 0 NOT NULL, | ||
| ADD COLUMN session_count_jetbrains bigint DEFAULT 0 NOT NULL, | ||
| ADD COLUMN session_count_reconnecting_pty bigint DEFAULT 0 NOT NULL, | ||
| ADD COLUMN session_count_ssh bigint DEFAULT 0 NOT NULL; | ||
|
|
||
| -- Restore the four known session counts. Other keys are discarded. | ||
| UPDATE workspace_agent_stats | ||
| SET | ||
| session_count_vscode = COALESCE((session_counts ->> 'vscode')::bigint, 0), | ||
| session_count_jetbrains = COALESCE((session_counts ->> 'jetbrains')::bigint, 0), | ||
| session_count_reconnecting_pty = COALESCE((session_counts ->> 'reconnecting_pty')::bigint, 0), | ||
| session_count_ssh = COALESCE((session_counts ->> 'ssh')::bigint, 0) | ||
| WHERE session_counts <> '{}'::jsonb; | ||
|
|
||
| DROP INDEX workspace_agent_stats_template_id_created_at_user_id_idx; | ||
|
|
||
| ALTER TABLE workspace_agent_stats | ||
| DROP COLUMN session_counts; | ||
|
|
||
| CREATE INDEX workspace_agent_stats_template_id_created_at_user_id_idx ON workspace_agent_stats USING btree (template_id, created_at, user_id) INCLUDE (session_count_vscode, session_count_jetbrains, session_count_reconnecting_pty, session_count_ssh, connection_median_latency_ms) WHERE (connection_count > 0); | ||
|
|
||
| COMMENT ON INDEX workspace_agent_stats_template_id_created_at_user_id_idx IS 'Support index for template insights endpoint to build interval reports faster.'; |
78 changes: 78 additions & 0 deletions
78
coderd/database/migrations/000590_workspace_agent_session_counts.up.sql
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
| Original file line number | Diff line number | Diff line change |
|---|---|---|
| @@ -0,0 +1,78 @@ | ||
| LOCK TABLE workspace_agent_stats IN ACCESS EXCLUSIVE MODE; | ||
|
|
||
| DO $$ | ||
| DECLARE | ||
| latest_rollup_start timestamptz; | ||
| backlog_start timestamptz; | ||
| backlog_end timestamptz; | ||
| BEGIN | ||
| SELECT MAX(start_time) INTO latest_rollup_start FROM template_usage_stats; | ||
|
|
||
| -- Every row the rollup has not consumed yet has to be converted below, and | ||
| -- the table stays locked for all of them. Measure the rollup against the | ||
| -- data rather than against the clock: a deployment shut down over a weekend | ||
| -- records nothing while it is off, and an idle deployment leaves the | ||
| -- watermark behind because there is nothing to roll up. Neither has a | ||
| -- backlog, and neither takes noticeably longer to convert. | ||
| SELECT MIN(created_at), MAX(created_at) | ||
| INTO backlog_start, backlog_end | ||
| FROM workspace_agent_stats | ||
| WHERE (latest_rollup_start IS NULL OR created_at >= latest_rollup_start) | ||
| AND ( | ||
| session_count_vscode > 0 | ||
| OR session_count_jetbrains > 0 | ||
| OR session_count_reconnecting_pty > 0 | ||
| OR session_count_ssh > 0 | ||
| ); | ||
|
|
||
| IF backlog_end - backlog_start > interval '24 hours' THEN | ||
| RAISE WARNING 'migration 000590 found % of workspace agent stats that template usage stats never rolled up', justify_hours(backlog_end - backlog_start) | ||
| USING DETAIL = format( | ||
| 'This migration converts every workspace_agent_stats row the rollup has not consumed, %s through %s, and holds ACCESS EXCLUSIVE on the table until it finishes. Expect the migration to take longer than usual; do not interrupt it.', | ||
| backlog_start, backlog_end | ||
| ), | ||
| HINT = 'A backlog this wide usually means the rollup is failing or blocked: after the upgrade, check coderd logs for "failed to rollup data", and check pg_locks for a session holding the rollup advisory lock.'; | ||
| END IF; | ||
| END | ||
| $$; | ||
|
|
||
| ALTER TABLE workspace_agent_stats | ||
| ADD COLUMN session_counts jsonb DEFAULT '{}'::jsonb NOT NULL; | ||
|
|
||
| COMMENT ON COLUMN workspace_agent_stats.session_counts IS 'Positive session counts keyed by the canonical app name reported by the agent.'; | ||
|
|
||
| -- Convert the window DeleteOldWorkspaceAgentStats retains plus everything the | ||
| -- rollup has not consumed yet, which is what the rollups and operational | ||
| -- statistics still read. Anything older has already been rolled up. When | ||
| -- nothing has ever rolled up there is no watermark to fall back on, so convert | ||
| -- every row rather than roll old activity up as zero minutes later. | ||
| UPDATE workspace_agent_stats | ||
| SET session_counts = jsonb_strip_nulls(jsonb_build_object( | ||
| 'vscode', CASE WHEN session_count_vscode > 0 THEN session_count_vscode END, | ||
| 'jetbrains', CASE WHEN session_count_jetbrains > 0 THEN session_count_jetbrains END, | ||
| 'reconnecting_pty', CASE WHEN session_count_reconnecting_pty > 0 THEN session_count_reconnecting_pty END, | ||
| 'ssh', CASE WHEN session_count_ssh > 0 THEN session_count_ssh END | ||
| )) | ||
| WHERE created_at >= ( | ||
| SELECT COALESCE(MAX(start_time) - interval '1 day', '-infinity'::timestamptz) | ||
| FROM template_usage_stats | ||
| ) | ||
| AND ( | ||
| session_count_vscode > 0 | ||
| OR session_count_jetbrains > 0 | ||
| OR session_count_reconnecting_pty > 0 | ||
| OR session_count_ssh > 0 | ||
| ); | ||
|
|
||
| -- Recreate the index without the dropped columns in its INCLUDE list. | ||
| DROP INDEX workspace_agent_stats_template_id_created_at_user_id_idx; | ||
|
|
||
| ALTER TABLE workspace_agent_stats | ||
| DROP COLUMN session_count_vscode, | ||
| DROP COLUMN session_count_jetbrains, | ||
| DROP COLUMN session_count_reconnecting_pty, | ||
| DROP COLUMN session_count_ssh; | ||
|
|
||
| CREATE INDEX workspace_agent_stats_template_id_created_at_user_id_idx ON workspace_agent_stats USING btree (template_id, created_at, user_id) INCLUDE (connection_median_latency_ms) WHERE (connection_count > 0); | ||
|
|
||
| COMMENT ON INDEX workspace_agent_stats_template_id_created_at_user_id_idx IS 'Support index for template insights endpoint to build interval reports faster.'; | ||
Oops, something went wrong.
Oops, something went wrong.
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.
Uh oh!
There was an error while loading. Please reload this page.