Skip to content

perf(coderd/database): skip unchanged rows in the template usage rollup - #31630

Open
kylecarbs wants to merge 1 commit into
perf/db-template-app-insightsfrom
perf/db-template-usage-rollup
Open

kylecarbs wants to merge 1 commit into
perf/db-template-app-insightsfrom
perf/db-template-usage-rollup

Conversation

@kylecarbs

@kylecarbs kylecarbs commented Nov 2, 2026 •

Copy link
Copy Markdown
Member

UpsertTemplateUsageStats, the periodic rollup behind template insights, recomputes every bucket since the latest stored one on each run. ON CONFLICT DO UPDATE then locked and WAL-logged every row, including unchanged ones.

Computed rows are now compared with stored ones first, and only the differences are written. Session app rows use one full join against the stored rows to find inserts, updates, and deletes. The latency join matches the bucket key directly (same 30-minute bucket expression as the rest of the query) instead of going through a minute-bucket CTE. Session minutes are gated with a window function instead of a join, which removes a 244 MB sort spill.

Rollup run Before After
Nothing new to roll up 22.7 to 24.9 s, 126 MB WAL 13.7 s, no WAL
Changed data 26.2 s 22.5 s

Review focus: this query writes persistent insights data. A subtle error would corrupt stored history silently and would not self-correct.

  • Stored rows matched the old query exactly in four scenarios: steady state, changed data, empty tables, and empty tables followed by changed data.
  • It has not been validated on real data. Before merging, I recommend running old and new side by side on a copy of a real deployment (for example dogfood) and diffing template_usage_stats and template_usage_stats_session_apps.

Depends on #31629.

Generated by Coder Agents on behalf of @kylecarbs.

@linear-code

linear-code Bot commented Nov 9, 2026

Copy link
Copy Markdown

CORE-837

@kylecarbs
kylecarbs force-pushed the perf/db-template-usage-rollup branch from 419e7eb to 7640160 Compare November 9, 2026 21:28
UpsertTemplateUsageStats recomputes every bucket since the latest stored
one on each run, and ON CONFLICT DO UPDATE locked and WAL-logged every
unchanged row. Compare computed rows with stored ones first and write only
the differences. The latency join matches the bucket key directly, and
session minutes are gated with a window function, which removes a large
sort spill.

Generated by Coder Agents.
@kylecarbs
kylecarbs force-pushed the perf/db-template-usage-rollup branch from 7640160 to 0f04c86 Compare November 10, 2026 16:11
@EhabY

EhabY commented Nov 10, 2026

Copy link
Copy Markdown
Collaborator

Warning

Risk: on PG13, the first rollup into an empty table takes ~100 s instead of ~17 s.
The rewritten latencies CTE groups by stats columns after the join, so the planner estimates 1 latency row (actual: tens of thousands). The upsert then joins stats to latencies with a nested loop. PG13, stack seed plus 8k agents reporting every 30 s (the default CODER_AGENT_STATS_REFRESH_INTERVAL):

Rollup Before This PR Suggestion
Nothing new 4.7 s 4.3 s 4.2 s
Empty table 16.8 s 100 s 15.1 s
Empty, then changed 18.4 s 99 s 18.5 s

The stack's seed doesn't show this (about one report per 6 minutes per agent): there, PG13 takes 25–27 s for an empty table with every variant.

Suggestion: stats has one row per bucket, so latencies can be a semi-join grouped on the stat's own keys:

latencies AS (
	SELECT
		date_trunc('hour', was.created_at) + trunc(date_part('minute', was.created_at) / 30) * 30 * '1 minute'::interval AS start_time,
		was.template_id,
		was.user_id,
		PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY was.connection_median_latency_ms)::real AS median_latency_ms
	FROM
		workspace_agent_stats AS was
	WHERE
		was.created_at >= (SELECT t FROM latest_start)
		AND was.created_at < NOW()
		AND was.connection_median_latency_ms > 0
		AND EXISTS (
			SELECT 1 FROM stats
			WHERE stats.start_time = date_trunc('hour', was.created_at) + trunc(date_part('minute', was.created_at) / 30) * 30 * '1 minute'::interval
				AND stats.template_id = was.template_id
				AND stats.user_id = was.user_id
				AND date_trunc('minute', was.created_at) = ANY(stats.minute_buckets)
		)
	GROUP BY
		1, was.template_id, was.user_id
),

Stored rows match the PR's in every scenario on PG13 and PG17.

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.

2 participants